Solved

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

Posted on 2008-10-17
15
3,547 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
ID: 22738670
Anyway is to use fulltext search.

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

Expert Comment

by:openshac
ID: 22738692
>>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
ID: 22738739
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 6

Expert Comment

by:openshac
ID: 22738777
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
ID: 22740539
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
 
LVL 4

Assisted Solution

by:tvedtem
tvedtem earned 90 total points
ID: 22754007
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
ID: 22812573
Problem is that it is really a huge database :(
0
 
LVL 10

Expert Comment

by:NeoDiffusion
ID: 22813067
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
ID: 22813082
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
ID: 24126746
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
ID: 24174119
I personnally suggest Accept Expert posts as the answer, nameley id #22813067
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Free HD cloner 11 63
How many transactions can mysql handle? 3 33
Create workstation base image 4 43
Present Absent from working date rage 11 21
If your app took Google’s lash recently, here are the 5 most likely reasons.
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
This video demonstrates basic masking and how to edit the mask to reveal the desired image.
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.

821 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