June 11, 2008 at 10:05 pm
pino_daddy (6/11/2008)
Thank you very much.I appreciate the response. I wish I had a copy of Quest Software, it seesm to help out sometimes by rewriting the query and trying it out. But my temporary subscription has expired.
This is one query I would really like to see run in 30 minutes or less. I just ran out of ideas and hit a brick wall.
I format my code manually. For short queries, I do it in SSMS, but for longer queries, or those with multiple joins and such, I use my favorite text editor: UltraEdit. It makes formatting code manually fairly easy and it isn't very expensive.
Didn't have any time this evening, had things to do around the house but hopefully tomorrow will be easier.
😎
June 11, 2008 at 10:10 pm
Matt,
I want to thank you for taking the time to format the test data pino_daddy provided. I just didn't have the time even though using UltraEdit would have made the task quite easy. Hopefully we will get a chance to work on his query shortly and see if its performance can be enhanced.
😎
June 12, 2008 at 5:40 am
WOW, came in this morning and I cannot believe how many people are just willing to help. In the past I never asked for help and just tried to figure it out. But this is beyond me.
I included the Execution Plan in a word document as a picture.
Not sure hwo to save the Execution Plan as an XML document. I will look more into this.
Again, I want to thank each and every one of you for your assistance.
June 12, 2008 at 8:36 am
Pino -
I didn't describe the saving of the exec plan very well. In SSMS, when you're viewing the actual exec plan, right-click on the exc plan pane (not the tab, but within the actual diagram), and you will get an option to Save Execution plan as...
the default extension is .sqlplan (which is an XML format). If you could post that (it has a lot of useful info hidden in the hover over stuff).
From the images you posted - looks to me that you have table spools taking up most of the plan, which may mean your indexing scheme isn't helping. Again - if you can get your index definitions posted - that would help a lot.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 12, 2008 at 3:04 pm
I saved the Plan with the extension like you said, unfortunately, that extension is not one supported by tSQL ServerCentral and it will not attach it. I tried it twice. It just tells me it is not a supported extension. My next idea is to rename the file and give it an extension of txt or doc or something and see if it will load it.
As for the definitions of the indexes, I will work on that next.
Thank you again
June 12, 2008 at 3:55 pm
You can also zip the plan to a .zip file and upload that to SSC.
😎
June 13, 2008 at 12:19 pm
ZIP file contains:
All scripts needed to create thetables and indexes
Copy of the Execution Plan
Copy of the results of the query
Copy of the Client Statistics
June 13, 2008 at 12:56 pm
Forgot to add teh zipped file, sorry all
June 16, 2008 at 8:26 am
Sorry for the delay in answering, I was out sick for a few days.
Here's what I meant by adding that one table back in to the query.
SELECT CHF.CONTACT_WID ,
COUNT(DISTINCT CHF.SOURCE_WID) ,
COUNT(DISTINCT POH.OFFER_WID)
FROM W_CAMP_HIST_F CHF
LEFT OUTER JOIN W_SRC_OFFR_H POH
ON CHF.SOURCE_WID = POH.SOURCE_WID
W_PARAM_G PARAM
ON CHF.ETL_PROC_WID = PARAM.ETL_PROC_WID
OR(POH.ETL_PROC_WID = PARAM.ETL_PROC_WID
and POH.ETL_PROC_WID is not null)
WHERE CHF.CONTACT_WID > 0
AND CHF.SOURCE_WID> 0
and(POH.OFFER_WID != 0
or POH.OFFER_WID is null)
GROUP BY CHF.CONTACT_WID
Does that get you the results you want?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2008 at 8:59 am
Thank you for your response, I hope you are feeling better.
I get a syntax error:
Incorrect syntax near 'W_PARAM_G'
June 16, 2008 at 9:28 am
Gus' statement is missing a JOIN clause right before that table name. Not sure which one he had in mind, but it kind of looks like a CROSS JOIN scenario.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 11:22 am
Yes Gus, this does return teh 1.8 million rows that is expected. Unfortunately it takes 21 minutes longer than the original.
A sample of the output is provided below:
5184773046
1880734317
192276711
5582125457
96377744
1794433418
11038294572
10483231119
17512101020
1751216382
Again, this is just a small sample of the 1.8 million rows that are returned.
I have run this query through the "Analyze Query in Database Engine Tuning Advisor" at least 7 times and each time it comes back with ZERO recommendations.
I added the join statement as follows to get rid of the syntax error:
SELECT CHF.CONTACT_WID ,
COUNT(DISTINCT CHF.SOURCE_WID) ,
COUNT(DISTINCT POH.OFFER_WID)
FROM W_CAMP_HIST_F CHF
LEFT OUTER JOIN W_SRC_OFFR_H POH
ON CHF.SOURCE_WID = POH.SOURCE_WID
LEFT OUTER JOIN W_PARAM_G PARAM
ON CHF.ETL_PROC_WID = PARAM.ETL_PROC_WID
OR (POH.ETL_PROC_WID = PARAM.ETL_PROC_WID
and POH.ETL_PROC_WID is not null)
WHERE CHF.CONTACT_WID > 0
AND CHF.SOURCE_WID> 0
and (POH.OFFER_WID != 0
or POH.OFFER_WID is null)
GROUP BY CHF.CONTACT_WID
June 16, 2008 at 11:24 am
The original query that runs in 51 minutes is:
SELECT
CHF.CONTACT_WID , COUNT(DISTINCT CHF.SOURCE_WID) , COUNT(DISTINCT CASE WHEN POH.OFFER_WID IS NULL THEN 0 ELSE POH.OFFER_WID END) - SUM (CASE WHEN (POH.OFFER_WID = 0 OR POH.OFFER_WID IS NULL) THEN 1 ELSE 0 END)
S
FROM
W_CAMP_HIST_F CHF LEFT OUTER JOIN W_SRC_OFFR_H POH ON CHF.SOURCE_WID = POH.SOURCE_WID ,
W_PARAM_G PARAM
WHERE
CHF.CONTACT_WID > 0 AND CHF.SOURCE_WID> 0 AND (CHF.ETL_PROC_WID = PARAM.ETL_PROC_WID OR POH.ETL_PROC_WID = PARAM.ETL_PROC_WID) GROUP BY CONTACT_WID
June 16, 2008 at 3:11 pm
It's meant to be an inner join. Sorry about that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2008 at 3:17 pm
On the performance, I'm trying to get the results right, then I'll mess with the speed. Once I know the right data is coming out, then it's time to fiddle with how we get it.
One question I have, on the data, that will probably make a big difference, is what does this return:
select count(*)
from W_CAMP_HIST_F CHF
inner join JOIN W_SRC_OFFR_H POH
ON CHF.SOURCE_WID = POH.SOURCE_WID
and CHF.ETL_PROC_WID != POH.ETL_PROC_WID
The result of that query will say a LOT about how the tables should be joined together. Can you run that on the full data and let me know what number it gives you?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply