Solved

Indexing Tables to Handle Column Sorts

Posted on 2011-02-16
3
747 Views
Last Modified: 2012-05-11
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
Comment
Question by:ws11
  • 2
3 Comments
 
LVL 3

Expert Comment

by:sunezapa
ID: 34911416
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
 

Author Comment

by:ws11
ID: 34925396
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
 
LVL 3

Accepted Solution

by:
sunezapa earned 500 total points
ID: 34925754
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question