September 29, 2008 at 2:25 pm
Hi, I hope someone can please point me at best practice to assist in the following:
Scenario:
Requirement to replicate a legacy system in SQL 2000 each night...thereafter we rebuild a fact table and process via Analysis Services 2000 into several cubes for management next morning.
We also create adhoc views of the data to meet the ever changing wishes of our management teams!
Current Process:
All legacy system tables are currently replicated in SQL...with PK’s; legacy indexes and other indexes for our own reporting use. There are no FK constraints in SQL tables. The SQL database is for reporting purposes only and is in Simple recovery mode.
Using SQL2K DTS, we truncate the SQL tables, and then import the legacy tables using Transform Data Task (viaODBC)...into the pre existing indexed SQL tables.
After that we truncate the SQL fact table and repopulate via numerous stored procedures and then process the AS cubes.
The Issue:
The current process coupled with the volume of data being imported (80 million records over 12 main tables) means that the import alone is taking 10 hours.
The Question:
What is best practice....?
Truncate and reload as our current process
OR
Drop the SQL tables, recreate tables without any indexes, load the data and then recreate the indexes?
OR
Is there a better way entirely?
What other maintenance should we be performing on the SQL db to ensure best performance...ie possible fragmentation, optimising indexes etc
Preferred Solution:
Is to reduce the time to import the data and improve query performance from the SQL tables
Or is it just a question of “suck it and see”?
Many thanks for reading
gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 2, 2008 at 9:47 am
Hello,
Some comments:
It's better to truncate and have the index already defined when you insert data.
It's cleaner to drop and recreate the tables and indexes before you bcp in data but only marginally so.
Is the legacy system able to have additional programming installed so that only the records changed, deleted, or added can be identified? If they can then the volume to communicate between the systems can be reduced.
Does the legacy system have any odbc compliency? If so you may be able to develope a service orientated transaction posting process between the systems.
DTS is not as fast as bcp. If you write a truncate tsql script and execute it before you then bcp the data in, you should see a shorter time to load the data.
I hope this helps. Interesting situation for sure.
Regards,
Terry
October 3, 2008 at 4:44 am
Terry, many thanks for your comments
what I have done so far is to truncate the tables, drop indexes, DTS in and recreate indexes.
this is marginally faster than loading with indexes in place.
We are making changes to the legacy system to export only updates/inserts..but this will not cover all tables required.
bcp ....isnt that for text files only...I havent explored that yet.
But it takes the legacy system as long to produce a text file as it does for me to DTS anyway!
We have tried outputting directly from legacy system into SQL....became overly complicated and from what I saw was not any quicker.
Thanks again
Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 3, 2008 at 7:07 am
Hello,
bcp does use text files but loads data faster than DTS. DTS can reformat data though.
I use bcp to load about a 1/2 million records to 20 data bases. Each one has to be loaded separately. bcp loads the 400~500 K records in about 10 sec. Pretty speedy. bcp also had options for delimeter control so depending on how your legacy system presents the data, bcp can probably deal with it.
You might want to give it a test.
Regards,
Terry
October 3, 2008 at 9:50 am
to clarify...
bcp is quick, but I need a text file...yes?
(ie a table dump to text from the legacy system)
if this is the case, then I am stymied ....see above
Thanks for your continued interest Terry...much appreciated
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply