Advanced Search Stored procedure
- by Ray Eatmon
So I am working on an MVC ASP.NET web application which centers around lots of data and data manipulation.
PROBLEM OVERVIEW:
We have an advanced search with 25 different filter criteria. I am using a stored procedure for this search. The stored procedure takes in parameters, filter for specific objects, and calculates return data from those objects. It queries large tables 14 millions records on some table, filtering and temp tables helped alleviate some of the bottle necks for those queries.
ISSUE:
The stored procedure used to take 1 min to run, which creates a timeout returning 0 results to the browser. I rewrote the procedure and got it down to 21 secs so the timeout does not occur. This ONLY occurs this slow the FIRST time the search is run, after that it takes like 5 secs.
I am wondering should I take a different approach to this problem, should I worry about this type of performance issue if it does not timeout?