 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Jan Doggen
Guest
|
 Posted: Thu Apr 15, 2004 6:02 pm Post subject: When to set TSQLQuery.Prepared? |
 |
|
Hi all,
For performance when updating/inserting records in SQL Server through
dbExpress:
1) When do I set TSQLQuery.Prepared := TRUE?
2) Do I set TSQLQuery.Prepared := FALSE?
Example:
WITH DModArboSQL.QryUpdate DO
BEGIN
SQL.Clear;
SQL.Add('UPDATE TellingenOOP')
SQL.Add('SET Tel_Chi2Geslacht=:Chi2Geslacht,');
SQL.Add(' Tel_Chi2Leeftijd=:Chi2Leeftijd');
SQL.Add('WHERE Tel_Run_ID=' + IntToStr(RunID));
SQL.Add('AND Tel_Schaal_ID=:SchaalID');
Params[0].ParamType := ptInput;
Params[0].DataType := ftFloat;
Params[1].ParamType := ptInput;
Params[1].DataType := ftFloat;
Params[2].ParamType := ptInput;
Params[2].DataType := ftInteger;
Prepared := TRUE; { 1a) Here ?? }
END; { WITH DModArboSQL.QryUpdate }
Then, in a later loop:
DModArboSQL.QryUpdate.Params[0].AsFloat := Chi2Geslacht;
DModArboSQL.QryUpdate.Params[1].AsFloat := Chi2Leeftijd;
DModArboSQL.QryUpdate.Params[2].AsInteger := SchaalID;
Prepared := TRUE; { 1b) Or here ?? }
DModArboSQL.QryUpdate.ExecSQL;
Prepared := FALSE; { 2) And do I do this then ?? }
Right now, I am only setting Prepared := TRUE once, but I have the app
hanging
on ExecSQL statement sometimes. In the NG, I see people doing either 1a or
1b.
QryUpdate.NoMetaData is TRUE.
Additional question:
3) Some time later on, I 're-use' QryUpdate for the same type of code.
Does this change the answers?
Thanks in advance,
Jan
|
|
| Back to top |
|
 |
Wayne Niddery [TeamB]
Guest
|
| Posted: Fri Apr 16, 2004 12:10 am Post subject: Re: When to set TSQLQuery.Prepared? |
|
|
Jan Doggen wrote:
| Quote: |
1) When do I set TSQLQuery.Prepared := TRUE?
2) Do I set TSQLQuery.Prepared := FALSE?
WITH DModArboSQL.QryUpdate DO
BEGIN
SQL.Clear;
SQL.Add('UPDATE TellingenOOP')
[...]
Params[0].ParamType := ptInput;
Params[0].DataType := ftFloat;
[...]
Prepared := TRUE; { 1a) Here ?? }
|
Correct.
| Quote: | Then, in a later loop:
|
.... do not call it in the loop at all.
The purpose of prepare is to allow the database server to set up for a
parameterized query and the only value of doing so is so the same query can
be repeatedly executed with different parameters without having to go
through the prepare step each time - incrasing performance. So it is never
correct to prepare or unprepare each time through a loop when all that is
needed is the new parameter values.
You can unprepare it after the loop has completed.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
