SQL so slow...

shawjames

So I have been having serious load time problems.

My scripts ran fine, up until a few weeks ago. Now they all run painfully slow. Nothing in the scripts have changed.... and sometimes they load at normal speed for a short period of time before going back to being painfully slow, and sometimes timing out.

This has been hair-ripping frustrating. I have done some testing, and found it only happens on pages with SQL queries. The more queries, the longer the page load. The actual size of the html does not seem to matter as much. A page with about 100 queries took 5-10 seconds before, now it takes 2-3 minutes and sometimes longer or time out.

I have had much discussion (and runaround) with my webhost ipower.com. I am on a shared webhost account. Usually, if I can get them to restart the SQL server, things clear up for awhile... then go back to being painful slow. Once, they claimed that someone else on my same server was abusing the server resources, and they were dealt with.

What should I do...
Should I move to another host? Another server?
Is this common with shared webhost accounts? Should I switch to a dedicated server?

Any input would be greatly appreciated!

frost110

Could be the shared webhost. I would make Indexes on your common search data fields. IE if you pull data by username mostly, make that an Index. This will increase the speed.

Other than that, I do not know.

--FrosT

Roger_Ramjet

100 queries on one page it TOOOO MANY, no matter what the reason. Smacks of queries inside loops, and almost always they could be done with 1 or 2 queries and no loop.

MarkR

Smells like a server problem.

On your local dev server, try to create a database the same size as the production server's one.

If it's not against your policy, load the actual production data on to your development server (even though this is not a recommended technique).

Then you can carry out whatever necessary load testing will determine the cause of the slowness.

Presumably your dev machine is as close to the host's one in terms of OS and hardware.

The problem "smells" like a server load issue; it could also be because the size of data has increased - which is why you're not seeing these problems on your development server.

100 queries is quite a lot, but not excessive, particularly if it's a low traffic site.

Mark