?
Solved

Indexing Tables to Handle Column Sorts

Posted on 2011-02-16
3
Medium Priority
?
758 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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

770 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