bad sql or dynamic where

Last Post 06 Nov 2007 02:33 AM by tom27. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
timcronin
New Member
New Member

--
17 Jan 2006 01:11 PM
Helping a developer with an sp from a .net program which will feed it several parameters (any of which can be null). In other words the where clause is somewhat dynamic. I am posting the select statement (not the parameters) he used. Currently this sp runs very slow. I was considering building some type of dynamic where clause to check for the parameters to create that, but was afraid that in itself might be just as bad. Any better way to handle it?

SELECT *
FROM vGrievances
WHERE
coalesce(GrievanceID,-1) = coalesce( @GID, GrievanceID, -1)
AND coalesce(Member, 'x') = coalesce( @M , Member, 'x')
AND coalesce(Subject,'x') LIKE coalesce( '%' + @S + '%' , Subject,'x')
AND coalesce(GrievDesc,'x') LIKE coalesce('%' + @GD+ '%', GrievDesc,'x')
AND coalesce(OwnerID, -1) = coalesce( @OID, OwnerID, -1)
AND coalesce(CreatorID, -1) = coalesce( @CRID, CreatorID, -1)
AND coalesce(AssignedToID, -1) = coalesce( @ATID, AssignedToID, -1)
AND DueDate >= coalesce(@DDS, DueDate)
AND DueDate <=coalesce(@DDE, DueDate)
AND coalesce(ContractID, -1) = coalesce(@COID, ContractID, -1)
AND coalesce(GrievTypeID, -1) = coalesce(@GTID , GrievTypeID, -1)
AND coalesce(StatusName,'x') = coalesce(@SN, StatusName,'x')
AND CreatedDate >= coalesce(@CDS, CreatedDate)
AND CreatedDate <= coalesce(@CDE, CreatedDate)
AND coalesce(StatusChangedDate,'1/1/1900') >= coalesce(@SCDS , StatusChangedDate,'1/1/1900')
AND coalesce(StatusChangedDate,'1/1/1900') <= coalesce(@SCDE, StatusChangedDate,'1/1/1900')
AND coalesce(Division,'x') = coalesce(@D, Division,'x')
AND coalesce(PriorityTypeID, -1) = coalesce(@PTID, PriorityTypeID, -1)
AND coalesce(OutcomeID, -1) = coalesce(@OcID, OutcomeID, -1)
AND ContractID IN (
(SELECT CONVERT(INT,CONTRACT_ID)
FROM SEC_CONTRACT_SELECT
WHERE SU_USER_ID = @USERID
AND SS_SYS_NAME = 'RESOLVE'))
AND GrievanceID between 1 AND 500

END
GO
mwesch
New Member
New Member

--
17 Jan 2006 04:36 PM
Applying functions to the field names will preclude the query from ever using an index. I would rewite each of the WHERE clause statements as...

WHERE (@GID is null or GrievanceID = @GID)
and (@M is null or Member = @M)
...

Bittela
New Member
New Member

--
02 Nov 2007 02:51 AM
I saw this 'old' topic. I have the same problem. My SQL. lngbcid has an index. But this statement doesn't use the index.

Without 'or @ind is null' the index is used.

Doese anyone has an idea?

Thanks!
Aldo


declare @int int
set @int = 1

select * from dbo.tabBC
WHERE (lngbcid = @int or @int is null )
tom27
New Member
New Member

--
06 Nov 2007 02:33 AM
I prefer a parametric dynamic sql here. But some times I felt not using the optimal plan even if I update statistics, rebuild indexes etc.
You are not authorized to post a reply.

Acceptable Use Policy