• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
  • Scott Selikoff
Bartenders:
  • Piet Souris
  • Jj Roberts
  • fred rosenberger

Confusion in creating indexes

 
Ranch Hand
Posts: 275
jQuery Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
pie. tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic