April 21, 2009 at 6:46 am
Hi,
We have a replicated database from a filemaker application that we use to run reports. The SQL replication is not a properly relational database so we are looking to optimise it so that we can ready it for use in Analysis Services, Reporting Services and Excel Services in Sharepoint.
All links in the database are from multiple fields and not from unique keys.
There are some indexes present in the database but not all tables have been indexed.
Some of the tables are in excess of 50 million records so reindexing would be a nightmare.
In order to maintain the replication from Filemaker we empty the tables and refill them on a weekly basis, making Unique Identifiers hard to maintain.
None of the existing system is my doing, I am just trying to make the best of what we have here.
Any help would be appreciated.
April 21, 2009 at 8:32 am
That's tough.
Without rearchitecting... I'd suggest setting up server side traces and capture query performance behavior. Then, go after the top poor performers. I'd focus on establishing a good set of clustered indexes. Watch to see if there is a pattern to the data access where certain values & keys are used more than others. These may be good candidates for the clustered indexes. Monitor, test, implement and repeat.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2009 at 9:17 am
Re-architecting is not a problem providing we can leave the existing structure as a data warehouse and have the data flow into the new structure through triggers and stored procedures.
April 21, 2009 at 9:25 am
OK. Well you have two choices. Stop reloading it every week and use some unique identifiers for the transfer process, or build the unique identifiers off of natural keys. I assumed the natural key and no rearchitecting. Either way, you'll need to look at the access patterns & figure out how best to build the clustered indexes and then look into building nonclustered indexes. Don't assume that the cluster and the PK have to be the same, but they frequently will be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2009 at 1:43 am
Thanks for the advice Grant, I will look at starting to implement this today and see how we go.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply