Slow script but why? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Slow script but why?

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-08-11 : 09:18:05

Hi Reader!

I have the below script

SELECT	Selection1.WeekAssessed,
	AVG (CYTD.ActualRating) as [Calendar YTD]

FROM	vw__DGW_Graph Selection1

	INNER JOIN vw__DGW_Graph CYTD
	ON CYTD.DateAssessed BETWEEN '2000/01/01' AND '2010/01/01'

WHERE	(CYTD.Template = 'Subcontractor/Contract Feedback')
	AND
	(Selection1.Template = 'Subcontractor/Contract Feedback')

GROUP BY
	Selection1.WeekAssessed,
	Selection1.WeekAsNumber

ORDER BY
	Selection1.WeekAsNumber

It runs pretty much instantly. If I change the code to add another inner join.

SELECT	Selection1.WeekAssessed,
	AVG (FYTD.ActualRating) as [Financial YTD],
	AVG (CYTD.ActualRating) as [Calendar YTD]

FROM	vw__DGW_Graph Selection1

	INNER JOIN vw__DGW_Graph FYTD
	ON FYTD.DateAssessed BETWEEN '2000/01/01' AND '2010/01/01'

	INNER JOIN vw__DGW_Graph CYTD
	ON CYTD.DateAssessed BETWEEN '2000/01/01' AND '2010/01/01'

WHERE	(FYTD.Template = 'Subcontractor/Contract Feedback')
	AND
	(CYTD.Template = 'Subcontractor/Contract Feedback')
	AND
	(Selection1.Template = 'Subcontractor/Contract Feedback')

GROUP BY
	Selection1.WeekAssessed,
	Selection1.WeekAsNumber

ORDER BY
	Selection1.WeekAsNumber

It takes 35 seconds to run, does any one have any ideas? The date ranges are normally dynamically set, I have just used set ones to try and work out why it is taking so long. I will be having up to 3 tables joined it.

Thanks

Leah

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-08-11 : 09:20:30

Surely adding another identical inner join can at worst only double the time it takes to run?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-11 : 09:29:22

You don't have any relationship defined in the join so its pretty much a cross join

table1 = 10
table2 = 100
table3 = 100

table1 join table2 = at most 10*100
table1 join table2 join table3 = at most 10*100*100

Try something more like:

SELECT
Selection1.WeekAssessed,
AVG (FYTD.ActualRating) as [Financial YTD],
AVG (CYTD.ActualRating) as [Calendar YTD]

FROM vw__DGW_Graph Selection1
INNER JOIN (Select * From vw__DGW_Graph Where DateAssessed BETWEEN '2000/01/01' AND '2010/01/01') CYTD
On Selection1.Template = CYTD.Template
INNER JOIN (Select * From vw__DGW_Graph Where DateAssessed BETWEEN '2000/01/01' AND '2010/01/01') FYTD
On Selection1.Template = FYTD.Template
GROUP BY
Selection1.WeekAssessed,
Selection1.WeekAsNumber

ORDER BY
Selection1.WeekAsNumber

Corey

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-11 : 09:30:06

I have a feeling you may have a missing join condition in there somewhere, which is why the additional table makes it take so much longer. The way i read it I think that the query above will show you one line for each entry between the dates vw__DGW_Graph and it will show you this line for every entry between the dates in vw__DGW_Graph (IE it will product them.)

-------
Moo. :)

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-08-11 : 09:33:35

Seventhnight,

I have tried your script and it took even longer than mine. It took 50 seconds.

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-08-11 : 09:35:05

The results look like this

Week 46	55.246913580246911	55.246913580246911
Week 50	56.196581196581199	56.196581196581199
Week 01	55.246913580246911	55.246913580246911
Week 02	57.629000000002065	57.629000000002101
Week 06	55.246913580246911	55.246913580246911
Week 08	56.196581196581199	56.196581196581199
Week 10	55.9599756968178	55.9599756968178
Week 14	56.253340374964466	56.253340374964459
Week 18	55.9599756968178	55.9599756968178
Week 23	55.880987354167722	55.880987354167722
Week 24	56.196581196581199	56.196581196581199
Week 27	56.351625851257587	56.351625851257587
Week 29	57.62900000000031	57.62900000000046

This data is going into a graph.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-11 : 11:11:21

you cannot join more than 1 transaction table at a time, w/o using subqueries. remove the GROUP BY and just return the raw data and look at it. you will see lots of repeating results and way too much data is being processed.

if you wish to combine the results of 2 seperate transaction tables, you need to seperately query each and GROUP BY common columns -- and then join the results together by those GROUPED columns.

Does this make sense? It's key to understanding SQL and how you can write reports and SELECT statements. YOu need to ensure you are always joining things properly and performing GROUP BY's in subqueries as needed to prevent cross joins.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-11 : 11:14:45

also, you have not established a relationship between your GraphSelection view and your transaction tables, other than the Template field. (which you should do using a JOIN and not repeating the WHERE clause over and over for each table).

rule of thumb: if you are joining 2 tables and in both cases you are not covering all of the PK columns, unless you know exactly what you are doing you will get bad data and have not established a good join. Add other tables into the mix and it gets even worse.

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-11 : 11:23:37

By the way, I feel I need to ask what the difference is between the CYTD and the FYTD... i just can't see that the two columns would ever be different!?

I think you might be better off giving us table layouts, sample data, and a desired output. Then we will give you a much better push in the right direction.

Corey

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-08-11 : 11:26:02

I have managed to get it working using unions. CYTD is calendar year to date and FYTD is financial YTD. The date ranges will change for these.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-11 : 11:31:32

well congrats on getting it working then!

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources