What fields should be indexed on a given table?

Question

I've a table with a lot of registers (more than 2 million). It's a transaction table but I need a report with a lot of joins. Whats the best practice to index that table because it's consuming too much time.

I'm paging the table using the storedprocedure paging method but I need an index because when I want to export the report I need to get the entire query without pagination and to get the total records I need a select all.

Any help?

Answer 1

Check your queries, and find which fields are used to match them. Those are usually the best candidates!

Answer 2

If I am not mistaken, please correct me if I am, I think you should create non-clustered Index on the fields of the conditions of the where clause. (Maybe this can be useful as a starting point to get some candidates for the indexes).

Good Luck

Answer 3

i did that but the execution plan still show Index Scan and should be a Index Seek.

Answer 4

if an Index Scan instead of a seek is performed, the cause might be that the fields are not in the correct order in the index.

Answer 5

SQL Server has a 'Database Engine Tuning Advisor' that could help you. This does not exist for SQL Server Express, but does for all other versions of SQL Server.

• Load your query in a query window.
• On the menu, click Query -> Analyze Query in Database Engine Tuning Advisor

The tuning advisor will identify indexes that could be added to your table(s) to improve performance. In my experience, the tuning advisor doesn't always help, but most of the time it does. It's where I suggest you start.

Answer 6

put indexes on all columns that you're joining and filtering on. the use of indexes is also determined by the selectivity of the indexed column. the best way would be to show us your query so we can try to improve it.

Answer 7

ok this is the query in doing

 SELECT 
  TBL.*
 FROM
  FOREINGDATABASE..TABLENAME TBL
   LEFT JOIN Status S 
     ON TBL.Status = S.Number
 WHERE
 (TBL.ID = CASE @Reference WHEN 0 THEN TBL.ID ELSE @Reference END) AND
 TBL.Date >= @FechaInicial AND 
 TBL.Date <= @FechaFinal AND
 (TBL.Channel = CASE @Canal WHEN '' THEN TBL.Channel ELSE @Canal END)AND
 (TBL.DocType = CASE @TipoDocumento WHEN '' THEN TBL.DocType ELSE @TipoDocumento END)AND
 (TBL.Document = CASE @NumDocumento WHEN '' THEN TBL.Document ELSE @NumDocumento END)AND
 (TBL.Login = CASE @Login WHEN '' THEN TBL.Login ELSE @Login END)AND
 (TBL.Login IS NULL AND TBL.Login <> 'dev' )  AND
 TBL.Status IN ('1','2')

key fields have to be everithing in the where clause ???

Answer 8

key fields have to be everithing in the where clause ???

No, that would be overkill. Indexing a field really only works if a) your WHERE clause is selective enough (that is: only selects out about 1-2% of the values; an index on a "Gender" field which can be only one of two or three possible values is pointless), and b) your WHERE clause doesn't involve function calls or other magic.

In your case, TBL.Status might be a candidate - how many possible values are there? You select the '1' and '2' value - if there are hundreds of possible values, then it's a good choice.

On a side note: this clause here: (TBL.Login IS NULL AND TBL.Login <> 'dev' ) is pretty pointless - if the value of TBL.login IS NULL, then it's DEFINITELY not 'dev' ..... so just the "IS NULL" will be more than sufficient......

The other field you might want to consider putting an index on is the TBL.Date, since you seem to select a range of dates here - that might be a good choice.

Also, on a general note: whenever possible, DO NOT use a SELECT * FROM ...... to select your fields. This causes a lot of overhead for SQL Server. SPECIFY your columns - and ONLY select those that you REALLY NEED - not just all of them for the heck of it.....

Marc