Access 2010 linked to SQL Server running very slow

[danyd]

I created an Access database and am running it with SQL Server on the back-end. As long as the tables are linked to SQL Server, some of the reports can take several minutes to run. But as soon as I convert the tables to local tables, the reports are instentaneous.

I contacted our company's help desk to see if they could figure out the problem and here's their thought.



Well, it seems that somehow within the design of these reports, it is querying SQL 100’s of times each time a report is run. Last week I ran a trace on one of the reports, and we could see it running the same query over and over numerous times, each time passing in different parameters. So we believe that’s what’s causing the poor performance when running against SQL vs local tables. So not necessarily an issue within SQL Server or the database, but how Access is building and passing the queries off to SQL and then pulling the data back.

I’ve done some Googling and found reference to using SQL pass-through rather than linked tables to improve performance in Access when querying SQL. Do you know anything about that?

Does this problem sound familiar? Thank you.

[Dal Jeanis]

Please post the SQL behind one of those reports, and indicate which of the tables are local and which are in the SQL server backend. Perhaps we can clarify what's causing this behavior.

[June7]

Any of those reports using domain aggregate functions (DSum, DLookup, DCount, etc)?

[danyd]

I hope I understand your request correctly. I'm posting the SQL code that's in the query behind one of the reports that runs so slow. All of the tables are in SQL Server.

SELECT qry_IAProgressReport_Detail_All.ESTIMATE_ID, qry_IAProgressReport_Detail_All.Claim, dbo_STATE.STATE_ABBR, qry_IAProgressReport_Detail_All.PQS, qry_IAProgressReport_Detail_All.Firm, qry_IAProgressReport_Detail_All.CONTRACTOR, qry_IAProgressReport_Detail_All.DATE_ASSIGNED_TO_IA, qry_IAProgressReport_Detail_All.DATE_RECEIVED_BY_PQS, qry_IAProgressReport_Detail_All.DATE_APPROVED_BY_IA, qry_IAProgressReport_Detail_All.MonthYear, qry_IAProgressReport_Detail_All.EstmtAmount, qry_IAProgressReport_Detail_All.ApprvdAmount, qry_IAProgressReport_Detail_All.SUPPLEMENT, qry_IAProgressReport_Detail_All.VAR_SCOPE, qry_IAProgressReport_Detail_All.VAR_OTHER, qry_IAProgressReport_Detail_All.VAR_PROTOCOL, qry_IAProgressReport_Detail_All.VAR_COVERAGE, qry_IAProgressReport_Detail_All.VAR_PRICING, qry_IAProgressReport_Detail_All.VAR_MATERIAL, qry_IAProgressReport_Detail_All.VAR_METHOD, qry_IAProgressReport_Detail_All.NOTES
FROM qry_IAProgressReport_Detail_All, dbo_STATE
WHERE (((dbo_STATE.STATE_ABBR) Like [Enter state or the wildcard character(*) to select all]) AND ((Left(LTrim$([Claim]),2))=[dbo_STATE]![STATE_CODE]));

Thank you

[danyd]

Thank you for your response. I'm not using any domain aggregates. In fact I don't know/understand how to use them.

[orange]

Can you show the sql for this query?
qry_IAProgressReport_Detail_All

[danyd]

I'm afraid I don't know what you mean. How do I attach in mdb format?

[danyd]

Sorry... I should have also included the actual qry that's pulling from the tables that are stored in SQL Server. So, the first code is the query that's pulling from the tables. The second code is the query behind the report that pulls data from the first code/query.

