MarcoP wrote:
I'm looking for a little guidance here and hoping some one(s) can help out. I have a de-normalized table with 6 millions rows in it. I am utilizing server-side paging and the user can sort by any of the grid columns (10). Also, the user can search based on pretty much the same 10 or so fields.
Now, it is taking the query to run around a minute (Select Count(*) take 30 seconds and the actual select takes another 30). I think the bottle neck is the ordering by the column (when I remove ordering it is sub second). So I think I have three choices.
MarcoP wrote:
- Add a index for all search/order columns. Will this really make a difference with 6 million rows?
I think it will make a difference, for sure. I don't know what would be the cost of having a lot of indexes in your table. (some useful info).
MarcoP wrote:
- Pick a column and make it a clustered index and sort by this guy when fetching and not allow the user to sort server side.
- No sorting. Very confusing for the user when using paging.
Sometimes we offer the user the possibility to sort on any column, but in the real life just some of them are really sorted. So you can stick with the indispensable.
