June 27, 2010 at 1:38 am
togaratw (6/26/2010)
Great article and it surely points the way for more efficient statements.I'm eagerly anticipating the next series of this article for more complicated scenarios where cursors can be replaced.
I'm somewhat novice but would be keen for pointers on how I can eliminate cursors in the following scenario where I have a table:
Column1 Column2
1 A
1 B
1 C
2 A
2 D
and I want to get a string like "A,B,C" when I search for 1, or "A,D" for 2 etc.
Hi
you could use:
Declare @res VarChar(max)
Set @res = ''
Select @res = @res + Column2 From [Table]
Where Column1 = 1
Select @res
June 27, 2010 at 2:42 am
lehmannds (6/27/2010)
togaratw (6/26/2010)
Great article and it surely points the way for more efficient statements.I'm eagerly anticipating the next series of this article for more complicated scenarios where cursors can be replaced.
I'm somewhat novice but would be keen for pointers on how I can eliminate cursors in the following scenario where I have a table:
Column1 Column2
1 A
1 B
1 C
2 A
2 D
and I want to get a string like "A,B,C" when I search for 1, or "A,D" for 2 etc.
Hi
you could use:
Declare @res VarChar(max)
Set @res = ''
Select @res = @res + Column2 From [Table]
Where Column1 = 1
Select @res
Actually that syntax can be problematic and can return incorrect results in some situations. I think Itzik did an article demonstrating how even an index change on the table can cause incorrect/partial results to be returned.
Use FOR XML instead:
DECLARE @res VARCHAR(MAX);
SELECT @res = REPLACE
(
(
SELECT Column2 AS "data()"
FROM [Table]
FOR XML PATH('')
), ' ', ', '
);
You can also use ORDER BY in the FOR XML subquery to guarantee ordering of items in the result.
Mike C
June 28, 2010 at 2:34 am
togaratw (6/26/2010)
Great article and it surely points the way for more efficient statements.I'm eagerly anticipating the next series of this article for more complicated scenarios where cursors can be replaced.
I'm somewhat novice but would be keen for pointers on how I can eliminate cursors in the following scenario where I have a table:
Column1 Column2
1 A
1 B
1 C
2 A
2 D
and I want to get a string like "A,B,C" when I search for 1, or "A,D" for 2 etc.
Read my blog post here which has an example of what you need. I wrote the solution to pull out a string of email addresses to use in a send field.
June 28, 2010 at 3:11 am
Thanks lehmannds for the solution, and Mike for the robust refinement. It works great, replaces my cursor 37x faster for the piece I was working on.
June 28, 2010 at 4:45 am
The article is proving very useful to me.
I've never used cursors before as the purpose for them has always eluded me and the code is horrendous to look at. Being the only pure-SQL writer in the IT team (being an MI analyst), I am having to work on automating code written by developers. They think procedurally and use cursors, so I'm having to convert their code to something sensible for reporting purposes. The article is therefore providing me with an excellent how-to.
I can't wait for the third installment.
There are 22 pages of what looks to be primarily cursor conversions. i think it would be really beneficial if some of the conversions could be compiled into a fourth installment. These would provide great real-world examples.
June 28, 2010 at 8:03 am
davec-640463 (6/28/2010)
togaratw (6/26/2010)
Great article and it surely points the way for more efficient statements.I'm eagerly anticipating the next series of this article for more complicated scenarios where cursors can be replaced.
I'm somewhat novice but would be keen for pointers on how I can eliminate cursors in the following scenario where I have a table:
Column1 Column2
1 A
1 B
1 C
2 A
2 D
and I want to get a string like "A,B,C" when I search for 1, or "A,D" for 2 etc.
Read my blog post here which has an example of what you need. I wrote the solution to pull out a string of email addresses to use in a send field.
Good article, Dave.
Just so that everyone knows (I can see it coming), "Loopless Recursion" doesn't ever mean "Recursive CTE" which only appears to be "loopless". Recursive CTE's are actually a form of hidden RBAR that can take as much CPU time as a While Loop and usually takes more than 3 times the reads. With rare exceptions, Recursive CTE's are nothing more than hidden RBAR.
The type of "Loopless Recursion" that Dave is talking about are the natural loops formed by SQL Server behind the scenes at the compiled code level. Barry calls such things "Pseudo Cursors" and they are very, very, powerful. In fact, they are the reason why Set Based code works so fast.
There are also some "Pseduo Cursor" methods that are even faster than what Dave's good article demonstrates.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2010 at 8:06 am
Thank you Jeff 🙂
June 29, 2010 at 7:13 am
great article and some great posts. I shall be keeping an eye on this article/thread!
June 29, 2010 at 11:35 am
Not realy sure I get this article.:crying: First you show us a cursor example which in my opion is beyond simple. Are there really programmers out there that use cursor statements for an incrediably simple Select Count statement? Really???
And then your second cursor examply is even simpler (in my opion). All it is doing is cating last name and first name. Again an incredable simple Select statement (Select Lastname + ', ' + Firstname as Fullname) will do the same thing.
Not trying to be a jerk here. I have always heard how terrible cursor statements are so I would like to find a replacement. But my cursors are a lot more detailed then the examples you give.
Here is one example of the kind of cursor statement I use.
In my Cursor statemant I grab a table of customer information on purchases customers did. One of the peices of information I grab is the amount they paid for this month. Then while I am in the While @@Fetch_Status = 0 loop I query the database again and get what the customer paid the last month. Then I compare the two values. If the customer paid more this month than last month, the difference is Inserted into another table. So the code looks like this.
DECLARE CursorTee CURSOR
SCROLL
KEYSET
FOR
SELECT MonthlyFee
From CustomerMonthlyFees
where Month = datepart(m, getdate())
DECLARE @MonthlyFee double
FETCH NEXT FROM CursorTee INTO @MonthlyFee
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @LastMonthFee as DOUBLE
SET @LastMonthFee = SELECT MonthlyFee FROM CustomerMonthlyFees WHERE Month = datepart(dateadd(m, -1, getdate())
IF @MonthlyFee > @LastMonthFee
Begin
INSERT INTO [MonthDiff]
{
[Difference]
}
VALUES
{
@MonthlyFee - @LastMonthFee
}
End
FETCH NEXT FROM CursorTee @MonthlyFee
End
CLOSE CursorTee
DEALLOCATE CursorTee
GO
Now this is not entire code. Left out many more data I grab in the Cursor and a lot more manipultion of the data inside the While loop. But I think you get the idea of what kind of Cursor statement I am working with.
Can you show me a better way of doing what I showed above without using Cursor?
Like I said not trying to be a jerk about this, just trying to figure it out.
Thanks
MutlyP
June 29, 2010 at 12:22 pm
mutlyp (6/29/2010)
Can you show me a better way of doing what I showed above without using Cursor?
I'm sure the idea is that the series of articles will start with the easy stuff and move to more advanced uses.
In any case here is an example to replace your cursor:
INSERT INTO [MonthDiff]
{
[Difference]
}
SELECT
CurrMonth.MonthlyFee - PriorMonth.MonthlyFee AS FeeDifference
FROM CustomerMonthlyFees CurrMonth
INNER JOIN CustomerMonthlyFees PriorMonth
ON CurrMonth.x = PriorMonth.x
AND DATEPART(DATEADD(M, -1, GETDATE()) = PriorMonth.Month
WHERE
CurrMonth.Month = DATEPART(M, GETDATE())
AND CurrMonth.MonthlyFee > PriorMonth.MonthlyFee;
It ins't an exact replacement because I added a condition to join the two tables "ON CurrMonth.x = PriorMonth.x" since I assume there is more than one customer. (I assume you would want to include the join field in the INSERT as well but I didn't include that.) If not, this is a more exact replacement:
INSERT INTO [MonthDiff]
{
[Difference]
}
SELECT
CurrMonth.MonthlyFee - PriorMonth.MonthlyFee AS FeeDifference
FROM CustomerMonthlyFees CurrMonth, CustomerMonthlyFees PriorMonth
WHERE
CurrMonth.Month = DATEPART(M, GETDATE())
AND PriorMonth.Month = DATEPART(DATEADD(M, -1, GETDATE())
AND CurrMonth.MonthlyFee > PriorMonth.MonthlyFee;
June 29, 2010 at 8:15 pm
mutlyp (6/29/2010)
Not realy sure I get this article.:crying: First you show us a cursor example which in my opion is beyond simple. Are there really programmers out there that use cursor statements for an incrediably simple Select Count statement? Really???
Mutlyp, there are programmers who use cursors to count rows, cursors to retrieve data one row at a time, cursors to concatenate strings, cursors to sum values, nested cursors and IF statements to manually recreate inner joins, .... :crazy: There are absolutely programmers out there who use cursors for *everything*.
Mike C
June 29, 2010 at 9:13 pm
Thanks for the reply.
You have the Insert in front of the select. Is that on purpose? I guess what I am asking is, if you put an Insert statement in the code and it is suppose to insert @Amount and then right after you do logic, @Amount = 2.00. Will 2.00 be Inserted?
But I do see your logic for this case but like I said there are a lot more data that I must modify, compare and then take actions all from that one employee record. In your logic you were able to do it for one piece of data. What from one row(record from the database) of the employee you must manipulate 8 different types of data.
Like for my example using it to compare values. But what if with another piece of data from that record you needed to decided if they get a discount. So first you have to call another table to figure out if they are eligible for the discount. Once that is determined then you must figure out how much of a discount they get. Then you must adjust their regular amount with the discount then after all that you must Insert the discount in the same table as the compare value went.
And then 6 other types of data that I must manipulate in different ways to Insert that finally value into that Insert table?
Hope this makes since because I would really like to stop using cursors because to the speed.
Thanks for the help
MutlyP
June 29, 2010 at 9:33 pm
mutlyp (6/29/2010)
Thanks for the reply.You have the Insert in front of the select. Is that on purpose? I guess what I am asking is, if you put an Insert statement in the code and it is suppose to insert @Amount and then right after you do logic, @Amount = 2.00. Will 2.00 be Inserted?
What UMG gave you here is a standard INSERT with a SELECT specified as the source instead of a VALUES clause. This is pretty standard, and yes it will give you the correct result. Logically you can think of the SELECT being performed first and the result of the SELECT being inserted into the table via the INSERT statement.
But I do see your logic for this case but like I said there are a lot more data that I must modify, compare and then take actions all from that one employee record. In your logic you were able to do it for one piece of data. What from one row(record from the database) of the employee you must manipulate 8 different types of data.
Like for my example using it to compare values. But what if with another piece of data from that record you needed to decided if they get a discount. So first you have to call another table to figure out if they are eligible for the discount. Once that is determined then you must figure out how much of a discount they get. Then you must adjust their regular amount with the discount then after all that you must Insert the discount in the same table as the compare value went.
And then 6 other types of data that I must manipulate in different ways to Insert that finally value into that Insert table?
Hope this makes since because I would really like to stop using cursors because to the speed.
Thanks for the help
MutlyP
You can use CASE expressions to compare from different columns and return results based on those comparisons in a single SELECT query. Do you have a more complex example you'd care to share? There are plenty of us here who'd be happy to help you look at your code differently.
The main problem with cursors (IMO) is that they specifically override the query engine's ability to optimize your queries. When you move away from cursors you give the optimizer more freedom to come up with a better query plan to get the job done faster and more efficiently.
Mike C
June 30, 2010 at 1:25 am
Hi MutlyP,
It also sounds like table variables could help you out here. You could possibly subset your data into table variables perfoming some of the logic you describe. Data can then be returned from those table variables instead of the physical tables. I also like this approach as it can provide huge speed improvements. For example, if you are going to be querying a table with a million rows and performing some kind of complex calculation using the data from only 50 of the rows. It is far quicker (in my opinion) to extract those 50 rows into a table variable and work with them there. Your table variable can have whatever columns you want so you can insert your data and then update additional columns to have calculated values.
June 30, 2010 at 2:34 am
To expand on davec's comment since you seem unfamiliar with the insert into x select * from z here is the basic syntax for temporary table population
CREATE TABLE #[tablename] (column1 int, column2 varchar(45))
INSERT INTO #[tablename]
SELECT * FROM [tablename]
or
SELECT *
INTO #[tablename]
FROM [tablename]
The first has the benefit of instantiating the table so that sqlserver can optimise the query, and you can also add indexes and things to make the table more useful. It also stops intellisense from putting red squiggles everywhere.
Another useful way of segmenting your code is by using common table expressions (CTEs) and there was a great article on these here: http://www.sqlservercentral.com/articles/Common+Table+Expression+(CTE)/62291/
When I have to do multiple transformations on a dataset I prefer doing multiple updates that are grouped logically. If all the transformations are based on the same tables (or they can all be joined easily) then they can easily go into one statement. The advantage (IMO) of multiple steps, is mainly clarity of code, and ease of debugging and error handling. Conventional wisdom is that updates and deletes take longer than inserts and selects so it's good if you can put your transformations into a temporary table and then update so that the update statement isn't weighed down.
Viewing 15 posts - 211 through 225 (of 316 total)
You must be logged in to reply to this topic. Login to reply