Speed difference between Sps
-
Hi there,
I've written 2 sp's one that selects top 100 id date order, the other usind 5 sub queries. The database contains approx 20,000 records.
The first sp without the subqueries takes approx 44 secs(!), however the other takes 20 secs.
Would the first sp be a lot quicker if it had an index on it?
Both queries actually return the same ampunt of records.
Below are the 2sp's
1st sp - 44 secs
CREATE PROCEDURE NWsp_GetNumberedIncidentsForGrid
@gLanguageCulture uniqueidentifier
AS
SELECT TOP 100 INCIDENT_DETAILS.INCIDENT_DETAILS, INCIDENT_DETAILS.EMPLOYEE, INCIDENT_DETAILS.INCIDENT_NO,
INCIDENT_DETAILS.EMPLOYEE_REF, INCIDENT_DETAILS.FORENAME, INCIDENT_DETAILS.SURNAME,
INCIDENT_DETAILS.RECORD_TYPE, INCIDENT_DETAILS.INCIDENT_DATE, INCIDENT_DETAILS.LOCAL_REFERENCE,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL1 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL1_DESCR,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL2 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL2_DESCR,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL3 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL3_DESCR,
(SELECT DESCR FROMLOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL4 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL4_DESCR,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL5 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL5_DESCR,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.SEVERITY AND LANGUAGE_CULTURE = @gLanguageCulture)) AS SEVERITY_DESCR, dbo.EMPLOYEE_CATEGORY.OTHER AS OTHER_EMPLOYEE_CATEGORY,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.EMPLOYEE_CATEGORY AND LANGUAGE_CULTURE = @gLanguageCulture)) AS EMPLOYEE_CATEGORY_DESCR,
INCIDENT_DETAILS.EMPLOYEE_CATEGORY_OTHER
FROM INCIDENT_DETAILS INNER JOIN EMPLOYEE_CATEGORY ON INCIDENT_DETAILS.EMPLOYEE_CATEGORY = EMPLOYEE_CATEGORY.EMPLOYEE_CATEGORY
ORDER BY INCIDENT_DETAILS.INCIDENT_DATE DESC
GO
2nd sp - 20 secs
CREATE PROCEDURE NWsp_GetNumberedPermittedIncidentsForGrid
@gUserID uniqueidentifier,
@gLanguageCulture uniqueidentifier
AS
SELECT TOP 100 INCIDENT_DETAILS.INCIDENT_DETAILS, INCIDENT_DETAILS.EMPLOYEE, INCIDENT_DETAILS.INCIDENT_NO,
INCIDENT_DETAILS.EMPLOYEE_REF, INCIDENT_DETAILS.FORENAME, INCIDENT_DETAILS.SURNAME,
INCIDENT_DETAILS.RECORD_TYPE, INCIDENT_DETAILS.INCIDENT_DATE, INCIDENT_DETAILS.LOCAL_REFERENCE,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL1 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL1_DESCR,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL2 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL2_DESCR,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL3 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL3_DESCR,
(SELECT DESCR FROMLOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL4 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL4_DESCR,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.LEVEL5 AND LANGUAGE_CULTURE = @gLanguageCulture))
AS LEVEL5_DESCR,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.SEVERITY AND LANGUAGE_CULTURE = @gLanguageCulture)) AS SEVERITY_DESCR, dbo.EMPLOYEE_CATEGORY.OTHER AS OTHER_EMPLOYEE_CATEGORY,
(SELECT DESCR FROM LOOKUP_DESCRIPTIONS WHERE (LOOKUP_CODE = INCIDENT_DETAILS.EMPLOYEE_CATEGORY AND LANGUAGE_CULTURE = @gLanguageCulture)) AS EMPLOYEE_CATEGORY_DESCR,
INCIDENT_DETAILS.EMPLOYEE_CATEGORY_OTHER
FROM INCIDENT_DETAILS INNER JOIN EMPLOYEE_CATEGORY ON INCIDENT_DETAILS.EMPLOYEE_CATEGORY = EMPLOYEE_CATEGORY.EMPLOYEE_CATEGORYWHERE ((INCIDENT_DETAILS.LEVEL1 IN (SELECT LEVEL1 FROM NWUSER_LEVEL1 WHERE NWUSER_LEVEL1.nwuser=@gUserID)) AND
(INCIDENT_DETAILS.LEVEL2 IN (SELECT LEVEL2 FROM NWUSER_LEVEL2 WHERE NWUSER_LEVEL2.nwuser=@gUserID)) AND
(INCIDENT_DETAILS.LEVEL3 IN (SELECT LEVEL3 FROM NWUSER_LEVEL3 WHERE NWUSER_LEVEL3.nwuser=@gUserID)) AND
(INCIDENT_DETAILS.LEVEL4 IN (SELECT LEVEL4 FROM NWUSER_LEVEL4 WHERE NWUSER_LEVEL4.nwuser=@gUserID)) AND
(INCIDENT_DETAILS.LEVEL5 IN (SELECT LEVEL5 FROM NWUSER_LEVEL5 WHERE NWUSER_LEVEL5.nwuser=@gUserID)))
ORDER BY INCIDENT_DETAILS.INCIDENT_DATE DESC
GO
Any help appreciated,
Cheers
Dave
-
David,
As there is no where clause in your main query you will have a full table scan.
What I would do is to create a clustered index on the INCIDENT_DETAILS table (INCIDENT_DATE, EMPLOYEE_CATEGORY)
Bye
Gabor -
Cheers for response, put an index on and both sp's now take only 10 secs to return results,
Thanks
Dave
-
Well, both variants use subquerys
In any case, it's not very easy to maintain this type of 'pivot' queries - they tend to be fairly ugly in Transact SQL (hence all the stacked subqueries)
I don't have the actual DDL, so I've just assumed that the language description lives in the lookuptable as well, and I also asumed that one or more of those might not exists - thus the numerous outer joins.
Just for readability's sake, I think this is also a way to write this... (I don't claim it to be "pretty" either, just more readable.)
SELECT TOP 100
d.incident_details,
d.employee,
d.incident_no,
d.employee_ref,
d.forename,
d.surname,
d.record_type,
d.incident_date,
d.local_reference,
c1.descr AS level1_descr,
c2.descr AS level2_descr,
c3.descr AS level3_descr,
c4.descr AS level4_descr,
c5.descr AS level5_descr,
c6.descr AS severity_descr,
e.other AS other_employee_category,
c7.descr AS employee_category_descr,
d.employee_category_other
FROM incident_details d
JOIN employee_category e
ON d.employee_category = e.employee_category
LEFT JOIN lookup_descriptions c1
ON d.level1 = c1.lookup_code
AND c1.language_culture = @glanguageculture
LEFT JOIN lookup_descriptions c2
ON d.level2 = c2.lookup_code
AND c2.language_culture = @glanguageculture
LEFT JOIN lookup_descriptions c3
ON d.level3 = c3.lookup_code
AND c3.language_culture = @glanguageculture
LEFT JOIN lookup_descriptions c4
ON d.level4 = c4.lookup_code
AND c4.language_culture = @glanguageculture
LEFT JOIN lookup_descriptions c5
ON d.level5 = c5.lookup_code
AND c5.language_culture = @glanguageculture
LEFT JOIN lookup_descriptions c6
ON d.severity = c6.lookup_code
AND c6.language_culture = @glanguageculture
LEFT JOIN lookup_descriptions c7
ON d.employee_category = c7.lookup_code
AND c7.language_culture = @glanguageculture
ORDER BY i.incident_date DESC
GO
A note on performance. Time from query -> results returned can be decieving - also check on the query plans and in particular the number of logical reads for the specific query. # of logical reads often have a strong correlation to how efficient one construct is vs another.
(no, there is no "right" or "wrong", just "different")
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply