SQLDB Query preparing sql every time is called
lazarus / 16-Databases

----------------------------------------------------------------------
DeBritto · 2016-06-22 15:58

Houston we have a problem!

Hi Folks, I'm trying to improve the performance of my app. What I'm
trying to do is to prepare the sql statement just once and, after
that, just change the parameters to execute. Without preparing another
time.

For this I wrote this piece of code. It runs!

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   // Show
Items' Note  If not DataModule_SmCube.SQLQuery_Notes.Prepared Then 
Begin    // Se o campo note possuir até 8190 bytes, retorna como
Varchar, caso contrário como Blob - Gambiarra Carlos H. Cantú   
DataModule_SmCube.SQLQuery_Notes.ReadOnly:=True;   
DataModule_SmCube.SQLQuery_Notes.SQL.Clear;   
DataModule_SmCube.SQLQuery_Notes.SQL.Add('SELECT sourceItemID,
iif(octet_length(items.note) <= 8190, cast(items.note as
varchar(8190)),null) as blob_txt, iif(octet_length(items.note) > 8190,
items.note, null) as blob_blob FROM items WHERE item = :item;');   
DataModule_SmCube.SQLQuery_Notes.Prepare;  End; 
DataModule_SmCube.SQLQuery_Notes.Close; 
DataModule_SmCube.SQLQuery_Notes.Params.ParamByName('item').AsString
:= Main.GridView_Notes.Columns.Items[0].Field.Text; 
DataModule_SmCube.SQLQuery_Notes.Open; 
But When I trace the server activity (with FB Tracemanager) I see that
everytime I OPEN the query it prepare it automatically.

I'm I missing something? What I have to do to change this behavior? Is
it a component problem? I'm using SQLdb native lazarus component 1.6 +
Free pascal 3.0

----------------------------------------------------------------------
rvk · 2016-06-22 16:09

From the source:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TCustomSQLQuery.SetActive(Value: Boolean);begin  inherited
SetActive(Value);  // The query is UnPrepared, so that if a
transaction closes all datasets  // they also get unprepared  if not
Value and IsPrepared then UnPrepare;end;
So your TSQLQuery gets Unprepared when you issue a Close;

And from the manual:
QuoteClosing the dataset will also unprepare the
query.http://www.freepascal.org/docs-
html/fcl/sqldb/tcustomsqlquery.unprepare.html

----------------------------------------------------------------------
DeBritto · 2016-06-22 16:18

Hy rvk,

What I have to do to correct this situation? Becaus If I don not call
Close and Open the query is not refreshed with the new values...  I
don't know what to do.

----------------------------------------------------------------------
rvk · 2016-06-22 16:29

Quote from: DeBritto on June 22, 2016, 06:18:58 pmI don't know what to
do.
Make sure you have an index on field item so the query is fast.

You can check DataModule_SmCube.SQLQuery_Notes.Plan to see if the
indexes are used.
If it says natural in the plan, no indexes are used and you need to
fix that.
(what is your .plan?)

Another option would be to use a second datasource for the source of
:item. In that case you don't have to close the dataset. But I'm not
sure if in that case there is an internal_disconnect/connect done (in
which case it wouldn't matter).
In case Main.GridView_Notes.Columns.Items[0].Field.Text comes from
another dataset (which I imagine does) just connect the datasets with
each-other and you can leave all that code out.

I've only used explicit prepare in case of INSERT statements (where
you repeat the ExecSQL). In that case prepare is still useful.