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: "Ed Avis" <eda at waniasset dot com>
Subject: RE: Is there a performance advantage to using placeholders with Sybase?
Date: Jan 8 2008 2:54PM

Steven Wechsler wrote:

>If not, although writing a stored proc beforehand isn't 
>practical, what I'm doing is simple enough that I could 
>generate a stored proc on the fly, use do() repeatedly and 
>then drop it at the end. Presumably that would be better than 
>continual $dbh->do("insert table_x (select * from table_y 
>where ...)") - right?

Another thing you could try is rephrasing what you want to do as a single query.  Even if you have to first construct a temporary table with the values you need it may still work out faster, and if you have a lot of values then you could bcp them in to a staging table.

As a very noddy example to explain what I mean, you could turn

    insert into x (select * from y where a = 1)
    insert into x (select * from y where a = 2)
    insert into x (select * from y where a = 3)

into something like

    create table #staging (a int not null)
    insert into #staging values (1)
    insert into #staging values (2)
    insert into #staging values (3)
    insert into x (select y.* from y join staging on y.a = staging.a)

Here the number of batches has also been reduced by sending several SQL statements in the same batch.  This really is faster, especially if you have a high latency network link.  Instead of making a temp table and a huge lump of SQL to populate it, you could make a text file containing the numbers 1 2 3 and bcp it into a table.

The key speed improvement though is probably having a single insert statement so the database can build up steam and insert a large number of values all at once rather than piecemeal.

Not every one of these changes is certain to give an improvement, but do try it.  Give the server a big lump to do rather than death by a thousand cuts.

Ed Avis 

This email has been scanned by the MessageLabs Email Security System.
For more information please visit