Very slow query execution. What can be done? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Very slow query execution. What can be done?

Author

Topic

beady
Starting Member

28 Posts

Posted - 2007-02-23 : 00:48:51

I need to run the SP shown below. However, it takes just about 2 minutes for my 5 year old server to execute.

Apart from a slow machine, I suspect that the query itself might be the main culprit. What do the resident sqlteam experts opine about it?

idCOURT integer = Null,
@idJUDGE integer = Null,
@idADMIN_PROCEEDING_TYPE integer = Null,
@idADMIN_PROCEDURAL_ISSUES integer = Null,
@idADMIN_REVIEW_TYPE integer = Null,
@idREF_DEFINITION_TYPE integer = Null,
@idREF_PRELIM_ISSUES integer = Null,
@citation_date1 datetime = 'January 1, 1753',
@citation_date2 datetime = 'December 31, 9999'
AS
SET NOCOUNT ON

SELECT
*,
st.description AS legal_status,
Outcome.description AS outcome_description,
cmp.description As court_composition,
ADMIN_PROCEEDING_TYPE.description AS [Proceeding Type],
ADMIN_PROCEEDING_TYPE.idADMIN_PROCEEDING_TYPE AS ProcTypeID,
ADMIN_PROCEDURAL_ISSUES.description AS [Procedural Issues],
ADMIN_PROCEDURAL_ISSUES.idADMIN_PROCEDURAL_ISSUES AS IssuesID,
ADMIN_CE_REVIEW_GROUNDS.[section] AS [Rev Grnds Section],
ADMIN_REVIEW_DEFINITION.description AS [Review Defn],
ADMIN_REVIEW_TYPE.description AS [Review Descn],
REF_DEFINITION_TYPE.description AS [REF TYPE DESCN],
REF_DEFINITION.description AS [REF DEF DESCN],
REF_CE_DEF_MA.[section] AS [MA SECTION],

0 AS ForAgainst

FROM CASE_ENTITY ce
LEFT OUTER JOIN COURT co ON co.idCOURT = ce.idCOURT
LEFT OUTER JOIN FOR_VOTE ON ce.idCASE_ENTITY = FOR_VOTE.idCASE_ENTITY
LEFT OUTER JOIN JUDGE jdg ON FOR_VOTE.idJudge = jdg.idJUDGE
LEFT OUTER JOIN COMPOSITION cmp ON ce.idCOMPOSITION = cmp.idCOMPOSITION
LEFT OUTER JOIN LEGAL_STATUS st ON ce.idLEGAL_STATUS = st.idLEGAL_STATUS
LEFT OUTER JOIN OUTCOME ON ce.idOUTCOME = OUTCOME.idOUTCOME
LEFT OUTER JOIN ADMIN_CE_PROC_TYPE ON ce.idCASE_ENTITY = ADMIN_CE_PROC_TYPE.idCASE_ENTITY
LEFT OUTER JOIN ADMIN_PROCEEDING_TYPE ON ADMIN_CE_PROC_TYPE.idADMIN_PROCEEDING_TYPE = ADMIN_PROCEEDING_TYPE.idADMIN_PROCEEDING_TYPE
LEFT OUTER JOIN ADMIN_CE_ISSUES ON ce.idCASE_ENTITY = ADMIN_CE_ISSUES.idCASE_ENTITY
LEFT OUTER JOIN ADMIN_PROCEDURAL_ISSUES ON ADMIN_CE_ISSUES.idPROC_ISSUES = ADMIN_PROCEDURAL_ISSUES.idADMIN_PROCEDURAL_ISSUES
LEFT OUTER JOIN ADMIN_CE_REVIEW_GROUNDS ON ce.idCASE_ENTITY = ADMIN_CE_REVIEW_GROUNDS.idCASE_ENTITY
LEFT OUTER JOIN ADMIN_REVIEW_DEFINITION ON ADMIN_CE_REVIEW_GROUNDS.idADMIN_REVIEW_DEF = ADMIN_REVIEW_DEFINITION.idADMIN_REV_DEF
LEFT OUTER JOIN ADMIN_REVIEW_TYPE ON ADMIN_REVIEW_DEFINITION.idADMIN_REV_TYPE = ADMIN_REVIEW_TYPE.idADMIN_REVIEW_TYPE
LEFT OUTER JOIN REF_CE_DEF_MA ON ce.idCASE_ENTITY = REF_CE_DEF_MA.idCASE_ENTITY
LEFT OUTER JOIN REF_DEFINITION ON REF_CE_DEF_MA.idREF_DEF = REF_DEFINITION.idREF_DEFINITION
LEFT OUTER JOIN REF_DEFINITION_TYPE ON REF_DEFINITION.idRef_Def_Type = REF_DEFINITION_TYPE.idREF_DEFINITION_TYPE
LEFT OUTER JOIN REF_CE_ISSUES ON ce.idCASE_ENTITY = REF_CE_ISSUES.idCase_Entity
LEFT OUTER JOIN REF_PRELIM_ISSUES ON REF_CE_ISSUES.idRef_Issues = REF_PRELIM_ISSUES.idREF_PRELIM_ISSUES

