Advanced Search Stored procedure

Question

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?

Answer 1

No, you don't need to worry about a problem that isn't occurring. If your query isn't timing out or running unacceptably slow, you can allocate your time elsewhere.

If you are allowing very complex queries, the question of why you are not allowing slightly sanitized SQL arises. And if you allow arbitrary select statements, then how you handle timeouts and errors and cancellations becomes more important than the performance of your gui-sourced sql.

I've seen and worked with web apps that don't allow direct sql even for competent users, and the results have always been disappointing. Empowerment lets you handle the edge cases from a design perspective, and focus your attention on where it can get the most benefit instead of doing a user's work for them all the time.