How to prepare a ZQuery?

[little_wings]

[mdaems]

Is your question : "Does ZQuery.Prepare exist?"

No it doesn't (yet). It is one of the first things we want to add once 6.6.X is released as stable, however.

For now : just 'open' and 'close' the component to get the query results. Parameters to queries should be set before the 'Open'.
More detailed :
For databases having a 'Prepare/Bind/Execute' API all 3 Phases are done every time a query is executed. (Except for mysql, where it's new in the API and not yet implemented in zeoslib)
For other databases parameters are substituted before the query is sent to the database.
In the future we want to make it possible to have the prepare phase separated from the 'bind/execute' phase.

Mark

[little_wings]

Hello Mark, thank you for answering.

I am glad that you will implement prepare as a separate method (and to prepare automatically only when needed) once 6.6.x is stable. Zeos is robust already in my opinion, but the speed needs more attention. I am sure that "prepare once, bind & execute many times" is the most inexpensive way to speed up queries.

[mdaems]

Hi Little_wings,
I must say I don't agree. Parsing usually is only a small part of the job when executing a query...
On mysql the prepared statement interface would also offer the small benefit of transfering the data in a binary format instead of plain text, but even then it would only be a small fraction of the so called performance issues.

If you need speed you'll have to get rid of the TDataset stuff and use the lower-level interfaces (zdbc) or even use the pure API calls. And certainly avoid the Metadata functionalities used by zeoslib. This also has a cost to the user however. (Luxury...)
Don't know what database you use. If it's mysql or firebird you could also have a look at http://sourceforge.net/projects/pdo . There prepared statements are supported, but you don't have the visual components.

Last question, would you like to join us developing and testing this feature? We certainly need help...

Mark

[andy]

But parsing on the client side is not preparing on the server side.

When you send the sql to the database, like firebird, the statement will be prepared there (for example finding the optimal indexes to use, reserving memory and so on).
And this process can cost a lot of time compared to fetching rows with data.

So preparing will speed up getting data, if someone only change parameter values. I miss this feature, too.

Andreas

[mdaems]

Please, don't misunderstand me, Andreas. I agree it would be a nice thing. I understand what prepared statements are. And the difference on the server side can be important. As said, it's the first thing I want to look at once 6.6.x is released as stable. (And I'm looking for collegues in the FB department)

My point was that the processing on Zeoslib side is usually the most important part of opening queries. eg. mapping raw data to TDataset fields, making updates easier and things like that. Probably preparing queries will only give you a few percents of gain.

It should be possible to use server side prepared statements on mysql using the 'Prepare xx from (select * from y)' syntax. I did never test it, however. The main problem would be updating the results using zeoslib datasets. In this case it would be necessary to use a TZUpdateSql component as the link with the original query doesn't exist at the execution time.

[little_wings]

Hello everybody,

So now we assume that a "Prepare" has unsignificant speed gain on the Zeos side, but significant speed gain on the server side (at least with Firebird).

With Firebird (I did notice when I first use FB ver. 1.5.1 with IBO | FIBPlus | MDO | UIB -- I don't remember exactly which one) the first time you prepare and open a query it will take, say, 300 msec. Next time you change parameters and open it again, it will give you results under 100 msec. That is huge speed gain. From application programmers' point of view, it is the *most inexpensive* way to gain speed.

The price I'd have to pay for that huge speed gain would be just typing:

Code: Select all

  if not Query.Prepared then Query.Prepare; 

before filling parameters and calling Open, and the query run faster automagically. Once prepared, the query will stay prepared until you change the sql.

But maybe it looked huge because the server and the client was on the same machine (my experience was with a 900 Mhz Celeron).

About developing: honestly I don't think I'm qualified in helping Zeos development (coding), but I could test the feature traditionally.