Community discussion forum

Optimizing the search query of a web application

• • Reply

  1 year ago

  by lynxmax

  Lynx Max

  , India

  Joined 1 year ago

  An application has a poorly performing search functionality. customer is keen to refactor the search and improve its performance. At a high level the sequence of steps in performing the search is as follows,

  • The end user entered the search criterion on a UI screen comprising of approximately ten fields, • The search is submitted to the web based application,

   • The web application calls a Stored Procedure and passes in all the parameters which were obtained from the UI,

  • The SP Retrieves the entire data set possible based on a basic filter criteria and loads a temporary table,

  • The SP then begins filtering out (deleting) the contents of the temporary table based on the filter criteria passed in as parameters. This is carried through further select statements in the same stored procedure. The technology utilized in this application is as follows,

  • Web Application - .Net 1.1 (C#)

  • Database – SQL Server 2000

  • Web Server – IIS 6.0 Deployment details

  • The application is deployed on a server farm

  • The database is not clustered (single instance) Could anyone share similar experiences they have had in optimizing web based search queries and an overview of the approaches they had taken.

  Report abuse
• • Reply

  1 year ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 8 years ago

  There are some key tools for you to use that will help at least narrow down the nasty queries:

  in SQL Query Analyzer/MAnagement Studio, choose "Show Execution Plan" when you run the stored procedure. This will show you where the time is being spent when executing your query, and also allows you to check that the indexes you expect are being hit.

  You can also use the SQL Profiler to get a top-level view of what queries are being executed, and which are taking the longest time.

  Once you've identified the problem areas, you can then drill in to the query further and work out if there are better indexes, or simply better queries that you could be writing.

  Hope that helps
  

  Report abuse