locked
Microsoft Access Pass-Through query to SQL running slow sometimes RRS feed

  • Question

  • I have a MS Access 2010 database linked to SQL Server via ODBC connection. I have several Pass-Through queries in Access that run just fine, but one in particular is running slow, sometimes. I'm not sure if it's a SQL and/or Access issue but this is what I've tried so far:

    In SQL server, if I exec the same stored procedure that is called in the Access pass-through query that sometimes runs slow, the speed is just fine. I have several backup copies of the SQL database so I tried to call the same stored procedure against those databases from both Access and SQL and the speed is just fine.  We tried to reboot the SQL server and that seemed to work for a day at least. There are times when the query runs just fine and other times slow. No rhyme or reason that I can find or think of.

    The stored procedure is below - within the stored procedure, there are calls to 2 functions. Removing the call to the 1st function or both functions does increase the speed of the stored procedure to normal. So that makes me think it's something with the 1st function. I've put the possible problematic function below the stored procedure. Any ideas? And why it works sometimes and not at other times for no apparent reason? I inherited this from a previous employee and have no one to bounce it off off.


    ALTER PROCEDURE [dbo].[TR_GUESS_WtCum]  (@EmpID as varchar(10)=null)
    
    AS
    
    set nocount on
    declare @EvalPer as varchar(4)
    
    SELECT     @EvalPer=CAST(AEVMessage AS varchar(4)) 
    FROM         AEVGlobalMessages
    WHERE     (AEVMessID = 'CURYR') 
    
    --Select with weighted average based on ContactExtent
    --uses tblRptCats to calc all categories and maintain order
    IF @EmpID is null
    
    SELECT  AR.EmpID, (E.LastName + ', ' + E.FirstName) as EmpName, E.ClassYear, AR.EvalPeriod,
    AR.EvalID, AR.EVALSys, 
    
    Category, CategoryText,
    dbo.CalcWeightedAvgPoints_4ALLEvals2(EvalID, CategoryText) as WtAvgScore, 
    dbo.CalcWeightedAvgPoints_4ALLEvals(EvalID, Null) as LineAvg2   
    
    
    FROM  (AnnualEvaluationRecord AR --INNER JOIN IndividualEvaluations IE ON AR.EvalID = IE.EvalID) 
    INNER JOIN Attorneys E ON (AR.EmpID=E.EmpID)), tblRptCats 
    -- (AR.EVALSys = 'AEV') AND
    WHERE (AR.EvalPeriod between '2005' and @EvalPer)
    ORDER BY 2,4,7 
    
    ELSE 
    
    SELECT  AR.EmpID, (E.LastName + ', ' + E.FirstName) as EmpName, E.ClassYear, AR.EvalPeriod,
    AR.EvalID, AR.EVALSys, 
    
    Category, CategoryText,
    dbo.CalcWeightedAvgPoints_4ALLEvals2(EvalID, CategoryText) as WtAvgScore, --problematic function??
    dbo.CalcWeightedAvgPoints_4ALLEvals(EvalID, Null) as LineAvg2    
    
    FROM  (AnnualEvaluationRecord AR --INNER JOIN IndividualEvaluations IE ON AR.EvalID = IE.EvalID) 
    INNER JOIN Attorneys E ON (AR.EmpID=E.EmpID)), tblRptCats 
    -- (AR.EVALSys = 'AEV') AND
    WHERE (AR.EvalPeriod between '2005' and @EvalPer) and (AR.EmpID=@EmpID)
    ORDER BY 2,4,7
    1st function:
    USE [AEVSQL2]
    GO
     
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    ALTER FUNCTION [dbo].[CalcWeightedAvgPoints_4ALLEvals2] (@EvalID as int=0, @CategoryText varchar(50)=null)  
    RETURNS numeric(18,5)
    AS  
    BEGIN 
    declare @ValOut as numeric(18,5)
    set @ValOut = 0
    
    if @CategoryText is not null
    
    SELECT @ValOut = sum(D1.SSWTAvg)/sum(D1.ContactExt) 
    FROM  
       (SELECT ie.EvalID, ie.ScoresetID, cast(ContactExt as int) as ContactExt,
        	avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
         	         WHEN [criteriavalue]<=0 THEN null 
         	         ELSE cast([CriteriaValue] as numeric(18,5)) END) as SSAvg,
        	ContactExt * avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
          						  WHEN [criteriavalue]<=0 THEN null 
          						  ELSE cast([CriteriaValue] as numeric(18,5)) END) as SSWTAvg
    			 
         FROM IndividualEvaluations IE INNER JOIN CriteriaIndiv CI ON (IE.ScoreSetID=CI.ScoreSetID)
         WHERE IE.EvalID=@EvalID AND CI.CategoryText=@CategoryText 
         GROUP BY ie.EvalID, ie.scoresetid, ContactExt 
         HAVING contactext>0 and 
              avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
                       WHEN [criteriavalue]<=0 THEN null 
                       ELSE cast([CriteriaValue] as numeric(18,5)) END) is not null 
         ) as D1
    
    return @ValOut
    END

    Tuesday, February 5, 2019 7:53 PM

Answers

  • That is because of the table tblrptcats which you make a cross join to. While both comma and CROSS JOIN can used to form a CROSS JOIN, the comma has higher operator precedence. That is only confusing, so replace the comma with CROSS JOIN. I would also recommend that you remove the parens around the first join. That is, you should have:

    FROM AnnualEvaluationRecord AR --INNER JOIN IndividualEvaluations IE ON AR.EvalID = IE.EvalID) 
    INNER JOIN Attorneys E ON (AR.EmpID=E.EmpID)
    CROSS JOIN tblrptcats
    outer apply CalcWeightedAvgPoints_4ALLEvals2Joe(ar.EvalID, CategoryText) as f
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Victor Gruber Monday, February 11, 2019 8:40 PM
    • Marked as answer by JMANCI Wednesday, February 13, 2019 6:14 PM
    Friday, February 8, 2019 10:11 PM
  • I may be missing something, but the function can be written as:

    CREATE FUNCTION [dbo].[CalcWeightedAvgPoints_4ALLEvals2] 
          (@EvalID as int=0, @CategoryText varchar(50)=null)  
    RETURNS TABLE AS  
    
    RETURN (
       SELECT sum(D1.SSWTAvg)/sum(D1.ContactExt) AS ValOut
       FROM  
          (SELECT ie.EvalID, ie.ScoresetID, cast(ContactExt as int) as ContactExt,
        	   avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
         	            WHEN [criteriavalue]<=0 THEN null 
         	            ELSE cast([CriteriaValue] as numeric(18,5)) END) as SSAvg,
        	   ContactExt * avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
          						     WHEN [criteriavalue]<=0 THEN null 
          						     ELSE cast([CriteriaValue] as numeric(18,5)) END) as SSWTAvg
    			 
            FROM IndividualEvaluations IE INNER JOIN CriteriaIndiv CI ON (IE.ScoreSetID=CI.ScoreSetID)
            WHERE IE.EvalID=@EvalID AND CI.CategoryText=@CategoryText 
            GROUP BY ie.EvalID, ie.scoresetid, ContactExt 
            HAVING contactext>0 and 
                 avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
                          WHEN [criteriavalue]<=0 THEN null 
                          ELSE cast([CriteriaValue] as numeric(18,5)) END) is not null 
            ) as D1
       WHERE @CategoryText IS NOT NULL
    )

    I made it an inline function to help the optimizer as much as possible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by JMANCI Wednesday, February 13, 2019 6:14 PM
    Monday, February 11, 2019 10:13 PM

