SQL Server Performance

SP_Prepare and SP_execute

Discussion in 'Performance Tuning for DBAs' started by WingSzeto, Jun 30, 2006.

  1. WingSzeto Member

    We are using SQL 2K sp4. I am trying to find out what query is being executed under sp_execute. I read through a lot of published postings and I don't seem to find the exact situation. Here is what I understand. Every sp_execute should have a corresponding sp_prepare which contains the SQL statement being executed. The passing handle is the one I use to tie them together. THe problem is that out of several hundred sp_execute statement found in SQL profiler, in most of the cases I only find less than 10 sp_prepare statements. In some profiler result, I don't even find any sp_prepare. Can anyone explain that and also how am I supposed to find the SQL statement being executed by sp_execute if there is no corresponding sp_prepare?

    Wingman
  2. joechang New Member

    the general idea behind using this API set is to prepare an SQL statement once,
    then execute multiple times with different parameters

    some inept client-side programmers fail to see the point of this and prepare before each execute

    even though i think this API is obsolete, and should be replaced with stored procs.
    atleast your programmer understood the purpose and implemented it correctly
    so you are probably missing the original sp_prepare call

Share This Page