March 22, 2006 at 5:25 am
I know that
If you create index on particular table & u have to put where condition in same sequence u create index then only SQL Server is using that index for Particular QUERY
But now suppose i have query like
SELECT Field1, Field2, Field3
FROM Table1
INNER JOIN Table2 ON Table1.Id = Table2.Id
INNER JOIN Table3 ON Table2.Id = Table3.Id
WHERE Table2.Id = <Value>
Now SQL Server will use index while filtering Data of Table3 & if yes then on what criteria it will take index
As i have more than around 7-8 tables to link, so it will Use index to take records from That table or Not ??
Thanks in advance for your efforts
March 22, 2006 at 7:03 am
I'm not sure exactly what you're asking but ... first off you should index the columns that are used in the join statements of your query. Then consider indexes on the columns occurring in the where clause.
The optimiser will work out the best way to arrange the joins and should return the best choice for you.
There's lots more you can do with indexing but a forum posting can't really cover indexing strategies in depth, the above should form a good starting point .. find some docs/books/courses - Kalen Delaney, Kimberley Tripp , Technet, ms training courses.
Queries usually perform better if every table has a clustered index.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 22, 2006 at 9:33 pm
you said - The optimiser will work out the best way to arrange the joins and should return the best choice for you
It means there is no need to put column name in Same order in which INDEX is created for the table ??
If yes then
If i have created index on 3 columns Field1, Field2, Field3 & i put only Field2 in WHERE Condition then optimiser will use Index or not ??
Thanks for your prompt reply
March 22, 2006 at 10:10 pm
>>It means there is no need to put column name in Same order in which INDEX is created for the table ?
No, it doesn't mean that. Order of columns in the index definition is critically important.
>>If i have created index on 3 columns Field1, Field2, Field3 & i put only Field2 in WHERE Condition then optimiser will use Index or not ??
It will not use it to index seek. It may use it to index-scan, if the the index is a covering index and the queried column set can be satisfied from the index and not the table.
>>Now SQL Server will use index while filtering Data of Table3 & if yes then on what criteria it will take index
It Table3 has an index where the Table3.Id column is 1st in an index, then SQL Server *may* decide to use that index in the join, but the decision to use it is based on many factors like data selectivity, data distribution, relative number of rows returned, size of Table3 and cost of tablescan in context of overal query cost.
March 22, 2006 at 10:43 pm
Thanks for your response
Can u give some suggestions on which i should go for Indexing & other issues to get better performance
we have created index & we have around 4 lacs records, After creating index it comes around 3 times faster & but we want to reduce this also, can we do anything else to get better performance
Thanks
March 23, 2006 at 1:50 am
You really have to examine your query plan to fine tune the query - I recommend the book SQL Server 2000 performance tuning isbn 0-7356-1270-6 as a start.
There are lots of aspects to composite indexes and some widely held beliefs about how they work ( or don't ) are not necesscarily true ( but i have no wish to start a discussion about indexing < grin > ) - the optimiser is pretty good but it does have to come with an optimal plan quickly, so helping it to make those choices with useful indexes is a good first step, then analyse the plan and look for those parts which don't go well. You might want to try dropping your query into the 2005 tuning advisor, this is much better at making suggestions for useful indexes than the 2000 version.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply