expanding on working query ....

Thursday, December 09, 2010 3:39 PM

I have this query that will run and give me the results for facility 888... I have about 255 facilities i need to run the same query on......

This query will select all of the facilities

SELECT Code, Name
FROM Facilities
ORDER BY Code

This is the working query that will select a single facility report..

DECLARE 
@haCount int, 
@oneOrMore int, 
@twentyFiveOrMore int, 
@fiftyOrMore int, 
@seventyFiveOrMore int, 
@hundredOrMore int, 
@registered int, 
@activities int,
@facilityCode int


	select @facilityCode = '888'
	



	SELECT @HACount = COUNT(EE.EmployeeId) 
	FROM XREFEmployeesInvitations EE 
	JOIn Activities EV ON EE.ActivityId = EV.id 
	Join Employees E ON EE.employeeid = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	AND IsCompleted = 1 AND EV.SectionId = 1

	DECLARE @results table (id int, credits int, IsSpouse bit)

	INSERT INTO @results (id, credits, IsSpouse) 
	
	
	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 1

	SELECT @oneOrMore = @@ROWCOUNT

	INSERT INTO @results (id, credits, IsSpouse) 
	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 25

	SELECT @twentyFiveOrMore = @@rowCount

	INSERT INTO @results (id, credits, IsSpouse) 

	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId , IsSpouse HAVING SUM(Credits) >= 50
	SELECT @fiftyOrMore = @@rowCount

	INSERT INTO @results (id, credits, IsSpouse) 
	SELECT V.EmployeeId, SUM(Credits) , ISSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 75

	SELECT @seventyFiveOrMore = @@rowCount

	INSERT INTO @results (id, credits, IsSpouse) 
	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 100

	SELECT @hundredOrMore = @@rowCount

	SELECT @registered = COUNT(E.ID) 
	FROM Employees E
	Join Locations L 
	ON E.LocationId = L.Id
	JOIN Facilities F ON L.FacilityID = F.ID
	WHERE UserId IS NOT NULL AND 
	F.Code = COALESCE(@facilityCode, F.Code)

	SELECT @registered = @registered + COUNT(E.ID) 
	FROM Employees E
	Join Locations L 
	ON E.LocationId = L.Id
	JOIN Facilities F ON L.FacilityID = F.ID
	WHERE SpouseId IS NOT NULL 
	AND F.Code = COALESCE(@facilityCode, F.Code)

	SELECT @activities = COUNT(Ev.ID) 
	FROM Activities EV 
	Join Employees E ON Ev.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code)

	SELECT @haCount AS HaCount, @oneOrMore As OneOrMore, @twentyFiveOrMore As TwentyFiveOrMore, @fiftyOrMore As FiftyOrMore, 
	@seventyFiveOrMore As 	SeventyFiveOrMore, @hundredOrMore As HundredOrMore, @registered As Registered, @activities As ActivityCount

I am pretty new to variables in sql

Thanks in advance!!!

All Replies

Thursday, December 09, 2010 3:56 PM

You must moved all the condition (WHERE F.Code = COALESCE(@facilityCode, F.Code) )

DECLARE 
@haCount int, 
@oneOrMore int, 
@twentyFiveOrMore int, 
@fiftyOrMore int, 
@seventyFiveOrMore int, 
@hundredOrMore int, 
@registered int, 
@activities int,
@facilityCode int
select @facilityCode = '888'
SELECT @HACount = COUNT(EE.EmployeeId) 
FROM XREFEmployeesInvitations EE 
JOIn Activities EV ON EE.ActivityId = EV.id 
Join Employees E ON EE.employeeid = E.ID 
JOIN Locations L ON E.LocationId = L.ID 
Join Facilities F ON L.FacilityID = F.Id
Where IsCompleted = 1 AND EV.SectionId = 1
DECLARE @results table (id int, credits int, IsSpouse bit)
INSERT INTO @results (id, credits, IsSpouse) 
SELECT V.EmployeeId, SUM(Credits) , IsSpouse
FROM vwUserCredits V 
JOIN Employees E ON V.EmployeeId = E.ID 
JOIN Locations L ON E.LocationId = L.ID 
Join Facilities F ON L.FacilityID = F.Id
GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 1
SELECT @oneOrMore = @@ROWCOUNT
INSERT INTO @results (id, credits, IsSpouse) 
SELECT V.EmployeeId, SUM(Credits) , IsSpouse
FROM vwUserCredits V 
JOIN Employees E ON V.EmployeeId = E.ID 
JOIN Locations L ON E.LocationId = L.ID 
Join Facilities F ON L.FacilityID = F.Id
GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 25
SELECT @twentyFiveOrMore = @@rowCount
INSERT INTO @results (id, credits, IsSpouse) 
SELECT V.EmployeeId, SUM(Credits) , IsSpouse
FROM vwUserCredits V 
JOIN Employees E ON V.EmployeeId = E.ID 
JOIN Locations L ON E.LocationId = L.ID 
Join Facilities F ON L.FacilityID = F.Id
GROUP BY V.EmployeeId , IsSpouse HAVING SUM(Credits) >= 50
SELECT @fiftyOrMore = @@rowCount
INSERT INTO @results (id, credits, IsSpouse) 
SELECT V.EmployeeId, SUM(Credits) , ISSpouse
FROM vwUserCredits V 
JOIN Employees E ON V.EmployeeId = E.ID 
JOIN Locations L ON E.LocationId = L.ID 
Join Facilities F ON L.FacilityID = F.Id
GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 75
SELECT @seventyFiveOrMore = @@rowCount
INSERT INTO @results (id, credits, IsSpouse) 
SELECT V.EmployeeId, SUM(Credits) , IsSpouse
FROM vwUserCredits V 
JOIN Employees E ON V.EmployeeId = E.ID 
JOIN Locations L ON E.LocationId = L.ID 
Join Facilities F ON L.FacilityID = F.Id
GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 100
SELECT @hundredOrMore = @@rowCount
SELECT @registered = COUNT(E.ID) 
FROM Employees E
Join Locations L 
ON E.LocationId = L.Id
JOIN Facilities F ON L.FacilityID = F.ID
WHERE UserId IS NOT NULL 
SELECT @registered = @registered + COUNT(E.ID) 
FROM Employees E
Join Locations L 
ON E.LocationId = L.Id
JOIN Facilities F ON L.FacilityID = F.ID
WHERE SpouseId IS NOT NULL 
SELECT @activities = COUNT(Ev.ID) 
FROM Activities EV 
Join Employees E ON Ev.EmployeeId = E.ID 
JOIN Locations L ON E.LocationId = L.ID 
Join Facilities F ON L.FacilityID = F.Id
SELECT @haCount AS HaCount, @oneOrMore As OneOrMore, @twentyFiveOrMore As TwentyFiveOrMore, @fiftyOrMore As FiftyOrMore, 
@seventyFiveOrMore As SeventyFiveOrMore, @hundredOrMore As HundredOrMore, @registered As Registered, @activities As ActivityCount

Best regards

Thursday, December 09, 2010 3:58 PM

Try putting you logic inside a cursor, like this:

declare @mycursor cursor, @Code int, @Name varchar(100)



DECLARE 

@haCount int, 

@oneOrMore int, 

@twentyFiveOrMore int, 

@fiftyOrMore int, 

@seventyFiveOrMore int, 

@hundredOrMore int, 

@registered int, 

@activities int,

@facilityCode int



set @mycursor=cursor for

SELECT Code, Name

FROM Facilities

ORDER BY Code



open @mycursor

fetch next from @mycursor into @Code, @Name

while @@fetch_status=0

begin

	select @facilityCode = @Code



SELECT @HACount = COUNT(EE.EmployeeId) 

	FROM XREFEmployeesInvitations EE 

	JOIn Activities EV ON EE.ActivityId = EV.id 

	Join Employees E ON EE.employeeid = E.ID 

	JOIN Locations L ON E.LocationId = L.ID 

	Join Facilities F ON L.FacilityID = F.Id

	WHERE F.Code = COALESCE(@facilityCode, F.Code) 

	AND IsCompleted = 1 AND EV.SectionId = 1



	DECLARE @results table (id int, credits int, IsSpouse bit)



	INSERT INTO @results (id, credits, IsSpouse) 

	

	

	SELECT V.EmployeeId, SUM(Credits) , IsSpouse

	FROM vwUserCredits V 

	JOIN Employees E ON V.EmployeeId = E.ID 

	JOIN Locations L ON E.LocationId = L.ID 

	Join Facilities F ON L.FacilityID = F.Id

	WHERE F.Code = COALESCE(@facilityCode, F.Code) 

	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 1



	SELECT @oneOrMore = @@ROWCOUNT



	INSERT INTO @results (id, credits, IsSpouse) 

	SELECT V.EmployeeId, SUM(Credits) , IsSpouse

	FROM vwUserCredits V 

	JOIN Employees E ON V.EmployeeId = E.ID 

	JOIN Locations L ON E.LocationId = L.ID 

	Join Facilities F ON L.FacilityID = F.Id

	WHERE F.Code = COALESCE(@facilityCode, F.Code) 

	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 25



	SELECT @twentyFiveOrMore = @@rowCount



	INSERT INTO @results (id, credits, IsSpouse) 



	SELECT V.EmployeeId, SUM(Credits) , IsSpouse

	FROM vwUserCredits V 

	JOIN Employees E ON V.EmployeeId = E.ID 

	JOIN Locations L ON E.LocationId = L.ID 

	Join Facilities F ON L.FacilityID = F.Id

	WHERE F.Code = COALESCE(@facilityCode, F.Code) 

	GROUP BY V.EmployeeId , IsSpouse HAVING SUM(Credits) >= 50

	SELECT @fiftyOrMore = @@rowCount



	INSERT INTO @results (id, credits, IsSpouse) 

	SELECT V.EmployeeId, SUM(Credits) , ISSpouse

	FROM vwUserCredits V 

	JOIN Employees E ON V.EmployeeId = E.ID 

	JOIN Locations L ON E.LocationId = L.ID 

	Join Facilities F ON L.FacilityID = F.Id

	WHERE F.Code = COALESCE(@facilityCode, F.Code) 

	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 75



	SELECT @seventyFiveOrMore = @@rowCount



	INSERT INTO @results (id, credits, IsSpouse) 

	SELECT V.EmployeeId, SUM(Credits) , IsSpouse

	FROM vwUserCredits V 

	JOIN Employees E ON V.EmployeeId = E.ID 

	JOIN Locations L ON E.LocationId = L.ID 

	Join Facilities F ON L.FacilityID = F.Id

	WHERE F.Code = COALESCE(@facilityCode, F.Code) 

	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 100



	SELECT @hundredOrMore = @@rowCount



	SELECT @registered = COUNT(E.ID) 

	FROM Employees E

	Join Locations L 

	ON E.LocationId = L.Id

	JOIN Facilities F ON L.FacilityID = F.ID

	WHERE UserId IS NOT NULL AND 

	F.Code = COALESCE(@facilityCode, F.Code)



	SELECT @registered = @registered + COUNT(E.ID) 

	FROM Employees E

	Join Locations L 

	ON E.LocationId = L.Id

	JOIN Facilities F ON L.FacilityID = F.ID

	WHERE SpouseId IS NOT NULL 

	AND F.Code = COALESCE(@facilityCode, F.Code)



	SELECT @activities = COUNT(Ev.ID) 

	FROM Activities EV 

	Join Employees E ON Ev.EmployeeId = E.ID 

	JOIN Locations L ON E.LocationId = L.ID 

	Join Facilities F ON L.FacilityID = F.Id

	WHERE F.Code = COALESCE(@facilityCode, F.Code)



	insert into anotherTable -- create this table at the top

	SELECT @Code, @Name, @haCount AS HaCount, @oneOrMore As OneOrMore, @twentyFiveOrMore As TwentyFiveOrMore, @fiftyOrMore As FiftyOrMore, 

	@seventyFiveOrMore As 	SeventyFiveOrMore, @hundredOrMore As HundredOrMore, @registered As Registered, @activities As ActivityCount

 

	fetch next from @mycursor into @Code, @Name

end

close @mycursor

deallocate @mycursor

select * from anotherTable

I haven't tested this... but this should give you an idea.

The new table will have your all calculated items/fields.

~Manu
http://sqlwithmanoj.wordpress.com

Marked As Answer by SBolton Thursday, December 09, 2010 4:44 PM

Thursday, December 09, 2010 4:25 PM

Cursors are boggling my mind right now.... I have tried this below and the error is at the very bottom" insert into AllFacilityTable -- create this table at the top" I have created the table soooo I do not understand yet this concept, can you please provide some more help....

error

Invalid object name 'FacilityTable'.

Thanks!!!

Create table #FacilityTable(

Code int, 
Name nvarchar (100), 
haCount int , 
oneOrMore int, 
twentyFiveOrMore int, 
fiftyOrMore int, 
seventyFiveOrMore int, 
hundredOrMore int,
registered int, 
activities int 
)

declare @mycursor cursor, @Code int, @Name varchar(100)

DECLARE 
@haCount int, 
@oneOrMore int, 
@twentyFiveOrMore int, 
@fiftyOrMore int, 
@seventyFiveOrMore int, 
@hundredOrMore int, 
@registered int, 
@activities int,
@facilityCode int

set @mycursor=cursor for
SELECT Code, Name
FROM Facilities
ORDER BY Code

open @mycursor
fetch next from @mycursor into @Code, @Name
while @@fetch_status=0
begin
	select @facilityCode = @Code
SELECT @HACount = COUNT(EE.EmployeeId) 
	FROM XREFEmployeesInvitations EE 
	JOIn Activities EV ON EE.ActivityId = EV.id 
	Join Employees E ON EE.employeeid = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	AND IsCompleted = 1 AND EV.SectionId = 1

	DECLARE @results table (id int, credits int, IsSpouse bit)

	INSERT INTO @results (id, credits, IsSpouse) 

	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 1

	SELECT @oneOrMore = @@ROWCOUNT

	INSERT INTO @results (id, credits, IsSpouse) 
	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 25

	SELECT @twentyFiveOrMore = @@rowCount

	INSERT INTO @results (id, credits, IsSpouse) 
	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId , IsSpouse HAVING SUM(Credits) >= 50
	SELECT @fiftyOrMore = @@rowCount

	INSERT INTO @results (id, credits, IsSpouse) 
	SELECT V.EmployeeId, SUM(Credits) , ISSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 75

	SELECT @seventyFiveOrMore = @@rowCount

	INSERT INTO @results (id, credits, IsSpouse) 
	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
	FROM vwUserCredits V 
	JOIN Employees E ON V.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 100

	SELECT @hundredOrMore = @@rowCount

	SELECT @registered = COUNT(E.ID) 
	FROM Employees E
	Join Locations L 
	ON E.LocationId = L.Id
	JOIN Facilities F ON L.FacilityID = F.ID
	WHERE UserId IS NOT NULL AND 
	F.Code = COALESCE(@facilityCode, F.Code)

	SELECT @registered = @registered + COUNT(E.ID) 
	FROM Employees E
	Join Locations L 
	ON E.LocationId = L.Id
	JOIN Facilities F ON L.FacilityID = F.ID
	WHERE SpouseId IS NOT NULL 
	AND F.Code = COALESCE(@facilityCode, F.Code)

	SELECT @activities = COUNT(Ev.ID) 
	FROM Activities EV 

	Join Employees E ON Ev.EmployeeId = E.ID 
	JOIN Locations L ON E.LocationId = L.ID 
	Join Facilities F ON L.FacilityID = F.Id
	WHERE F.Code = COALESCE(@facilityCode, F.Code)

	insert into FacilityTable -- create this table at the top
	SELECT @Code, @Name, @haCount AS HaCount, @oneOrMore As OneOrMore, @twentyFiveOrMore As TwentyFiveOrMore, @fiftyOrMore As FiftyOrMore, 
	@seventyFiveOrMore As 	SeventyFiveOrMore, @hundredOrMore As HundredOrMore, @registered As Registered, @activities As ActivityCount
	fetch next from @mycursor into @Code, @Name

end
close @mycursor
deallocate @mycursor
select * from FacilityTable

Thursday, December 09, 2010 4:30 PM

1
You have created a temp table #FacilityTable.

So, use this table inside this cursor, add # sign with the table name.
```
insert into #FacilityTable -- create this table at the top
```
```
and again at the end... 
```
```
select * from #FacilityTable
```
~Manu
http://sqlwithmanoj.wordpress.com
- Reply
- Quote
Thursday, December 09, 2010 4:44 PM

0

manub- your amazing.. i am going to have to look up Cursors this saved me SOOOO much time!!!!

Thanks again
- Reply
- Quote
Friday, December 10, 2010 11:09 PM

0

>manub- your amazing.. i am going to have to look up Cursors this saved me SOOOO much time!!!!

If you do, then please be aware that cursors are a suboptimal solution
for 99.9% of the database problems. At least in the SQL Server world.
SQL Server is optimized for set-based processing, not for iterative
processing (which is what cursors do).
I did not check the details of this duscussion, so the cursor might be
the best solution in this case - but in general terms, cursors are
best avoided.

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
- Reply
- Quote
Saturday, December 11, 2010 3:35 AM

0

Yes, Hugo is right.

We should avoid using CURSORS whenever we can. But as you mentioned that your criteria will run for 255 time, so its not a bid trade-off with cursors. Avoid using cursors if you have a bigger record-set to iterate on where you are doing DMLs. Instead of cursors you could use WHILE loop with temp tables.

Some useful links:

http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d13c99f7-0178-47ed-9ffc-34bc27334174

http://blogs.techrepublic.com.com/datacenter/?p=412

http://searchsqlserver.techtarget.com/tip/Avoid-cursors-in-SQL-Server-with-these-methods-to-loop-over-records
~Manu
http://sqlwithmanoj.wordpress.com
- Reply
- Quote
Sunday, December 12, 2010 11:45 PM

0

>Instead of cursors you could use WHILE loop with temp tables.

Ugh! That's even worse!

If you have to iterate over rows, use a cursor. And do it sensibly,
that is: with the right options. The default options for cursors suck
big time. Specify to use a FAST_FORWARD cursor when the total amount
of data returned by the cursor exceeds the available cache, or a
STATIC cursor if you do have enough cache to hold the entire
resultset. I have never seen any T-SQL code beat this for performance
if row-based iteration is required. (But looping over a SqlDataReader
in SQLCLR code does beat it hands-down).

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
- Reply
- Quote
Monday, December 13, 2010 3:35 AM

0

Hi Hugo,

Thanks for the insight on CURSORS.

It may be true that information on internet is not always right. Early I used to use cursors very often for iterating records but I avoided and decreased on using CURSORS when I came through these kind of articles on net:

http://blog.sqlauthority.com/2008/05/21/sql-server-2005-twelve-tips-for-optimizing-sql-server-2005-query-performance/

http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CursorsAlternative09012009011823AM/CursorsAlternative.aspx

http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

... there are lot more articles on net that suggests to use WHILE loops instead of CURSORS.

Can you through some light on the use of WHILE loops or I misunderstood them.
~Manu
http://sqlwithmanoj.wordpress.com
- Reply
- Quote
Monday, December 13, 2010 10:24 AM

1
Hi manub22,

It may be true that information on internet is not always right.

Actually, I'd go a step further and say that the internet is littered
with ____. Everybody can publish everything, without any checking - so
you should always verify for yourself everything you read.

Some comments on the articles you reference:

http://blog.sqlauthority.com/2008/05/21/sql-server-2005-twelve-tips-for-optimizing-sql-server-2005-query-performance/

Pinal Dave suggests to avoid cursors, but fails to give you an
alternative. That's like telling people it's dangerous to run while
holding scissors without adding that they should walk instead. People
might misinterpret the advice and proceed to skip while holding
scissors.

The suggestion to avoid cursors is good, but should be interpreted as
a suggestion to avoid any kind of iteration in T-SQL code. SQL Server
is heavily optimized for set-based processing, so a single set-based
query almost always runs rings around any kind of iteration. In a test
I did three years ago, I found that using proper options on a cursor
can save 50% - 70% of execution time - but replacing the cursor with a
single set-based query saves almost 95% of execution time when
compared to the fastest cursor option!

http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CursorsAlternative09012009011823AM/CursorsAlternative.aspx

This is a fine example of the nonsense you can easily find on
internet. The author simply claims that the WHILE loop uses less IO
then the cursor loop, but obviously never ran the code with STATISTICS
IO enabled. And he never bothered to check the documentation of the
DECLARE CURSOR statement, or he would have known that there are four
base types of cursor in SQL Server, that all operate very differently.

He uses the default cursor options in his article (which, as I have
already told you, are awful). And his alternative is also one of the
worst possible. If you check the code, you'll see that the table
variable used for the looping has no indexes. That means that you get
a table scan for every execution of the loop. No problem for 10
iterations over a 10-row table variable, but I can assure you that the
execution time for 10 million iterations over a 10-millioin-row table
variable is brutal.

The killer is in the final paragraphs, where the author manages to
produce no less than four incorrect statements about the difference
between temporary tables and table variables. Nuff said.

http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

And another ____-filled article. The author claims that cursors can
lock the entire table, preventing concurrent updates. Since the four
base cursor types differ in how concurrent updates are handled, just
checking the documentation would suffice to see that this is
incorrect. And it's also easy to run a cursor, then (from a different
connection) actually TRY to do some updates. Or query
sys.dm_tran_locks.

Incidentally, this author makes the EXACT same mistakes in his code as
the author of the previous article - default options for the cursor,
and no indexes for the temporary table.

And this author also includes with an unrelated major mistake. Since
user-defined functions can't be inlined (except inline table-valued
functions), using a user-defined function is a known performance
killer, because it forces the optimizer to create a hidden cursor and
execute the function once for each row - which is often not required
if you don't use a user-defined function. The example code in the
article could be sped up enormously by using a single set-based query
with a CASE expression to find the discount percentage.

You had a bunch of links in your previous reply as well - I'm sure if
you read those articles critically, you'll find issues similar to the
ones described above.

And now I urge you to fire up your test server, create some test
tables and test all the various alternatives for yourself. For, as I
already wrote at the start of my reply, everybody can publish
everything, without any checking - so you should always verify for
yourself everything you read.

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Monday, December 13, 2010 2:03 PM
- Reply
- Quote
Monday, December 13, 2010 12:20 PM

0

Hey Hugo! you are an eye opener.

People have this misconception of not using CURORS with correct options, providing ineffective alternates and believing everything whats on internet. And sure one (everyone) needs to check on its own by getting one step ahead before believing whats on internet.

I really love this forum, lot more to learn from you guys!!!

Thanks again for the detailed clarification.
~Manu
http://sqlwithmanoj.wordpress.com
- Reply
- Quote