Solved

Indexing Tables to Handle Column Sorts

Posted on 2011-02-16
3
753 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need return values from a stored procedure 8 62
MS SQL Conditional WHERE clause 3 47
sql query 5 56
Need more granular date groupings 4 44
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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