SQLTeam.com | Weblogs | Forums

Passing a parameter to filter a table


#1

I have a stored procedure which outputs a table. However, I want the output to be contingent on the @Status parameter that is passed to the stored procedure.

I have resolved it by doing the following. It works but it is slow. Very slow. A small search takes about 30 seconds. If I remove the bottom two @Status options the same query drops to 4 seconds. Is there a better way to write the same thing? It looks like it's evaluating all of the conditions even though @Status can only be one of those listed.

	WHERE 
	(@Status = 1 
		AND d2aso.[date_time] BETWEEN @StartDate AND @EndDate
		AND o.TimeOfOrder BETWEEN @StartDate AND @EndDate
		AND (LEN(d2aso.order_id) > 2 OR d2aso.order_id IS NULL)
		AND oso.[OrderSource] IN (4,20)
		AND o.CompanyID = @CompanyID
		AND oi.ProductID = d2aso.sku)
OR	
	(@Status = 2 
		AND o.TimeOfOrder BETWEEN @StartDate AND @EndDate
		AND (LEN(d2aso.order_id) > 2 OR d2aso.order_id IS NULL)
		AND oso.[OrderSource] IN (4,20)
		AND d2aso.id IS NULL
		AND o.CompanyID = @CompanyID)
OR	
	(@Status = 3
		AND d2aso.[date_time] BETWEEN @StartDate AND @EndDate
		AND (LEN(d2aso.order_id) > 2 OR d2aso.order_id IS NULL)
		AND oso.[OrderSource] IN (4,20)
		AND o.CompanyID = @CompanyID
		AND (o.TimeOfOrder > @EndDate
			OR o.TimeOfOrder < @StartDate)
		)

#2

Have you tried running each status section separately?

Is one of the sections slowing it down, the you can focus in on that one.


#3

Would have to see the query plan to be sure what's going on here.

But, at least make sure that variables match the data type of the column (except for @StartDate and @EndDate which can be varchar in YYYYMMDD format). For accuracy, you should use GE and LT rather than between for dates:
AND o.TimeOfOrder >= @StartDate AND o.TimeOfOrder < {next day after @EndDate}


#4

I would start by simplifying the conditional matching:

WHERE o.CompanyID = @CompanyID
  AND oso.[OrderSource] IN (4,20)
  AND (LEN(d2aso.order_id) > 2 OR d2aso.order_id IS NULL)
  AND (
	(@Status = 1 
		AND d2aso.[date_time] BETWEEN @StartDate AND @EndDate
		AND o.TimeOfOrder BETWEEN @StartDate AND @EndDate
		AND oi.ProductID = d2aso.sku)
OR	
	(@Status = 2 
		AND o.TimeOfOrder BETWEEN @StartDate AND @EndDate
		AND d2aso.id IS NULL)
OR	
	(@Status = 3
		AND d2aso.[date_time] BETWEEN @StartDate AND @EndDate
		AND (o.TimeOfOrder > @EndDate OR o.TimeOfOrder < @StartDate)
		)
      )

Review what is left and see if you can move more out...for example, you don't include the d2aso.[date_time] when @Status = 2 - but should you? If you can include it and move it out of the conditional filtering it should also help...


#5

I didn't spend too much time looking at this, but on first glance I would recommend a table-valued function for anything that has identical WHERE clauses, rather than a stored procedure with separate OR clauses for each. The output is a function of the status, and nothing else, correct? Does that help?


#6

You might consider separate stored procedures based on Status.