June 22, 2011 at 4:21 pm
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
June 23, 2011 at 12:01 pm
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
June 23, 2011 at 12:38 pm
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
June 23, 2011 at 1:03 pm
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.
June 23, 2011 at 3:55 pm
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply