Re: Index Seek (or) Index Scan in Execution Plan
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 11/02/04
- Next message: Bob Castleman: "Stupid mistake ..."
- Previous message: Shaun: "Help !! Nested Stored Procedure, is this the best way?????"
- In reply to: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Next in thread: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Reply: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 2 Nov 2004 08:25:33 -0800
Prabhat
A scan is not always worse than a seek.
If you really want to use these hints, you should make absolutely sure that
your queries do run better when you use them. Don't just use them because
you think you know better than the optimizer.
Run your query with and without the hint, and see which performs better
using SET STATISTICS IO ON and SET STATISTICS TIME ON.
-- HTH ---------------- Kalen Delaney SQL Server MVP www.SolidQualityLearning.com "Prabhat" <not_a_mail@hotmail.com> wrote in message news:Ock$6lKwEHA.3296@TK2MSFTNGP10.phx.gbl... > Hi Kalen, > > Thanks for reply. > > I use the "Index =" mainly for 2 reasons. > > 1) My SQL Query uses 2 Conditions in where clause. And I can see that > there > is a Index Scan Involve in that Query. So I prefer "Index =" which make > Index Seek. > 2) In Some cases My output should be Order by Lastname, FirstName. And > Also > the query will have the Where Clause as above. So Here also i can see some > time it uses Index Scan. And I use a Compound Index on Lastname, > Firstname - > To get the order. So I use the Index= in this case also. > > You can see the Example of Query in the Main (TOP / original Post). > > Kindly suggest. > > Thanks > Prabhat > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:#h9EUmCwEHA.1264@TK2MSFTNGP12.phx.gbl... >> Prabhat >> >> If you use the (INDEX = ..) hint you are FORCING SQL Server to use the > index >> you tell it to use, whether or not that is a good choice. If you measure > the >> peformance (perhaps SET STATISTICS IO ON) you will see that when you >> force >> the index, the performance is worse than when you let SQL Server make its >> own choice, and it chooses to do the scan. >> >> -- >> HTH >> ---------------- >> Kalen Delaney >> SQL Server MVP >> www.SolidQualityLearning.com >> >> >> "Prabhat" <not_a_mail@hotmail.com> wrote in message >> news:uuZ%23H4BwEHA.2944@TK2MSFTNGP12.phx.gbl... >> > Hi Wayne, >> > >> > Thanks for your Suggestions. >> > >> > Reg the 2 Queries: >> > >> > Yes They are searching for different Rows. And the 1st Query is > Retrieving >> > 2 >> > Rows while the 2nd Query returns 5622 Rows. >> > So As you told SQL Server optimizer will Use Index Seek for 1st Query > and >> > Index Scan for 2nd Query? >> > >> > Then If I use "Index=" Keyword in the Second Query then that the 2nd > Query >> > uses the Index Seek. Why is like that? >> > >> > And Now If I write : >> > select * from TABLE with(index = ind_CMP_name) where lastname like >> > 'smith%' >> > >> > So This is Better then using Only "Index=" as this is Also Supported in >> > 2005? >> > >> > Thanks >> > Prabhat >> > >> > "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message >> > news:e6A#GRBwEHA.1400@TK2MSFTNGP11.phx.gbl... >> >> The 2 queries in your list are not the same. They are searching for >> >> different rows, and a different number of rows will be returned for > each. >> >> This is called selectivity - If a very small percentage of rows in the >> > table >> >> will be returned ( 3-5%) then the query is very selective. Index Seeks >> >> are >> >> better for very selective queries and index scans or better for >> >> queries >> > with >> >> low selectivity. SQL Server's optimizer is smart enough to figure this >> >> out >> >> and (generally) choose a good plan.. >> >> >> >> see inline >> >> >> >> -- >> >> Wayne Snyder, MCDBA, SQL Server MVP >> >> Mariner, Charlotte, NC >> >> www.mariner-usa.com >> >> (Please respond only to the newsgroups.) >> >> >> >> I support the Professional Association of SQL Server (PASS) and it's >> >> community of SQL Server professionals. >> >> www.sqlpass.org >> >> >> >> "Prabhat" <not_a_mail@hotmail.com> wrote in message >> >> news:%23ByYLXAwEHA.3908@TK2MSFTNGP12.phx.gbl... >> >> > Hi all, >> >> > >> >> > In Adition to Above Post / Question I have 2 More Questions: >> >> > >> >> > 1) Is the Index Seek is Faster or Index Scan? and Why? >> >> >> >> Index seek does a binary search from the root to the leaf level, a >> >> Scan >> >> reads through part of all of the leaf level.... So scans generally do >> >> more >> >> IO than seeks. >> >> > >> >> > 2) How Can I Replace the Index = IndexName in the Above Post? (I > Think >> > the >> >> > Index = is used only for backward compatibility in SQL Server 2000) >> >> > >> >> It is preferable to not use index hints, but if performance is killing >> >> you...( update statistics first, then see if you get better response). >> >> >> >> select yad yad from table WITH (index = whatever) >> >> >> >> Be sure to use the with clause for compatilibility with SQL 2005 >> >> > Thanks in Advance for any Suggestion and help for these 2 posts... >> >> > >> >> > Thanks >> >> > Prabhat >> >> > >> >> > >> >> > "Prabhat" <not_a_mail@hotmail.com> wrote in message >> >> > news:#J1NJu$vEHA.3096@TK2MSFTNGP14.phx.gbl... >> >> > > Hi all, >> >> > > >> >> > > I have one table. Where : >> >> > > >> >> > > DonorID Int (Identity) Primary Key >> >> > > FirstName Varchar(25) >> >> > > LastName Varchar(25) >> >> > > ... >> >> > > ... >> >> > > >> >> > > I have One nonclustred index on Lastname another nonclustred index > on >> >> > > (lastname, firstname). >> >> > > >> >> > > Suppose I Execute the Query: >> >> > > >> >> > > select * from TABLE where lastname like 'abott%' (This >> > Query >> >> > uses >> >> > > Index Seek on the Compound Index) >> >> > > >> >> > > But if I use the below Query: >> >> > > >> >> > > select * from TABLE where lastname like 'smith%' (This >> >> > > Query >> > uses >> >> > > Index Scan) >> >> > > >> >> > > But >> >> > > >> >> > > select * from TABLE (index = ind_CMP_name) where lastname like >> > 'smith%' >> >> > > (But this Query uses the Index Seek) >> >> > > >> >> > > NOTE: ind_CMP_name is the Compound Index. >> >> > > >> >> > > Why there is the Difference, One Query uses Index Seek while other >> > uses >> >> > > Index Scan, even if both the query uses the same where condition >> >> > > on >> > same >> >> > > column? >> >> > > >> >> > > Thanks >> >> > > Prabhat >> >> > > >> >> > > >> >> > >> >> > >> >> >> >> >> > >> > >> >> > >
- Next message: Bob Castleman: "Stupid mistake ..."
- Previous message: Shaun: "Help !! Nested Stored Procedure, is this the best way?????"
- In reply to: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Next in thread: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Reply: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
