Advanced Search Stored procedure

Posted by Ray Eatmon on Programmers See other posts from Programmers or by Ray Eatmon
Published on 2013-07-02T14:50:44Z Indexed on 2013/07/02 17:14 UTC
Read the original article Hit count: 468

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?

© Programmers or respective owner

Related posts about sql

Related posts about sql-server