June 17, 2008 at 4:43 am
After removing the second word "JOIN" this query runs in 5 seconds and returns teh value 92377906
June 17, 2008 at 7:07 am
Try this, see if it gives the right results:
;WITH POH (OFFER_WID, SOURCE_WID) as
(SELECT OFFER_WID, SOURCE_WID
FROM W_SRC_OFFR_H
INNER JOIN W_PARAM_G
ON W_SRC_OFFR_H.ETL_PROC_WID = W_PARAM_G.ETL_PROC_WID
WHERE OFFER_WID != 0)
SELECT CHF.CONTACT_WID ,
COUNT(DISTINCT CHF.SOURCE_WID) ,
COUNT(DISTINCT POH.OFFER_WID)
FROM W_CAMP_HIST_F CHF
INNER JOIN W_PARAM_G PARAM
ON CHF.ETL_PROC_WID = PARAM.ETL_PROC_WID
LEFT OUTER JOIN POH
ON CHF.SOURCE_WID = POH.SOURCE_WID
WHERE CHF.CONTACT_WID > 0
AND CHF.SOURCE_WID> 0
GROUP BY CHF.CONTACT_WID
- 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 17, 2008 at 8:55 am
Let me first say I am impressed with the timing of this query, it runs in 21 seconds. But unfortunately, it returns nothing.
I appreciate your effort on this.
June 17, 2008 at 9:20 am
I apologize, please disregard my last post. I need to load "new" records into our OLTP database and run an ETL job to pull the new records into our OLAP system.
The query returned nothing because there was nothing there. So, please give me 3 hours to test this again. Our ETL process will take about 2 1/2 hours to run and then I can test this new query.
Thanks you for your time and patience, it is greatly appreciated.
June 17, 2008 at 6:28 pm
Your query is very fast and I noticed it is SQL Server 2005 only. That is okay, I don't have SQL Server 2000 any more.
I have attached the execution plans for both queries as well as the results.
You new query runs in about 30 seconds, the old query runs in 51 minutes.
The new query returns 150 rows, the old query returns 153 rows.
The last column of the new qyery contains all 3's, the original query teh 3rd column contains all 5's.
Your query is close to the correct results. It is treating the OR clause in the original query as if it were an AND. If I change the OR clause in the original query to and AND, it returns the same results your query does and it runs in 30 seconds.
So..... I hope you can modify YOUR query to return the same results and have it finish in 30 minutes or less.
Thank you again
June 18, 2008 at 7:46 am
I'm having trouble duplicating the results you have, since the sample data from your first post in this thread gives zero results for either query when I tested it.
That means I'm kind of running a little bit blind here. Try modifying it to:
;WITH POH (OFFER_WID, SOURCE_WID) as
(SELECT OFFER_WID, SOURCE_WID
FROM W_SRC_OFFR_H
INNER JOIN W_PARAM_G
ON W_SRC_OFFR_H.ETL_PROC_WID = W_PARAM_G.ETL_PROC_WID
WHERE OFFER_WID != 0)
SELECT CHF.CONTACT_WID ,
COUNT(DISTINCT CHF.SOURCE_WID) ,
COUNT(DISTINCT POH.OFFER_WID)
FROM W_CAMP_HIST_F CHF
LEFT OUTER JOIN W_PARAM_G PARAM
ON CHF.ETL_PROC_WID = PARAM.ETL_PROC_WID
LEFT OUTER JOIN POH
ON CHF.SOURCE_WID = POH.SOURCE_WID
WHERE CHF.CONTACT_WID > 0
AND CHF.SOURCE_WID> 0
GROUP BY CHF.CONTACT_WID
On the 2005 vs 2000 point, you posted this in the 2005 forum, so I assumed you were using 2005. I can modify this to work in 2000, just by changing the CTE to an inline derived table in the From clause. Let me know if you want that done.
- 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 18, 2008 at 12:11 pm
No I am sorry to report that your modification is a lot worse. It is suppose to return 150 rows, this new modified query return 1.7 million rows and the values aren't even close. The last column is suppose to be all 5's and yours are all zeroes. It also took 36 minutes to execute.
I have attached Excel Spreadsheets that have samples of the data for the two large tables. Unfortunately I could only extract the first 65536 rows of the W_CAMP_HIST_F table. But unfortunately, it would not upload the Excel Spredsheet for some reason for teh CAMP_HIST_F table. Not sure why. SO I had to zip that file.
I hope that helps.
June 18, 2008 at 1:42 pm
Do you have some data for the Param table that will match the data in the sample data from those spreadsheets? I just imported them, and ran your original query (page 1 of this thread), and got 0 rows.
- 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 19, 2008 at 11:33 am
What you posted about or vs and made me take a slightly different tack on this. Try this:
;with Main (Contact_WID, Source_WID, Offer_WID) as
(select chf.contact_wid, chf.source_wid,
isnull(poh.offer_wid, 0)
from w_camp_hist_f Chf
left outer join w.src_offr_h POH
on chf.source_wid= poh.source_wid
inner join w_param_g [param]
on chf.etl_proc_wid = param.etl_proc_wid
union
select chf.contact_wid, chf.source_wid,
isnull(poh.offer_wid, 0)
from w_camp_hist_f Chf
inner join w.src_offr_h POH
on chf.source_wid= poh.source_wid
inner join w_param_g [param]
on poh.etl_proc_wid = param.etl_proc_wid)
select Contact_WID,
count(distinct source_wid) as Qty_Source_WID,
count(distinct offer_wid) as Qty_Offer_WID
from Main
- 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 20, 2008 at 5:48 am
Because my Sort order is Binary, I need to capitalize everything as follows:
;with Main (CONTACT_WID, SOURCE_WID, OFFER_WID) as
(select Chf.CONTACT_WID, Chf.SOURCE_WID,
isnull(Poh.OFFER_WID, 0)
from W_CAMP_HIST_F Chf
left outer join W_SRC_OFFR_H Poh
on Chf.SOURCE_WID= Poh.SOURCE_WID
inner join W_PARAM_G [param]
on Chf.ETL_PROC_WID = param.ETL_PROC_WID
union
select Chf.CONTACT_WID, Chf.SOURCE_WID,
isnull(Poh.OFFER_WID, 0)
from W_CAMP_HIST_F Chf
inner join W_SRC_OFFR_H Poh
on Chf.SOURCE_WID= Poh.SOURCE_WID
inner join W_PARAM_G [param]
on Poh.ETL_PROC_WID = param.ETL_PROC_WID)
select CONTACT_WID,
count(distinct SOURCE_WID) as Qty_Source_WID,
count(distinct OFFER_WID) as Qty_Offer_WID
from Main
BUT I still get an error executing it. The error is as follows:
Msg 8120, Level 16, State 1, Line 1
Column 'Main.CONTACT_WID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
June 20, 2008 at 12:06 pm
Add "Group By Main.CONTACT_WID" at the bottom (after "from Main").
- 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 21, 2008 at 5:09 am
I tried that and it still returns nothing.
The param table always has a single row. It contains the number to match with of which rows to return. Each time a new ETL job runs it increments this value to determine which rows are NEW.
June 23, 2008 at 7:49 am
I'm afraid I can't take this any further without test data that returns something for the original query.
- 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 23, 2008 at 8:21 am
I understand completely. I will put together data for all 3 tables that you can load and give you what the results should be. This should make it a lot easier for you.
You will have to keep in mind that this will be a small subset of data, our W_CAMP_HIST_F table contains over 50 million rows.
Thank you again for responding and I will try and get the data to load ASAP (hopefully today)
Is there anyway you would suggest that I package up the data for you to load?
Thanks again
June 24, 2008 at 9:41 am
Just make sure that the original query will return rows from the test data, and that it gives you the kind of results you want to see.
- 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 - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply