I have been working on making this query work more efficiently.
I found that the number of Ors within the where clause are the biggest problem within this query. This query sits in a stored procedure.
I am to the point where the only options I can think of is. :
- Creating 16 different queries for all the different inbound parameter possibilities.
- Creating a dynamic sql query but I don't believe this will be any faster
- Reverting back to string sql, but I don't ever like to do this as these do not perform as quickly as stored procedures.
I am sure others have ran into this issue before. The query performance isn't terrible to begin with at roughly a second or less, but there are instances where it is hit numerous times causing a lag of up to 5 or 6 seconds.
Query below. :
DECLARE @PERSON_ID AS INT DECLARE @ITEM_ID AS INT DECLARE @ITEM_VERSION AS INT DECLARE @ITEM_SUB_NAME AS VARCHAR(250) DECLARE @ITEM_SUB_SUB_NAME AS VARCHAR(250) --DEFAULTS SET @PERSON_ID = 0 SET @ITEM_ID = 0 SET @ITEM_VERSION = 1 SET @ITEM_SUB_NAME = NULL SET @ITEM_SUB_SUB_NAME = NULL SELECT ID, PERSON_ID, ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION, ISNULL(ITEM_SUB_NAME, '') AS 'ITEM_SUB_NAME', ISNULL(ITEM_SUB_SUB_NAME, '') AS 'ITEM_SUB_SUB_NAME', ISNULL(ITEM_DATE, '1/1/1900') AS 'ITEM_DATE', FROM PERSON_TBL s WITH (NOLOCK) WHERE ( PERSON_ID = @PERSON_ID OR @PERSON_ID = 0 ) AND ( ITEM_VERSION = @ITEM_VERSION OR ( @ITEM_VERSION = 1 AND ITEM_VERSION IS NULL )) AND ( EMPLOYEE_ID = @EMPLOYEE_ID OR @EMPLOYEE_ID = 0 ) AND ( ITEM_SUB_NAME = @ITEM_SUB_NAME OR @ITEM_SUB_NAME IS NULL ) AND ( ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME OR @ITEM_SUB_SUB_NAME IS NULL ) ORDER BY PERSON_ID, ITEM_SUB_NAME