SQLTeam.com | Weblogs | Forums

Passing a parameter to filter a table

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)
		)

Have you tried running each status section separately?

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

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}

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...

1 Like

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?

You might consider separate stored procedures based on Status.