wood burning stoves 2.0*
The moose likes JDBC and the fly likes Confusion in creating indexes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Confusion in creating indexes" Watch "Confusion in creating indexes" New topic
Author

Confusion in creating indexes

Aniruddh Joshi
Ranch Hand

Joined: Jul 29, 2008
Posts: 275

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


Anrd
"One of the best things you could do is to simplify a larger application into a smaller one by reducing its process and complexity - Fowler"
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Confusion in creating indexes