ADO Prepared Query Gets Unprepared | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ADO Prepared Query Gets Unprepared

I have a VB6 app that uses an ADO.Command object to execute a parameterized query. When I set the "Prepared" property to TRUE, the query gets reprepared every time it is executed. The sequence on the server looks like this: +SQL:BatchCompleted SET FMTONLY ON select…from…(index(nDate)) SET FMTONLY OFF
+RPC:Completed sp_prepare @P1 output, N’@P1 datetime,@P2 datetime’, N’select … from … (index(nDate))Where nDate Between @P1 and @P2′
+RPC:Completed sp_execute 1, ‘20040702 10:11:32:000’, ‘20040702 11:11:32:000’
+RPC:Completed sp_unprepare 1 The query gets reprepared each time it is executed.
If "Prepared" is set to False, I get this: +RPC:Completed sp_executesql N’select…from…(index(nDate)) Where nDate Between @P1 and @P2′, N’@P1 datetime,@P2 datetime’, ‘20040702 10:11:32:000’, ‘20040702 11:11:32:000’ Can anyone tell me why the query keeps getting unprepared and reprepared? Note that nothing else is running on the server during this. Thanks,
Paul
Forgive me, but isn’t this the expected behaviour when you manipulate the ‘prepared’ property of the command object? Nathan H.O.
Moderator
SQL-Server-Performance.com
Nathan, Maybe those of you who know more about this than I do expect this behaviour. I expected to see the statement prepared once and executed multiple times without being unprepared or reprepared. That would be the efficient way to work, anyway. – Paul
Paul, I ran some tests to see whether sp_cursorunprepare causes recompile of SQL Statement. I found that it doesn’t cause a SQL statement to recompile so I think it is just an API thing. I confirmed this by looking at SQL profiler and also querying syscacheobjects table. Whenever I ran the same query using application (our application uses ODBC to connect to SQL Server), it increased the "usecounts" column in syscacheobjects table by 1 while it still showed sp_cursorunprepare in SQL profiler trace. I too want to know more about these extended system stored procedures. Does anyone know where I can find detailed information about them? Thanks
-Rajeev Lahoty
]]>