Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Confusion in creating indexes

 
Aniruddh Joshi
Ranch Hand
Posts: 275
Eclipse IDE jQuery Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All

This is regarding indexes in MSSQL.
I have a query which takes long time ( while populating data from ResultSet ) when large number of users are online.
It is a select query with some joins in it.

I wanted to index all columns in the joins and in the where clause.
When I open MSSQL studio, and see the indexes for a table it shows some of these columns in the indexes as a primary key.

My doubt is that are all the primary keys indexed by default or do I need to index the columns explicitly.

Please help me by clarifying it. Also, what are the probable reasons of a query taking long only when the number of concurrent users are higher.


The primary keys are composit... found this about clustered indexes
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

My doubt is that are all the primary keys indexed by default or do I need to index the columns explicitly.

SQL Server will create an index by default when you define a primary key constraint so no.


Please help me by clarifying it. Also, what are the probable reasons of a query taking long only when the number of concurrent users are higher.

Your server is doing more work, simple as that. Have you run your query through the analyser to see if SQL Server suggests any indices?
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Creating index on all join columns may not lead you to best results. An index is most useful when data in index column has a higher number of distinct values. RDBMS creates unique index on PK constraints. While creating an index if index values are as many as table rows which means that every column data is unique then it is mostly likely that you will not get your query time boosted. An index is a smart way of accessing rows faster than full table scan.

Most database -I know MySQL and Oracle does- provides analyze tools for sql optimization, this tool shows how RDBMS is joining tables and accessing rows, either using an index or full table scan and how much it costs. This is a general perception not specific to Ms Sql Server, hope it helps.

Regards,

Fatih.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic