SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Vs Select criteria sequence


Index Vs Select criteria sequence

Author
Message
Jaya Chitra
Jaya Chitra
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 470
Dear All,

Can any one tell me the relation between the index fields and the query criteria sequence.

I have created the index for all the fields in the database individually.

If I am querying the database with by specifying two fields in the criteria field.

What will happen, will the performance is increased or not?

Kindly give me your suggestion.

Thanks a lot in advance. Smile

Nothing is impossible with Hard WorkSmile
Chandra Sekhara Vyas Dhara
Chandra Sekhara Vyas Dhara
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4377 Visits: 1149
If your join condition matches with the index fields the query will also performs faster.

It is not the index that imporve the performance. You should look out at defragmentation aslo. If the fragmentation is at the highest level the query also improves.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81645 Visits: 19210
You should not index ALL the fields. That will slow down your updates/inserts/deletes.

You want to include indexes on those fields which are mostly used for queries. If you have two items in the WHERE clause, it depends. You can include both fields in the index, you can have separate indexes that might be used.

Best bet is to get the execution plans for those queries often run and look to see if you are getting index seeks.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23840 Visits: 14905
It depends. What other columns are being returned by the query? What is the selectivity of the column(s) in the index?

As a rule you should not place a separate single column index on every column in a table. It creates more overhead than it is worth. You need to analyze the queries of the database and then index appropriately. A single composite index will likely be better (more selective) than 2 single column indexes and will reduce IO's. FOr example a person search on last name and first name will be faster if the index is a composite index including the last name and first name columns than 2 indexes on last name and first name.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jaya Chitra
Jaya Chitra
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 470
Dear All,

Thanks for you replies.

I have the table which contains some properties of the document in my knowledge repository.

The user can search by specifying any values to the properties.

Based on the values in the properties the where criteria is framed and then I am sending this to the stored procedure.

In the stored procedure I am framing the dynamic query with the where clause with the input where clause.

The table has minimum more than 200000 documents.

So I don't know how to make the index.

I had a discussion and some were saying that I can have index for all the fields.

Can any one tell me how to resolve this?

Thanks a lot.

Nothing is impossible with Hard WorkSmile
GilaMonster
GilaMonster
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114259 Visits: 45481
Can you post the proc, the table and the index defs? Are there certain columns that will be more frequently searched than others?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Ahmad Osama
Ahmad Osama
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2025 Visits: 1660
Jaya Chitra (9/23/2008)
Dear All,

Thanks for you replies.

I have the table which contains some properties of the document in my knowledge repository.

The user can search by specifying any values to the properties.

Based on the values in the properties the where criteria is framed and then I am sending this to the stored procedure.

In the stored procedure I am framing the dynamic query with the where clause with the input where clause.

The table has minimum more than 200000 documents.

So I don't know how to make the index.

I had a discussion and some were saying that I can have index for all the fields.

Can any one tell me how to resolve this?

Thanks a lot.


Study the clustered,nonclustered and covering indexes in BOL....
Making a clustered index on the columns in where clause will improve performance

Regards,
Sqlfrenzy
Ahmad Osama
Ahmad Osama
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2025 Visits: 1660
Jaya Chitra (9/23/2008)
Dear All,

Thanks for you replies.

I have the table which contains some properties of the document in my knowledge repository.

The user can search by specifying any values to the properties.

Based on the values in the properties the where criteria is framed and then I am sending this to the stored procedure.

In the stored procedure I am framing the dynamic query with the where clause with the input where clause.

The table has minimum more than 200000 documents.

So I don't know how to make the index.

I had a discussion and some were saying that I can have index for all the fields.

Can any one tell me how to resolve this?

Thanks a lot.


Study the clustered,nonclustered and covering indexes in BOL....
Making a clustered index on the columns in where clause will improve performance

Regards,
Sqlfrenzy
rvphx
rvphx
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 296
I would stick with the Design Guidelines for using clustered/non clustered indexes that appears on MSDN. It should give a pretty good idea as to where and what kind of index you might want to use. :-)
rvphx
rvphx
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 296
I was just curious about the columns that you might have in there! Could you provide us some detail about that (It would be great for me since I am new to this field and exploring things :coolSmile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search