July 17, 2016 at 10:46 am
@sivajaggu11 and anyone else interested...
I realize that most of the posts in this thread are ages old but let's replay a couple of things...
pshvets (4/15/2009)
My question is not a homework question. 🙂My background is BI Development on Oracle, Teradata, Unix platforms. I am simply learning MS SQL Server and T-SQL.
I love that spirit. As several of the others have mentioned, especially Gus, the challenge of doing something different with limitations on the tools that can be used can be a great teacher.
RBarryYoung (4/15/2009)
This is actually from Jacob Sebastiens Challenge #3, where the actual rules were: "Write a single query that can reverse the strings in this column without using the REVERSE() function".
Jacob's challenges are great for learning and this one is no exception. Since the OP used other functions in his original code, let's assume that Jacob's challenge correctly defines the rules and that anything else that is done in T-SQL (excludes things like SQLCLR and calls to xp_CmdShell) is fair game.
sivajaggu11 (7/16/2016)
declare @string varchar(60)set @string='reverse string'
;with Reverse_string
as
(
select @string string,cast('' as varchar(50)) Rstring,LEN(@string)LN
union all
select SUBSTRING(string,0,ln)string,cast(Rstring+substring(string,LN,1)as varchar(50)) Rstring,ln-1 ln from Reverse_string
where LN>0
)
select Rstring from Reverse_string where ln=0
With the idea and spirit of this being a learning exercise, I'll state that the code above is common solution that many people have used and, although it does work, it will perform worse than even a well written WHILE loop and certainly much worse than it needs to. Please see the following for why Recursive CTEs (rCTE from here on) that increment or decrement a counter within should not be used.
[font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's[/font][/url]
Further, you'd need to specify the OPTION (MAXRECURSION 8000) for strings up to VARCHAR(8000) and OPTION (MAXRECURSION 0) if you intended to reverse VARCHAR(MAX).
Ok. So what's the alternative when you need to loop through a string? As most of the heavy hitters will tell you (and some have demonstrated on this thread), some form of a "Tally Table" should come into play. If you don't know what that is, please see the following article. It actually has an example with pictures and an explanation of how to do the per character tokenizing that this problem requires, although it doesn't do it in descending order.
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/font][/url]
Since a Tally Table is small enough to fit in and persist in memory, it's usually the fastest solution there is. However, contests and challenges such as those that Jacob's site poses count "Logical Reads" as part of the formulation for determining the winner of such challenges. Although the Tally Table is very fast, it does use a large number of Logical Reads and so the "best" code for performance may not win such a contest. Also, since a lot of DBAs look for code that has a large number of Logical Reads in their quest to find poor performing code on their systems, it can provide an unwanted distraction for them and they sometimes get a bit uppity about it.
As a bit of a sidebar, Jacob's site does provide a Tally Table that can be called upon, but it wasn't available at the time of Challenge #3.
So, rCTEs suck for performance, WHILE loops suck for performance, and Tally Tables, although very fast and easy to use, such for Logical reads. What else is there?
The answer comes from the "Master" of T-SQL, Mr. Itzik Ben-Gan. He uses a "virtual Tally Table" in the form of very fast "Cascading CTEs" (my term for his technique and I abbreviate them as "cCTEs" from here on). They're not quite as fast as a physical Tally Table but are so close in performance the differences almost aren't worth mentioning. You can find his great article on the subject at the following URL.
[font="Arial Black"]Virtual Auxiliary Table of Numbers[/font]
Since the time that great article was published, lot's of people have attempted to optimize Itzik's inline cCTE method (which are patently NOT rCTEs and suffer no such performance problems) for both performance and simplicity. With the improvements to the VALUES clause as of SQAL Server 2008, most people have adopted code similar to the following.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 123456 --Just as an example
;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N)) --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
Knowing full well that loops, recursion, and other forms of RBAR are bad for performance, we still need a way to concatenate the characters in reverse order AND we need to do it without using a variable, which would force us to use some form of RBAR. One way is to use de-entitized XML to do the concatenation. The following article explains how to do that and the link after that explains how to make that a little faster, as well.
[font="Arial Black"]Creating a comma-separated list (SQL Spackle)
[/font][/url][font="Arial Black"]http://www.sqlservercentral.com/Forums/FindPost1046467.aspx[/font]
Finally, and to round out getting the max performance we can, there's also the subject of the slothfulness of using Scalar Functions. Generally, even so called "memory only" Scalar Functions are 7 times slower than inline code. That's a real problem because abstracting functionality in a UDF saves a whole lot of programming time and prevents many errors. Fortunately, there's even a solution for that little problem and the solution can be found in the following article.
[font="Arial Black"]How to Make Scalar UDFs Run Faster (SQL Spackle)[/font][/url]
Now, with all of that in mind and not wanting to spoil any self-learning by posting a possible solution, try again with no loops, rCTEs, or Scalar Functions (an "iSF", find the definition of that in one of the links I provided is ok).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing post 31 (of 30 total)
You must be logged in to reply to this topic. Login to reply