All replies

  • User-defined functions with data access is a good recipe to make your code run slower, at least in released version of SQL Server. (There are some intriguing improvements in SQL 2019, currently in beta.)

    You could rewrite the function to be an inline table function. The you can call it as

    SELECT ..., func.result
    FROM    tbl
    OUTER APPLY YourFunc(EvalID, CategoryText) func


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, February 5, 2019 10:20 PM
  • You should migrate your data to SQL Server and not use Access. This is not a database server so it will always run slow.

    You are using Access for something its not really designed to do.

    Thanks,

    Victor


    Tuesday, February 5, 2019 10:56 PM
  • You should migrate your data to SQL Server and not use Access. This is not a database server so it will always run slow.

    You are using Access for something its not really designed to do.


    Not that I know Access or I am an advocate of Access, but the above appears to me as completely misguided. JMANCI runs a stored procedure in SQL Server from a client. That client happens to be Access, but the problem is not really any different than if had been a .NET client.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, February 6, 2019 8:30 AM
  • I'm looking at your suggestion, but as I'm not entirely familiar with inline table functions, it's taking me some time to get up to speed to on inline functions so that I can re-write this function.

    Interestingly, my query from Access/function was running at a normal speed yesterday morning and again today. Was looking at the table variable D1 that gets created in my existing function above to see if that was building up data, but apparently it gets cleared out once the call to the function ends.

    Thursday, February 7, 2019 7:29 PM
  • Erland - I was able to figure out how to re-write the function as an Inline table function. Now I'm having issues calling it from the original stored procedure so I can fully test it. See below.

    I called the new function CalcWeightedAvgPoints_4ALLEvals2Joe.  I need to pass it 2 variables, Evalid and CategoryText. Right now, I'm getting "The multi-part identifier "ar.EvalID" could not be bound.". I tried to move the inner join and tblrptcats around in the From statement, but then I get the error bound on CategoryText. Think I'm close. Any ideas?

    declare @EvalPer as varchar(4)
    
    SELECT     @EvalPer=CAST(AEVMessage AS varchar(4)) 
    FROM         AEVGlobalMessages
    WHERE     (AEVMessID = 'CURYR') 
    
    --Select with weighted average based on ContactExtent
    --uses tblRptCats to calc all categories and maintain order
    
    	SELECT  AR.EmpID, (E.LastName + ', ' + E.FirstName) as EmpName, E.ClassYear, AR.EvalPeriod,
    		AR.EvalID, AR.EVALSys, tblrptcats.Category, tblrptcats.CategoryText, f.ValOut,
    		--CalcWeightedAvgPoints_4ALLEvals2Joe(EvalID, CategoryText) as WtAvgScore 
    		dbo.CalcWeightedAvgPoints_4ALLEvals(EvalID, Null) as LineAvg2    
    
    	FROM (AnnualEvaluationRecord AR --INNER JOIN IndividualEvaluations IE ON AR.EvalID = IE.EvalID) 
    			INNER JOIN Attorneys E ON (AR.EmpID=E.EmpID)), tblrptcats
    			outer apply CalcWeightedAvgPoints_4ALLEvals2Joe(ar.EvalID, CategoryText) as f
    
    	-- (AR.EVALSys = 'AEV') AND
    	WHERE (AR.EvalPeriod between '2005' and @EvalPer) and (AR.EmpID='0553')
    	ORDER BY 2,4,7

    Friday, February 8, 2019 4:41 PM
  • That is because of the table tblrptcats which you make a cross join to. While both comma and CROSS JOIN can used to form a CROSS JOIN, the comma has higher operator precedence. That is only confusing, so replace the comma with CROSS JOIN. I would also recommend that you remove the parens around the first join. That is, you should have:

    FROM AnnualEvaluationRecord AR --INNER JOIN IndividualEvaluations IE ON AR.EvalID = IE.EvalID) 
    INNER JOIN Attorneys E ON (AR.EmpID=E.EmpID)
    CROSS JOIN tblrptcats
    outer apply CalcWeightedAvgPoints_4ALLEvals2Joe(ar.EvalID, CategoryText) as f
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Victor Gruber Monday, February 11, 2019 8:40 PM
    • Marked as answer by JMANCI Wednesday, February 13, 2019 6:14 PM
    Friday, February 8, 2019 10:11 PM
  • Erland - That works great, thanks. But in testing the function, I now I realize I missed summing up some values in the function. In the original function I originally pasted above, I had this to get out the @ValOut value:

    SELECT @ValOut = sum(D1.SSWTAvg)/sum(D1.ContactExt) 

    I'm not sure how or where to calculate this value in the new inline table function I created, which I have pasted below. I bolded/italicized the select statement where I am trying to get this calculation, but I'm getting an error that the alias column, SSWTAvg, is not defined. I've tried it a few different ways with no luck. I'll keep trying. Let me know if you have any ideas, but thinking this should be the last issue to get this resolved.

    ALTER FUNCTION [dbo].[CalcWeightedAvgPoints_4ALLEvals2Joe] (@EvalID as int=0, @CategoryText varchar(50)=null)  
    RETURNS @rows table(
    	EvalID int not null,
    	ScoresetID int not null,
    	ContactExt int not null,
    	SSAvg numeric(18,2),
    	SSWTavg numeric(18,2),
    	ValOut as sswtavg/ContactExt,
    	SummedValue numeric(18,2)
    )
    AS
    BEGIN
    	
    	insert into @rows(EvalID, ScoresetID, ContactExt, SSAvg, SSWTavg, SummedValue)
    
    
    SELECT ie.evalid, ie.ScoresetID, cast(ContactExt as int) as ContactExt,
        	avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
         	         WHEN [criteriavalue]<=0 THEN null 
         	         ELSE cast([CriteriaValue] as numeric(18,5)) END) as SSAvg,
        	ContactExt * avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
          						  WHEN [criteriavalue]<=0 THEN null 
          						  ELSE cast([CriteriaValue] as numeric(18,5)) END) as SSWTAvg,
    							  (select sum(SSWTAvg)/sum(contactext) as SummedValue
    							  )
    			 
         FROM IndividualEvaluations IE INNER JOIN CriteriaIndiv CI ON (IE.ScoreSetID=CI.ScoreSetID)
         WHERE IE.EvalID=@EvalID AND CI.CategoryText=@CategoryText
         GROUP BY ie.EvalID, ie.scoresetid, ContactExt 
         HAVING contactext>0 and 
              avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
                       WHEN [criteriavalue]<=0 THEN null 
                       ELSE cast([CriteriaValue] as numeric(18,5)) END) is not null
    		return 
    	end

    Monday, February 11, 2019 4:52 PM
  • I may be missing something, but the function can be written as:

    CREATE FUNCTION [dbo].[CalcWeightedAvgPoints_4ALLEvals2] 
          (@EvalID as int=0, @CategoryText varchar(50)=null)  
    RETURNS TABLE AS  
    
    RETURN (
       SELECT sum(D1.SSWTAvg)/sum(D1.ContactExt) AS ValOut
       FROM  
          (SELECT ie.EvalID, ie.ScoresetID, cast(ContactExt as int) as ContactExt,
        	   avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
         	            WHEN [criteriavalue]<=0 THEN null 
         	            ELSE cast([CriteriaValue] as numeric(18,5)) END) as SSAvg,
        	   ContactExt * avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
          						     WHEN [criteriavalue]<=0 THEN null 
          						     ELSE cast([CriteriaValue] as numeric(18,5)) END) as SSWTAvg
    			 
            FROM IndividualEvaluations IE INNER JOIN CriteriaIndiv CI ON (IE.ScoreSetID=CI.ScoreSetID)
            WHERE IE.EvalID=@EvalID AND CI.CategoryText=@CategoryText 
            GROUP BY ie.EvalID, ie.scoresetid, ContactExt 
            HAVING contactext>0 and 
                 avg(CASE WHEN  [criteriavalue] IS NULL THEN null 
                          WHEN [criteriavalue]<=0 THEN null 
                          ELSE cast([CriteriaValue] as numeric(18,5)) END) is not null 
            ) as D1
       WHERE @CategoryText IS NOT NULL
    )

    I made it an inline function to help the optimizer as much as possible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by JMANCI Wednesday, February 13, 2019 6:14 PM
    Monday, February 11, 2019 10:13 PM
  • Thanks Erland - Works great! Let me see if I or my users can get that access database to misbehave so I can see if this new function is the solution to the slowness or not.

    

    Wednesday, February 13, 2019 6:14 PM