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

[Ruslan Zasukhin]

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 !
>>>> 
>> 
>> No.
>> 
>> But I had an exchange with Ruslan to whom I have already sent an
>> example of program.
>> 
>> He had to see for the 2.06!
>> 
>> I am going, maybe, to make it soon.
> 
> Please do add it into Mantis. It will get done faster that way.
> 
> In the beginning of the Val 2 betas, I noticed that SQL UPDATE is
> much slower than Val 1.x so I switched to API UPDATE and the speed
> returned. I believe the same is true for INSERT. Can you try it that
> way and let us know the results?

Hi Guys,

Yes SQL way takes longer time than API way. And secret not in parser as I
have think. Main time eat checks of e..g

    field with specified name exists
    table with such name exists...
    And so on.

I wonder if we have inform you.

    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 ?!

Answer is: use bindings!!!

    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....

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.


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]