[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

indexing for business intelligence

Posted on 2012-09-14
6
Medium Priority
?
632 Views
Last Modified: 2012-09-30
I've recently completed a project that will allow a client to upload data from several different legacy systems to SQL Server.

They have a BI package called SpotFire, and many of the queries they are running are taking inordinately long (10+ minutes) to run.  At the moment, I do not have any indices configured for the SQL data, and know I need to do so, so I have asked them to identify the fields that they most commonly use for their queries, so I can start with those.

Anyone have any other recommendations?
0
Comment
Question by:Dale Fye
  • 3
  • 3
6 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38398445
Start examining the actual SQL that that is hitting the SQL Server - you can possibly capture this using SQL Profiler and putting a filter on the Application field for the Spotfire application.
Maybe manually run some of this SQL to see that it taking a long time and that it is not some other performance bottleneck in the network or in the Spotfire application.
Maybe try and prioritize what is most important to the business users to see what type of queries that they may need most urgently - no point at first improving the performance of a query that may not be representative of what is mostly run and that may only be run very seldom.
When you have some of the important SQL queries, you can manually run these in SQL Server and in the Query menu switch on to Display the Actual Execution plan.
This will show potential bottlenecks in the query processing and recommend possible indexes.
You can also use the Database Engine Tuning Advisor - feed a SQL query into it and it will make recommendations.
Also explore partitioning large fact tables
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38398452
If you have some of the badly performing SQL already and are allowed to post it up, maybe do this and we may be able to make some general recommendations - Is the SQL auto-generated by Spotfire? - Is there any way to control this?
0
 
LVL 49

Author Comment

by:Dale Fye
ID: 38398500
Thanks for your input.

I don't know enough about SpotFire yet.  Have some experimenting to do on those lines.  Part of it may be some of the "queries" that the client is building, and I will have to look at those.

My plan was to:
1.  Look at the fields they are using to aggregate by (either by their telling me, or by looking at the queries themselves).
2.  Do some performance monitoring, as you suggested above, using SQL Profiler.  But was not aware of the Database Engine Tuning Advisor, so will also take a look at that.

It may be that I can do some of the aggregating ahead of time and build some tables or views for them, which might also speed up some of the operations, but I don't want to do too much of that because that could destroy the drill down capabilities of the application.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Accepted Solution

by:
Barry Cunney earned 2000 total points
ID: 38398592
Yes if your users are looking for as an example aggregates by month, maybe they need to see something like Total Sales for Jan 2012, Feb 2012, Mar 2012 etc. then you could design a stored procedure that populates a separate Month Sales Totals Summary table from the main fact table which stores sales by day - and then schedule the execution of this stored procedure in a job the runs at night.
But then the question is if there is a way to get Spotfire to look at this Month Sales Totals Summary table when required.
Also you have to think about if the situation arises if you need to repopulate this summary table - like for example say in September some issue was noticed with data previously loaded for March and this data needed to be reloaded, but if business users had already signed off Monthly Summary reports for March, this is more a business process concern, but these are the types of things to be aware of and to consider.
0
 
LVL 49

Author Comment

by:Dale Fye
ID: 38445696
have not had an opportunity to get to this yet.  But hope to in the next week or so.
0
 
LVL 49

Author Closing Comment

by:Dale Fye
ID: 38449093
Thanks for your input.  I added several indices based on some of the queries the client was using and that seemed to have helped significantly.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Introduction to Processes
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

867 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