x
login about faq Site discussion (meta-askssc)

Query optimization

I entered this challenge 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

;with 
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
)
select 
    String, 
    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

more ▼

asked Feb 02 '11 at 03:43 PM in Default

Scot Hauder gravatar image

Scot Hauder
5.7k 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 '11 at 03:59 PM

Mister Magoo gravatar image

Mister Magoo
420 3

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 '11 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 '11 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 '11 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 '11 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

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x28

asked: Feb 02 '11 at 03:43 PM

Seen: 611 times

Last Updated: Feb 02 '11 at 03:56 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.