TIP OF THE DAY!!! : SQL request and speed ? III

[Thierry Nauze]

Le 30 oct. 05 à 19:25, Ruslan Zasukhin a écrit :

> On 10/30/05 5:11 PM, "Frank Schima" <macsforever2000 at goodeast.com>  
> wrote:
>
>
>>>>> For a rudimentary request :
>>>>>
>>>>>   for i=1 to 10000
>>>>>     call baseValentina.SqlExecute( "INSERT INTO maTable
>>>>> (id,maVarChar) VALUES ("+str(i)+",'A')" )
>>>>>   next
>>>>>
>>>>> RealSQL is always 8 (even 10) time faster than Valentina 2.11  :
>>>>> 0.45 s  vs 4.87 s !



> Hi Guys,
>
> Yes SQL way takes longer time than API way.

I do not doubt it.
But to have such a difference between two bases on the same  
rudimentary and correct request SQL is a little bit surprising.


>     In 2.0.5 Ivan have implement Pool of queries for Valentina.
>     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> * when you do query which byte to byte is the same as prev one,
>     then Valentina do not parse it and do not check nothing,
>     instead it takes from pool ready execution plan and do it.
>
> So question is: how INSERT in above loop can be THE SAME for each  
> loop ?!

It is about the example only for the test.
To replace 'A' by different values do not change anything.

> Answer is: use bindings!!!

So, if I understand well , it is better to avoid classic requests SQL  
with Valentina 2.

>     query = "INSERT INTO maTable (id,maVarChar) VALUES (:1,'A')"
>
>    for i=1 to 10000
>        call baseValentina.SqlExecute( query, Array( str(i) ) )
>    next
>
> Such way is much faster of original way, although still (of course)  
> is not
> so fast as Api calls. But close enough....

OK. With bindings, Velentina 2  : 0.53 s
And, with classic resquest, RealSQL : 0.46 s.


How  to use bingings with :
baseValentina.SqlExecute("CREATE TABLE maTable (id UMedium, maVarChar  
varchar(1022) )")
0.116 s for Valentina  vs  0.013 s for ...
baseSQLite.SQLExecute("CREATE TABLE maTable (id integer, maVarChar  
varchar)") ?

>
> This is the most simple optimization you can do.
> It also simplify and clean your code.
> This way works in most major DBMS.
>
> Note, using binding, you do not need escape strings. Another speedup.

???

   s="l'oiseau"
   query = "INSERT INTO maTable (id,maVarChar) VALUES (:1,:2)"
   for i=1 to 10000
     call baseValentina.SqlExecute( query, Array( str(i), s ) )
   next

0.70 s for Valentina  vs   0.57 s for ...

   for i=1 to 10000
     baseSQLite.SqlExecute( "INSERT INTO maTable (id,maVarChar)  
VALUES ("+str(i)+",'"+replace("l'oiseau","'","''")+"')" )
   next

It is not evident !

Regards,

--
Thierry NAUZE        Saint-Denis de la Réunion