sql query times out...case / if / what to do

sql query times out...case / if / what to do

I have the following sql server sproc:
PROCEDURE [dbo].[GetSoftwareProgramsGrid]
@SoftwareTitle varchar(1000)='All',
@CategoryID varchar(100)='All',
@ManufacturerID varchar(50)='All',
@ModelID int=0, -- 0 means all
@AssetID int=0, -- 0 means all
@AssetStatus int=0, --0 is active, 1 is inactive, and 2 is all
@Status int=0, --0 is active, 1 is inactive, and 2 is all
@Type varchar(100)='All',
@Site varchar(100)='All',
@Department varchar(100)='All',
@Manager varchar(100)='All',
@Employee varchar(100)='All',
@SortExpression varchar(100)='Software',
@SortOrder int=0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
*
FROM
(
SELECT DISTINCT
Program AS Software
FROM
AssetProgram ap
LEFT JOIN
AssetAssignment aa
ON
aa.AssetID = ap.AssetID
LEFT JOIN
[MyLinkedServer].MyDB.dbo.Login l
ON
l.LoginID = aa.LoginID
LEFT JOIN
Asset a
ON
a.AssetID = ap.AssetID
INNER JOIN Model m
ON
a.ModelID = m.ModelID
INNER JOIN
Category c
ON
c.CategoryID = m.CategoryID
INNER JOIN Manufacturer ma
ON
ma.ManufacturerID = m.ManufacturerID
WHERE
(
--Software filters
(ap.Program = @SoftwareTitle OR @SoftwareTitle='All')
--Asset filters
AND (c.CategoryID = @CategoryID OR @CategoryID='All')
--filter category
AND (ma.ManufacturerID = @ManufacturerID OR
@ManufacturerID='All') --filter manufacturer
AND (m.ModelID = @ModelID OR @ModelID = 0) --filter model
AND (a.AssetID = @AssetID OR @AssetID = 0) --filter by asset
name (the actual asset id)
AND (((a.Inactive=@AssetStatus) OR (@AssetStatus=2)))
AND (aa.Inactive=0)
AND (ap.Inactive=0)
--Employee filters
/*AND ((l.Inactive=@Status) OR (@Status=2)) --status of
employee 2 is all, 1 is inactive, and 0 is active
AND (@Type='All' OR (@Type='Contractor' AND l.IsContractor=1)
OR (@Type='Regular' AND l.IsContractor=0)) --contractor or
regular employee
AND (@Site='All' OR @Site=l.ClientID) --the site
AND (@Department='All' OR @Department=l.FunctionalGroupID)
--the department
AND ((l.Manager = @Manager OR l.FullName=@Manager) OR
@Manager='All') --the manager
AND (l.FullName = @Employee OR @Employee='All') --the employee
*/
)) ttt
ORDER BY
CASE WHEN @SortExpression='Software' AND @SortOrder=0 THEN
Software END ASC,
CASE WHEN @SortExpression='Software' AND @SortOrder=1 THEN
Software END DESC
This query has to include a linked server, due to our setup. The query
runs fine and is fast as long as I comment out my employee parameters,
namely this section:
--Employee filters
/*AND ((l.Inactive=@Status) OR (@Status=2)) --status of
employee 2 is all, 1 is inactive, and 0 is active
AND (@Type='All' OR (@Type='Contractor' AND
l.IsContractor=1) OR (@Type='Regular' AND
l.IsContractor=0)) --contractor or regular employee
AND (@Site='All' OR @Site=l.ClientID) --the site
AND (@Department='All' OR @Department=l.FunctionalGroupID)
--the department
AND ((l.Manager = @Manager OR l.FullName=@Manager) OR
@Manager='All') --the manager
AND (l.FullName = @Employee OR @Employee='All') --the
employee
*/
The minute I bring even the first line of that section in, for example
just this one:
AND ((l.Inactive=@Status) OR (@Status=2))
The entire sproc hangs (times out)....I've properly indexed my tables and
I even have an index on the Inactive field within my linked table...If I
take that same line above and just say:
AND (l.Inactive=0)
It runs fine, so the OR condition is causing it (boolean). However, I need
this condition as a parameter is passed that needs to be satisfied. What
are my other options, do I have to IF BEGIN... using all these parameters?
It seems cumbersome...For anyones information the AssetProgram table has a
total of 50k rows, so that isn't too much.