sybperl-l Archive

From: "Wechsler, Steven" <Steven dot Wechsler at mtvstaff dot com>
Subject: RE: Is there a performance advantage to using placeholders with Sybase?
Date: Jan 8 2008 3:01PM

Well, unfortunately, that wouldn't work with what I'm trying to do:

I am working on a script to recover whatever data I can on a table that
has "692" errors (similar to the "605" error, the bane of every Sybase
DBA) by querying the corrupt table based on explicit key values and
retrieving each row that I can. This can't be batched in SQL because
once that error hits the SQL batch is terminated, but in Perl I can take
action based on that error (try to retrieve the data from a different
server which has an older version of the table).


Steve Wechsler | steven.wechsler@mtvstaff.com
Sybase/SQL Server/MySQL Database Administrator
212-846-5683

MTV Networks


-----Original Message-----
From: Ed Avis [mailto:eda@waniasset.com] 
Sent: Tuesday, January 08, 2008 9:55 AM
To: Wechsler, Steven; michael.x.peppler@jpmchase.com;
sybperl-l@peppler.org
Subject: RE: Is there a performance advantage to using placeholders with
Sybase?

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