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.
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email