SELECT dbo_IA_ESTIMATE.ESTIMATE_ID, dbo_IA_ESTIMATE.CLAIM_NUMBER AS Claim, dbo_CLAIM_CR_1.CR_NUMBER AS PQS, dbo_IA_FIRM.IA_FIRM_NAME, [dbo_IA_FIRM]![IA_FIRM_NAME] & " (" & [IA_FIRM_STATE_ABBR] & ")" AS Firm, dbo_IA_ESTIMATE.CONTRACTOR, dbo_IA_ESTIMATE.DATE_ASSIGNED_TO_IA, dbo_IA_ESTIMATE.DATE_RECEIVED_BY_PQS, dbo_IA_ESTIMATE.DATE_APPROVED_BY_IA, Format$([DATE_APPROVED_BY_IA],"mmm yyyy") AS MonthYear, dbo_IA_ESTIMATE.IA_ESTIMATE_AMT AS EstmtAmount, dbo_IA_ESTIMATE.APPROVED_IA_ESTIMATE_AMT AS ApprvdAmount, dbo_IA_ESTIMATE.SUPPLEMENT, dbo_IA_ESTIMATE.VAR_SCOPE, dbo_IA_ESTIMATE.VAR_OTHER, dbo_IA_ESTIMATE.VAR_PROTOCOL, dbo_IA_ESTIMATE.VAR_COVERAGE, dbo_IA_ESTIMATE.VAR_PRICING, dbo_IA_ESTIMATE.VAR_MATERIAL, dbo_IA_ESTIMATE.VAR_METHOD, dbo_IA_ESTIMATE.NOTES
FROM ((dbo_IA_ESTIMATE LEFT JOIN dbo_IA_FIRM ON dbo_IA_ESTIMATE.IA_FIRM_ID = dbo_IA_FIRM.IA_FIRM_ID) LEFT JOIN dbo_CLAIM_CR ON dbo_IA_ESTIMATE.CR_ID = dbo_CLAIM_CR.CR_ID) LEFT JOIN dbo_CLAIM_CR AS dbo_CLAIM_CR_1 ON dbo_IA_ESTIMATE.PQS_ID = dbo_CLAIM_CR_1.CR_ID
WHERE (((dbo_IA_FIRM.IA_FIRM_NAME)=[Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![FirmName]) AND ((dbo_IA_ESTIMATE.CONTRACTOR)=False) AND ((Format$([DATE_APPROVED_BY_IA],"mmm yyyy")) Between [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![StartMonth] And [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![EndMonth]));

SELECT qry_IAProgressReport_Detail_All.ESTIMATE_ID, qry_IAProgressReport_Detail_All.Claim, dbo_STATE.STATE_ABBR, qry_IAProgressReport_Detail_All.PQS, qry_IAProgressReport_Detail_All.Firm, qry_IAProgressReport_Detail_All.CONTRACTOR, qry_IAProgressReport_Detail_All.DATE_ASSIGNED_TO_IA, qry_IAProgressReport_Detail_All.DATE_RECEIVED_BY_PQS, qry_IAProgressReport_Detail_All.DATE_APPROVED_BY_IA, qry_IAProgressReport_Detail_All.MonthYear, qry_IAProgressReport_Detail_All.EstmtAmount, qry_IAProgressReport_Detail_All.ApprvdAmount, qry_IAProgressReport_Detail_All.SUPPLEMENT, qry_IAProgressReport_Detail_All.VAR_SCOPE, qry_IAProgressReport_Detail_All.VAR_OTHER, qry_IAProgressReport_Detail_All.VAR_PROTOCOL, qry_IAProgressReport_Detail_All.VAR_COVERAGE, qry_IAProgressReport_Detail_All.VAR_PRICING, qry_IAProgressReport_Detail_All.VAR_MATERIAL, qry_IAProgressReport_Detail_All.VAR_METHOD, qry_IAProgressReport_Detail_All.NOTES
FROM qry_IAProgressReport_Detail_All, dbo_STATE
WHERE (((dbo_STATE.STATE_ABBR) Like [Enter state or the wildcard character(*) to select all]) AND ((Left(LTrim$([Claim]),2))=[dbo_STATE]![STATE_CODE]));

Thank you.

[Dal Jeanis]

First, make sure that the field TS.State_ABBR on table dbo_STATE is indexed, since you are using "Like" against it. If not, then changing it to indexed should have a major positive effect. Also Index STATE_CODE. Try it and see what happens.

The Claim number is probably already indexed, but check that one too.

[Dal Jeanis]

I went through by hand and aliased your query. I noticed a couple places where the table name was not specified, which isn't a big deal as long as there's no ambiguity. However, I also noticed this bit -

 [dbo_IA_FIRM]![IA_FIRM_NAME] & " (" & [IA_FIRM_STATE_ABBR] & ")" AS Firm,

That's probably legal, but it's not in the same format as the rest, so I fixed it. Here's the resulting query SQL:

SELECT 
   T1.ESTIMATE_ID, 
   T1.CLAIM_NUMBER AS Claim, 
   TC1.CR_NUMBER AS PQS, 
   TF.IA_FIRM_NAME, 
   TF.IA_FIRM_NAME & " (" & TF.IA_FIRM_STATE_ABBR & ")" AS Firm, 
   T1.CONTRACTOR, 
   T1.DATE_ASSIGNED_TO_IA, 
   T1.DATE_RECEIVED_BY_PQS, 
   T1.DATE_APPROVED_BY_IA, 
   Format$(T1.DATE_APPROVED_BY_IA,"mmm yyyy") AS MonthYear, 
   T1.IA_ESTIMATE_AMT AS EstmtAmount, 
   T1.APPROVED_IA_ESTIMATE_AMT AS ApprvdAmount, 
   T1.SUPPLEMENT, 
   T1.VAR_SCOPE, 
   T1.VAR_OTHER, 
   T1.VAR_PROTOCOL, 
   T1.VAR_COVERAGE, 
   T1.VAR_PRICING, 
   T1.VAR_MATERIAL, 
   T1.VAR_METHOD, 
   T1.NOTES
FROM 
  (    (dbo_IA_ESTIMATE AS T1
       LEFT JOIN dbo_IA_FIRM AS TF
       ON T1.IA_FIRM_ID = TF.IA_FIRM_ID) 
     LEFT JOIN dbo_CLAIM_CR AS TC0
     ON T1.CR_ID = TC0.CR_ID) 
   LEFT JOIN dbo_CLAIM_CR AS TC1
   ON T1.PQS_ID = TC1.CR_ID
WHERE 
   (((TF.IA_FIRM_NAME)=[Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![FirmName]) 
      AND ((T1.CONTRACTOR)=False) 
      AND ((Format$(T1.DATE_APPROVED_BY_IA,"mmm yyyy")) 
          Between [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![StartMonth] 
              And [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![EndMonth]));

Once I got finished aliasing, I noticed that TC0, the first copy of dbo_Claim_CR, is not being used for anything in that query. So this one should achieve the same thing slightly more efficiently.

SELECT 
   T1.ESTIMATE_ID, 
   T1.CLAIM_NUMBER AS Claim, 
   TC1.CR_NUMBER AS PQS, 
   TF.IA_FIRM_NAME, 
   TF.IA_FIRM_NAME & " (" & TF.IA_FIRM_STATE_ABBR & ")" AS Firm, 
   T1.CONTRACTOR, 
   T1.DATE_ASSIGNED_TO_IA, 
   T1.DATE_RECEIVED_BY_PQS, 
   T1.DATE_APPROVED_BY_IA, 
   Format$(T1.DATE_APPROVED_BY_IA,"mmm yyyy") AS MonthYear, 
   T1.IA_ESTIMATE_AMT AS EstmtAmount, 
   T1.APPROVED_IA_ESTIMATE_AMT AS ApprvdAmount, 
   T1.SUPPLEMENT, 
   T1.VAR_SCOPE, 
   T1.VAR_OTHER, 
   T1.VAR_PROTOCOL, 
   T1.VAR_COVERAGE, 
   T1.VAR_PRICING, 
   T1.VAR_MATERIAL, 
   T1.VAR_METHOD, 
   T1.NOTES
FROM 
      (dbo_IA_ESTIMATE AS T1
       LEFT JOIN dbo_IA_FIRM AS TF
       ON T1.IA_FIRM_ID = TF.IA_FIRM_ID) 
     LEFT JOIN dbo_CLAIM_CR AS TC1
     ON T1.PQS_ID = TC1.CR_ID
WHERE 
   (((TF.IA_FIRM_NAME)=[Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![FirmName]) 
      AND ((T1.CONTRACTOR)=False) 
      AND ((Format$(T1.DATE_APPROVED_BY_IA,"mmm yyyy")) 
          Between [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![StartMonth] 
              And [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![EndMonth]));

[Dal Jeanis]

If adding indexes doesn't solve the problem, then you might try adding a field to the first query:

(Left(LTrim$(T1.CLAIM_NUMBER),2)) AS ClaimState,

and then adding an inner join in the second query:

SELECT 
   Q1.ESTIMATE_ID, 
   Q1.Claim, 
   TS.STATE_ABBR, 
   Q1.PQS, 
   Q1.Firm, 
   Q1.CONTRACTOR, 
   Q1.DATE_ASSIGNED_TO_IA, 
   Q1.DATE_RECEIVED_BY_PQS, 
   Q1.DATE_APPROVED_BY_IA, 
   Q1.MonthYear, 
   Q1.EstmtAmount, 
   Q1.ApprvdAmount, 
   Q1.SUPPLEMENT, 
   Q1.VAR_SCOPE, 
   Q1.VAR_OTHER, 
   Q1.VAR_PROTOCOL, 
   Q1.VAR_COVERAGE, 
   Q1.VAR_PRICING, 
   Q1.VAR_MATERIAL, 
   Q1.VAR_METHOD, 
   Q1.NOTES
FROM 
   qry_IAProgressReport_Detail_All AS Q1
   INNER JOIN dbo_STATE AS TS
   ON Q1.ClaimState = TS.STATE_CODE
WHERE 
   ((TS.STATE_ABBR) Like [Enter state or the wildcard character(*) to select all]);

NOTE: I notice from your second query that you are looking for the state abbreviation, and in your first query you already had one on the claim record. Perhaps this field (TF.IA_FIRM_STATE_ABBR) is the firm's state, rather than the claim's state?

[danyd]

Dal Jeanis... wow... thank you so much for your responses and for taking the time. I'm going to need a day or so to process and look at all the information you just gave me and will get back here as soon as I can. Thank you.

[Dal Jeanis]

No problem. I'm trying to master crosstabs. Getting there.