Alex
asked on
Delphi Firebird speed problem
Hi,
I develop an application in Delphi 7 using a firebird database. I have a big problem with speed in a select statement that i use after i insert into a table some records.I insert records into the table and everything is ok with speed if i don't select anything after this and doesn't matter how many records i have into this table but when i select some data because i want to present them into a dbgrid and to take some totals into editboxes and i have more than 500 records the query takes more than 4 seconds to executed!!!
This table could be have thousands of records and now i have more than 100,000 records.
Here is an example of the source code:
~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~
//here i insert records
ibquery2.SQL.Clear;
ibquery2.sql.Append('INSER T INTO table (var1,var2,var3) VALUES (test,test,test);
Ibquery2.ExecSQL;
ibtransaction1.Commit;
//here I select some data for the dbgrid and here starts the speed problem if I have many
//records into the table as i said before
//the query executed into 2 or 3 secs
ibquery2.sql.Text:='select id,pid,var1,var2,var3 from table where id=' + label5.Caption + ' order by id,pid ASC';
ibquery2.Open;
//and here the speed problem seems to be very big
//the query executed into 4 or 5 secs
ibquery4.SQL.Clear;
ibquery4.SQL.Text:='select sum(var1) from table where id=' +label5.Caption + ' AND level=1';
ibquery4.Open;
edit1.Text:=FormatFloat('# ,##0.00',i bquery4.Fi elds[0].Va lue);
edit7.Text:=FormatFloat('# ,##0.00',i bquery4.Fi elds[0].Va lue);
ibquery4.SQL.Clear;
ibquery4.SQL.Text:='select sum(var2) from table where id=' +label5.Caption + ' AND level=1';
ibquery4.Open;
edit2.Text:=FormatFloat('# ,##0.00',i bquery4.Fi elds[0].Va lue);
~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~
Help me about what i am doing wrong and i have these speed problems and what i have to do to fix this.
Thanks in advance.
I develop an application in Delphi 7 using a firebird database. I have a big problem with speed in a select statement that i use after i insert into a table some records.I insert records into the table and everything is ok with speed if i don't select anything after this and doesn't matter how many records i have into this table but when i select some data because i want to present them into a dbgrid and to take some totals into editboxes and i have more than 500 records the query takes more than 4 seconds to executed!!!
This table could be have thousands of records and now i have more than 100,000 records.
Here is an example of the source code:
~~~~~~~~~~~~~~~~~~~~~~~~~~
//here i insert records
ibquery2.SQL.Clear;
ibquery2.sql.Append('INSER
Ibquery2.ExecSQL;
ibtransaction1.Commit;
//here I select some data for the dbgrid and here starts the speed problem if I have many
//records into the table as i said before
//the query executed into 2 or 3 secs
ibquery2.sql.Text:='select
ibquery2.Open;
//and here the speed problem seems to be very big
//the query executed into 4 or 5 secs
ibquery4.SQL.Clear;
ibquery4.SQL.Text:='select
ibquery4.Open;
edit1.Text:=FormatFloat('#
edit7.Text:=FormatFloat('#
ibquery4.SQL.Clear;
ibquery4.SQL.Text:='select
ibquery4.Open;
edit2.Text:=FormatFloat('#
~~~~~~~~~~~~~~~~~~~~~~~~~~
Help me about what i am doing wrong and i have these speed problems and what i have to do to fix this.
Thanks in advance.
Sorry I meant I thought you used commit for transactions and I couldnt see a begin
ASKER
Hi mikelittlewood,
The problem is at the select so the ibtransaction1.Commit does not have any relationship with the speed problem.If i put the code with the select statements into comments and run only the insert everything is and if uncommented and run it i have speed problems.
----> Have you tried using a different query object for the second part of your code?
Yes and i have the same problem :(
The problem is at the select so the ibtransaction1.Commit does not have any relationship with the speed problem.If i put the code with the select statements into comments and run only the insert everything is and if uncommented and run it i have speed problems.
----> Have you tried using a different query object for the second part of your code?
Yes and i have the same problem :(
How about changing code to do the insert, and introduce parameters instead.
hmm are you sure this is right?
ibquery2.sql.Append('INSER T INTO table (var1,var2,var3) VALUES (test,test,test);
should it not be
ibquery2.sql.Append('INSER T INTO table (var1,var2,var3) VALUES (' + quotedStr('test') + ',' + quotedStr('test') + ',' + quotedStr('test') + ')');
I'm assuming you are inserting strings
hmm are you sure this is right?
ibquery2.sql.Append('INSER
should it not be
ibquery2.sql.Append('INSER
I'm assuming you are inserting strings
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
database:
bind variables
when using statement with bind variables only 1 query will get stored by the database,
if not all queries will be stored that are used
'select sum(var1) from table where id=' +label5.Caption + ' AND level=1';
should be changed to
'select sum(var1) from table where id=:id AND level=:alevel';
ParamByName('ID').AsIntege r := aId;
ParamByName('ALEVEL').AsIn teger := 1;
your insert statements should be changed likewise
this may not be seen on a small database,
but think of later.
let's say your queries will perform about .1 secs faster in general terms
just do that for 10x transactions per minute per 100x users
not really that much, but you just loose .1x10x100 secs per minute
slooooow database ? no: bad queries
bind variables
when using statement with bind variables only 1 query will get stored by the database,
if not all queries will be stored that are used
'select sum(var1) from table where id=' +label5.Caption + ' AND level=1';
should be changed to
'select sum(var1) from table where id=:id AND level=:alevel';
ParamByName('ID').AsIntege
ParamByName('ALEVEL').AsIn
your insert statements should be changed likewise
this may not be seen on a small database,
but think of later.
let's say your queries will perform about .1 secs faster in general terms
just do that for 10x transactions per minute per 100x users
not really that much, but you just loose .1x10x100 secs per minute
slooooow database ? no: bad queries
ASKER
mikelittlewood: yes i have quotedstr i remove them only to make the example more clear for all.
Geert_Gruwez: I try enable and disable controls but i have the same problem yet.
Do you have a primary key in the table ? <--- Yes i have a primary key
Do you have an index on the table <--- I think that the problem of the speed has to do with the indexes as i read something on the internet but i don't know things about the index could you please help me on this subject with the indexes what i have to do.
Geert_Gruwez: I try enable and disable controls but i have the same problem yet.
Do you have a primary key in the table ? <--- Yes i have a primary key
Do you have an index on the table <--- I think that the problem of the speed has to do with the indexes as i read something on the internet but i don't know things about the index could you please help me on this subject with the indexes what i have to do.
ASKER
and something else Geert_Gruwez
could you please make me an example of the source code that i gave with these modificatons that you suggest me with your example:
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
'select sum(var1) from table where id=' +label5.Caption + ' AND level=1';
should be changed to
'select sum(var1) from table where id=:id AND level=:alevel';
ParamByName('ID').AsIntege r := aId;
ParamByName('ALEVEL').AsIn teger := 1;
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
the id in the where clause is a label caption that holds the id value so you said to change this into where id=:id but how to get the label caption value?Because of that i ask to make me an example based on the code example i gave.
could you please make me an example of the source code that i gave with these modificatons that you suggest me with your example:
--------------------------
'select sum(var1) from table where id=' +label5.Caption + ' AND level=1';
should be changed to
'select sum(var1) from table where id=:id AND level=:alevel';
ParamByName('ID').AsIntege
ParamByName('ALEVEL').AsIn
--------------------------
the id in the where clause is a label caption that holds the id value so you said to change this into where id=:id but how to get the label caption value?Because of that i ask to make me an example based on the code example i gave.
ASKER
Geert_Gruwez forget the last post i did those changes as you said with where id:id etc i find how to did this but i still have the speed problem :(
what else can i do?something with the indexes?
what else can i do?something with the indexes?
ASKER
ok i solved my problem!i create indexes and everything is fine except the locate but i will find a solution for this also and i'll post back.
speed over internet ?
you could put some timechecking in your routines
procedure TForm1.TestTimes;
var Qry: TQuery;
S: TStringList;
D: TDateTime;
begin
S := TStringList.Create;
try
Qry := TQuery.Create(Self);
try
QRY.SQL.Text :=
'SELECT COUNT(*) FROM TABLE';
D := Now;
Qry.Prepare;
S.Add(Format('%f seconds prepare', [SecondSpan(Now, D)]);
D := Now;
Qry.Open;
S.Add(Format('%f seconds open ', [SecondSpan(Now, D)]);
Qry.Close;
finally
FreeAndNil(Qry);
end;
S.SaveToFile(Format('log_% s'.txt', [FormatDateTime(Now, 'yyyymmddhhnnsszzz')]));
finally
FreeAndNil(S);
end;
end;
sorry, didn't have a lot of time yesterdayafternoon
the locate ?
you could put some timechecking in your routines
procedure TForm1.TestTimes;
var Qry: TQuery;
S: TStringList;
D: TDateTime;
begin
S := TStringList.Create;
try
Qry := TQuery.Create(Self);
try
QRY.SQL.Text :=
'SELECT COUNT(*) FROM TABLE';
D := Now;
Qry.Prepare;
S.Add(Format('%f seconds prepare', [SecondSpan(Now, D)]);
D := Now;
Qry.Open;
S.Add(Format('%f seconds open ', [SecondSpan(Now, D)]);
Qry.Close;
finally
FreeAndNil(Qry);
end;
S.SaveToFile(Format('log_%
finally
FreeAndNil(S);
end;
end;
sorry, didn't have a lot of time yesterdayafternoon
the locate ?
i object to closing this question like this.
Suggestion was to look at the indexes, or the primary key (which is an index)
after following the suggestion, (creating the index) the problem was solved
Suggestion was to look at the indexes, or the primary key (which is an index)
after following the suggestion, (creating the index) the problem was solved
you're right. my bad. I actually didn't read that part. I saw that you split it up into 2 posts and I thought you put only UI stuff in first post and DB stuff in the second, so I jumped over it. sorry for that.
ASKER
Hello Geert_Gruwez, hello ciuly
Mary x-mas, i wish you the best.
I agree with Geert_Gruwez he gave me the idea about that i am wondering for the table indexes, i was create indexes and everything was ok but help me about what to give you A or B because you gave me the idea and i found my own how to create them.I want to give you A but i am just wondering which is the right.
Mary x-mas, i wish you the best.
I agree with Geert_Gruwez he gave me the idea about that i am wondering for the table indexes, i was create indexes and everything was ok but help me about what to give you A or B because you gave me the idea and i found my own how to create them.I want to give you A but i am just wondering which is the right.
what about the locate, what's the problem with it ... helping there may deserver an A ?
merry Xmas too
merry Xmas too
ASKER
It has to do something with the return row to dbadvgrid i didn't have any free time to solve this but it has not to do with the problem i had so the solution is only for the indexes.Please help me about what to gave you or ciuly suggest me which is the right to do.
lol, ciuly only likes an a
i had this once with devexpress too, but they fixed it
i solved with looking at the top index, the index of the line visible at the top of the grid
i don't have the advgrid so, won't be able to help there.
as it's xmas i'll except anything(, um except c), a beer would be nice too.
the wife would say i had enough, but there is allways room for one more ...
i had this once with devexpress too, but they fixed it
i solved with looking at the top index, the index of the line visible at the top of the grid
i don't have the advgrid so, won't be able to help there.
as it's xmas i'll except anything(, um except c), a beer would be nice too.
the wife would say i had enough, but there is allways room for one more ...
for the rest, i'm gone, ciuly, it's all yours for the remainder of the year,
have a good end, and a very good beginning ...
bye bye, c ya next year
have a good end, and a very good beginning ...
bye bye, c ya next year
ASKER
Ok i give an A because i always give A for any help from anyone ...and for the beer if any time you plan to come in Thessaloniki in Greece we will go for beers and keep a big room for those!!!!
I wish you a healthly year!!!!
I wish you a healthly year!!!!
Have you tried using a different query object for the second part of your code?