Hits and Misses

• scott mcnitt

  SSC Eights!

  Points: 998

  More actions

  March 10, 2006 at 11:46 am

  #626059

  Question (with apologies in advance for my ignorance):

  I use ADO in my app to execute the sproc. I create a command object with collection of parm objects standard (works with input and/or out parms).

  When I set the CommandText I do not fully qualify the sproc name.

  *This* is what the article is talking about, correct? I should set the CommandText to "dbo.spDoSomething" instead of just "spDoSomething".

  The real question is: the issue is the same if using ADO and not just in T-SQL, correct? The names (or case) of the members of the parms collection does not matter, although the order does.

• Tien Tran

  SSC Veteran

  Points: 222

  More actions

  February 27, 2009 at 4:45 pm

  #951434

  Thanks for the articles.

  However, when I run the same SP a couple times, I see a cache miss right after cache hit and the usecounts in syscacheobjects does increase.

  Everytimes I run the sp, a cachemiss will happen before the cache hit

  Could you explain?

  Thanks!

  T.

  Here is my example copied from BOL

  USE AdventureWorks;

  GO

  IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL

  DROP PROCEDURE HumanResources.uspGetAllEmployees;

  GO

  CREATE PROCEDURE HumanResources.uspGetAllEmployees

  AS

  SET NOCOUNT ON;

  SELECT LastName, FirstName, JobTitle, Department

  FROM HumanResources.vEmployeeDepartment;

  GO

  EXECUTE HumanResources.uspGetAllEmployees