Board index » delphi » Slow params access with IBX, TIBQuery and TParams

Slow params access with IBX, TIBQuery and TParams

Hi all (and Jeff),

I have an insert statement with a large number of params.  I use code like:
q.sql.text := 'insert into table (c1, c2...) values (:c1, :c2...)';

q.prepare;
for i := 1 to aBillion do
begin
  q.Params[1].asInteger := 1;
  q.Params[2].asString := 'two';
  ...
  q.exec;
end;

When q is a TIBQuery, this code inserts records about 10 per second.  This
is very slow compared to a TIBSql which inserts at over 100 per second.  I
traced into the code to see what was taking so long, and it turns out the
Params[1].asXXX eventually calls ParamByName!  All this, of course, is
inside TParams (delphi stuff, not IB stuff), but I was wondering, if there
was anyway I could avoid this and still use TIBQuery.

Any of that make sense?  Thanks for all your comments.

-Andy

 

Re:Slow params access with IBX, TIBQuery and TParams


Quote
Andy Colson wrote:

> When q is a TIBQuery, this code inserts records about 10 per second.  This
> is very slow compared to a TIBSql which inserts at over 100 per second.  I
> traced into the code to see what was taking so long, and it turns out the
> Params[1].asXXX eventually calls ParamByName!  All this, of course, is
> inside TParams (delphi stuff, not IB stuff), but I was wondering, if there
> was anyway I could avoid this and still use TIBQuery.

Not that I am aware of.  I don't "own" the TParam code so there is nothing I can
do to change this behavior.

You can try IBDataset since it does not use TParams and I don't think it does
this.

Quote
> Any of that make sense?  Thanks for all your comments.

> -Andy

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
Anyone who cannot cope with mathematics is not fully human.
At best he is a tolerable subhuman who has learned to wear
shoes, bathe and not make messes in the house.   (Heinlein)

Re:Slow params access with IBX, TIBQuery and TParams


Hi Andy.

The params array is zero based.

Quote
"Andy Colson" <code...@cnip.net> wrote in message news:3b0d5c3c$1_1@dnews...
> Hi all (and Jeff),

> I have an insert statement with a large number of params.  I use code
like:
> q.sql.text := 'insert into table (c1, c2...) values (:c1, :c2...)';

> q.prepare;
> for i := 1 to aBillion do
> begin
>   q.Params[1].asInteger := 1;
>   q.Params[2].asString := 'two';
>   ...
>   q.exec;
> end;

> When q is a TIBQuery, this code inserts records about 10 per second.  This
> is very slow compared to a TIBSql which inserts at over 100 per second.  I
> traced into the code to see what was taking so long, and it turns out the
> Params[1].asXXX eventually calls ParamByName!  All this, of course, is
> inside TParams (delphi stuff, not IB stuff), but I was wondering, if there
> was anyway I could avoid this and still use TIBQuery.

> Any of that make sense?  Thanks for all your comments.

> -Andy

Other Threads