Geek Answers Handbook

Speeding up SQL Server stored procedure

I really need help speeding up the next query in SQL Server. Right now I have it as a view, but I don’t care if it is a view or function, I just need to return the recordset to the MS Access interface. The request runs for 11 seconds, but with all the cycles that it goes through, I need it up to 3 or less seconds. I tried to index the view that I have now, but unfortunately I cannot use the AVG function or use the HAVING clause if I am going to index anything.

SELECT tblFi.fldFN
    ,tblFc.fldFC
    ,tblFc.fldCity
    ,tblFc.fldState
    ,tblFi.fldIN
    ,tblAPL.fldPK
    ,tblAPL.fldCI
    ,AVG(tblAPD.fldND) AS fldND
FROM tblAPD
INNER JOIN tblAPL ON tblAPD.fldLK = tblAPL.fldLID
INNER JOIN tblUA ON tblAPD.fldUAK = tblUA.fldUAID
INNER JOIN tblUL ON tblUA.fldULK = tblUL.fldULID
INNER JOIN tblFi ON tblUL.fldFK = tblFi.fldFID
INNER JOIN tblFc ON tblFi.fldFAC = tblFc.fldFacID
GROUP BY tblFi.fldFN
    ,tblFc.fldFac
    ,tblFc.fldCity
    ,tblFc.fldState
    ,tblFi.fldIN
    ,tblAPL.fldPK
    ,tblAPL.fldCI
    ,tblAPL.fldPG
    ,tblAPD.fldIG
HAVING (tblAPL.fldCI <> 0) 
AND (AVG(tblAPD.fldND) IS NOT NULL) 
AND (tblAPL.fldPG = 1) 
AND (tblAPD.fldIG = 0)

Below are the tables (I only included pseudo-names for the columns referenced by the query, and then fld1, fld2, fld3 for those that are not related) ...

tblAPL - 4.5 million rows, clustered index on fldLID

fldLID... INT
fld1... FLOAT
fldCI... FLOAT
fldPK... BIGINT
fldPG... TINYINT
fld2... TINYINT
fld3... TINYINT
fld4... NVARCHAR(15)
fld5... DATETIME
fld6... TINYINT
fld7... TINYINT

tblAPD - 12 , fldDID

fldND... FLOAT
fldLK... INT
fldUAK... INT
fldIG... TINYINT
fld1... SMALLINT
fld2... SMALLINT
fld3... SMALLINT
fld4... NVARCHAR(20)
fld5... TINYINT
fldDID... INT

tblUA - 850 000 , fldUAID

fldULK... INT
fldUAID... INT
fld1... NVARCHAR(10)
fld2... INT
fld3... NVARCHAR(15)
fld4... INT
fld5... TINYINT
fld6... NVARCHAR(10)
fld7... INT
fld8... TINYINT
fld9...TINYINT
fld10...TINYINT

tblUL - 200 000 , fldULID

fldULID... INT
fldFK... INT
fld1... INT
fld2... INT
fld3... INT
fld4... NVARCHAR(15)
fld5... DATETIME
fld6... DATETIME
+4
3

SET NOCOUNT ON; .

0

HAVING WHERE? , WHERE, - ( AVG), ? , HAVING , .

0
SELECT tblFi.fldFN
    ,tblFc.fldFC
    ,tblFc.fldCity
    ,tblFc.fldState
    ,tblFi.fldIN
    ,tblAPL.fldPK
    ,tblAPL.fldCI
    ,AVG(tblAPD.fldND) AS fldND
FROM tblAPD (NOLOCK)
INNER JOIN tblAPL (NOLOCK) ON tblAPD.fldLK = tblAPL.fldLID
INNER JOIN tblUA (NOLOCK) ON tblAPD.fldUAK = tblUA.fldUAID
INNER JOIN tblUL (NOLOCK) ON tblUA.fldULK = tblUL.fldULID
INNER JOIN tblFi (NOLOCK) ON tblUL.fldFK = tblFi.fldFID
INNER JOIN tblFc (NOLOCK) ON tblFi.fldFAC = tblFc.fldFacID
GROUP BY tblFi.fldFN
    ,tblFc.fldFac
    ,tblFc.fldCity
    ,tblFc.fldState
    ,tblFi.fldIN
    ,tblAPL.fldPK
    ,tblAPL.fldCI
    ,tblAPL.fldPG
    ,tblAPD.fldIG
WHERE (tblAPL.fldCI <> 0) 
AND (tblAPL.fldCI = 1) 
AND (tblAPD.fldIG = 0)
HAVING (AVG(tblAPD.fldND) IS NOT NULL)

(fldCI) (fldIG) , .

0

More articles:


All Articles