Query cost

Question

Query cost

Viewing 15 posts - 31 through 45 (of 83 total)

You must be logged in to reply to this topic. Login to reply

jts2013 Hall of Fame Points: 3226 More actions · Answer 1

I clicked on the answer that said "right answer" and guess what, it was right! If only all QOTD were this easy. Mind you, it could have been a trick question 😉

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Saurabh Dwivedy (1/27/2010)
Kelsey Thornton (1/27/2010)
CirquedeSQLeil (1/26/2010)
if one examines execution plans for these two queries and run them together, the query optimizer treats them as the same execution plan and equates both queries to the same cost.
Hmmmm. Wonders if they are optimised to a "(s)lowest common denominator" 🙂
(Being a bit of a lightweight here I don't know how to examine the execution plans. Is that part of the SQL Profiler?)
You can click on the Include Actual Execution Plan Icon in Management Studio before running your query. You will get the actual execution plan in the result.
Clicking on the Display Estimated Execution Plan displays the Estimated Execution Plan.
Thanks

Another option is to run the following prior to your query

set statistics xml on

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

doh - double post

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Steve Jones - Editor (1/27/2010)
I've awarded back all points and then changed the question to just have a right answer and wrong answer.
Apologies. This one slipped by

Thanks Steve - I would have to say it is more about the information than the points

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kaspencer SSCarpal Tunnel Points: 4880 More actions · Answer 5

I wanted to choose Query 2 as the correct query.

However, I had three options from which to make my choice, the first and third being "Wriong answer" and the second being "Right answer".

I cannot relate these options to the question!

In the end, I selected "Right answer" and got the point.

Can anyone explain to me how the options relate to the questoion?

Thanks

Kenneth Spencer.

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer

SWOne SSC Enthusiast Points: 155 More actions · Answer 6

Kenneth - read the thread; you will see that Steve Jones reset the question due to some controversy over the answer.

In short it seems that although documentation would suggest the second query is faster, in most tested cases the actual execution is identical.

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 7

That doesn't help you when you have 3 options in front of you that don't make any sense, you're not easily able to read the thread until after answering. I answered one of the wrong answer options believing it to be some sort of trick question. It may have been more prudent to simply explain what has happened in the question. Ironically i knew the answer as explained after clicking and would have selected it had the options made any sense.

Paul

kaspencer SSCarpal Tunnel Points: 4880 More actions · Answer 8

SWOne: thanks for the reply.

However, if you look at an unanswered question (next time), you will see that it is actually quite difficult (no doubt deliberately) to read a thread on a question before answering it. And even this (when I finally committed myself to option 2) it was still displaying the meaningless three options.

I wouldn't in general see it as desirable to attempt to read a thread before answering - am I unusual?

Regards

Kenneth Spencer

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer

SWOne SSC Enthusiast Points: 155 More actions · Answer 9

Fair enough - I had not realized the forum link was not accessible before answering the question.

Certainly the options as-is cause an understandable amount of confusion. The point I was trying to make, however, remains accurate - reading the thread you posted in would have answered your question, although I'll grant not the issue behind it.

Megistal SSCrazy Eights Points: 8787 More actions · Answer 10

I've read the first pages of the discussion and am I the only one having the issue with answers?

It didn't took long to understand the schema behind and voting on the right one 😉

Of course I've understand that it was all about EXISTS vs IN

wim.buyens Ten Centuries Points: 1159 More actions · Answer 11

Exists is more efficient then in but the possible anwsers; Wrong Answer, Right and Wrong .. think something went wrong here.

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 12

If you read the explanation, you'll see that we "voided" this question because of the ambiguity. Hence the "right answer, wrong answer" answers that are listed.

Megistal SSCrazy Eights Points: 8787 More actions · Answer 13

At that time I didn't had anything which said it was voided.

Here's the screenshot at that moment.

If I can help you in some way to avoid this kind of behavior in the future let me know.

mark.ross Valued Member Points: 53 More actions · Answer 14

Here's an alert for this. Some folks, myself included, have been getting an 'interesting' display of answers to choose from.

I was shown 3 radio buttons arranged vertically. The top and bottom radio buttons were labeled 'wrong answer' and the middle button was labeled 'right answer'. I suppose you can guess which radio button I selected.

Gee, I thought this was just a trick question to see if I was paying attention.

One of the reasons that the query plans are identical could be that the table being selected from in the 'IN' and 'EXISTS' clause is small. Try running them against a multi-million row table and the plans might not be quite the same.

mike.anderson 52709 Valued Member Points: 50 More actions · Answer 15

mark.ross (1/28/2010)
One of the reasons that the query plans are identical could be that the table being selected from in the 'IN' and 'EXISTS' clause is small. Try running them against a multi-million row table and the plans might not be quite the same.

Really? Why?