Solved

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

Posted on 2008-10-17
15
3,548 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
Application Crash 2 25
Function after success of Jquery/AJAX action 9 28
Gaming Software 1 19
Formating field inside mysql query 2 13
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

860 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