How to optimize this query

Question

How to optimize this query

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

seftest_09 (6/22/2011)
Can you show me an example as how to store the result set of an inner query to a temp table and then use it. And why does it take less time?
Thanks,
Ratan

A full answer to your question is way more than a simple forum response and touches on 300+ level tuning stuff.

Short answer: too many things joined and/or too complex where clauses makes estimates used by optimizer sometimes be SPECTACULARLY inaccurate. And when you do nested loop joins or index seeks/bookmark lookups for a bajillion rows OR to a bunch of large table scans and hashes for just a few rows you are dead meat from a performance perspective. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

1Ankit1 Ten Centuries Points: 1253 More actions · Answer 2

You might as well better the query using a CTE. You can avoid the cost of creating and inserting into a temp table.

WITH CTE (QuestionID)

AS

(select distinct sf.questionID

from surveyFilled sf

inner join svCategoryQuestionRisk svr on sf.questionID = svr.questionID and sf.surveyInstanceID = 4153

inner join vw_categoryColsQuestionLORAll cc on svr.categoryID = cc.ID and svr.surveyVersionID = 5

inner join vw_surveyCategoryHierarchyFlat fl on cc.ID = fl.categoryID

and ( fl.fullPath like '%>67>%' or fl.fullPath like '%>67')

and cc.type = 'Category'

and cc.surveyInstanceID = 4153 and fl.surveyVersionID = cc.surveyVersionID)

select answered = sum(

CASE WHEN

sff.answerNumeric IS NULL AND (sff.answerText IS NULL OR rtrim(sff.answerText) = '')

THEN 0 ELSE 1 END)

FROM surveyFilled sff

INNER JOIN CTE tq ON sff.questionID = tq.questionID and sff.surveyInstanceID = 4153

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

ankit.shukla1105 (6/23/2011)
You might as well better the query using a CTE. You can avoid the cost of creating and inserting into a temp table.
WITH CTE (QuestionID)
AS
(select distinct sf.questionID
from surveyFilled sf
inner join svCategoryQuestionRisk svr on sf.questionID = svr.questionID and sf.surveyInstanceID = 4153
inner join vw_categoryColsQuestionLORAll cc on svr.categoryID = cc.ID and svr.surveyVersionID = 5
inner join vw_surveyCategoryHierarchyFlat fl on cc.ID = fl.categoryID
and ( fl.fullPath like '%>67>%' or fl.fullPath like '%>67')
and cc.type = 'Category'
and cc.surveyInstanceID = 4153 and fl.surveyVersionID = cc.surveyVersionID)
select answered = sum(
CASE WHEN
sff.answerNumeric IS NULL AND (sff.answerText IS NULL OR rtrim(sff.answerText) = '')
THEN 0 ELSE 1 END)
FROM surveyFilled sff
INNER JOIN CTE tq ON sff.questionID = tq.questionID and sff.surveyInstanceID = 4153

Be very careful with this approach. The temp table is a tried and true method and they have statistics, which help the optimizer choose indexes from the main table in the JOIN operation. I've seen CTE's really suck when compared to a temp table. Creating temp tables is NOT that expensive.

I've also seen CTE's increase performance. It depends.

Todd Fifield

LutzM SSC Guru Points: 107049 More actions · Answer 4

Since the CTE still contains the join to the views I wouldn't expect a major improvement.

As I stated before, one of the methods to improve the performance of such kind of joins (involving views) is to limit the number of rows returned by one or more of the objects involved before the join.

Depending on the data structure the CTE may return exactly the same execution plan.

As Todd summarized it: it depends.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

seftest_09 SSC Enthusiast Points: 147 More actions · Answer 5

Thanks you all, CTE does not seem to make much difference, but I did some work to optimize the views I have been using, and now it runs a lot faster.

One more question was, If I want to use the result of this query in another query and join it on surveyInstanceID and surveyVersionID, how do I do that since the temporary tables use them as fixed value but it wont be fixed anymore. Can I create these temp tables in a nested select of the outer query?

Thanks,

Ratan