Hello. > -----Original Message----- > From: roberto [mailto:[EMAIL PROTECTED]
> 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. The problem is that MS SQL doesn't know anything about parameter values - so it may select wrong plan AFAIK. I've Googled this problem - it is very difficult to tune up query plan in the sp_executesql. With inline values server could evaluate each parameter cost and select appropriate plan. Now I think that embedded parameters should be implemented only for MS SQL (special switch or something else). Yes, it is possible extend providers configuration, add default patterns and override it in the database or somewhere else and replace parameter names in the command text after ApplyParameterMap call in CreatePreparedCommand method. But what for? AFAIK Firebird, MySQL servers don't anything like sp_executesql. Am I right? I nothing cannot say about Oracle and DB2 unfortunatly - does anyone use it with iBATIS? BTW - still having little trouble with positional parameters. The problem is that command text should be parsed more carefully than with IndexOf function ;) if there is not parameter names in the command text. Good luck. ___________________________________________________________________________ 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).
