• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3557
  • Last Modified:

Ajax - Auto suggest drop down box. How to reduce Database overheads?

Hi everyone,

Right now, we are developing a system with which the results are displayed real time via AJAX. What happens is that as user is keying  in a value in the textbox, the results are displayed accordingly and is changed immediately based on what is keyed-in.

This in turn will cause a huge database overhead in a heavy traffic enviroment.

Most of the queries are

select col1, col2 from table1 where col3 like '%something%'

Are there any better ways to implement this?

Please advise.

Thanks
0
stevong
Asked:
stevong
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
stevongAuthor Commented:
Anyway is to use fulltext search.

But it's still a lot of db request and overheads :(
0
 
openshacCommented:
>>This in turn will cause a huge database overhead in a heavy traffic enviroment.

Not really you're only send a few records (probably no more than 10) at a time.
Yes there are many more trips to the database but the "TRAFFIC" is by no mean "heavy".
0
 
stevongAuthor Commented:
So far most of the the systems I've implemented are using some kind of caching mecahnism. Server will fetch from plain text files instead of fetching from DB everytime. Cache will only be rebuilt based on the timestamp when the article is generated. Database request is kept to the utmost minimum.

But when it comes to the auto suggest fanciful features, I'm a little bit worried of the database load.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
openshacCommented:
What is the difference in traffic between returning the full resultset and a set of autosuggested results.
My hunch would be that the traffic is less for the autosuggested results
0
 
NeoDiffusionCommented:
Hello stevong,

If you're worried about over-loading your sql server by "useless" queries, I see 2 improvements axis:

  1. reduce Ajax calls; Usually, this is done by:
    • wait for a certain amount of characters before running ajax query (3 for instance)
    • do not run query until user stops typing for xx msec (500msec for instance)
  2. cache sql answer; efficency will vary depending on frequency of searchs. This can be done by managing an index DB or flat file(s) with query and answers.
Hope this helps,

Werner.


0
 
tvedtemCommented:
NeoDiffs two ideas are both good ones.

If you are worried about the DB load though, best to arm yourself with some facts / timings from the DB.  That's pretty quick to do and will tell you how much you need to think about caching, delays etc.

I suspect you are worried that '%something%' doesn't lend itself to indexes - you'd be right to worry about that.  Options might include text-based indexing on your DB (depending what it is) - or if you can get away with 'something%' rather than '%something%' then so much the better.

Unless your tables or volumes are very large, though, you might find that it performs OK.
0
 
stevongAuthor Commented:
Problem is that it is really a huge database :(
0
 
NeoDiffusionCommented:
Hello Stevong,


Having a huge database is another reason for applying the recommendations :-)

Note that "caching" does not mean "caching everything". In your context, caching will be there not to speed up delivery, but to reduce queries. As such, you don't need to have a huge cache. You can start by caching (storing) the answers from last 1 hour. If too few items cached, increase (for 2, 4, 8 or more hours), if too many, decrease (for 30min, 10 min, or less) cache validity duration.

Implementing such cache is pretty easy, even with "flat files".

Then, don't forget that you can also reduce request count by setting timeout before running an ajax query against your server.

Rgds,

Werner.

0
 
NeoDiffusionCommented:
Note:
as tvedtem wrote, spending some time on analyzing (first: recording...) the usual requests  will help you uderstand where to put the emphazis: if all requests are different, caching is of no interest, for instance.
0
 
NeoDiffusionCommented:
Hello angellll,

I believe that myself (neodiffusion) and tvedtem provided clear answers and follow'up.

I would rather suggest a point split.

Rgds,
Werner.
0
 
NeoDiffusionCommented:
I personnally suggest Accept Expert posts as the answer, nameley id #22813067
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now