Query Cost - 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

SQL Server Development (2000)

Query Cost

Author

Topic

arvind
Starting Member

12 Posts

Posted - 2006-12-29 : 02:34:47

hi to all,

I write two Query for same purpose And run these simultaniouly in query analyzer,

SELECT COUNT(*) FROM Assm_Temp_Ques_OptionGrade INNER JOIN Assm_GradeT ON
Assm_Temp_Ques_OptionGrade.GradeValueId = Assm_GradeT.GradeValueId INNER JOIN Assm_Grade ON
Assm_GradeT.GradeId = Assm_Grade.GradeId WHERE Assm_Grade.GradeId = 141

SELECT COUNT(*) FROM Assm_Temp_Ques_OptionGrade WHERE GradeValueId IN (SELECT GradeValueId FROM Assm_GradeT
WHERE GradeId=141)

after that show excution plan for that this plan show first query cost is 52.31% and another on is 47.69% Show please tell me which one is the best query ( lower Quety cost is best or higher is best)
because when i run both query as a single then show query cost is 100% for both, So plz tell me how identify that the particular query is very efficeant
thanks in advance
arvind kumar

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-29 : 04:19:18

Generally the one with the lower value is better because it is taking up less of the overall time of the batch, but you should also compare other things like the statistics IO because the execution plans are not always the best indicator of the best query performance.

Your query would probably be even better one of these two ways

SELECT COUNT(*)
FROM Assm_Temp_Ques_OptionGrade
INNER JOIN Assm_GradeT
ON Assm_Temp_Ques_OptionGrade.GradeValueId = Assm_GradeT.GradeValueId
WHERE Assm_GradeT.GradeId = 141

SELECT COUNT(*)
FROM Assm_Temp_Ques_OptionGrade OG
WHERE EXISTS (SELECT * FROM Assm_GradeT T
WHERE OG.GradeValueId = T.GradeValueId AND T.GradeId=141)

I removed the Assm_Grade table from the first query because you didn't use it in your second query, so I assume it isn't necessary, if it is then your second query is wrong. That's also why it is showing up as faster in the execution plans.

arvind
Starting Member

12 Posts

Posted - 2006-12-29 : 04:48:59

first of all thanks to u for reply, consider yr removal of Assm_Grade table but after that i saw in excution plan first query cost is 50.96 where as 2nd query cost is 49.04 when simultaniouly run then how decide which one is best where as i think join is much better then sub query. So i little bit confiuse plz clear it.
Also u told that execution plan are not always right then tell how i decided which query is best.
arvind

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-29 : 05:51:29

With such a small difference I'd use the inner join if I were you but acording to the query optimizer the EXISTS-statement is marginally better. If you run "UPDATE STATISTICS table" and "DBCC FREEPROCCACHE" prior to running the comparison you should get reliable results. If the difference is similar I'd go for the one you like best...I too prefer using JOINS so I would do that.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Kristen
Test

22859 Posts

Posted - 2006-12-29 : 06:23:45

I suggest you ignore the Percentages and look at Logical I/O and whether the query is use Seeks or Scans, and if Indexes are being used.

I use the following:


-- Clear cache (for level playing field 
--    - only if wanting to check PHYSICAL performance rather than LOGICAL performance)
-- Do **NOT** run these on a Live server
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

-- Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats
-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

-- ... put query here - e.g.:

SELECT * FROM Northwind.dbo.Products

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SET SHOWPLAN_TEXT OFF
GO

Kristen

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 11:23:58

The two queries are NOT the same... the first query joins 3 tables, the second only joins 2.

I'm thinking that you should use the following for the first query and try again...

SELECT COUNT(*)
FROM Assm_Temp_Ques_OptionGrade og
INNER JOIN Assm_GradeT gt
ON og.GradeValueId = gt.GradeValueId
WHERE gt.GradeId = 141

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources