sybperl-l Archive

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)
    go
    insert into x (select * from y where a = 2)
    go
    insert into x (select * from y where a = 3)
    go

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)
    go

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 http://www.messagelabs.com/email 
______________________________________________________________________