Insert Taking A Long Time

Question

Insert Taking A Long Time

TonyD-431511

SSC-Addicted

Points: 465
More actions
March 18, 2008 at 1:39 pm

#121161

Hi All,
I'm having a problem with a DTS that use to work fine but now is incredibly slow. It use to take 1 to 2 hours to run and now it takes 6 to 8 hours. Something literally changed overnight because one day it took 2 hours and the next day it took 6 hours. The DTS queries an older database system (non-microsoft product) and inserts the data into a table in SQL Server.
Since the purpose of the data is just to create a daily report, all the data in the table is deleted prior to execution. It is inserting around 200,000 rows each time it runs and this number hasn't changed much (even when it worked correctly).
Originally I assumed it was the query running against the older system that was slowing it down but I've run that query successfully in 30 minutes using the products "query analyzer." I monitored it with SQL Query Profiler and the Insert definitly took the longest to complete. I re-created the DTS on a different server and it was roughly the same amount of time to complete. I tried changing the transformation settings to a bulk copy and set the options "Use fast load" and unchecked the "Check constraints" setting and it still took around the same amount of time.
I'm now stuck so any ideas will be greatly appreciated!

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Just to be sure we are on the same page. When you say you delete the data in the destination table, are you doing a DELETE or TRUNCATE?

Have you tried dropping all the indexes on the destination table, do the import, then rebuild the indexes on the destination table?

There is a specific order for dropping and creating the indexes: drop all nonclustered indexes first, then the clustered index (if there is one),... do the import,... create the clustered index, create the nonclustered indexes

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Lynn Pettis (3/18/2008)
Just to be sure we are on the same page. When you say you delete the data in the destination table, are you doing a DELETE or TRUNCATE?
Have you tried dropping all the indexes on the destination table, do the import, then rebuild the indexes on the destination table?
There is a specific order for dropping and creating the indexes: drop all nonclustered indexes first, then the clustered index (if there is one),... do the import,... create the clustered index, create the nonclustered indexes
😎

I'd agree with the indexes with a caveat: it is at times faster to leave the CLUSTERED index alone (meaning - do the insert while the clustered index is active). The non-clustered should be dropped and rebuilt after the insert though.

This is one of those Your mileage may vary things. According to the one MS case study on the matter, they seem to think that it IS faster overall with the UCI still active. I've seen it go either way.

You're not shrinking databases (like this one, or TempDB), are you? You're not shrinking the logs either, are you? Since you're rebuilding this daily for a report - there's really no reason for it to be on Full recovery (assuming this is the only data in the DB). You may care to switch to bulk-logged or simple recovery.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

TonyD-431511 SSC-Addicted Points: 465 More actions · Answer 3

I'm deleting the data from the destination table (ie. delete tmpTable).

I just discovered yesterday that the table does not have an index so I created a clustered index on the table. I modified my DTS to execute a DBReindex after the delete but I haven't tried dropping and rebuilding the index.

The DBReindex had no effect on the completion time but I will try dropping and rebuilding this afternoon.

Thanks for the advice, I'll let you know it goes!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I think keeping or dropping the clustered index also depends on how the data is coming in. If the incoming data is already sorted based on the clustered index, keeping it would be okay, but if you start getting a lot of page splits because the data isn't sorted, then dropping the clustered index may be beneficial. Like you said, your milage may differ.

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Since it looks like you are doing a delete tmpTable, also try changing that to truncate tmpTable.

😎

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 6

Heh... the first indication of a problem would be the 1 or 2 hours that it originally took never mind the 6-8 it's taking now! 😉

You wrote...

The DTS queries an older database system (non-microsoft product)

... what would that "non-microsoft product" be?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TonyD-431511 SSC-Addicted Points: 465 More actions · Answer 7

Indeed the 1 to 2 hours is not good but I attributed that to the query of the "non-microsoft" system. The non-microsoft system in question would be MUMPS. When I run both queries in the DTS straight through a query analyzer for MUMPS they both take around 45 minutes to an hour to run. So I assumed the insert was probably taking around 30 to 45 minutes.

Now, the queries in MUMPS still runs in 30 to 45 minutes but the Insert seems to be what is taking the bulk of the time.

For testing purposes I created a DTS that just runs the 1 big query (70 fields) and it takes about 3 to 4 hours to run.

Here's what the new DTS does (that takes 3 to 4 hours to run):

Deletes tmpTable on Sql Server, Connects to MUMPS through ODBC, Transform Data Task runs SELECT query (70 fields) and Inserts the data into the tmpTable.

I've tried everyone's suggestions and ran the DTS last night on my testing DTS and it still ran in about 3 to 4 hours.

I'm going to create a new DTS that outputs to a text file instead of a database. I'm not entirely convinced yet that the problem doesn't reside on the MUMPS system.

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 8

Deletes tmpTable on Sql Server

By that, do you mean it uses a DROP TABLE command, A DELETE statement to remove all rows, or a TRUNCATE to remove all rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Are you doing any data transformations in the direct move to the tmpTable from the MUMPS system, or is it a straight transfer?

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Tony - any way to get MUMPS to "dump" that data out to a text file? How long does that take? Some of the ODBC implementations just were horrible performance-wise. Especially when converting from hierarchical to relational.

There are some truly high-speed way to import text-based files. It may be faster in the long run to export/import than to try the direct link thing.

I've seen BCP chew through a million record file in minutes, sometimes less. Even if it took 45 minutes for MUMPS to build the file, the import would likely take a fraction of that.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

TonyD-431511 SSC-Addicted Points: 465 More actions · Answer 11

By that, do you mean it uses a DROP TABLE command, A DELETE statement to remove all rows, or a TRUNCATE to remove all rows?

I've tried both a DELETE tmpTable and a TRUNCATE tmpTABLE. Neither have had an effect on performance.

Are you doing any data transformations in the direct move to the tmpTable from the MUMPS system, or is it a straight transfer?

No data transformations, just a query of the MUMPS system and all returned rows get inserted into the tmpTable.

TonyD-431511 SSC-Addicted Points: 465 More actions · Answer 12

Matt Miller (3/19/2008)
Tony - any way to get MUMPS to "dump" that data out to a text file? How long does that take?

Matt, thanks for that tip! I talked to the MUMPS system analyst and had her run the query directly on the server and output it to a txt file like you suggested. The query took 2 hours and 46 minutes to complete. That is just one of the two queries so it looks like the problem is on the MUMPS system after all. I'm relieved to know it's not a problem with the SQL Server.

Thanks everyone for the help and advice, it has been greatly appreciated!

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 13

Heh... guess that's why they call it "Mumps"... sounds like a real pain in the neck 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)