Slow Query

[Guest]

This query is a little obnoxious so I'll be appreciative if somebody wants to
dive in and equally unsurprised if nobody does.

Basically, I have a series of queries built off of one another. The first
query gathers fields from about 5 tables... 4 of these are ODBC linked tables
from the back-end of our SQL system database. I have the ability to query
these tables but not alter them in any… at least not easily. All of the
subsequent queries gradually massage the data into the form I need to report
it in.

It actually all seems to work... the only problem is that it's very slow and
occasionally crashes. It takes about 1.5 minutes to run the report based on
the last query... I can live with this but I'm looking for some pointers on
how to improve the performance of the queries as the database grows etc.

I do have one specific question, in Query 3, every time I go into design
view, I get a message that Access can’t represent to of my joins in design
view. The joins it’s having issues with are: [qry - 0007b
NCVWeighting].sLoanNum = [tbl - TSG NCVs].LnNum and [qry - 0007b
NCVWeighting].sCoNum = [tbl - TSG NCVs].CoNum


So here we go:

Query 1: [qry - 0007 NCVWeighting]

SELECT dbo_ARSDetail.sCliNum, dbo_ARSDetail.sCoNum, dbo_ARSDetail.sLoanNum,
dbo_ARSDetail.sDebtorNum, dbo_ARSDetail.sInvoiceNum, dbo_ARSDetail.dtAge,
dbo_ARSDetail.dtAsOf, dbo_ARSDetail.dtInvoice, dbo_ARSDetail.sTransType,
dbo_ARSDetail.sFinClass, [tbl - TSG NCVs].NCV, IIf((IIf([sInelCode]="1" Or
[sInelCode]="2" Or [sInelCode]="3" Or [sInelCode]="4" Or
[sInelCode]="5","",[sInelCode])) & (IIf([dbo_ARSMstDebtor]![sInelType1]="1"
Or [dbo_ARSMstDebtor]![sInelType1]="2" Or [dbo_ARSMstDebtor]![sInelType1]="3"
Or [dbo_ARSMstDebtor]![sInelType1]="4" Or
[dbo_ARSMstDebtor]![sInelType1]="5","",[dbo_ARSMstDebtor]![sInelType1])) &
(IIf([dbo_ARSMstDebtor]![sInelType2]="1" Or
[dbo_ARSMstDebtor]![sInelType2]="2" Or [dbo_ARSMstDebtor]![sInelType2]="3" Or
[dbo_ARSMstDebtor]![sInelType2]="4" Or
[dbo_ARSMstDebtor]![sInelType2]="5","",[dbo_ARSMstDebtor]![sInelType2]))<>"","Yes")
AS OtherInel,
IIf([sTransType]="I",[dbo_ARSDetail].[cAmount],[dbo_ARSDetail].[cAmount]*-1)
AS NetBalance, dbo_ARSLoans.dCrossAging, dbo_ARSLoans.dConcentration,
dbo_ARSLoans.iDaysInPeriod, dbo_ARSLoans.iPeriodNumInel,
dbo_ARSDebtor.sMasterDebt, dbo_ARSLoans.sLoanName, dbo_ARSDetail.cAmount
FROM dbo_ARSLoans INNER JOIN ((([tbl - TSG NCVs] INNER JOIN dbo_ARSDetail ON
([tbl - TSG NCVs].LnNum = dbo_ARSDetail.sLoanNum) AND ([tbl - TSG NCVs].CoNum
= dbo_ARSDetail.sCoNum) AND ([tbl - TSG NCVs].FC = dbo_ARSDetail.sFinClass)
AND ([tbl - TSG NCVs].Client = dbo_ARSDetail.sCliNum)) INNER JOIN
dbo_ARSDebtor ON (dbo_ARSDetail.sDebtorNum = dbo_ARSDebtor.sDebtorNum) AND
(dbo_ARSDetail.sLoanNum = dbo_ARSDebtor.sLoanNum) AND (dbo_ARSDetail.sCoNum =
dbo_ARSDebtor.sCoNum) AND (dbo_ARSDetail.sCliNum = dbo_ARSDebtor.sCliNum))
LEFT JOIN dbo_ARSMstDebtor ON (dbo_ARSDebtor.sMasterDebt =
dbo_ARSMstDebtor.sMstDebtorNum) AND (dbo_ARSDebtor.sLoanNum =
dbo_ARSMstDebtor.sLoanNum) AND (dbo_ARSDebtor.sCoNum =
dbo_ARSMstDebtor.sCoNum) AND (dbo_ARSDebtor.sCliNum =
dbo_ARSMstDebtor.sCliNum)) ON (dbo_ARSLoans.sLoanNum =
dbo_ARSDetail.sLoanNum) AND (dbo_ARSLoans.sCoNum = dbo_ARSDetail.sCoNum) AND
(dbo_ARSLoans.sCliNum = dbo_ARSDetail.sCliNum)
WHERE (((dbo_ARSDetail.sCliNum)="thsc" Or (dbo_ARSDetail.sCliNum)="tsgi")
AND ((dbo_ARSDetail.cAmount)<>0))
ORDER BY IIf((IIf([sInelCode]="1" Or [sInelCode]="2" Or [sInelCode]="3" Or
[sInelCode]="4" Or [sInelCode]="5","",[sInelCode])) &
(IIf([dbo_ARSMstDebtor]![sInelType1]="1" Or
[dbo_ARSMstDebtor]![sInelType1]="2" Or [dbo_ARSMstDebtor]![sInelType1]="3" Or
[dbo_ARSMstDebtor]![sInelType1]="4" Or
[dbo_ARSMstDebtor]![sInelType1]="5","",[dbo_ARSMstDebtor]![sInelType1])) &
(IIf([dbo_ARSMstDebtor]![sInelType2]="1" Or
[dbo_ARSMstDebtor]![sInelType2]="2" Or [dbo_ARSMstDebtor]![sInelType2]="3" Or
[dbo_ARSMstDebtor]![sInelType2]="4" Or
[dbo_ARSMstDebtor]![sInelType2]="5","",[dbo_ARSMstDebtor]![sInelType2]))<>"","Yes") DESC , dbo_ARSDetail.cAmount;

Query 2: [qry - 0007a NCVWeighting]

SELECT [qry - 0007 NCVWeighting].sCliNum, [qry - 0007
NCVWeighting].OtherInel, [qry - 0007 NCVWeighting].sCoNum, [qry - 0007
NCVWeighting].sLoanNum, [qry - 0007 NCVWeighting].sFinClass, [qry - 0007
NCVWeighting].dtAsOf, [qry - 0007 NCVWeighting].dtAge, [qry - 0007
NCVWeighting].dtInvoice, IIf([OtherInel]="Yes",[netbalance],0) AS
OtherIEGroups, [qry - 0007 NCVWeighting].sDebtorNum, [qry - 0007
NCVWeighting].sTransType, [qry - 0007 NCVWeighting].NCV, [qry - 0007
NCVWeighting].NetBalance, [qry - 0007 NCVWeighting].dCrossAging, [qry - 0007
NCVWeighting].sMasterDebt,
IIf([otherInel]="yes",0,IIf([dtAsOf]-[dtInvoice]+1>[iDaysInPeriod]*[iPeriodNumInel]-[iDaysInPeriod],[NetBalance],0))
AS IneligibleAged,
IIf([otherInel]="yes",0,IIf([dtAsOf]-[dtInvoice]+1>[iDaysInPeriod]*[iPeriodNumInel]-[iDaysInPeriod]
And ([STRANSTYPE]="C" Or [STRANSTYPE]="P"),[NetBalance],0)) AS
IneligibleCredits, [qry - 0007 NCVWeighting].sLoanName
FROM [qry - 0007 NCVWeighting]
WHERE ((([qry - 0007 NCVWeighting].sCliNum)="thsc" Or ([qry - 0007
NCVWeighting].sCliNum)="tsgi"));

Query 3: [qry - 0007a1 NCVWeighting]

SELECT [qry - 0007a NCVWeighting].sCliNum, [qry - 0007a
NCVWeighting].sCoNum, [qry - 0007a NCVWeighting].sLoanNum, First([qry - 0007a
NCVWeighting].sFinClass) AS FirstOfsFinClass, Sum([qry - 0007a
NCVWeighting].IneligibleAged) AS SumOfIneligibleAged, Sum([qry - 0007a
NCVWeighting].NetBalance) AS SumOfNetBalance,
IIf([sumofIneligibleAged]/[sumofNetBalance]>=IIf([dCrossAging]=0,100,[dCrossAging]/100),[sumofNetBalance]-[SumOfIneligibleAged],0)
AS CrossAge, IIf(Len([sMasterDebt])<1,[sDebtorNum],[sMasterDebt]) AS
CrossGroup, [qry - 0007a NCVWeighting].dCrossAging
FROM [qry - 0007a NCVWeighting]
WHERE ((([qry - 0007a NCVWeighting].OtherIEGroups)=0))
GROUP BY [qry - 0007a NCVWeighting].sCliNum, [qry - 0007a
NCVWeighting].sCoNum, [qry - 0007a NCVWeighting].sLoanNum,
IIf(Len([sMasterDebt])<1,[sDebtorNum],[sMasterDebt]), [qry - 0007a
NCVWeighting].dCrossAging
HAVING (((Sum([qry - 0007a NCVWeighting].IneligibleAged))<>0) AND ((Sum([qry
- 0007a NCVWeighting].NetBalance))>0));

Query 4: [qry - 0007a2 NCVWeighting]

SELECT [qry - 0007a1 NCVWeighting].sCliNum, [qry - 0007a1
NCVWeighting].sCoNum, [qry - 0007a1 NCVWeighting].sLoanNum, [qry - 0007a1
NCVWeighting].FirstOfsFinClass, Sum([qry - 0007a1 NCVWeighting].CrossAge) AS
SumOfCrossAge
FROM [qry - 0007a1 NCVWeighting]
GROUP BY [qry - 0007a1 NCVWeighting].sCliNum, [qry - 0007a1
NCVWeighting].sCoNum, [qry - 0007a1 NCVWeighting].sLoanNum, [qry - 0007a1
NCVWeighting].FirstOfsFinClass;

Query 5: [qry - 0007b NCVWeighting]


SELECT [qry - 0007a NCVWeighting].sCliNum, [qry - 0007a
NCVWeighting].sCoNum, [qry - 0007a NCVWeighting].sLoanNum, [qry - 0007a
NCVWeighting].sFinClass, Sum([qry - 0007a NCVWeighting].NetBalance) AS
SumOfNetBalance, Sum([qry - 0007a NCVWeighting]!IneligibleAged-[qry - 0007a
NCVWeighting]!IneligibleCredits+[qry - 0007a
NCVWeighting]!OtherIEGroups)+IIf([SumOfCrossAge] Is Not
Null,[SumOfCrossAge],0) AS SumOfIneligible, Sum([qry - 0007a
NCVWeighting].IneligibleAged) AS SumOfIneligibleAged, Sum([qry - 0007a
NCVWeighting].IneligibleCredits) AS SumOfIneligibleCredits, Sum([qry - 0007a
NCVWeighting].OtherIEGroups) AS SumOfOtherIEGroups, [qry - 0007a2
NCVWeighting].SumOfCrossAge, [qry - 0007a NCVWeighting].sLoanName
FROM [qry - 0007a NCVWeighting] LEFT JOIN [qry - 0007a2 NCVWeighting] ON
([qry - 0007a NCVWeighting].sFinClass = [qry - 0007a2
NCVWeighting].FirstOfsFinClass) AND ([qry - 0007a NCVWeighting].sLoanNum =
[qry - 0007a2 NCVWeighting].sLoanNum) AND ([qry - 0007a NCVWeighting].sCoNum
= [qry - 0007a2 NCVWeighting].sCoNum) AND ([qry - 0007a NCVWeighting].sCliNum
= [qry - 0007a2 NCVWeighting].sCliNum)
GROUP BY [qry - 0007a NCVWeighting].sCliNum, [qry - 0007a
NCVWeighting].sCoNum, [qry - 0007a NCVWeighting].sLoanNum, [qry - 0007a
NCVWeighting].sFinClass, [qry - 0007a2 NCVWeighting].SumOfCrossAge, [qry -
0007a NCVWeighting].sLoanName;


Query 6: [qry - 0007c NCVWeighting]

SELECT [qry - 0007b NCVWeighting].sCliNum, [qry - 0007b
NCVWeighting].sCoNum, [qry - 0007b NCVWeighting].sLoanNum, [qry - 0007b
NCVWeighting].sFinClass, tbl_BHFFinClasses.FinClassDescription, [qry - 0007b
NCVWeighting].SumOfNetBalance, [qry - 0007b NCVWeighting].SumOfIneligible,
[qry - 0007b NCVWeighting]!SumOfNetBalance-[qry - 0007b
NCVWeighting]!SumOfIneligible AS Eligible, [tbl - TSG NCVs].NCV
FROM tbl_BHFFinClasses INNER JOIN ([tbl - TSG NCVs] INNER JOIN [qry - 0007b
NCVWeighting] ON ([qry - 0007b NCVWeighting].sLoanNum = [tbl - TSG
NCVs].LnNum) AND ([qry - 0007b NCVWeighting].sCoNum = [tbl - TSG NCVs].CoNum)
AND ([tbl - TSG NCVs].Client = [qry - 0007b NCVWeighting].sCliNum) AND ([tbl
- TSG NCVs].FC = [qry - 0007b NCVWeighting].sFinClass)) ON
tbl_BHFFinClasses.FinClassCode = [tbl - TSG NCVs].FC;

Query 6: [qry – 0007d NCVWeighting]


SELECT [qry - 0007c NCVWeighting].sCliNum, [qry - 0007c
NCVWeighting].sCoNum, [qry - 0007c NCVWeighting].sLoanNum,
dbo_ARSLoans.sLoanName, Sum([qry - 0007c NCVWeighting].SumOfNetBalance) AS
SumOfSumOfNetBalance, Sum([qry - 0007c NCVWeighting].SumOfIneligible) AS
SumOfSumOfIneligible, Sum(IIf([NCV]=0,0,[Eligible])) AS TotalEligible
FROM [qry - 0007c NCVWeighting] INNER JOIN dbo_ARSLoans ON ([qry - 0007c
NCVWeighting].sLoanNum = dbo_ARSLoans.sLoanNum) AND ([qry - 0007c
NCVWeighting].sCoNum = dbo_ARSLoans.sCoNum) AND ([qry - 0007c
NCVWeighting].sCliNum = dbo_ARSLoans.sCliNum)
GROUP BY [qry - 0007c NCVWeighting].sCliNum, [qry - 0007c
NCVWeighting].sCoNum, [qry - 0007c NCVWeighting].sLoanNum,
dbo_ARSLoans.sLoanName;

Query 7: [qry – 0007e NCVWeighting]

SELECT [qry - 0007c NCVWeighting].sCliNum, [qry - 0007c
NCVWeighting].sCoNum, [qry - 0007c NCVWeighting].sLoanNum, [qry - 0007d
NCVWeighting].sLoanName, [qry - 0007c NCVWeighting].sFinClass, [qry - 0007c
NCVWeighting].FinClassDescription, [qry - 0007c
NCVWeighting].SumOfNetBalance, [qry - 0007c NCVWeighting].SumOfIneligible,
[qry - 0007c NCVWeighting].Eligible, [qry - 0007c NCVWeighting].NCV,
([Eligible]/[TotalEligible])*[NCV] AS WeightedNCV
FROM [qry - 0007c NCVWeighting] LEFT JOIN [qry - 0007d NCVWeighting] ON
([qry - 0007c NCVWeighting].sCliNum = [qry - 0007d NCVWeighting].sCliNum) AND
([qry - 0007c NCVWeighting].sCoNum = [qry - 0007d NCVWeighting].sCoNum) AND
([qry - 0007c NCVWeighting].sLoanNum = [qry - 0007d NCVWeighting].sLoanNum);

 

[MGFoster]

This query is a little obnoxious so I'll be appreciative if somebody wants to
dive in and equally unsurprised if nobody does.

Basically, I have a series of queries built off of one another. The first
query gathers fields from about 5 tables... 4 of these are ODBC linked tables
from the back-end of our SQL system database. I have the ability to query
these tables but not alter them in any… at least not easily. All of the
subsequent queries gradually massage the data into the form I need to report
it in.

< SNIP >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you can try to use SQL pass-thru queries to get the data from the
ODBC back-end first. Then use those queries as data sources for your
local queries. That should speed up the final query.

Part of the obnoxious problem is the naming convention you are using -
very long names with spaces (ugh). For readability try using aliases
instead of full table names. E.g.:

