Comparison of query plan - 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

General SQL Server Forums

New to SQL Server Programming

Comparison of query plan

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-13 : 02:27:22

Query 1
Cached plan size - 102b
compile cpu - 116
compile memory - 1704
compile time - 116
estimated number of rows 25342.4
estimated subtree cost - 8.03056

Query 2
Cached plan size - 91b
compile cpu - 182
compile memory - 2208
compile time - 182
estimated number of rows 73782.8
estimated subtree cost - 11.7951

How to know which is better?

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-13 : 02:38:09

[code]DECLARE @tempfun TABLE(col1 INT, col2 INT)
DECLARE @tempfun1 TABLE(col1 INT, col2 INT)
DECLARE @tempfun2 TABLE(col2 INT, col3 INT)

INSERT INTO @tempfun
SELECT 1,2 UNION ALL
SELECT 3,4

INSERT INTO @tempfun1
SELECT 5,6 UNION ALL
SELECT 7,8

INSERT INTO @tempfun2
SELECT 2, 22 UNION ALL
SELECT 6, 66
query 1
SELECT t2.col2, t2.col3
FROM (
SELECT col1, col2
FROM @tempfun
UNION
SELECT col1, col2
FROM @tempfun1
)t1 join @tempfun2 t2 on t1.col2=t2.col2
query 2
SELECT *
FROM (
SELECT t2.col2, t2.col3
FROM @tempfun t1 join @tempfun2 t2 on t1.col2=t2.col2
UNION
SELECT t2.col2, t2.col3
FROM @tempfun1 t1 join @tempfun2 t2 on t1.col2=t2.col2
)m[/code]
well...the query was something like tat...

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-13 : 05:17:07

hmmm i think i choosed query 1

Hope can help...but advise to wait pros with confirmation...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 05:33:51

I think query 2 is better.

Don't trust the query plan estimations.

Try this:


DECLARE @tempfun TABLE(col1 INT, col2 INT)
SET STATISTICS IO ON
SET STATISTICS TIME ON


DECLARE @tempfun1 TABLE(col1 INT, col2 INT)
DECLARE @tempfun2 TABLE(col2 INT, col3 INT)

INSERT INTO @tempfun
SELECT	1,2 UNION ALL
SELECT	3,4

INSERT INTO @tempfun1
SELECT	5,6 UNION ALL
SELECT	7,8

INSERT INTO @tempfun2
SELECT	2, 22 UNION ALL
SELECT	6, 66

-- query 1
SELECT	t2.col2, t2.col3
FROM	(
	SELECT	col1, col2
	FROM	@tempfun
	UNION
	SELECT	col1, col2
	FROM	@tempfun1
	)t1 join @tempfun2 t2 on t1.col2=t2.col2

-- query 2
SELECT	*
FROM	(
	SELECT	t2.col2, t2.col3
	FROM	@tempfun t1 join @tempfun2 t2 on t1.col2=t2.col2
	UNION
	SELECT	t2.col2, t2.col3
	FROM	@tempfun1 t1 join @tempfun2 t2 on t1.col2=t2.col2
	)m

It seems query 2 avoids a worktable, takes less time, and takes less reads.

I'd test this on actual data though -- not table variables.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-13 : 06:23:43

hmmm i did test this in the real table with index and pk...but the result was almost same...

Hope can help...but advise to wait pros with confirmation...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 06:40:38

did you get any difference in the reads / cpu time / execution time?

using

SET STATISTICS IO ON
SET STATISTICS TIME ON

?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-13 : 07:08:08

quote:
SET STATISTICS IO ON
SET STATISTICS TIME ON

wow what was that...later i go home try...

Hope can help...but advise to wait pros with confirmation...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 07:10:23

They give you good info in the message's tab.

IO tells you the physical and logical read and writes performed.

TIME tells you how long, how much cpu resource, etc.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-13 : 21:27:04

hi charlie, may i know IO are important or compile time important? cause my query have such differents

query that looks like query 1
(484 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '1'. Scan count 531, logical reads 1797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '2'. Scan count 10, logical reads 3452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '3'. Scan count 1, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '5'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '4'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 200 ms.

query that looks like query 2
(484 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '1'. Scan count 531, logical reads 1797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '2'. Scan count 10, logical reads 3452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '3'. Scan count 135, logical reads 335, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '4'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '5'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 156 ms.

Hope can help...but advise to wait pros with confirmation...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 05:01:16

484 Rows is a tiny number of rows to make a decision on.

I'd scale up your data if you can -- make up data or clone the table(s) over and over a few times.

Then run 10 tests for each method, take the average as your median time.

Then you could compare.

This still doesn't tell you if you are hitting the indexes in a good way. use this in combination with the actual query plan and look for index seeks / scans.

(you can use SET STATISTICS XML ON to get the query plan in xml format)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-14 : 05:11:28

wow charlie...you are actually teaching me lots of new stuff ^^ really thx you!!

Hope can help...but advise to wait pros with confirmation...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 05:44:02

Glad I can help.

Check out the following thread where Peso and I had a pretty in depth discussion about a performance issue:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129549

The Sql isn't like yours but the general testing methods will probably be useful.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources