July 7, 2014 at 6:02 am
If you're working with a specific range of numbers, for example 1980 to 2020, simply add that to the WHERE statement.
SELECT [Value]
FROM [sysNumbers]
WHERE Value BETWEEN 13 and 17
July 7, 2014 at 8:27 am
david.holley (7/7/2014)
If you're working with a specific range of numbers, for example 1980 to 2020, simply add that to the WHERE statement.
SELECT [Value]
FROM [sysNumbers]
WHERE Value BETWEEN 13 and 17
I have to ask, how do the values of 13 thru 17 translate to "1980 to 2020" in that example?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2014 at 8:46 am
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!
Cheers
July 7, 2014 at 8:49 am
Jeff Moden (7/7/2014)
david.holley (7/7/2014)
If you're working with a specific range of numbers, for example 1980 to 2020, simply add that to the WHERE statement.
SELECT [Value]
FROM [sysNumbers]
WHERE Value BETWEEN 13 and 17
I have to ask, how do the values of 13 thru 17 translate to "1980 to 2020" in that example?
They don't. I did a simple select on the table that I have and then realized that I don't go up that hight.
July 7, 2014 at 9:05 am
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!
Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2014 at 9:08 am
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
July 7, 2014 at 9:19 am
david.holley (7/7/2014)
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
Cursors aren't evil, people are evil (or ignorant). It's the same premise as "guns don't kill people, people kill people". Cursors won't kill performance, developers that use where they shouldn't, kill performance.
July 7, 2014 at 9:57 am
Luis Cazares (7/7/2014)
david.holley (7/7/2014)
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
Cursors aren't evil, people are evil (or ignorant). It's the same premise as "guns don't kill people, people kill people". Cursors won't kill performance, developers that use where they shouldn't, kill performance.
Thank you for the highlighted part above. I try very hard NOT to use cursors, however, there are times when they are the appropriate tool to use. I don't use them often so I have to refer to BOL when I do find I need them to get them right.
July 7, 2014 at 12:45 pm
david.holley (7/7/2014)
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
Oddly enough, the Cross Join of a Tally Table (or Tally cte) causes the Tally Table (or Tally cte) to be used as a {drum roll please} "pseudo-cursor", which is a term that R.Barry Young coined for the nearly machine-language "loops" that occur behind the scenes of SELECTs (and other SQL Statements).
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2014 at 12:47 pm
Luis Cazares (7/7/2014)
david.holley (7/7/2014)
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
Cursors aren't evil, people are evil (or ignorant). It's the same premise as "guns don't kill people, people kill people". Cursors won't kill performance, developers that use where they shouldn't, kill performance.
Oddly enough, a well written WHILE loop is typically faster and less resource intensive that rCTEs... even the non-counting type.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2014 at 12:53 pm
Jeff Moden (7/7/2014)
Luis Cazares (7/7/2014)
david.holley (7/7/2014)
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
Cursors aren't evil, people are evil (or ignorant). It's the same premise as "guns don't kill people, people kill people". Cursors won't kill performance, developers that use where they shouldn't, kill performance.
Oddly enough, a well written WHILE loop is typically faster and less resource intensive that rCTEs... even the non-counting type.
Which begs the question: what separates a well written while loop from a wantonly written while loop?
😎
July 7, 2014 at 1:02 pm
sqldriver (7/7/2014)
Jeff Moden (7/7/2014)
Luis Cazares (7/7/2014)
david.holley (7/7/2014)
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
Cursors aren't evil, people are evil (or ignorant). It's the same premise as "guns don't kill people, people kill people". Cursors won't kill performance, developers that use where they shouldn't, kill performance.
Oddly enough, a well written WHILE loop is typically faster and less resource intensive that rCTEs... even the non-counting type.
Which begs the question: what separates a well written while loop from a wantonly written while loop?
😎
For me, it would be a loop that reduces the iterations, table scans and variable usage to a minimum.
Some people use while loops and read the table on each iteration to get the values for each row. That's a way to slowly kill your server.
July 9, 2014 at 5:16 pm
sqldriver (7/7/2014)
Jeff Moden (7/7/2014)
Luis Cazares (7/7/2014)
david.holley (7/7/2014)
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
Cursors aren't evil, people are evil (or ignorant). It's the same premise as "guns don't kill people, people kill people". Cursors won't kill performance, developers that use where they shouldn't, kill performance.
Oddly enough, a well written WHILE loop is typically faster and less resource intensive that rCTEs... even the non-counting type.
Which begs the question: what separates a well written while loop from a wantonly written while loop?
😎
Obviously, the logic needs to be tight but there are some things that can be done to make one run twice as fast.
1. SET NOCOUNT ON
2. Run the loop inside a transaction to reduce the number of trips to the log file. The exception to that rule would be if referenences to external tables are present especially if the WHILE loops {gasp!} is calling stored procedure(s) during each iteration.
3. Combine SET statements for variables into single SELECTs.
4. Use WITH (TABLOCKX) when using a WHILE loop to populate or update a Temp Table or a new table.
Of course, the best thing to do would be to avoid rCTEs and WHILE loops whenever possible.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2014 at 5:25 pm
Jeff Moden (7/9/2014)
sqldriver (7/7/2014)
Jeff Moden (7/7/2014)
Luis Cazares (7/7/2014)
david.holley (7/7/2014)
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
Cursors aren't evil, people are evil (or ignorant). It's the same premise as "guns don't kill people, people kill people". Cursors won't kill performance, developers that use where they shouldn't, kill performance.
Oddly enough, a well written WHILE loop is typically faster and less resource intensive that rCTEs... even the non-counting type.
Which begs the question: what separates a well written while loop from a wantonly written while loop?
😎
Obviously, the logic needs to be tight but there are some things that can be done to make one run twice as fast.
1. SET NOCOUNT ON
2. Run the loop inside a transaction to reduce the number of trips to the log file. The exception to that rule would be if referenences to external tables are present especially if the WHILE loops {gasp!} is calling stored procedure(s) during each iteration.
3. Combine SET statements for variables into single SELECTs.
4. Use WITH (TABLOCKX) when using a WHILE loop to populate or update a Temp Table or a new table.
Of course, the best thing to do would be to avoid rCTEs and WHILE loops whenever possible.
One to add to the list:
5. Use a single statement WHILE whenever possible (i.e. no BEGIN...END) and you may be surprised at the performance - especially when recoding a poorly performing rCTE.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 9, 2014 at 6:36 pm
Jeff Moden (7/9/2014)
sqldriver (7/7/2014)
Jeff Moden (7/7/2014)
Luis Cazares (7/7/2014)
david.holley (7/7/2014)
Jeff Moden (7/7/2014)
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.
As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.
Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
Please don't say 'cursor' they are as evil as they are popular with the key developer behind a legacy system that I support. He's no longer with us, but we think of him on a regular basis.
Cursors aren't evil, people are evil (or ignorant). It's the same premise as "guns don't kill people, people kill people". Cursors won't kill performance, developers that use where they shouldn't, kill performance.
Oddly enough, a well written WHILE loop is typically faster and less resource intensive that rCTEs... even the non-counting type.
Which begs the question: what separates a well written while loop from a wantonly written while loop?
😎
Obviously, the logic needs to be tight but there are some things that can be done to make one run twice as fast.
1. SET NOCOUNT ON
2. Run the loop inside a transaction to reduce the number of trips to the log file. The exception to that rule would be if referenences to external tables are present especially if the WHILE loops {gasp!} is calling stored procedure(s) during each iteration.
3. Combine SET statements for variables into single SELECTs.
4. Use WITH (TABLOCKX) when using a WHILE loop to populate or update a Temp Table or a new table.
Of course, the best thing to do would be to avoid rCTEs and WHILE loops whenever possible.
Does anyone else smell spackle? 🙂
Viewing 15 posts - 406 through 420 (of 511 total)
You must be logged in to reply to this topic. Login to reply