Re:Local SQL INSERT
Quote
In article <77dfbc$o...@forums.borland.com>, n...@yahoo.net (Nick) wrote:
> Hi Michael!
> Thanks for reply! Would this be any faster then just using TTable?
> The reason I wanted to do it via SQL is the speed. I need to insert
> thousands of records and diong it via TTable takes a bit of a time.
In that case, you'll find this approach faster:
var
i : integer;
begin
Query1.SQL.Clear;
Query1.Sql.Add('insert into TEST(ADATE,DESCRIPTION)');
Query1.Sql.Add(' Values(:ADATE,:DESC)');
Query1.Prepare;
for i:=1 to 1000 do
begin
Query1.ParamByName('ADATE').AsDate:=StrToDate('12/03/1998');
Query1.ParamByName('DESC').AsString:='whatever';
Query1.ExecSQL;
end;
Query1.Unprepare;
end;
note that, if any of those parameters remain constant, then only set them
up once outside of your loop, but after the Prepare statement. Thus:
var
i : integer;
begin
Query1.SQL.Clear;
Query1.Sql.Add('insert into TEST(ADATE,DESCRIPTION)');
Query1.Sql.Add(' Values(:ADATE,:DESC)');
Query1.Prepare;
Query1.ParamByName('DESC').AsString:=
'some text that never changes';
for i:=1 to 1000 do
begin
Query1.ParamByName('ADATE').AsDate:=StrToDate('12/03/1998');
Query1.ExecSQL;
end;
Query1.Unprepare;
end;
and finally, if you really need to wring all the speed out of it that you
can, replace the ParamByName('ADATE').AsDate type bits with
Params[0].AsDate
as Params is a zero-based index of the parameters parsed from the query. Of
course, this makes your code harder to read/maintain and might cause bugs
to creep in should you change your SQL later on in life, but it does mean
that Delphi isn't scanning the Params list for an entry 'ADATE'.
You must remember to call Unprepare after you've done though (and before
you do a Query1.SQL.Clear, more SQL etc).
Hope that helps
Rob
--
r...@robsoft.co.uk
http://www.robsoft.co.uk
