Solved

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

Posted on 2008-10-17
15
3,544 Views
Last Modified: 2013-12-17
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
Comment
Question by:stevong
  • 5
  • 3
  • 2
  • +1
15 Comments
 

Author Comment

by:stevong
Comment Utility
Anyway is to use fulltext search.

But it's still a lot of db request and overheads :(
0
 
LVL 6

Expert Comment

by:openshac
Comment Utility
>>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
 

Author Comment

by:stevong
Comment Utility
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
 
LVL 6

Expert Comment

by:openshac
Comment Utility
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
 
LVL 10

Accepted Solution

by:
NeoDiffusion earned 90 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Assisted Solution

by:tvedtem
tvedtem earned 90 total points
Comment Utility
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
 

Author Comment

by:stevong
Comment Utility
Problem is that it is really a huge database :(
0
 
LVL 10

Expert Comment

by:NeoDiffusion
Comment Utility
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
 
LVL 10

Expert Comment

by:NeoDiffusion
Comment Utility
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
 
LVL 10

Expert Comment

by:NeoDiffusion
Comment Utility
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
 
LVL 10

Expert Comment

by:NeoDiffusion
Comment Utility
I personnally suggest Accept Expert posts as the answer, nameley id #22813067
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
The viewer will learn common shortcuts with easy ways to remember them. The viewer will then learn where to find all of the keyboard shortcuts, how to create/change them, and how to speed up their workflow.
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now