Ajax suggest – bash your SQL server!
We just went live with 6 months worth of work and it has been really smooth considering the amount of changes. Aside from the stupid questions from userland we haven’t had any major issues. We did however experience some slow downs which we could attribute only to the AJAX suggester tools we had implemented in about 5 different places. Using CFAJAX we help the users pull out suggestions for property addresses, client names, suburbs, solicitor companies and activity plans.
This is going to grow as time goes on. Simply its a text box that the user starts typing into and then we display a div below it that lists the result of the query matching the input. We quickly realised that each keystroke fires a query on the server. So you could get alot of queries in a VERY short period of time, especially when 10 users are doing it all at the same time. This was blocking up the SQL server and dragging down all services that rely on it. So we had to introduce a timeout on the keypress:
Instead of the keypress function on the text input pointing to getPropertyHelper() we moved it to point to timeProperty() – this then stops the requests being fired instantly, and allows a one second break so we only fire one query!