Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
matty1stop
Starting Member
37 Posts |
 Posted - 2007-05-22 : 08:50:52
|
| I've got a view that is driven from a 80 million record table in a data warehouse. I am trying to populate an aggregate table in a datamart, but am running into preformance problems. The datamart table needs to be updated daily. I understand there are many factors that effect performance, but in general would the fastest approach be:1) Truncate the datamart table 2) Perform a bcp of the view to a text file 3) Bulk Insert to the datamart tableIf you need more information to answer this please let me know.Thanks,Matt |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-05-22 : 09:01:13
|
| One thing you can also try is to remove the indexes of your datamart table, insert your records to it, then re-create all your indexes.SQL Server Helperhttp://www.sql-server-helper.com |
 |
|
|
matty1stop
Starting Member
37 Posts |
Posted - 2007-05-22 : 09:19:59
|
| I'm running the bcp export now. It has been running for over an hour with no change to the size of the text file it is writing to. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-22 : 09:24:58
|
Do you have a last change datetime column in the source table ? If not why not create one ? This will help in determine which are the records that was modified since you last import to the target.
KH |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-22 : 10:56:28
|
| One thing that helped me one time was to not use a view, meaning, I just copied the sql into my script. I saw a signifacnt performance increase.Just something to try, not knowing your view or anything... |
|
|
|
matty1stop
Starting Member
37 Posts |
Posted - 2007-05-22 : 13:11:18
|
| The BCP finally finished. Took 3 hours to export 3 million recordsI was hoping for a way to somehow get this feed down to minutes instead of hours. |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-22 : 15:36:37
|
quote:
Originally posted by matty1stop
The BCP finally finished. Took 3 hours to export 3 million recordsI was hoping for a way to somehow get this feed down to minutes instead of hours.
Post your Sql. That's too long for inserts. Make sure to drop the indexes too, and then re-add them after the batch is done. |
|
|
|
matty1stop
Starting Member
37 Posts |
Posted - 2007-05-22 : 15:39:22
|
| That was 3 hours for the Export not the Insert. |
|
|
|
|
|
|
Fastest way to copy data?