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