August 28, 2008 at 5:24 pm
The query I'm listing below is taking about 6 seconds to complete, I have tried taking the count() function and group by clause out but that just makes it worst since many more rows are returned. The question_responses_t has over 500K rows. I know that the delay is joining this table. I have one clustored primary key and two additional indexes.
I'm having trouble understanding what that the explain plan is telling me. I have attached the step in XML format.
Any help will be greatly appreciated.
Environment: SQL Server 2005
-AP
select
qr.q_id0
, q.text
, count(qr.value) as SumOnly
, count(qr.value) * qr.value * 20 as SumProduct
, qs.q_set_id0
, qs.parent_set_id0
from properties_t p
inner join prop_surv_responses_t psr on p.prop_id0 = psr.prop_id0 and p.proj_id0 = 2
inner join survey_responses_t sr on psr.survey_resp_id0 = sr.survey_resp_id0 and sr.state_id0 = 2
inner join question_responses_t qr on sr.survey_resp_id0 = qr.survey_resp_id0 and qr.value is not null and qr.value > 0
inner join questions_t q on qr.q_id0 = q.q_id0
inner join question_sets_t qs on q.q_set_id0 = qs.q_set_id0 and qs.parent_set_id0 = 20009
group by qr.q_id0, q.text, qr.value, qs.q_set_id0, qs.parent_set_id0
August 28, 2008 at 6:55 pm
This is a corrupted execution plan file. Please re-save it as a *.sqlplan file and attach it to a reply.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 7:24 pm
The complete execution plan is now attached to the post.
Thank you
-AP
August 28, 2008 at 7:47 pm
OK, there's nothing obvious in the execution plan. We'll need to see the table definitions including the keys and indexes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 8:19 pm
Actually, I probably only need the definition, keys and indexes for [question_responses_t].
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 9:09 pm
Attached you will find the table and index creation scripts for question_responses_t
Thank you for taking the time to look at this
-AP
August 28, 2008 at 10:27 pm
Include VALUE in the index _dta_index_QUESTION_RESPONSES_T_8_1557580587__K5_K4 as a covered column:
DROP INDEX [_dta_index_QUESTION_RESPONSES_T_8_1557580587__K5_K4] ON [dbo].[QUESTION_RESPONSES_T]
GO
CREATE NONCLUSTERED INDEX [_dta_index_QUESTION_RESPONSES_T_8_1557580587__K5_K4] ON [dbo].[QUESTION_RESPONSES_T]
([SURVEY_RESP_ID0] ASC,[Q_ID0] ASC)
INCLUDE (VALUE)
WITH (DROP_EXISTING=ON, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 6:30 am
Alex,
Are the tables in your FROM clause ordered correctly? One thing that I've noticed is that not a lot of folks are aware that you start listing the tables in the FROM clause with the ones that will return the least amount of data working your way up to the ones returning the most.
We had a query that was taking 85 seconds to execute and after changing ONLY the table order, it ran in 46 seconds.
September 5, 2008 at 6:41 am
mdean1962 (9/5/2008)
One thing that I've noticed is that not a lot of folks are aware that you start listing the tables in the FROM clause with the ones that will return the least amount of data working your way up to the ones returning the most.We had a query that was taking 85 seconds to execute and after changing ONLY the table order, it ran in 46 seconds.
This is at best an aberration and does not reflect normal SQL Server behavior. Normally, the order of tables in a series of Inner Joins makes NO difference to the optimizer.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 6:42 am
Alex, how did this work out for you? Can you give us an update?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 1:46 pm
- I updated the index as Barry suggested. Execution time was faster but it was still taking over 4 secs to complete
- I removed a few uncessary columns
- I broke down the query using a dynamic view. After speaking with the client we realized that only the first 10 records were representative for the feature. This made que query run in under 1 sec
Thanks for your suggestions. Sorry I hadden't provided feedback sooner.
-AP
[font="Courier New"]with question_response_data_v
as
(
select
qr.q_id0
, count(qr.value) as SumOnly
, count(qr.value) * qr.value * 20 as SumProduct
from properties_t p
inner join prop_surv_responses_t psr on p.prop_id0 = psr.prop_id0 and p.proj_id0 = 1
inner join survey_responses_t sr on psr.survey_resp_id0 = sr.survey_resp_id0 and sr.state_id0 = 2
inner join question_responses_t qr on sr.survey_resp_id0 = qr.survey_resp_id0 and qr.value is not null and qr.value > 0
inner join questions_t q on qr.q_id0 = q.q_id0
inner join question_sets_t qs on q.q_set_id0 = qs.q_set_id0 and qs.parent_set_id0 = 20000
group by qr.q_id0, qr.value
)
select top 10
qr.q_id0,
q.text,
qr.sumonly,
qr.sumproduct
from question_response_data_v qr
inner join questions_t q on qr.q_id0 = q.q_id0[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply