1

We've just finishing up the first release of a database driven web application, which is now in regression testing. The application has an advanced search with many different filtering criteria. When the search is first used with only the 3(of 28) minimum required search criteria it takes like 15-20 secs to calculate and retrieve the data for the page to load.

This happens only the first time the search(uses a stored proc) is run. The search thereafter take 2-6 secs.

The stored procedure has medium complexity around 1500 - 2000 lines. I would like to get it down to run the first time to 6 secs and thereafter like 1-2 secs but I can't seem to find the time to make it faster.

My question is, is this acceptable for users if it is only slow the first time the search is run? Has anyone else had a similar experience with creating advanced searches? What was your solution?

Improve this question

2 Answers 2

Reset to default
11

"Is it acceptable" is not a technical question; it's a product question. In some products, 2-6 seconds might be considered hideously slow while in others, 15-20 seconds might be pretty zippy. How fast are competing products? How fast are similar products? What percentage of customers will see the long original query vs. the faster cached query? If you expect ten million queries, one slow one will be trivial. If you expect ten, then not so much. Answers to these questions will tell you if you need to optimize.

One technical solution you might try would be to prewarm these queries. It isn't clear to me exactly how many possible queries there are, but if you have a low enough number of these "slow" queries, just immediately run them after you deploy so no actual customer sees them. If there are an extremely large number of possible queries, then maybe run those that you find are most common.

Improve this answer
3
  • It's just one specific query that takes parameters. I did look a little into running the query immediately after deployment. That might be the best option for the moment. Commented Jul 5, 2013 at 4:15
  • @RayEatmon precompiling queries and preloading caches (which is what you're achieving by running that) is often a good way to flatten out performance spikes like that. Commented Jul 5, 2013 at 5:30
  • Good example of Steven's answer: during my graduation work I was running experiments which took 12-48 hours to collect a single set of data for my thesis, I needed several dozen such sets. I couldn't care less if the software I was using took hours to process the results of each run, as long as it was done before the next run was complete. Commented Jul 5, 2013 at 5:32
2

You can do several things:

Create views for performance. A view is a contract of intention that tells the RDBMS "I will be running this query very often". When you do that the RDBMS optimizes the access to this data.

Create views for simplicity and tuning. You create views in order to split the big solution into smaller solutions. This way you can measure exactly where the bottleneck is and try to improve the problematic areas.

Create denormalized tables ( materialized views ). Sometimes it pays back to create summarized tables in order to make certain reports faster, especially of data that is not transactional.

Create a non-transactional database or datawarehouse for queries. Most RDBMS has ways of tuning a special database for queries when this databases is not transactional, ie, it's not being hit continuosly with inserts or updates. The whole set of tuning configurations if different when the database has this purpose. It's more efficient than a general purpose database.

Put most queried tables in faster disks. Move most queried tables in tablespaces located in better, faster disks and better connections (SAN). Move not so important tables to slower, cheaper disks and not so fast connections.

Use structure programming techniques to split your stored procedures into smaller ones, so you can keep complexity at bay. That way you can concentrate better on improving performance once you don't have all the complecity in your face all the time.

Use queries with binding variables avoiding concatenated ones. That way the RDBMS is able to cash them in the query area and they run faster because you save parsing time and planning time.

Remember that your DBA is your friend, and he/she can do a lot to improve database performance. Don't rely solely on your programming skills. You are not alone.

Improve this answer
3
  • Do you have benchmarks supporting theview ccreation that show significant differences from creating the right indicies? Commented Jul 5, 2013 at 5:16
  • 1
    @BillyONeal Did I imply that the use of views involves not using appropriate indexes ? Views are based on queries. Queries should use the right indexes. Commented Jul 5, 2013 at 5:37
  • No, I mean, do you have any benchmark showing adding indices + adding views giving a greater performance than just adding indices? Commented Jul 5, 2013 at 18:31

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.