SELECT C.sCliNum, C.sCoNum, C.sLoanNum, D.sLoanName, C.sFinClass,
C.FinClassDescription, C.SumOfNetBalance, C.SumOfIneligible, C.Eligible,

C.NCV, ([Eligible]/[TotalEligible])*[NCV] AS WeightedNCV

FROM [qry - 0007c NCVWeighting] As C LEFT JOIN [qry - 0007d
NCVWeighting] As D ON
(NCVW.sCliNum = D.sCliNum) AND (NCVW.sCoNum = D.sCoNum)
AND (NCVW.sLoanNum = D.sLoanNum)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtKlx4echKqOuFEgEQIsgQCglqp24f+abtomYzbm4/B8dr18lMUAmwSJ
e0vQf4TubYl8aM+xPMgV2DVX
=ZQ9Y
-----END PGP SIGNATURE-----

 

[Guest]

Ok, aliases are not a problem. I'm not familiar with Pass-through queries.
how do they work?

This query is a little obnoxious so I'll be appreciative if somebody wants to
dive in and equally unsurprised if nobody does.

Basically, I have a series of queries built off of one another. The first
query gathers fields from about 5 tables... 4 of these are ODBC linked tables
from the back-end of our SQL system database. I have the ability to query
these tables but not alter them in any… at least not easily. All of the
subsequent queries gradually massage the data into the form I need to report
it in.

< SNIP >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you can try to use SQL pass-thru queries to get the data from the
ODBC back-end first. Then use those queries as data sources for your
local queries. That should speed up the final query.

Part of the obnoxious problem is the naming convention you are using -
very long names with spaces (ugh). For readability try using aliases
instead of full table names. E.g.:

SELECT C.sCliNum, C.sCoNum, C.sLoanNum, D.sLoanName, C.sFinClass,
C.FinClassDescription, C.SumOfNetBalance, C.SumOfIneligible, C.Eligible,

C.NCV, ([Eligible]/[TotalEligible])*[NCV] AS WeightedNCV

FROM [qry - 0007c NCVWeighting] As C LEFT JOIN [qry - 0007d
NCVWeighting] As D ON
(NCVW.sCliNum = D.sCliNum) AND (NCVW.sCoNum = D.sCoNum)
AND (NCVW.sLoanNum = D.sLoanNum)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtKlx4echKqOuFEgEQIsgQCglqp24f+abtomYzbm4/B8dr18lMUAmwSJ
e0vQf4TubYl8aM+xPMgV2DVX
=ZQ9Y
-----END PGP SIGNATURE-----

 

[MGFoster]

Ok, aliases are not a problem. I'm not familiar with Pass-through queries.
how do they work?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Pass-thru queries are queries written in the syntax of the DB server.
It looks like you're using SQL Server so, you'd use T-SQL syntax &
language to create the query.

How to create a pass-thru query:

1. Open a new query in design mode.
2. On the menu bar click Query > SQL Specific > Pass-Through.
3. In the SQL view type in the T-SQL command that will read data ONLY
from the DB server. IOW, you can't use the names of tables in the
Access file, only the tables on the SQL Server.
4. On the menu bar click View > Properties - the Query Properties
dialog box will appear.
5. In the ODBC Connect Str property put the correct ODBC connection
string. E.g. (a DSN-less connection string [all one line]):

ODBC;Driver=SQL Server;Server=myServerName;Database=MyDatabaseName;
Trusted_Connection=Yes;

Trusted_connection means use Windows authentication.

Each connection string depends on the environment. Find out the name of
your server, or it's IP address, the database name and if you can use
Windows authentication to log on to the SQL Server. If not in place of
the Trusted_Connection parameter, use:

UID=LoginName;PWD=SQLServerPassword

Substitute your login for "LoginName" and that user's SQL Server
password for "SQLServerPassword."

6. Test the query.
7. Save the query.

For more info read the Access article on Pass-thru queries and the SQL
Server Books On Line (BOL) documentation on T-SQL.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtMZNIechKqOuFEgEQJglQCg7bN1qDBqLDc6TbL4jXkQDQVb/tsAnipK
R1rxkwaVo/o75GdzI0VOq7r/
=INGA
-----END PGP SIGNATURE-----