Prepared statements cache inside .NET provider

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Prepared statements cache inside .NET provider

Slavomir Skopalik
Hi all,

after discussion with Jiri Cincura I prepare proposal for prepared
statement cache that will be implemented inside .NET provider.

Motivation:

Preparation of SQL statement take a time and also server resources. Many
SQL server has this functionality build in in engine, but FB not.

More reading for example here:
http://stackoverflow.com/questions/12141036/preparedstatement-caching-what-does-it-mean-how-does-it-work

We implement this to speed up our web application (cache is persistent
across web requests).


Ideas:

Each connection that already exists in pool well keep a private
dictionary of cached statements (can be disable by config).

Not used SQL will be released after some time out, also cache will have
maximum statements like default 100.

When application obtaining new connection from pool, provider will look
for free connection with higher number of cached statements.


The question:

Is this interesting to have this functionality inside provider and fully
transparent?

Slavek

PS: We combine this with RO transaction pool and also with data caching,
but this is different story.


--
Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
Collection and evaluation of data from machines and laboratories
by means of system MASA (http://www.elektlabs.cz/m2demo)
-----------------------------------------------------------------
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---------------------------------------------------------------
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:[hidden email]
http://www.elektlabs.cz



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Prepared statements cache inside .NET provider

Gerdus van Zyl
Sound interesting to have in the provider.

Is this only for commands that has Prepare() explicitly called on?
Is there a scenario where performance will be slower than currently?

On 29 March 2017 at 11:58, Slavomir Skopalik <[hidden email]> wrote:
Hi all,

after discussion with Jiri Cincura I prepare proposal for prepared
statement cache that will be implemented inside .NET provider.

Motivation:

Preparation of SQL statement take a time and also server resources. Many
SQL server has this functionality build in in engine, but FB not.

More reading for example here:
http://stackoverflow.com/questions/12141036/preparedstatement-caching-what-does-it-mean-how-does-it-work

We implement this to speed up our web application (cache is persistent
across web requests).


Ideas:

Each connection that already exists in pool well keep a private
dictionary of cached statements (can be disable by config).

Not used SQL will be released after some time out, also cache will have
maximum statements like default 100.

When application obtaining new connection from pool, provider will look
for free connection with higher number of cached statements.


The question:

Is this interesting to have this functionality inside provider and fully
transparent?

Slavek

PS: We combine this with RO transaction pool and also with data caching,
but this is different story.


--
Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
Collection and evaluation of data from machines and laboratories
by means of system MASA (http://www.elektlabs.cz/m2demo)
-----------------------------------------------------------------
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---------------------------------------------------------------
Mobile: <a href="tel:%2B420%20724%20207%20851" value="+420724207851">+420 724 207 851
icq:199 118 333
skype:skopaliks
[hidden email]
http://www.elektlabs.cz



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider



--
------------------------------------------------------------------------
Gerdus van Zyl

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Prepared statements cache inside .NET provider

Slavomir Skopalik
We are using this for all commands because developer are lazy.
Together with explicit prepare, no possible performance loss, just memory.
But I'm not sure for commands without prepare.

If I remember API, there is possibility to execute SQL without prepare.
And in this case, the first time will be slower because of roundtrip to
server.
Next time will be faster.
But if are you using parameters, you must prepare SQL -> fist is same,
second is better.

Slavek


> Is this only for commands that has Prepare() explicitly called on?
> Is there a scenario where performance will be slower than currently?
>
> On 29 March 2017 at 11:58, Slavomir Skopalik <[hidden email]> wrote:
>
>> Hi all,
>>
>> after discussion with Jiri Cincura I prepare proposal for prepared
>> statement cache that will be implemented inside .NET provider.
>>
>> Motivation:
>>
>> Preparation of SQL statement take a time and also server resources. Many
>> SQL server has this functionality build in in engine, but FB not.
>>
>> More reading for example here:
>> http://stackoverflow.com/questions/12141036/preparedstatement-caching-
>> what-does-it-mean-how-does-it-work
>>
>> We implement this to speed up our web application (cache is persistent
>> across web requests).
>>
>>
>> Ideas:
>>
>> Each connection that already exists in pool well keep a private
>> dictionary of cached statements (can be disable by config).
>>
>> Not used SQL will be released after some time out, also cache will have
>> maximum statements like default 100.
>>
>> When application obtaining new connection from pool, provider will look
>> for free connection with higher number of cached statements.
>>
>>
>> The question:
>>
>> Is this interesting to have this functionality inside provider and fully
>> transparent?
>>



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Loading...