Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Confusion in creating indexes

 
Ranch Hand
Posts: 275
jQuery Eclipse IDE 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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE 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?
 
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.
 
    Bookmark Topic Watch Topic
  • New Topic