WHERE
((ce.idCOURT = @idCOURT) OR(@idCOURT IS NULL) )
AND((jdg.idJUDGE =@idJUDGE) OR(@idJUDGE IS NULL))
AND citation_date BETWEEN @citation_date1 AND @citation_date2
AND((ADMIN_PROCEEDING_TYPE.idADMIN_PROCEEDING_TYPE = @idADMIN_PROCEEDING_TYPE)
OR (@idADMIN_PROCEEDING_TYPE IS NULL))
AND((ADMIN_PROCEDURAL_ISSUES.idADMIN_PROCEDURAL_ISSUES = @idADMIN_PROCEDURAL_ISSUES)
OR (@idADMIN_PROCEDURAL_ISSUES IS NULL))
AND(( ADMIN_REVIEW_TYPE.idADMIN_REVIEW_TYPE = @idADMIN_REVIEW_TYPE)
OR (@idADMIN_REVIEW_TYPE IS NULL))
AND((REF_DEFINITION_TYPE.idREF_DEFINITION_TYPE = @idREF_DEFINITION_TYPE)
OR (@idREF_DEFINITION_TYPE IS NULL))
AND((REF_PRELIM_ISSUES.idREF_PRELIM_ISSUES = @idREF_PRELIM_ISSUES)
OR (@idREF_PRELIM_ISSUES IS NULL))

UNION ALL
SELECT
*,
st.description AS legal_status,
Outcome.description AS outcome_description,
cmp.description As court_composition,
ADMIN_PROCEEDING_TYPE.description AS [Proceeding Type],
ADMIN_PROCEEDING_TYPE.idADMIN_PROCEEDING_TYPE AS ProcTypeID,
ADMIN_PROCEDURAL_ISSUES.description AS [Procedural Issues],
ADMIN_PROCEDURAL_ISSUES.idADMIN_PROCEDURAL_ISSUES AS IssuesID,
ADMIN_CE_REVIEW_GROUNDS.[section] AS [Rev Grnds Section],
ADMIN_REVIEW_DEFINITION.description AS [Review Defn],
ADMIN_REVIEW_TYPE.description AS [Review Descn],
REF_DEFINITION_TYPE.description AS [REF TYPE DESCN],
REF_DEFINITION.description AS [REF DEF DESCN],
REF_CE_DEF_MA.[section] AS [MA SECTION],

1 AS ForAgainst
FROM CASE_ENTITY ce
LEFT OUTER JOIN COURT co ON co.idCOURT = ce.idCOURT
LEFT OUTER JOIN AGAINST_VOTE ON ce.idCASE_ENTITY = AGAINST_VOTE.idCASE_ENTITY
LEFT OUTER JOIN JUDGE jdg ON AGAINST_VOTE.idJUDGE = jdg.idJUDGE
LEFT OUTER JOIN COMPOSITION cmp ON ce.idCOMPOSITION = cmp.idCOMPOSITION
LEFT OUTER JOIN LEGAL_STATUS st ON ce.idLEGAL_STATUS = st.idLEGAL_STATUS
LEFT OUTER JOIN OUTCOME ON ce.idOUTCOME = OUTCOME.idOUTCOME
LEFT OUTER JOIN ADMIN_CE_PROC_TYPE ON ce.idCASE_ENTITY = ADMIN_CE_PROC_TYPE.idCASE_ENTITY
LEFT OUTER JOIN ADMIN_PROCEEDING_TYPE ON ADMIN_CE_PROC_TYPE.idADMIN_PROCEEDING_TYPE = ADMIN_PROCEEDING_TYPE.idADMIN_PROCEEDING_TYPE
LEFT OUTER JOIN ADMIN_CE_ISSUES ON ce.idCASE_ENTITY = ADMIN_CE_ISSUES.idCASE_ENTITY
LEFT OUTER JOIN ADMIN_PROCEDURAL_ISSUES ON ADMIN_CE_ISSUES.idPROC_ISSUES = ADMIN_PROCEDURAL_ISSUES.idADMIN_PROCEDURAL_ISSUES
LEFT OUTER JOIN ADMIN_CE_REVIEW_GROUNDS ON ce.idCASE_ENTITY = ADMIN_CE_REVIEW_GROUNDS.idCASE_ENTITY
LEFT OUTER JOIN ADMIN_REVIEW_DEFINITION ON ADMIN_CE_REVIEW_GROUNDS.idADMIN_REVIEW_DEF = ADMIN_REVIEW_DEFINITION.idADMIN_REV_DEF
LEFT OUTER JOIN ADMIN_REVIEW_TYPE ON ADMIN_REVIEW_DEFINITION.idADMIN_REV_TYPE = ADMIN_REVIEW_TYPE.idADMIN_REVIEW_TYPE
LEFT OUTER JOIN REF_CE_DEF_MA ON ce.idCASE_ENTITY = REF_CE_DEF_MA.idCASE_ENTITY
LEFT OUTER JOIN REF_DEFINITION ON REF_CE_DEF_MA.idREF_DEF = REF_DEFINITION.idREF_DEFINITION
LEFT OUTER JOIN REF_DEFINITION_TYPE ON REF_DEFINITION.idRef_Def_Type = REF_DEFINITION_TYPE.idREF_DEFINITION_TYPE
LEFT OUTER JOIN REF_CE_ISSUES ON ce.idCASE_ENTITY = REF_CE_ISSUES.idCase_Entity
LEFT OUTER JOIN REF_PRELIM_ISSUES ON REF_CE_ISSUES.idRef_Issues = REF_PRELIM_ISSUES.idREF_PRELIM_ISSUES
WHERE
((ce.idCOURT = @idCOURT) OR(@idCOURT IS NULL) )
AND((jdg.idJUDGE =@idJUDGE) OR(@idJUDGE IS NULL))
AND citation_date BETWEEN @citation_date1 AND @citation_date2
AND((ADMIN_PROCEEDING_TYPE.idADMIN_PROCEEDING_TYPE = @idADMIN_PROCEEDING_TYPE)
OR (@idADMIN_PROCEEDING_TYPE IS NULL))
AND((ADMIN_PROCEDURAL_ISSUES.idADMIN_PROCEDURAL_ISSUES = @idADMIN_PROCEDURAL_ISSUES)
OR (@idADMIN_PROCEDURAL_ISSUES IS NULL))
AND(( ADMIN_REVIEW_TYPE.idADMIN_REVIEW_TYPE = @idADMIN_REVIEW_TYPE)
OR (@idADMIN_REVIEW_TYPE IS NULL))
AND((REF_DEFINITION_TYPE.idREF_DEFINITION_TYPE = @idREF_DEFINITION_TYPE)
OR (@idREF_DEFINITION_TYPE IS NULL))
AND((REF_PRELIM_ISSUES.idREF_PRELIM_ISSUES = @idREF_PRELIM_ISSUES)
OR (@idREF_PRELIM_ISSUES IS NULL))

ORDER BY ForAgainst,
ce.citation_date desc

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-23 : 02:18:28

Keep a record of the executions that are made then optimise the most frequent. This would mean having a path in the sp for those calls and this one at the end.
Another option is to build the query in dynamic sql so that you only include the filters that are necessary.

Both of these options should allow you to make better use of indexes by changing the query that is executed.

Can you also limit what is returned - You don't need the select * for instance as that is duplicating the join columns - you can probably get rid of a lot more data which would help the use of indexes.
On the same note maybe make some indexes covering - especially if you go the multiple query route.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

beady
Starting Member

28 Posts

Posted - 2007-02-23 : 03:02:17

Thanks, nr, I'll look into your suggestions.

Kristen
Test

22859 Posts

Posted - 2007-02-23 : 08:41:18

"What do the resident sqlteam experts opine about it?"

I've had a number of these over the years, and mostly I have done what Nigel has said.

It might be worth EXEC'ing an alternative SProc for each of the "special cases" - an SProc gets information cached based on its first execution, so if it contains a bunch of IF ... THEN ... ELSEIF ... type logic then the optimiser might not cache the best overall case - whereas each "Child SProc" will gets their own cached entries.

I tend to put some PK information into a Temporary Table and then refine that and THEN JOIN to all the things I need for the SELECT of the actual data-to-be-returned.

I choose which query (or Child SProc) to initially populate the TempTable with depending on which parameters I have.

If I have ZipCode then the chances are that the Zip code is very selective. So I would select into my TempTable, then DROP from the TempTable using a big WHERE NOT EXISTS on all the other possible criteria

If I have Name that's more likely to be less selective.

I would also evaluate the two UNION ALL statements separately into my TempTable (or even have two TempTables if they have are basically returning PKs onto different tables, so they can be evaluated and optimised separately)

One last point, unproven/untested but I've read it more than once:

AND((jdg.idJUDGE =@idJUDGE) OR(@idJUDGE IS NULL))

might be better ordered the other way round:

AND ((@idJUDGE IS NULL) OR (jdg.idJUDGE =@idJUDGE))

on the grounds that the whole statement can be ignored if the @Parameter is NULL, whereas the other way round maybe the

jdg.idJUDGE = NULL

scenario gets evaluated first, and then the

OR NULL IS NULL

Kristen

beady
Starting Member

28 Posts

Posted - 2007-02-25 : 19:28:12

Thank you, Kristen for your comments. I think you might be right about
AND ((@idJUDGE IS NULL) OR (jdg.idJUDGE =@idJUDGE))
being better than
AND((jdg.idJUDGE =@idJUDGE) OR(@idJUDGE IS NULL))
It might be a tiny saving in speed but anything adds up at the end of the run :)

Kristen
Test

22859 Posts

Posted - 2007-02-26 : 06:13:32

Yeah, well I'm sceptical about it always helping, but I doubt it makes it any worse, and if its saving you a few CPU cycles along the way then that's excellent!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources