CODEWITHSUNDEEP
sql-serverreporting-services
Sultan of Swing
Sultan of Swing

Reputation: 468

SQL Server function optimization (takes too long)

I have designed a function that works with an SSRS report. I have a drop down parameter that lists multiple items and only one can be selected. This drop down gets its data from a query/data set, and I added one line of data that says 'All' in it. So the dropdown will look like this:

  1. Item1
  2. Item2
  3. Item3
  4. All

And then in the function, I make one small change in the where clause:

...where (@parameterName = 'All' or table.name = @parameterName)

The problem with this is that table.name has about 50000 rows of data. When the user selects 'All' in the drop down, I would have thought that since the first statement in brackets is true, and that the next statement (after the 'or') should not even be executed. But it causes the query to run for 5-20 minutes and still does not produce any result after that long. If I simply change the where clause to

...where (@parameterName = 'All')

The function runs in less than a second, if the user still selects 'All' from the drop down.

I implement a similar concept with another filter but I guess because the table that that parameter uses is much smaller (about 90 rows), so it doesn't take long.

Is there basically a way to have an optional parameter that is not expensive to calculate?

EDIT: I will add that the parameter is declared as nvarchar(max). Will changing this to something smaller help the query?

Upvotes: 0

Views: 36

Answers (1)

Thom A
Thom A

Reputation: 95830

What you have there is a catch-all query. Consider adding OPTION (RECOMPILE) to the end of your statement. This'll force the engine to recreate the plan each time it runs the query, meaning it won't use poor choices based on a previous run where your variable has a value like 'Item1'.

Upvotes: 1

Related Questions