Support of dB parametrized queries

[lelion]

Hi Team, Hi all,

This is a database performance issue.
I wrote many apps in other languages dealing with real time with database as repository (no file system)
I like the database support in PB but the lack of real parametrized queries is really an issue. Here's an example :

Code: Select all

; very very short event !
While #True 
  qryExec("select * from events where MICROSECOND between 1000000 and 1000500 and evType = 'Pulse' and angle > 11.546 ")
  Process(result)
Wend

This query returns all events of a certain type occured during half a millisecond. We presume the speed of execution should not exceed this duration or latency/jittering may grow up and up ...

PureBasic let us replace parts of the sql text queries ; for example by the use of a numeric variable :

Code: Select all

While #True 
  qryExec("select * from events where MICROSECOND between " + Str(offset) +" and " + Str(offset+wSize) +" and evType = 'Pulse' and angle > " + Str(Angle) + "")
  Process(result)
Wend

You might pass many decimal for float values (you never know how many is required)
But the real problem is that your database is set to 1. Parse SQL syntax - 2. Compile SQL ; [BEFORE] 3. executing - Every steps are processed for each loop which may take quite long..

By experience , I get a twice to trice faster code by using parametrized queries which could look like this :

Code: Select all

; compile sql command once forever 
sqlCommand("select * from events where MICROSECOND between ? and ? and evType = 'Pulse' and angle > ?")
While #True 
  sqlAddParameterQuad(offset)
  sqlAddParameterQuad(offset+wSize)
  sqlAddParameterDouble(Angle) 
  sqlExecute()
  Process(result)
Wend

Variables are passed in their native format : no need to convert to string before convert back to int or double ...
SQL compilation is done only once for all !

This results in a 2 or 3 times faster code

I noticed that BLOBS use such a parametrized syntax (I never use blobs) ,

SO would it be possible to extend blobs process to any type of data ?

I would love this !

[LuckyLuke]

Yes, would be a very nice feature.

Another feature I'm missing is the possibility to retrieve multiple rows at once. (Performance issue when using VPN connections or poor performace networks)

Code: Select all

DatabaseQuery(#Database, Request$, rows)

rows : the number of rows retrieved at once.

[lelion]

Yes, would be a very nice feature.

Another feature I'm missing is the possibility to retrieve multiple rows at once. (Performance issue when using VPN connections or poor performace networks)

Code: Select all

DatabaseQuery(#Database, Request$, rows)

rows : the number of rows retrieved at once.

Did you try something like that (or maybe I didn't understand your suggestion properly)

Code: Select all

    If DatabaseQuery(#dB, "select * from toa_range LIMIT 0,10")
       While NextDatabaseRow(#dB)
         Tr(a)\idchu    = GetDatabaseQuad(#dB, 0) ; should not be called more than once for a given field 
         Tr(a)\mintoa   = GetDatabaseQuad(#dB, 1)
         Tr(a)\maxtoa   = GetDatabaseQuad(#dB, 2)
         a + 1
       Wend
      FinishDatabaseQuery(#dB)
    EndIf

In this example, only the first 10 rows are retrieved. The "LIMIT" SQL clause is reconized by both MySQL and SqLite. Oracle does the same with a "WHERE rownum < 10 " syntax, Access use a "SELECT TOP 10.." equivalent.

[LuckyLuke]

The feature I need is to be able to specify the number of rows fetched by one NextDatabaseRow.
Why ? If you get one row at the time it becomes slow on VPN.

I know this link : http://www.purebasic.fr/english/viewtop ... =5&t=43322, but a native solution would be nice.