Database sorting SQL Server

views:

answers:

+1 Q:

Database sorting SQL Server

Hi folks! I made a windows app and I need to add a "sort" by selected column. The grid has 4 columns and lot of records (paged of course).

The question is:

Should I consider to add an index for every column that need sorting in the grid?

thanks in advances.

Regards

MSSQL has clustered indexes which improves order BY http://www.databasejournal.com/features/mssql/article.php/1443581/Index-Optimization-Tips.htm

You can have only one clustered index per table :-(

Your indexes must fit in RAM.

iddqd 2010-10-12 21:56:55

+1 A:

Choices of index depend on a lot of factors, including frequency of insert/update/delete queries vs. frequency of select queries. Indexes can help with ORDER BY, and four indexes may not be too bad for performance otherwise, but it really depends what you are trying to accomplish.

Andrew 2010-10-12 21:59:45

+2 A:

Yes - consider adding the indexes.

The only way to be sure that things you do for performance reasons really help is to test them. But if there's lots of data in the table and people are likely to use the sorting on all those columns, then I'd add the indexes.

Rory 2010-10-12 21:59:56

No, for big tables indexing doesn't improve sorting. Full scans are better than index scan + full scan.

iddqd 2010-10-12 22:09:45

@iddqd - Indexes do improve sorting. Indexes are pre-sorted so avoids the need to sort the whole table on demand.

Martin Smith 2010-10-12 22:14:09

@Martin Smith INDEXes are useless for large tables (OLTP databases), If you have a lot of DML queries are maintenance costs are very high. If INDEXes exceed some size limit, the random reads kills every server . You can try http://www.codeproject.com/kb/aspnet/PagingLarge.aspx but i'm 100% sure at some point, paging time will grow to very big values.

iddqd 2010-10-13 06:52:36

@iddqd - What are you talking about? Why would you need to do an "index scan + full scan"? Why would you need to do random reads rather than sequential? (maybe some bookmark lookups if the index is not covering but only for the page to be returned) How can not having to sort the entire table every time possibly not be of benefit to this query? Are you sure you know how indexes are structured in SQL Server?

Martin Smith 2010-10-13 07:34:26

I know very well how indexes are structured in MSSQL,Sybase Oracle, Postgresql, MySQL. Random reads - for nonclustered indexes You need to hit data from beggining, middle,end of PHYSICAL storage even You have composite covered indexes.

iddqd 2010-10-13 08:28:04

ansaurus

tags:

views:

answers:

Database sorting SQL Server

related questions