slight variation, huge differences

 Two sql statements, both return the same result.

SELECT        [ID]      , [SecurityUserID]      , [SecurityRoleID]
    FROM      dbo.[SecurityUserRoles]
    WHERE  ((@Id is null) or ([Id] = @Id))
            and ((@SecurityUserID is null) or (SecurityUserID = @SecurityUserID))
            and ((@SecurityRoleID is null) or (SecurityRoleID = @SecurityRoleID))

versus

          SELECT [ID], [SecurityUserID],[SecurityRoleID]
          FROM dbo.[SecurityUserRoles]
          WHERE SecurityUserID = @SecurityUserID and SecurityRoleID = @SecurityRoleID

The top SQL does an index scan and runs approminately 3 to 4 seconds on a production server. a 5 hour long profile job showed that only the two parameters SecurityUserID and SecurityRoleID where being requested.

The bottom sql statement, returns the results back in 10 to 25ms.

NOLOCK even in a 2008 world

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Migrating your brain.

We have a new person join our data team.  it’s been approximately 2 years since I’ve have someone to share my work.

Without going into great detail of all the systems, the multiple environments and the little oddities, I’ve learned that I didn’t have enough stuff documented.

It would be great if I was able to share a ‘current’ state of ALL the systems and how things worked.

I was able to share all the details from memory, (as we are a small shop) but it was a great kick in the butt to get the details down on paper/electronic.

Tuning a Vendor Proc

4 dual core cpus del 2950,  64gig memory, 64bit Win2003.   24gig in use by sql.

140   ms with my changes
if @IsSuperUser = 1 
begin
   INSERT INTO @sec (SecurityKey, CanView)
 SELECT DISTINCT S.SecurityKey,1 as [CanView]
 FROM dbo.Security AS S LEFT OUTER JOIN
 dbo.UserRoles AS R ON S.ObjectId = R.RoleID
 WHERE @IsSuperUser = 1     Group By s.SecurityKey
end
else
begin
 INSERT INTO @sec (SecurityKey, CanView)
 SELECT DISTINCT S.SecurityKey, IsNull(MAX(Convert(int,S.[View])),0) as [CanView]
 FROM dbo.Security AS S LEFT OUTER JOIN
  dbo.UserRoles AS R ON S.ObjectId = R.RoleIDWHERE (S.[View] = 1) AND
  ( (
  (R.EffectiveDate IS NULL) AND (R.ExpiryDate >= GETDATE()) OR
  (R.EffectiveDate IS NULL) AND (R.ExpiryDate IS NULL) OR
  (R.EffectiveDate <= GETDATE()) AND (R.ExpiryDate IS NULL) OR
  (R.EffectiveDate <= GETDATE()) AND (R.ExpiryDate >= GETDATE()))
  AND R.UserId = @UserId AND @UserId > 0)
    Group By s.SecurityKey
 union
  SELECT DISTINCT S.SecurityKey, IsNull(MAX(Convert(int,S.[View])),0) as [CanView]
 FROM dbo.Security AS S LEFT OUTER JOIN
  dbo.UserRoles AS R ON S.ObjectId = R.RoleIDWHERE (S.[View] = 1)
   AND ( (@UserId = -1 AND S.ObjectId = -3)
   OR (S.ObjectId = -1) )
 Group By s.SecurityKey
end

 VERSUS Original logic from vendor code
 18547 ms

INSERT INTO @sec (SecurityKey, CanView)
SELECT DISTINCT S.SecurityKey,CASE WHEN @IsSuperUser = 1 THEN 1 ELSE IsNull(MAX(Convert(int,S.[View])),0)END as [CanView]
FROM  dbo.Security AS S LEFT OUTER JOIN
dbo.UserRoles AS R ON S.ObjectId = R.RoleID
WHERE (@IsSuperUser = 1) OR
 (
 (S.[View] = 1)
 AND 
 (
  (
   (R.EffectiveDate IS NULL) AND (R.ExpiryDate >= GETDATE()) OR
   (R.EffectiveDate IS NULL) AND (R.ExpiryDate IS NULL) OR
   (R.EffectiveDate <= GETDATE()) AND (R.ExpiryDate IS NULL) OR
   (R.EffectiveDate <= GETDATE()) AND (R.ExpiryDate >= GETDATE()))
   AND
   R.UserId = @UserId AND @UserId > 0
  )
  OR
   (
   (@UserId = -1 AND S.ObjectId = -3)
   OR
   (S.ObjectId = -1)
  )
 )
Group By s.SecurityKey

 The issue is the nested OR statements.  They ALL run and all add to the time and execution of the sql.    This was a prior version,  I can’t wait to see their performance tuned one in their next release.