Why is this loop sooooo slow ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Why is this loop sooooo slow ?

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-16 : 09:57:39

I'm playing with a loop, trying to measure which select statment would be faster:

Select partcost * quantity
or
Select Total

(The total field already contains partcost * quantity.)

There is only one record in the table (Parts):

               
                          Record 1
 
PartName  varchar(20)     Part 1
partcost  smallmoney      12.0000      
Quantity  int             5
Total     smallmoney      60.0000

Here is the test code for the first SELECT I'm running - it takes over 10 seconds on my machine.

set nocount on
declare @tempans smallmoney
declare @start datetime
declare @end datetime
declare @loopcount int

set @loopcount = 0
set @start = getdate()

while @loopcount < 1000
begin 	
  set @loopcount = @loopcount + 1
  set @tempans = (select (partcost * quantity) from parts)
end

set @end = getdate()
set nocount off

select  @start
select  @end

Can anybody tell me why this runs soooo slow ?

Thanks,
Kevin

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-16 : 10:49:10

Takes a few milliseconds this system.
You don't have the execution plan display turned on do you? That would cause a huge amount of data to be sent to the client since it would have send a plan for each iteration of the loop!

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-16 : 11:02:03

Doh !

You da man !

Thanks,
Kevin

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-16 : 13:35:31

That was an impressive guess Arnold.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-08-18 : 08:57:44

Hi,

When we create an stored procedure using this same loop. What will do? I think that will faster compare to this SQL statements. Am i right?

":-) IT Knowledge is power :-)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources