[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Indexing Tables to Handle Column Sorts

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

656 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