insert data in batches

Question

Post reply

insert data in batches

Sridhar-137443

Hall of Fame

Points: 3762
More actions
March 16, 2010 at 8:28 am

#219882

Hi,
we have a query that copies data from one table to another. Sometimes we copy like 25000 records. This copy is called from the visual basic application. It has a default timeout of 30 seconds. so sometimes it times out. so we are thinking of doing the copy in batches. I looked online to see if I can find any information on how to do it. But I couldn't find any information. can some one help me with this?
Thanks,
sridhar.

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Only 25,000 rows? (rows... not records) No way should that be taking 30 seconds. Try this.

INSERT INTO TargetTable (column list)

SELECT FROM SourceTable (column list)

WHERE (criteria for rows you want to copy)

Don't bounce result sets from the server to a client application and then back to the server. It is an atrocious waste of time and resources.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

LutzM SSC Guru Points: 107049 More actions · Answer 2

One more side effect of the "bouncing method": sometimes it's done row by row (get one row from the source table and insert it into the target table). That would make things even worse...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Hey Lutz 🙂 I beat you to the punch for once !! 😀

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

LutzM SSC Guru Points: 107049 More actions · Answer 4

The Dixie Flatline (3/16/2010)
Hey Lutz 🙂 I beat you to the punch for once !! 😀

You probably benefit from the time zone difference / foreigner issues I suffer...(takes longer to get the data to the server since it has to pass customs/NSA) 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 5

Hi,

Thanks for the reply. I am not sending the records back to client. It doesn't time out every time. Also the target table has lot of indexes.

Thanks,

sridhar.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Sorry, I misunderstood the role of the VB application. Is the visual basic application creating the query string and passing it? Or is it executing a stored procedure that does the copy?

Many indexes create a lot of maintenance overhead. Do you have other transactions inserting/updating/deleting rows at the same time?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 7

The vb application just calls the stored procedure. The stored procedure does the copy of records. As of now it is the only stored procedure that inserts data into the table.

LutzM SSC Guru Points: 107049 More actions · Answer 8

Would it be possible for you to post the procedure?

Mabe we can help to speed it up a little bit.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]