Re: [Firebird-net-provider] Prepared statements cache inside .NET provider
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 <skopa...@elektlabs.cz> 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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
[Firebird-net-provider] Prepared statements cache inside .NET provider
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:skopa...@elektlabs.cz 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 Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Re: [Firebird-net-provider] Peformance running a procedure
Hi, the LIKE statement is very complex and in some cases it can used index. .. WHERE aaa LIKE 'aaa%' and in outher not: ... WHERE aaa LIKE '% aaa' SP must create plan when you are preparing statament and this is before sending parameters. In second case, you run SQL on fly and in this case the plan is prepared when on fly is prepared (when sp is running). Summary, use other statement instead LIKE. LIKE must be used very carefully on big tables :). Also if autor will send execution plan this will be more helpfull then only general question. BTW: I think on the holiday we can be litle offtopic :)) BTW2: And because I'm lazy I don't subcribe support list yet :(( Slavek Hi, this is because LIKE operator in general case CAN'T use a index. You must equal or other operator instead. Slavek PS: Like in this case is dangerou because huge performance penalty (try use second case with SuchName='%'). Hi Slavek, isn't the problem on other side? AFAIU the problem, the first procedure (without exec stmt) runs very slowly and the second (same statement, but only executed thru exec stmt) runs fast. Or did I miss something? BTW We're little bit OT in this list. - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
