Board index » delphi » Storing 120'000 records in SQL Server 7

Storing 120'000 records in SQL Server 7

Folks,

I'm trying to store 120'000 records in a SQL Server 7 database - I
need to check for the existance of the entry and if so, I need to
increment some numbers - if the records doesn't exist yet, I insert a
new one.

It's fairly straightforward, two tables involved, no BLOBs or
anything, and I'm calling a stored proc from Delphi. I am taking
control of the transaction handling by issuing BeginTrans and
CommitTrans in batches of about 1000 records.

Still, with all those things in place, the insert takes more than 7.5
hours !! I must be missing something really big here....... any ideas?
I've stuffed millions of rows into Oracle in less time than that ;-))

Marc

------------------------------------------------------------------------
Marc Scheuner                                          Software Engineer
FastLane Technologies Inc.                  Halifax, Nova Scotia, Canada
Email: mscheu...@fastlane.com                    http://www.fastlane.com

 

Re:Storing 120'000 records in SQL Server 7


Marc,

Another thing to check are your indices. I had a was using a tquery to
insert records (not that many, I admit) with similar rules and it was taking
around 10 minutes. I put the statements into the query analyzer and asked it
to do an index analysis. It had a couple of changes it want to make and I
let it. After that was done the time was less than a second.

Mike Walsh

Quote
"Marc Scheuner" <mscheu...@no.spam.for.me.ca> wrote in message

news:nv0kbtocii175vvr0p7kbh1ehau43kl1vi@4ax.com...
Quote
> Folks,

> I'm trying to store 120'000 records in a SQL Server 7 database - I
> need to check for the existance of the entry and if so, I need to
> increment some numbers - if the records doesn't exist yet, I insert a
> new one.

> It's fairly straightforward, two tables involved, no BLOBs or
> anything, and I'm calling a stored proc from Delphi. I am taking
> control of the transaction handling by issuing BeginTrans and
> CommitTrans in batches of about 1000 records.

> Still, with all those things in place, the insert takes more than 7.5
> hours !! I must be missing something really big here....... any ideas?
> I've stuffed millions of rows into Oracle in less time than that ;-))

> Marc

> ------------------------------------------------------------------------
> Marc Scheuner                                          Software Engineer
> FastLane Technologies Inc.                  Halifax, Nova Scotia, Canada
> Email: mscheu...@fastlane.com                    http://www.fastlane.com

Re:Storing 120'000 records in SQL Server 7


"Marc Scheuner" <mscheu...@no.spam.for.me.ca> wrote

Quote
> I'm trying to store 120'000 records in a SQL Server 7 database - I
> need to check for the existance of the entry and if so, I need to
> increment some numbers - if the records doesn't exist yet, I insert a
> new one.

> It's fairly straightforward, two tables involved, no BLOBs or
> anything, and I'm calling a stored proc from Delphi. I am taking
> control of the transaction handling by issuing BeginTrans and
> CommitTrans in batches of about 1000 records.

> Still, with all those things in place, the insert takes more than 7.5
> hours !! I must be missing something really big here....... any ideas?
> I've stuffed millions of rows into Oracle in less time than that ;-))

Mark,
Here are some of the alternatives available.

Method 1)
You have to use Batch Updates. Try inserting a multiple of records in a
batch say 200 using a parametrzed query:

        insert into <table> values (?, ?, ?, ..)

Then Commit after a batch of 100 or so. Fisrt try without the Updates, just
plain inserts. Then create an index on the columns you are using to find the
rows. Then try using the insert or update technique.

Method 2)
Use TBatchMove to copy the table into a temporary table. Then use a stored
proc or a qery to update the actual table. This is a better aproach if you
know the specific table you are updating.

Method 3)
Use bcp to copy the table instead of TBatchMove in method 2. This would be
complete in less than 20 mins. Also, you can use the BULK COPY TSQL
statement to insert from a file into a table. Putting the file on the Server
itself would yield some improvements.

Method 4)
Use DTS.

Make sure your server is not running in the default setup settings. Use the
profiler to see whats taking a lot of time.

Goodluck,
-- Reddy Palle.

Re:Storing 120'000 records in SQL Server 7


Whats the source? What are using to identify whether the record exists or
not? Have you run profiler to see where the app is spending all its time?

Your numbers appear to be way off. Using DTS I can get SQL Server to do 20
million + transformed inserts an hour or 2 million transformed updates an
hour, on a 4 way with 2GB of RAM. However both memory and CPU are at 50%.

The key is to identify the inserts and process them as a special case, SQL
Server includes a load of special code paths for high performance, minimally
logged inserts. But there are some pretty severe limitations as to what you
need to do to get this to work.

Post more info and I can pretty much guarantee to get the time down. If you
want to see how to use DTS from Delphi I posted some sample code in
.attachements earlier this week.

-Euan

Quote
"Marc Scheuner" <mscheu...@no.spam.for.me.ca> wrote in message

news:nv0kbtocii175vvr0p7kbh1ehau43kl1vi@4ax.com...
Quote
> Folks,

> I'm trying to store 120'000 records in a SQL Server 7 database - I
> need to check for the existance of the entry and if so, I need to
> increment some numbers - if the records doesn't exist yet, I insert a
> new one.

> It's fairly straightforward, two tables involved, no BLOBs or
> anything, and I'm calling a stored proc from Delphi. I am taking
> control of the transaction handling by issuing BeginTrans and
> CommitTrans in batches of about 1000 records.

> Still, with all those things in place, the insert takes more than 7.5
> hours !! I must be missing something really big here....... any ideas?
> I've stuffed millions of rows into Oracle in less time than that ;-))

> Marc

> ------------------------------------------------------------------------
> Marc Scheuner                                          Software Engineer
> FastLane Technologies Inc.                  Halifax, Nova Scotia, Canada
> Email: mscheu...@fastlane.com                    http://www.fastlane.com

Re:Storing 120'000 records in SQL Server 7


On Thu, 22 Mar 2001 14:38:54 -0500, "Mike Walsh" <te...@msllib.com>
wrote:

Quote
>Another thing to check are your indices. I had a was using a tquery to
>insert records (not that many, I admit) with similar rules and it was taking
>around 10 minutes.

Yeah, I thought of that, too, but both tables I'm inserting into only
have a primary index on the ID field (an int). That shouldn't be
costing that much performance, eh?

Marc

------------------------------------------------------------------------
Marc Scheuner                                          Software Engineer
FastLane Technologies Inc.                  Halifax, Nova Scotia, Canada
Email: mscheu...@fastlane.com                    http://www.fastlane.com

Re:Storing 120'000 records in SQL Server 7


Maybe switching-off any indexes before the bulk insert and back on
after the bulk insert helps to speed things up.

Joost

Quote
"Marc Scheuner" <mscheu...@no.spam.for.me.ca> wrote in message

news:nv0kbtocii175vvr0p7kbh1ehau43kl1vi@4ax.com...
Quote
> Folks,

> I'm trying to store 120'000 records in a SQL Server 7 database - I
> need to check for the existance of the entry and if so, I need to
> increment some numbers - if the records doesn't exist yet, I insert a
> new one.

> It's fairly straightforward, two tables involved, no BLOBs or
> anything, and I'm calling a stored proc from Delphi. I am taking
> control of the transaction handling by issuing BeginTrans and
> CommitTrans in batches of about 1000 records.

> Still, with all those things in place, the insert takes more than 7.5
> hours !! I must be missing something really big here....... any ideas?
> I've stuffed millions of rows into Oracle in less time than that ;-))

> Marc

> ------------------------------------------------------------------------
> Marc Scheuner                                          Software Engineer
> FastLane Technologies Inc.                  Halifax, Nova Scotia, Canada
> Email: mscheu...@fastlane.com                    http://www.fastlane.com

Re:Storing 120'000 records in SQL Server 7


On Thu, 22 Mar 2001 22:36:04 -0800, "Euan Garden"

Quote
<euan.gar...@spicedham.usa.net> wrote:
>Whats the source?

Parsed textual log files.

Quote
>What are using to identify whether the record exists or not?

By checking for its existance based on the primary key:

if exists (select serverid from t_server where servername =
@servername)

Something like that.

Quote
>Using DTS I can get SQL Server to do 20 million + transformed inserts an hour
>or 2 million transformed updates an hour, on a 4 way with 2GB of RAM.

Okay, so inserting seems to be a whole lot faster than updating.
hmmm...

Also, I'm running this on my dev machine - 1 x 600 MHz Pentium III,
with 256 MB of RAM and lots of other stuff happening...

Quote
>Post more info and I can pretty much guarantee to get the time down. If you
>want to see how to use DTS from Delphi I posted some sample code in
>.attachements earlier this week.

I'll see if I can find that, thanks!

Marc

------------------------------------------------------------------------
Marc Scheuner                                          Software Engineer
FastLane Technologies Inc.                  Halifax, Nova Scotia, Canada
Email: mscheu...@fastlane.com                    http://www.fastlane.com

Re:Storing 120'000 records in SQL Server 7


On Thu, 22 Mar 2001 13:01:42 -0500, "Reddy Palle"

Quote
<pvre...@rocketmail.com> wrote:
>Method 1)
>Then Commit after a batch of 100 or so.

Ahem.....I'm already doing that - I am inserting the records in
batches of 1000 and then committing the transaction.

Quote
>Method 3)
>Use bcp to copy the table instead of TBatchMove in method 2. This would be
>complete in less than 20 mins. Also, you can use the BULK COPY TSQL
>statement to insert from a file into a table. Putting the file on the Server
>itself would yield some improvements.

I was wondering if that might be a solution - it would have to be into
a temporary table, however, since I always need to take into account
the possibility that a records for a given server and day already
exists - in that case, I need to update some stats, if it doesn't
exist, I need to insert a new row.

Marc

------------------------------------------------------------------------
Marc Scheuner                                          Software Engineer
FastLane Technologies Inc.                  Halifax, Nova Scotia, Canada
Email: mscheu...@fastlane.com                    http://www.fastlane.com

Other Threads