Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

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, 
     much faster.
     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).
     Good luck,
     Tim Green                              
     Administrative Solutions Group
     "An ADP/Mercer Alliance"

______________________________ Reply Separator _________________________________
Subject: Performance Question
Author: 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