Running Insert Script

Question

Running Insert Script

Vítor Nuno Viana Ferreira

SSC Veteran

Points: 255
More actions
July 20, 2005 at 4:45 am

#61999

Hi
I'm using an Insert Script inside a DTS Execute SQL Task, but it's running realy slow. I tried the same in Query Analyser and it's running as slow as in the DTS. When i say slow it's like 1 row each 2 seconds. I'm talking in a table with 7 or 8 fields.
My insert instruction it's like "INSERT «TABLE_NAME» VALUES («VALUE1»,«VALUE2»,...).
What should i do to make it run faster, some Hints would help?
Thanks,
Vítor

Viewing 8 posts - 1 through 7 (of 7 total)

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

Peter Williams SSC Veteran Points: 217 More actions · Answer 1

I'd check what's going on behind the scenes on the server itself! Is it just this particular insert statement which is running slow, or is it everything in general??

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 2

It sounds like you have something going on in the background and whatever you do in dts won't help a great deal.

Have you got any complex triggers set on the table you are inserting into?

That would slow things down a lot and they cannot be bypassed without disabling first.

Put a trace on so you can see exactly what is happening.

------------------------------
The Users are always right - when I'm not wrong!

Vítor Nuno Viana Ferreira SSC Veteran Points: 255 More actions · Answer 3

Hi,

I will try to explain whay my DTS is doing.

I'm loading some files that have the DROP/ CREATE of a table and the insert script. I'm loading the Script of the file to an Execute SQL Task, as i have more then one file to load my DTS is looping until all my files are loaded.

Some os the scripts are loading real fast, but some others are as i explained previously.

Has i have told, i tried to run in query analyser and it needed almost the same time, but now it runnes has i will expect. But in DTS is still running very slowly!!I just don't understant why!?

I don't have any triggers, no index, the tables doesn't have locks as i am droping and creating at that exact moment, what could be happening?

Is there any other why of doing it?

Thanks, Vítor

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 4

Sounds very mysterious.

Could be something to do with dropping and recreating the tables.

Perhaps truncating would help. Will definitley be quicker.

------------------------------
The Users are always right - when I'm not wrong!

Vítor Nuno Viana Ferreira SSC Veteran Points: 255 More actions · Answer 5

But when i execute the script on query analyser, i'm dropping and creating the tables to!!!

I don't understand why this differences. Another difference is that, in query analyser i can run the script With "GO"'s in the middle, but if i don't remove it from Execute SQL task, it returns an error!

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 6

I'm all out of ideas. If all your packages are runing slowly, I would think about a reinstallation on the client that you are running the package on.

------------------------------
The Users are always right - when I'm not wrong!

Vítor Nuno Viana Ferreira SSC Veteran Points: 255 More actions · Answer 7

No, only this package is running slow, and not for all files... and i don't belive that's a client problem, because when i schedule a Job i got exactly the same problem.

I Just can't find some other why to do this process. I have tried the "master.dbo.xp_cmdshell " command, but i guess i'm not passing him the right parameters (master.dbo.xp_cmdshell "osql /E /d «db_name» /i «file_path»"), because he doesn't do anything!

Thanks,

Vítor