May 28, 2002 at 8:25 am
Hi,
I am currently loading our datawarehouse via stored procedures that "push" data from our staging dB [SQL server] and loads into our data warehouse dB [SQL server].
Background info: We have a 10/100Base network, running Microsoft 2000 Server. I am connecting through ODBC to dB servers. And finally I execute the package from a client.
Can anyone advise on best pratices to move the data and how I can get the best performance. I just read where it took someone 26 minutes to move 2 million rows. It takes me 5 hours to move 200,000 records. The data bases have the same schema but reside on different servers. Also, the sprocs are not that complicated and are written pretty efficiently. Please help!!!!
Christine
Aurora
May 28, 2002 at 1:34 pm
Christine,
If you are doing transformations during this process you will definitely slow things down. Best bet is to do a BCP of Bulk Insert type operation from source server to destination server and then do transformations there.
In order to do that you will have to create an identical table on your destination server (duplicate storage) and then pump the data across either with script or DTS. Should be much faster than what you are reporting now.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 28, 2002 at 1:44 pm
Thanks for your response. Sadly though, I am not doing ANY transformations and it still takes this long. Could it be issues not even related to SQL Server i.e. network? Or could it be how I connect to the other server? Please help,
Christine
Aurora
May 28, 2002 at 3:10 pm
If your databases are identical just back up the database on the data source server, and restore the database on the target server.
It should take about 5 minutes to move 2 milliion records. 🙂
Good Luck
Dan
May 29, 2002 at 8:05 am
Are you using bcp? Do you have a lot of indexes that need to be built?
May 29, 2002 at 8:15 am
I am moving data from a staging dB to a datawarehouse, I gave the wrong information above, I actually do a few data type conversions but that's all. I am moving from SQL server to SQL Server. There is the staging (server) which houses the staging dB's and then there is the DW server which houses the datawarehouse dB. It takes me over 5 hours just to move one table with approx. 230000 records in it. That's way too long. And moreover, I can move (whole database) data (via DTS, straight copy tables) from an ASA/Sybase dB server into our SQL dB server in 2 hours (This is how I get the repository data into our staging area). My problem is when I'm ready to move the data into our SQL server datawarehouse it takes almost 12 hours. What am I missing?
Aurora
May 29, 2002 at 8:29 am
You'll get the max speed by removing all indexes from the destination table and using BCP with a large batch size. Given that the performance using DTS is acceptable (more or less) I'd say that rules out network issues. If you're executing one stored proc call per row it will take approximately forever. Why wouldnt you use DTS and do your transformations there?
Andy
May 29, 2002 at 8:37 am
See also thread http://qa.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4479&FORUM_ID=19&CAT_ID=3&Topic_Title=Improve+Time&Forum_Title=Data+Transformation+Services+%28DTS%29 is related with some more info.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 29, 2002 at 10:15 am
Yes Antares686, I started that thread as well. Discussing the very same issue 🙂
Thanks
Aurora
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply