sp_prepexec performance

• Green Armchair

  Hall of Fame

  Points: 3561

  More actions

  October 19, 2005 at 8:44 am

  #89786

  Hi

  We have a 3rd party app that is using sp_prepexec for all its queries on the database.

  A simple query run through QA takes 1 sec on average. The same query run through the app via sp_prepexec takes on average 30 secs!

  Is this a known issue/drawback of using sp_prepexec?

  Thanks

  Andy

• Jo Pattyn

  SSC-Dedicated

  Points: 32347

  More actions

  October 19, 2005 at 10:48 am

  #598389

  sp_prepexec is explained a bit here:

  http://jtds.sourceforge.net/apiCursors.html

  Normally used by Ado (since it uses cursors to retrieve data)

  Normally it would be like

  sp_prepare..

  sp_execute.. parameter query

  sp_execute..parameter query

  sp_execute..parameter query

  sp_execute..parameter query

  sp_unprepare

  A one time query doesn't need sp_prepare,unprepare