I have a test page that connects to a third-party DB, executes a stored procedure, and then spits out results.
I can literally see that page takes about 8-10 seconds to execute. Maybe more.
I try to time it in PHP but I get a 2 second result from the microtime() function.
Here is what I’m doing (I’ve stripped out the execution code to focus on the timing stuff).
$startMS = microtime(true); // Do stuff with DB $finishMS = microtime(true); $totalMS = ($finishMS - $startMS); echo "Started: ".$startMS.'<br>'; echo "Ended: ".$finishMS.'<br>'; echo "This page was created in ".$totalMS." seconds";
I literally SEE the page take a long time to show anything, but $totalMS returns 2.xxxxx seconds or so. Am I doing anything wrong? Should I go about timing the script another way?
A few possibilities:
You could check the server response times using a program like
traceroute (on linux; I’m sure there are Windows equivalents). Basically, just time a blank request/response.
(You say “third-party DB” — are you including the time it takes to connect/ disconnect in your test?)
To see if it’s activity other than your DB calls, literally remove everything else from the script and HTML response (a reduced test case —if you haven’t done this already).
As for checking Apache loads and request times, you’ll probably be at the mercy of your host (or whatever they pass off as “tech support”). Not to be negative, but it’s flat-out unlikely that you’ll get anything useful. Instead, you might ask them to put you on another, less-crowded server and see if the problem persists.
I think it’s very unlikely that PHP’s
microtime is not returning the correct times. It gets the time from the host system, so if it’s wrong/ slow, it would mean that the server’s clock is wrong/ slow. I suppose you could check by getting the timestamp a few mornings in a row, at the same time, and checking that the intervals are (reasonably) equal.
I think I mis-titled the post. Microtime is definately NOT the cause here. I didn’t mean that at all. I guess I was just unsure of whether or not I was actually storing the proper time of athe PHP execution.
I’m using an IIS server set up and controlled by our client. I can have influence on how things are set up, but right now I have to use a VPN to gain access.
The database is MS Server.
They attribute the speed to the number of records in the view I am using as my query (~22,500 records). I can run a query on 10k records in an MySQL database on another Apache server at 40ms. When I look at Chrome’s dev tools for the third-party DB query, I see it receives data for ~8 seconds. Quite the jump.
When I strip out the sql query, it takes milliseconds to connect.
Put the query back in, back up to 8-10 seconds.
So perhaps it is the number of records…? I thought views were quicker than queries and would store temp results but the execution time is about the same each time I run the script. I am by no means an expert on server configuration, and I barely ever use views, so take my results as you’d like. I’m appreciating any insight anyone has one the reasons why a stored procedure takes that long to run. I didn’t think 22k records was a whole lot :/
I’m using an IIS server set up and controlled by our client.
My condolences (on all fronts).
So perhaps it is the number of records…? I thought views were quicker than queries and would store temp results… I’m appreciating any insight anyone has one the reasons why a stored procedure takes that long to run. I didn’t think 22k records was a whole lot :/
Well, 22k rows is nothing if the database and queries are well-designed and indexed. On the other hand, 100 rows might be an impractically huge query if the DB is poorly optimized.
afterthought: how large is the data you’re retrieving?
Did you build the DB (from your comments, I get the impression the DB was preexisting, but you might have written the particular stored procedure)?
I might be able to offer some insight if I saw the stored procedure you’re using. I don’t work with MSSQL specifically, but I’m good with SQL in general and relational design.
Well I spoke to my client and they gave me some insight and info on their database schema and the views they use.
Yes, I am using someone else’s DB. They are a fairly large company and have multiple departments and politics to sort through in order to get things done. Not bad turn-around though for my requests :)
I got the stored procedure code and was able to break it down and figure out how to speed it up. There is a LOT of joins and data in each table. I started by searching for a single piece of data from a single table. It took the same 8-10 seconds. I counted the rows and there were ~500k.
So I looked into using LIMIT… nope that’s MySQL. Gotta use TOP. So I searched for the TOP 10 records. BAM quick as a cat!
I then wanted to try a paging system. FETCH-OFFSET… Nope this is SQL Server 2008 :(
Another Google search lead me to an inner-select query using ROW_NUMBER(). And it turned out it was also super quick! :)
Now my question is, if I’m searching for JUST one piece of data (the ID of a table), is 500k rows a lot? Should it take 8 seconds to return JUST the ids of this table? This is the first time I’ve used a table this large so perhaps it is normal.
Now my question is, if I’m searching for JUST one piece of data (the ID of a table), is 500k rows a lot?
Are you actually selecting all those
ids? A result set like that could be several MBs in size, depending on the data type of the
id column. The query itself should still be fairly quick, but transferring the data might take a noticeable amount of time.
Or are you looking for a specific
id from a table with 500k rows? Assuming the
id is indexed, finding it should be nearly instantaneous.
Is there a
where clause or other conditions in your query? If so, make sure the table’s indexes also cover those conditions as much as possible.
You also mentioned “inner select” —SQL engines can optimize some inner queries, but not others. Or, certain types of inner queries can be run once for the entire query, where others trick the engine into running them once per row. (This is the same problem people often run into with joins: joins aren’t slow in and of themselves, but it’s easy to trap the DB into executing them very inefficiently.) If you’ve got one that can’t be optimized, you should look into how to get it optimized. This will be very MSSQL-specific; I’m afraid I couldn’t offer much advice on it.
I started by searching for a single piece of data from a single table. It took the same 8-10 seconds
This would make me worry that there is no index for the data you are searching, forcing the DB to scan the entire table. See if you can add an index that covers your search.
I’m sure MSSQL has something similar to
EXPLAIN. Results from explains can be difficult to read/understand (I hate them), but there’s usually something you can combine with super-googling and get something helpful in return. Finding the right words to search S/O could be very helpful as well.
Thank you VERY much for taking the time to shed some light on this. It’s definately helped me keep my head on straight.
I think the query was actually fairly quick, but returning the result set was more of the issue. I now know how to differentiate between the query and returning the data. I had no idea a result set could be an issue.
Lots of great info here.
You must be logged in to reply to this topic.