Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

indexing for business intelligence

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?
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

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
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?
Avatar of Dale Fye

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
have not had an opportunity to get to this yet.  But hope to in the next week or so.
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.