Query optimization

I entered this [challenge][1] on beyondrelational. With the sample data of about 300 rows my solution ran in 0 ms so I thought I had something, but alas the load testing showed it took about 8000ms. Looking at the winners, my solution is almost identical to Stefan's solution below except mine has a replace and I do not do the "pseudo recursion"

-- tc46_v2
-- stefan_G

-- uses pseudo-recursion to avoid executing the patindex more than once for each string
-- handle the case with leading ']' separately
-- use datalength() instead of len() to handle trailing spaces properly

cte1 as (
    select string, patindex(case when substring(string,1,1)=']' then '%[ -\^-ÿ]%' else '%[^'+substring(string,1,1)+']%' end, string collate latin1_general_bin) as i
    from tc46
    union all
    select * from cte1 where i<0
    case when i=0 then substring(string,1,1) else substring(string,i,2147483647) end as Result
from cte1
order by datalength(string), string collate latin1_general_bin

How can mine be 8 seconds slower than this, can anyone shed light on what the pseudo-recursion is doing? i will not be <0 so it will only return the anchor part, how is this processed differently than a generic cte? Even without it, shouldn't the patindex only be executed once for each row during the table scan? I am floored at the discrepancy of 8 seconds. Further it shows mine having 4000 writes but Stefan's only 41, something doesn't seem right for how close our solutions are

[1]: http://beyondrelational.com/blogs/tc/archive/2010/12/27/tsql-challenge-46-Remove-leading-occurrences-of-the-first-character-in-a-string.aspx
more ▼

asked Feb 02, 2011 at 03:43 PM in Default

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest
It is my understanding that Stefan's "Pseudo recursion" trick makes the engine store the results of the cte query ( a bit like using a temp table without any indexes ) for re-use rather than re-processing the underlying query - however I cannot see how it helps in this query! Ask Stefan to explain is my advise.
more ▼

answered Feb 02, 2011 at 03:59 PM

Mister Magoo gravatar image

Mister Magoo
1.8k 2 3 5

Definitely a good trick to have in your bag if it makes that big of a difference. I will have to dig into this deeper.
Feb 02, 2011 at 04:03 PM Scot Hauder
I've tried it a few times and have yet to see a benefit on my code - It would be great to understand a bit more about when it helps!
Feb 02, 2011 at 04:04 PM Mister Magoo
I would hope it doesn't materialize the cte for every row referenced in the main select, or we should never use them. I have seen some ugly performance with simple ctes like this so I have been avoiding them unless I need recursion. Instead of the recursion trick I will have to see if just using a derived table has the same effect.
Feb 02, 2011 at 04:19 PM Scot Hauder
Now, the really big WOW! for me is in the winning solution - the ORDER BY. If I implement the same order by on my solution it comes out identical to the winner...I would re-submit but that seems mean spirited...
Feb 02, 2011 at 04:48 PM Mister Magoo
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 02, 2011 at 03:43 PM

Seen: 1117 times

Last Updated: Feb 02, 2011 at 03:56 PM