Hi everyone
I’m pretty sure you already know that, but anyway:
I tested today if the MS SQL Server’s Query Optimizer is smart enough, so it seems it is. There is no difference in executions plans for these two queries:
Select * From table1 Where Name Like ‘%’ Order By Name
And
Select * From table1
It seems it figures I want all the records and simply removes the where from the query. The good thing is I can always write LIKE parameterized queries, and if I need some of the records, I send the parameter; if I need them all, I send ‘%’ and there will no performance penalty.
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/000001cb2f2f$42346710$c69d3530$@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Of course, I meant the second query like this:
Select * from table1 Order by Name
Sorry, guys, I screwed it up (again) ;)
From: Grigore Dolghin [mailto:gdolghin@gmail.com]
Sent: Thursday, July 29, 2010 6:04 PM
To: 'profox@leafe.com'
Subject: Difference between SELECTs
Hi everyone
I’m pretty sure you already know that, but anyway:
I tested today if the MS SQL Server’s Query Optimizer is smart enough, so it seems it is. There is no difference in executions plans for these two queries:
Select * From table1 Where Name Like ‘%’ Order By Name
And
Select * From table1
It seems it figures I want all the records and simply removes the where from the query. The good thing is I can always write LIKE parameterized queries, and if I need some of the records, I send the parameter; if I need them all, I send ‘%’ and there will no performance penalty.
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/000501cb2f2f$763cb710$62b62530$@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Thu, Jul 29, 2010 at 10:05 AM, Grigore Dolghin <gdolghin@gmail.com> wrote:
> Of course, I meant the second query like this:
>
>
>
> Select * from table1 Order by Name
>
>
>
> Sorry, guys, I screwed it up (again) ;)
Well it is pretty easy to understand why.
your Like '%' was off of the same column you were using in your Order by.
The execution plan give you information on WHAT TO CONSIDER changing
to make your request better. Well that is very well hidden ;-> but it
is there.
Table scan or index scan ?
But in all honesty there is nothing complicated in dump a table
ordered if there is an index on that column.
Join that table to another and now you get a whole new ball of wax to
play with. Making compound indexes that have not only the FKs but the
where columns takes a lot of painful work. Well making the index is
easy. Determining if it is used and if not what other one is is what
takes the time. ;->
SELECT TCAG.CodeDescription, tp.TCAID, TCAG.TCAType
FROM dbo.TCAPicked AS tp
INNER JOIN
dbo.TCACodeSubPicks AS tsp ON tp.SearchKey = tsp.ID
LEFT OUTER JOIN
dbo.TCAGroups AS TCAG ON tsp.GroupID = TCAG.TCAType
My cost is split 32-35-32 on Clustered index seeks or Index Seek.
That rocks. 0 in nested loops.
This is the guts of a view that I query against. I copied in the code
above and asked for the execution plan and it KNEW where it was from.
I am impressed.
Script for SelectTopNRows command from SSMS ******--select * FROM
[ELogs2].[dbo].[lv_TCAIDS]
--
Stephen Russell
Sr. Production Systems Programmer
CIMSgts
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/AANLkTi=wNYUBQ33cT5JSzTa5p2zwjaGAM630O4UBkA+Y@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.