SQL Server WHILE vs C# WHILE

[vjamalpur]

I was running a small test on how WHILE loop fucntions.
this is a simple SQL code that I ran

declare @COUNTER int
set @COUNTER = 1

while @COUNTER <= 100000
begin
select @COUNTER = @COUNTER + 1
end

It took 7 seconds to run

I ran the C# equivalent which did not even take a second (fraction of second)

int counter = 1;
while (counter<= 100000)
{
counter += 1;
}

My question .... Is the WHILE in SQL Server so bad in comparison to C# one... or Am i doing smth wrong or Can I do smth to improve it...

[ck9663]

There are a lot of factors why it's slow in SQL Server. One of those factors is the optimizer. When you run it the first time, there's no execution plan for your query yet. Try running it again a couple of times after and you'll see an improvement in execution time.

Good Luck!!!

~~ CK

[vjamalpur]

Ran it multiple times but did not reduce the execution time drastically... I only could get it down to 6 secs....


I even created a Stored Procedure (for execution plan sake)... and executed the stored procedure multiple times... even then the execution time stayed at 6 secs....

Thanks for your insights...

[ck9663]

There must be something else going on with your server. I ran your code and it did not even reached 1 sec.

Happy Coding!!!

~~ CK

[vjamalpur]

Thanks for the advice...
I'm connect to my company's network using VPN....
then I connect to my Database Server to open the Management Studio...and run the query that I gave...

My understanding is that the above setup should not be an issue... there should be smth else ?

[ck9663]

Try connecting via RDC to your server and run it from there. The traffic could be a factor.

~~ CK

[NeoPa]

Where did you run the C# process from?