Jump to content

Ajax query taking a while to return


oracle765

Recommended Posts

Hi all

 

we have an ajax query which queries a MySQL database for cities around the world for our hotel search.  We are running on a Linux web hosted server.

 

The problem we are having is when you first visit out site and type in Brisbane or Sydney it takes around 15 seconds maybe to return.  After that what ever city you query is lightening fast.  I have had a few friends try this and when they first visit our site they experience the same then second time around it is fast.

 

Could this be some sort of caching issue or setting somewhere that I am missing?

 

I have attached a file so you can see what I mean

 

Thanks in advance

post-166889-0-93344600-1401266572_thumb.jpg

Link to comment
Share on other sites

yes, if you look at the url it is compareandchoose.com.au/newlook/hotel as the compareandchoose.com.au/hotel just uses hotels combined search widget with compareandchoose.com.au/newlook/hotel we are querying expedias api and are trying to get the drop down to display the search results of cities which are in a MySQL database.

 

when a user first visitis our site to search on a city which is extracted from our db the first time round this is very slow, second time around it is very fast

 

 

please advise

 

thanks

Link to comment
Share on other sites

It seemed fast for me when I tried it.

You can try posting your code here, maybe is something that would stand out.

 

Can make an index in mysql

http://dev.mysql.com/doc/refman/5.7/en/create-index.html

 

Run explain to see whats going on

http://dev.mysql.com/doc/refman/5.7/en/explain.html

 

Check the slow query log

http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

Link to comment
Share on other sites

I can try and do this tomorrow for you, I am not that advanced in MySQL but I will give it ago.  But I would like to mention first that I do not think its the actual query as when I run the query in MySQL itself it returns in milliseconds.

 

I think it is initially between the user first visiting our website, I think because they first visit and try to search for a city we then connect to the db and issue the ajax query so I think it could be something where we initiate the connection initially what is causing they delay.

 

Could I be correct?

 

thanks

Link to comment
Share on other sites

without any specific information to go by (i.e. profiling your site and profiling your code to find out exactly where the time is spent and how it changes between the first visit and follow on visits), this is just a general guess based on the symptom. your web server and/or database server may be either allowing the disk drives to come to a stop (in general, disk drives on servers run continuously) or they are not configured with a significantly long enough (in time) disk caching (query caching for the database server) so that the initial page request is always reading from the disk instead of from the disk cache/query cache.
 
i did check your page - compareandchoose.com.au/newlook/hotel at pingdom.com - http://tools.pingdom.com/fpt/#!/xwShk/compareandchoose.com.au/newlook/hotel and the only serious thing that jumps out is your bg2.png image is 1.5mb, it's also apparently loaded via javascrpt and so doesn't even get requested until after the javascript library has been loaded and runs.

 

this may not be relevant to your problem, but one of the times that i can recall a php application taking a huge amount of time on the first page load after a period of inactivity was for a shopping cart script that was built using a 'read everything regardless of it was even used' plugin method that literally included every possible/optional file in a collection of folders and was reading several hundred files (200+) every time a page was requested and when the content of these files was not in the disk cache, they had to be read from the disk.

Edited by mac_gyver
Link to comment
Share on other sites

ok thanks I will look into the png problem.

 

in the meantime I have collected all the files that I think may be getting called which includes the actual ajax query and also a screen shot of the database tables because this was written by an offshore company so I am actually not sure what is exactly going on or exactly where the problem lies .

 

I hope this helps & thanks again for you taking the time to assist me it is very much appreciated

post-166889-0-51069400-1401580303_thumb.png

config.txt

hotel form check.txt

hotel search ajax.txt

hotel.txt

post-166889-0-97597000-1401580715_thumb.png

post-166889-0-18163200-1401580734_thumb.png

Link to comment
Share on other sites

i have reviewed your recent threads, this thread again, the code you posted above, and tried the page.

 

it would seem this is the third thread since the start of this year where you have mentioned slow response times. do pages on your site that also depend on the database work as expected? is this only when searching via ajax? do you have more than one database server and this only occurs for one specific server?

 

the only things i have to recommend at this time are -

 

1) add a page generation time calculation to the hotel search ajax.php page, using microtime(true), and log it to a file, along with the date/time, ip address, and the search value. you can then look at this log of information to both see if the problem is in getting the data from the database server or is elsewhere and if the problem only occurs after a period of inactivity.

 

2) the code has NO security against sql injection. the search string needs to be escaped before putting it into the sql query statement.  also, since the purpose of hotel search ajax.php is to only service the ajax request, the database connection code should be inside the if($_POST) { ... } logic.

Link to comment
Share on other sites

hi mac_gyver

 

thanks for that, I have put the connection info within the If statement to only serve the purpose of the Ajax and by the looks of things that seems to have done the trick, I asked a friend to try it to and he said it was instant, many thanks for that, I will double check this in the morning after a period of time to make sure its stable but I can tell you I tried on ipad and mobile phone and it was quick so looks like it was the issue

 

anyway you mentioned in your reply that there is no security against sql injection. the search string needs to be escaped before putting it into the sql query statement.

 

can you explain this in liament terms please as I have no idea how to do that

 

thanks again

 

alan

Edited by oracle765
Link to comment
Share on other sites

the suggestions to put the connection lines inside the logic was just to prevent unnecessary database connections when things like search engines or bot scripts request the page using a request that's not from your ajax based search page.

 

the delay symptom is consistent with the database server's disks not being configured to run continuously and you must wait for the disk to start and come up to speed before it can be accessed, though the delay would be more likely less than 10 seconds. the database server may be experiencing a more serious problem. in any case, you must find the actual cause of the problem before you can do anything to fix it.

 

as to escaping string data, all string data being put into a sql query needs to be escaped to prevent any sql special characters from breaking the sql syntax, which also allows hackers to inject their own sql into your sql statement. for the php mysql_ functions, you would use mysql_real_escape_string() (and if you happen to be using an older version of php, you need to test if php is trying to escape the data and actually undo what php is doing before properly escaping the data yourself.) however, the mysql_ functions are now depreciated and you should be using msyqli or PDO functions (which will let you use prepared query statements that also prevent sql injection.)

Edited by mac_gyver
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.