Viewing 15 posts - 61 through 75 (of 1,243 total)
Shifting gears back to the original topic, if the Recursive CTE method proves to be a bit slow, post back... I have a bit of an unorthodox but very...
August 27, 2020 at 6:47 pm
Steve Collins- I didn't see a "function-ized" version of your code in the thread. If I missed it, point it out and I'll include it in another round...
August 25, 2020 at 3:26 pm
Lordy... absolutely nothing could go wrong with this little test, right?
https://www.yahoo.com/news/jurassic-park-experiment-750-million-132816442.html
Apparently they didn't watch the part in the original movie where "nature found a way".
2020 is definitely NOT...
August 20, 2020 at 8:27 pm
First - I 100% agree with everything Jeff said above...
Second - All your problems go away if you were to use Windows Active Directory security groups. Rather than assigning permissions...
August 19, 2020 at 6:31 pm
Jeff - I'm looking and comparing my second function to your code to see if I can spot the reason(s) for the difference in execution plans and, in particular, the...
August 19, 2020 at 1:55 pm
My 15 yo brain is saying "ass/butt being divided infinity"...
Like Ed said, you're closer than you think...
Rather than continuing to torture with trivia... the answer is...
"My ass is...
August 18, 2020 at 11:00 pm
I've finally had a chance to look at all that more carefully and, man, that has a VERY interesting execution plan. With a pre-split table, I might be able...
August 18, 2020 at 8:25 pm
Steve Collins- I didn't see a "function-ized" version of your code in the thread. If I missed it, point it out and I'll include it in another round of testing.
August 18, 2020 at 7:25 pm
Using my standard test harness... (query options: Discard results after execution and use DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; between executions)
SET NOCOUNT ON;
GO
-- DBCC FREEPROCCACHE WITH NO_INFOMSGS;
...
August 18, 2020 at 7:23 pm
Just to get an idea of what patterns exist in the TestData table...
SELECT
f.char_pos,
COUNT(1)
FROM
dbo.TestData td
CROSS APPLY dbo.find_all_match_positions_JL(td.word_array_6, td.word_array_5, 2) f
GROUP BY
f.char_pos
ORDER BY
COUNT(1) DESC;
Results...
/*
char_posoccurance_count
2,3119002
2,4115004
3,4112494
1,3112334
3,5112289
1,4111446
1,5111083
1,2111015
2,5110999
4,5106304
1,2,45374
2,3,54660
1,2,54635
1,2,34589
1,4,54449
2,4,54443
2,3,44441
3,4,54151
1,3,44124
1,3,53140
1,3,4,5329
1,2,4,5256
1,2,3,4232
2,3,4,5213
1,2,3,5119
*/
August 18, 2020 at 7:17 pm
Here are my previous scripts converted to iTVFs...
USE tempdb;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO
CREATE FUNCTION dbo.find_all_match_positions_JL
/* ===================================================================
08/18/2020 JL, Created: will identify all of the position/character matches and tell...
August 18, 2020 at 6:51 pm
My 15 yo brain is saying "ass/butt being divided infinity"...
August 18, 2020 at 5:23 pm
I did a little work coming up this good test data. Here is what I cam up with...
It'll generate 100K (adjustable) rows with both 5 char and 6 char arrays...
August 18, 2020 at 5:11 pm
Nice work Jeff. I think my 2nd attempt is actually closer to your solution than the first. We both omitted the tally function/table and simply used the position list to parse...
August 15, 2020 at 8:00 pm
Deleted due to Jeff's delete. 😀
August 15, 2020 at 7:41 pm
Viewing 15 posts - 61 through 75 (of 1,243 total)