• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 761
  • Last Modified:

Indexing Tables to Handle Column Sorts

What is the proper way to handle large queries when column sorts are an option in a web application interface?  These queries have several joins.  The user has options to custom create reports by picking the columns the report requires so there is the potential for a column count of 20 or more as well.  Data in the tables frequently change from insertions, edits or deletes.
0
ws11
Asked:
ws11
  • 2
1 Solution
 
sunezapaCommented:
an index pr column that possible will be used in for sorting... it is no problem ;-9
if you are curious for what is costing time/cpu then investigate: start Activity Monitor for the the server (in SQL Server Management Studio), look at "recent Expensive Queries", right click an interesting query (for the actual database) and click "show Execution Plan" - and see details, eventuallm play with cost with and without indexing :-)
0
 
ws11Author Commented:
Indexing every field does not make sense to me.  That is one of the reasons I am asking this question.  What is the most efficent way of doing this.  I also wonder what the difference is in creating views for this with indexes.  Since the data is constantly be edited it is my understanding that you don't use indexed views.  But what is the difference in that and the index table that are constantly edited.
0
 
sunezapaCommented:
if you want a quick results you will generally add an index on every field in the table that will be used in a WHERE or a JOIN or an ORDER clause. So if users can create reports sorted by "any field" then every field should in principle  be indexed...?
I was not aware that you use a views for the user to select what columns to use or not, - indexed views can extend the speed in some cases, see http://technet.microsoft.com/en-us/library/cc917715.aspx - especially the "Tables can be prejoined and the resulting data set stored" could be at your interest?
Did you play with the Activity Monitor and the Execution Plans? it is a way to notice differences (find bottlenecks, create indexes, see improvements or the opposite)...
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now