468,283 Members | 1,949 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,283 developers. It's quick & easy.

SQL server - script execution time

Hello All.

We have tested following SQL script from query analyzer:
-- Script begin
DECLARE @I int;
SET @I = 1;
WHILE @I < 10000000 BEGIN
SET @I = @I + 1;
END
-- Script end

The script was tested on the folowing PCs with following results:
PC 1:
Pentium 4:
CPU: 1.6 Mhz
RAM: 256 Mb
HDD: 80 Gb (IDE)
OS: Windows Advanced Server SP4
SQL: Developer Edition SP4

Result: Script execution time - 0:54

PC 2:
IBM X445, 4 XEON 3.0 Ghz, 4 Mb L3 Cache
RAM: 8 Gb
RAID 5 with (SCSI) 15k disks
OS: Windows 2000 Advanced server SP4
SQL: Enterprise Edition SP 4 + Fix

Result: Script execution time - 2:19

PC 3:
DELL Power edge 1600, 2 XEON 2.40 Ghz,
RAM: 1 Gb
RAID 1 with (SCSI) 10k disks
OS: Windows 2003 Enterprise Edition SP1
SQL: Enterprise Edition SP 4 + Fix

Result: Script execution time - 1:16

NOTE 1:
All PCs have latest hardware updates

NOTE 2:
PC 1 - is local pc, with no external connections
PC 2 - is server with some number of other, external connections;
PC 3 - is server with a few external connections;

I have following question: Is the above mentioned execution time is
normal for such script ?
Also it would be very nice of You to run the script on Your SQL servers
and inform me about execution time results

Thank You beforehand

Nov 23 '05 #1
3 16213
Am 22 Nov 2005 07:57:33 -0800 schrieb ia****@gmail.com:
DECLARE @I int;
SET @I = 1;
WHILE @I < 10000000 BEGIN
SET @I = @I + 1;
END


In the QueryAnalizer on my cheap A64/3000+ it needs 23 seconds to finish.

bye,
Helmut
Nov 23 '05 #2
(ia****@gmail.com) writes:
Hello All.

We have tested following SQL script from query analyzer:
-- Script begin
DECLARE @I int;
SET @I = 1;
WHILE @I < 10000000 BEGIN
SET @I = @I + 1;
END
-- Script end
...
I have following question: Is the above mentioned execution time is
normal for such script ?
Also it would be very nice of You to run the script on Your SQL servers
and inform me about execution time results


On my machine it ran for 11 minutes and 15 seconds on SQL 2000 and 15
seconds on SQL 2005.

When I put it into a stored procedure and had SET NOCOUNT ON, it ran for
19 seconds on SQL 2000.

I ran into this some time ago, and first I thought it was an issue with
SP4, but that was a false alert. I think I came to the conclusion that
parallelism was part of the plot. That is, this sort of script typically
runs slower on a multi-CPU machine. (Including hyper-threaded CPU:s).

I also found that with SET NOCOUNT ON *and* the loop in a stored
procedure, that I did not get the absymal performance.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #3
1 x XP2000+ 512MB 27 seconds 100% CPU usage.
2 x MP2000+ 1GB - gave up after 6 minutes, 0% CPU usage.

Nov 28 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by 11abacus | last post: by
10 posts views Thread by Doug | last post: by
13 posts views Thread by Fortepianissimo | last post: by
3 posts views Thread by #pragma once | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
2 posts views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.