> He-he. I think that only MS SQL server could use sp_executesql procedure > because it is MS SQL procedure only. Firebird AFAIR have EXECUTE_STATEMENT > but it couldn't be used with parameterized queries. > Does MySQL have something like sp_executesql feature? Does MySQL (e.g.) > performance differs for plain-text and parameterized query?
The performance difference that I am aware of between plain-text and parameterized queries/stored procs for MSSQL is in its execution plan optimization and parameter sniffing based on the SQL, expected/common/default parameters, and indexes/stats available on the table(s). I'm actually surprised that the query is running much faster without parameters since MSSQL is supposed to figure out a new execution plan each time when using sp_executesql (vs a stored proc where default param values can skew the execution plan for good until recompile). If it’s supposed to figure out a new plan each time given diff't query params with sp_executesql, I'm lost as to why it's MUCH slower than the straight plain-text query. > One provider - odbc e.g. could be used to access MS SQL and MySQL > servers. > In such case two the same providers should be added with different names > and > patterns. Is it right even for MS SQL specific feature? If the provider is ODBC or OLEDB, there could be default values in place to be overwritten/customized by the developer for the null patterns appropriate to their database. Off-topic for this e-mail, but: If the providers.config file starts getting more attributes, how about some place for giving custom mappings for provider/db types to primitives such as the translations found in NHibernate's Dialect classes? Roberto > -----Original Message----- > From: Alexey Boroday [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 04, 2005 11:10 AM > To: ibatis-user-cs@incubator.apache.org > Subject: RE: MS SQL slow query performance > > > He-he. I think that only MS SQL server could use sp_executesql procedure > because it is MS SQL procedure only. Firebird AFAIR have EXECUTE_STATEMENT > but it couldn't be used with parameterized queries. > Does MySQL have something like sp_executesql feature? Does MySQL (e.g.) > performance differs for plain-text and parameterized query? > > > The best place to keep this convertion rule will be the > > provider.config > > Each provider tag need to define new attributes to represent > > each rule, > > Something like this > > <provider nullValuePattern="null" > > dateTimePattern="#?#" .../> > > One provider - odbc e.g. could be used to access MS SQL and MySQL > servers. > In such case two the same providers should be added with different names > and > patterns. Is it right even for MS SQL specific feature? > > > A new setting in sqlMap.config could also be set to allow or > > not the use of parameters embedded in sql text statement. > > > > Something like this > > <setting allowEmbeddedParameters"true|false"/> > > > > Hello. > > > > > > Test code was written. I could say that on the MS SQL > > queries without > > > parameters works much faster than via sp_executesql. > > > > > > I see some problems with converting parameter values to the > > string. Could > > > it be discussed here? > > > > > > Parameter values could be embedded to the command text for the Input > > > parameters only (its not a big problem - just FYI). > > > > > > The problem is: > > > Does all servers accept "NULL" in the query string as null value? > > > DateTime, string, numeric variables should be correctly > > corrected to the > > > string representation for each server. > > > Does iBATIS contain such rules now? Somewhere in the > > session parameters or > > > elsewhere? > > > Should convertion rules be as additional options for the > > sqlmap.config > > > "database" element? > > > May be additional attribute should be added for each > > statement - should be > > > parameters embedded or not? Or a global option for the database? > > > > > > Thank you. > > > > > > > -----Original Message----- > > > > From: Gilles Bayon [mailto:[EMAIL PROTECTED] > > > > Sent: Saturday, January 01, 2005 10:32 PM > > > > To: ibatis-user-cs@incubator.apache.org > > > > Subject: RE: MS SQL slow query performance > > > > > > > > Fine idea, I have created a JIRA issue of your improvement. > > > > > > > > Happy New Year. > > > > -Gilles > > > > > > > > -----Message d'origine----- > > > > De : Alexey Boroday [mailto:[EMAIL PROTECTED] > > > > Envoyé : vendredi 31 décembre 2004 08:27 > > > > À : ibatis-user-cs@incubator.apache.org > > > > Objet : RE: MS SQL slow query performance > > > > > > > > Hello > > > > > > > > I see that SqlCommand executes commands with parameters with > > > > sp_execsql. > > > > But it is too slow. > > > > My proposition is parse CommandText and replace parameter > > names with > > > > parameter values inside CommandText e.g. > > > > > > > > Lets say we have command > > > > > > > > delete from Table1 where id = @id > > > > > > > > so this cammand will be passed to the server as > > > > > > > > exec sp_executesql N'delete from Table1 where id = @P1 ', N'@P1 > > > > nvarchar(2)', N'5' > > > > > > > > This command performance isn't very differs from the plain > > > > text command. > > > > But for complex select query performance very much slow. The > > > > same query > > > > without parameters works faster. > > > > I'd like to propose inside iBATIS (somewhere, I cann't still > > > > find where) > > > > parse command text and replace params via their values in the > > > > CommandText. > > > > After that into the server will be posted query like this: > > > > > > > > delete from Table1 where id = 5 > > > > > > > > May I try to implement such behaviour as an option? > > > > > > > > Happy New Year. > > > > > > > > > > > ______________________________________________________________ > > _____________ > > > > > > This communication is confidential and may be legally privileged. > > > If you believe you are not an intended recipient, please > > inform the sender, > > > delete the e-mail and do not copy, print or use it or > > disclose it to others. > > > You can inform the sender by replying to this e-mail or by > > telephone (+380 44 4906080). > > > > > > > > > > > > Accédez au courrier électronique de La Poste : www.laposte.net ; > > 3615 LAPOSTENET (0,34 /mn) ; tél : 08 92 68 13 50 (0,34/mn) > > > > > __________________________________________________________________________ > _ > > This communication is confidential and may be legally privileged. > If you believe you are not an intended recipient, please inform the > sender, > delete the e-mail and do not copy, print or use it or disclose it to > others. > You can inform the sender by replying to this e-mail or by telephone (+380 > 44 4906080). > >
