sp_prepexec vs. stored procedures
-
we have an application that uses prepared SQL statements sent from a ASP.NET application. The traffic I'm seeing from Profiler shows SQL statements being executed through sp_prepexec rather then stored procedures. I'm aware of the benefits of using stored procs, but does sp_prepexec take advantage of query optimizations provided by SQL? (i.e. cached statements, statistics, etc)
also, after each sp_prepexec statement, there's also the sp_unprepare. Does this hamper performance in anyway?
Are there any known performance issues between using this method rather than doing stored procedures? Is there a preferred method?
-
This was removed by the editor as SPAM
-
Good question, i've not had a huge amount of experience with the sp_prepexec and sp_unprepare, although I have on occasion spent a great deal of time trying to troubleshoot a system that used it.
Given a choice I would always go down the Stored procedure route, the sp_prepare is probably fine but in the event of trying to work out what's going on if there's a problem I like to be able to get access directly to what's going on rather than trying to dig about a bit.
I'm fairly sure that it would be able to reuse the query plans and data cache but to my mind it just adds extra complexity into the design.
Just my 2 cents, feel free to disagree
-
We deal with this from a custom application and from a 3rd party app [Scribe] other info I have found says that these are old and now undocumented because the MS does NOT encourage the use. When running one dts through SCRIBE there will be upwards of 50 process ids for the one job. The statements alternate between the sp_prepexec, sp_prepare & sp_unprepare ... and then the one real statement. FYI to see documentation for these undocumented procedures go to : http://jtds.sourceforge.net/apiCursors.html . If you look in the syscacheobjects table it will be filled with these statements. I have no answers but have a question. Would setting 'Close cursor on commit' ON help close some of the open processes?
-
On a related note:
I recently discovered that an application using JDBC for sql server to connect to the database was generating sp_prepexec calls when generating SQL. All attempts to optimize the queries were unsuccessful. No matter what indices were added, I could not get the optimizer to use them.
Upon further examination of the queries, I noticed that all values of type char and varchar were being converted to nchar and nvarchar. Furthermore, each parameter in the WHERE clause was being converted to Unicode on the fly (prefaced with N, i.e. @parm1=N'MyValue'). After researching, I found documentation that stated the DEFAULT connection property of sendStringParametersAsUnicode is TRUE. This is why everything was being converted.
After having the developers change that setting to FALSE, all the indices were used! Problem solved.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply