October 16, 2002 at 8:19 am
Scenario:
1) Transactional Database: Holds several tables, but 2 inpaticular are hit every 15 minutes and on average contain over 3 million records.
2) Reporting Database: I have a stored procedure that grabs data (on a weekly basis) from the transactional database and drops the data into 3 newly created tables in this reporting database. My stored procedure does a lot of joins with other tables while this process is running. The procedure then creates indexes on all the reporting tables.
3) Reports: Reports are then created utilizing an application which creats crystal report generated .htm and .pdf files. The queries within crystal are very basic doing only grouping and summary work.
Question: What should I do to optimize this process. It takes a few hours and I really need to get it down.
Remember: This is only performed on a weekly and quarterly basis. I create 3 new tables (flat) that holds information for the crystal reports to pull data from.
Should I be using the Index Tuning Wizard? If so, what else can I do to optimize this.
Thanks for any help you can give me.
October 16, 2002 at 8:44 am
Hard to say with out seeing your code. You can copy millions of rows in seconds. Is your cpu, io, memory maxed out?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 16, 2002 at 8:48 am
If the slow part is when you move the data (item 2) you can try using a data pump in a DTS package. That could make moving the data faster.
If you are experiencing slowness for the reports (item 3) then it would be a good idea to do an index analysis to make sure you have all the indexes you need.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 16, 2002 at 4:37 pm
Quickest way to copy data from db to db is select into, from server to server is bcp out and then bulk insert in. Putting exclusive tablelocks speeds things up as well.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 16, 2002 at 5:55 pm
What part of the process is taking so much time ?
The systems fine with no users loggged in. Can we keep it that way ?br>
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply