Up Prev Next
From: Tim_Green at mercer dot com
Subject: Re: Performance Question
Date: Feb 18 1998 11:28PM
Personally, I doubt it's DBlib that's your bottleneck considering the
scenario you've described below.
Some things to look at:
1. Can you BCP the data to the table first, and then call your stored
procedure for each row? Or better yet, call a stored procedure once
that processes of all your new data all at once? This will be much,
2. Is your update statement and stored procedure using an index to
access the last row you've inserted, or are they table scanning? Look
at the query plan.
3. Is the update deferred, or in-place? If you can promote your
update to in-place you'll see large performance gains.
4. Is there a trigger on the table that's slowing things down?
5. If at all possible, combine the insert and update into a single
insert statement. If your update is deferred, the two statement will
use 3x the log space of a single insert.
6. What's the data distribution before and after? If your inserting
into an empty table (or the 25000 rows are significant compared to the
overall size of the table) each new row will make the statistics grow
more and more out of date. Update statistics every so often; every
1000 rows or so.
7. Are you executing the stored procedure with recompile? If you
update the statistics, the stored procedure won't use them unless
sp_recompile on the table or executed the procedure with recompile.
It's best to batch an sp_recompile with the periodic update of
statistics. BTW, an obvious symptom indicating you'll get improvement
update stats and recompiling is that with each row processed the
overall throughput decreases (e.g. 1st 1000 tows take 30 sec.; last
1000 take 5 min.)
8. Are the inserts grouped into 1 or at least multiple transactions?
If there is no explicit transaction, then each statement represents a
single implicit transaction which must be committed. Batching a group
of rows in a single transaction will improve performance considerably.
If you've got the log space, use one transaction for all rows between
your update statistic statements. Note that you can't do an update
statistics with a transaction open.
9. Execute more than one command with each call to dbsqlexec(). But
some simple error handling between each statement so it aborts the
batch if @@error is set and call dbsqlexec() with a whole bunch of sql
statements in the buffer. This will reduce the network overhead
considerably (though I doubt this is a big factor in your problem).
Tim Green Tim_Green@mercer.com
Administrative Solutions Group
"An ADP/Mercer Alliance"
______________________________ Reply Separator _________________________________
Subject: Performance Question
Author: SYBPERL-L@trln.lib.unc.edu at uucp
Date: 2/18/98 2:19 PM
I've just completed writing a data loader using Sybase::DBlib and want
to get more performance out of it. It does an INSERT, UPDATE then a
stored procedure for each line, there are >25,000 lines. This currently
takes 40mins to run and I believe the bottleneck is Sybase::DBlib or
the method I am calling it with since CPU on the client is low, and the
server doesn't have much load at all while this runs.
The way I am currently doing this is building each statment in a
scalar then using dbcmd, dbsqlexec, dbresults, dbcancel.
Couple of questions:
* Is there a significant performance improvement if I put all commands
in the scalar then loop through Sybase::DBlib executing each one?
* Is there a performance advantage to using Sybase::CTlib over
Jamie Thingelstad, Sr. VP & CIO (Fax) 612.338.0069 (Voice) 612.338.0049x24
Concerto Technologies 123 North 3rd St. Suite 300, Minneapolis, MN 55401