April 9, 2010 at 9:54 pm
Ninja's_RGR'us (4/9/2010)
Any luck on the clr routine?
Been busy on another thread - writing a SQLCLR routine to return the phase of the moon for a given date :w00t:
Blame Lowell. I'll get to this soon.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 10, 2010 at 6:19 am
Paul White NZ (4/9/2010)
Ninja's_RGR'us (4/9/2010)
Any luck on the clr routine?Been busy on another thread - writing a SQLCLR routine to return the phase of the moon for a given date :w00t:
Blame Lowell. I'll get to this soon.
No hurries, I was just curious...
April 10, 2010 at 7:54 am
While revisiting the Numbers table solution, I discovered that the following code creates a table from 20 names in 6 seconds on my old laptop:
USE tempdb;
GO
IF OBJECT_ID(N'temp..#t', N'U')
IS NOT NULL
DROP TABLE #t;
GO
SELECT N,
list =
(
CASE WHEN N & 1 = 1 THEN 'Alice,' ELSE '' END +
CASE WHEN N & 2 = 2 THEN 'Bob,' ELSE '' END +
CASE WHEN N & 4 = 4 THEN 'Carol,' ELSE '' END +
CASE WHEN N & 8 = 8 THEN 'Dan,' ELSE '' END +
CASE WHEN N & 16 = 16 THEN 'Eric,' ELSE '' END +
CASE WHEN N & 32 = 32 THEN 'Frank,' ELSE '' END +
CASE WHEN N & 64 = 64 THEN 'George,' ELSE '' END +
CASE WHEN N & 128 = 128 THEN 'Harry,' ELSE '' END +
CASE WHEN N & 256 = 256 THEN 'Ian,' ELSE '' END +
CASE WHEN N & 512 = 512 THEN 'John,' ELSE '' END +
CASE WHEN N & 1024 = 1024 THEN 'Kevin,' ELSE '' END +
CASE WHEN N & 2048 = 2048 THEN 'Lisa,' ELSE '' END +
CASE WHEN N & 4096 = 4096 THEN 'Mandy,' ELSE '' END +
CASE WHEN N & 8192 = 8192 THEN 'Norman,' ELSE '' END +
CASE WHEN N & 16384 = 16384 THEN 'Oscar,' ELSE '' END +
CASE WHEN N & 32768 = 32768 THEN 'Paul,' ELSE '' END +
CASE WHEN N & 65536 = 65536 THEN 'Quincy,' ELSE '' END +
CASE WHEN N & 131072 = 131072 THEN 'Roger,' ELSE '' END +
CASE WHEN N & 262144 = 262144 THEN 'Sam,' ELSE '' END +
CASE WHEN N & 524288 = 524288 THEN 'Terri,' ELSE '' END
)
INTO #t
FROM dbo.Numbers(POWER(2, 20) - 1);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 10, 2010 at 1:00 pm
Paul White NZ (4/10/2010)
While revisiting the Numbers table solution, I discovered that the following code creates a table from 20 names in 6 seconds on my old laptop:
USE tempdb;
GO
IF OBJECT_ID(N'temp..#t', N'U')
IS NOT NULL
DROP TABLE #t;
GO
SELECT N,
list =
(
CASE WHEN N & 1 = 1 THEN 'Alice,' ELSE '' END +
CASE WHEN N & 2 = 2 THEN 'Bob,' ELSE '' END +
CASE WHEN N & 4 = 4 THEN 'Carol,' ELSE '' END +
CASE WHEN N & 8 = 8 THEN 'Dan,' ELSE '' END +
CASE WHEN N & 16 = 16 THEN 'Eric,' ELSE '' END +
CASE WHEN N & 32 = 32 THEN 'Frank,' ELSE '' END +
CASE WHEN N & 64 = 64 THEN 'George,' ELSE '' END +
CASE WHEN N & 128 = 128 THEN 'Harry,' ELSE '' END +
CASE WHEN N & 256 = 256 THEN 'Ian,' ELSE '' END +
CASE WHEN N & 512 = 512 THEN 'John,' ELSE '' END +
CASE WHEN N & 1024 = 1024 THEN 'Kevin,' ELSE '' END +
CASE WHEN N & 2048 = 2048 THEN 'Lisa,' ELSE '' END +
CASE WHEN N & 4096 = 4096 THEN 'Mandy,' ELSE '' END +
CASE WHEN N & 8192 = 8192 THEN 'Norman,' ELSE '' END +
CASE WHEN N & 16384 = 16384 THEN 'Oscar,' ELSE '' END +
CASE WHEN N & 32768 = 32768 THEN 'Paul,' ELSE '' END +
CASE WHEN N & 65536 = 65536 THEN 'Quincy,' ELSE '' END +
CASE WHEN N & 131072 = 131072 THEN 'Roger,' ELSE '' END +
CASE WHEN N & 262144 = 262144 THEN 'Sam,' ELSE '' END +
CASE WHEN N & 524288 = 524288 THEN 'Terri,' ELSE '' END
)
INTO #t
FROM dbo.Numbers(POWER(2, 20) - 1);
Wow that's fast... under 3 secs on our prod server.
Only 3 more secs to shave off boys ! 😉
April 10, 2010 at 5:52 pm
Ninja's_RGR'us (4/10/2010)
Wow that's fast... under 3 secs on our prod server.
Only 3 more secs to shave off boys ! 😉
If my math skills are anywhere near where I think they are, you now want the routine to go back in time? DBCC TimeWarp?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 10, 2010 at 9:15 pm
Ninja's_RGR'us (4/10/2010)
Wow that's fast... under 3 secs on our prod server.
Now you have made my laptop feel inadequate 🙁
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2010 at 6:34 am
DBCC TimeWarp would be so usefull for this one :-D.
April 12, 2010 at 3:02 am
Ninja's_RGR'us (4/9/2010)
-- Maxrows = 16: (65,535 row(s) affected) / 00:00:02
-- Maxrows = 20: (1,048,575 row(s) affected) / 00:00:46
-- Maxrows = 21: (2,097,151 row(s) affected) / 00:01:33
-- Maxrows = 22: (4,194,303 row(s) affected) / 00:03:08
I'm not sure I'm using it right...
-- 16 : 65518
-- 20 : 1048554
That's the WHERE clause, which filters out the single row in the result set which contains all values plus other rows which contain only a single value.
Maxrows = 16: (65,535 row(s) affected) without the WHERE clause
16 : 65518 with the WHERE clause filtering out 16+1 rows
Heh in any case, it's a little sluggish compared to Paul's code 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 12, 2010 at 3:13 am
Chris Morris-439714 (4/12/2010)
Heh in any case, it's a little sluggish...
But full of recursive awesomeness!!!
Great code.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2010 at 4:05 am
Paul White NZ (4/12/2010)
Chris Morris-439714 (4/12/2010)
Heh in any case, it's a little sluggish...But full of recursive awesomeness!!!
Great code.
But so slow! I've got a little library of rCTE's now for a variety of jobs, but they're mostly code curiosities which have quicker equivalents using more conventional TSQL - no opportunity yet to say "the fastest solution for this is a rCTE".
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 14, 2010 at 7:27 am
Nice examples.
IMO for a very temporary solution it still may be tolerable ... to use a hybrid solution
CREATE function ufn_GetOrderDetailsCombinations ( @PurchaseOrderID int )
returns table
as
return (
/*
Select *
from ufn_GetOrderDetailsCombinations ( 167 )
-- MUST USE MAXRECURSION IN QUERY !
OPTION (MAXRECURSION 0)
*/
with cteOrderDetailSeq
as ( --Minimize the data scope as soon as possible !
SELECT POD.PurchaseOrderID
, convert(varchar(5000), POD.PurchaseOrderDetailID) + ' (' + convert(char(10), POH.ShipDate, 121) + ')' as OrderDetailInfo
, rank() over ( partition by POD.PurchaseOrderID order by POD.PurchaseOrderID, POD.PurchaseOrderDetailID ) as SEQNR
FROM [AdventureWorks].[Purchasing].[PurchaseOrderHeader] POH
INNER JOIN [AdventureWorks].[Purchasing].[PurchaseOrderDetail] POD
on POD.PurchaseOrderID = POH.PurchaseOrderID
Where POH.PurchaseOrderID = @PurchaseOrderID
) ,
cteMaxMembers
as (
Select count(*) as MaxMembers
from cteOrderDetailSeq
) ,
cteCompositions
AS ( -- Chris Morris-439714 calculator cte
SELECT ColumnNo = 1
, Number = 1
, Store = CAST(' 1' AS VARCHAR(3000))
UNION ALL
SELECT ColumnNo = CASE WHEN lr.Number = tr.MaxRows THEN lr.ColumnNo - 1
ELSE lr.ColumnNo + 1
END
, Number = CASE WHEN lr.Number = tr.MaxRows
-- go back one 3-char column position, increment number found there
THEN CAST(RIGHT(LEFT(lr.Store, ( lr.ColumnNo - 1 ) * 3), 3) AS INT) + 1
ELSE lr.Number + 1
END
, Store = CASE WHEN lr.Number = tr.MaxRows THEN CAST(LEFT(lr.Store, ( lr.ColumnNo - 2 ) * 3) -- < chop off last two 3-char column positions
+ RIGHT(' ' + LTRIM(STR( -- < add new Number
CAST(RIGHT(LEFT(lr.Store, ( lr.ColumnNo - 1 ) * 3), 3) AS INT) + 1)), 3) AS VARCHAR(3000))
ELSE CAST(lr.Store + RIGHT(' ' + LTRIM(STR(lr.Number + 1)), 3) AS VARCHAR(3000))
END
FROM cteCompositions lr
INNER JOIN (
Select MaxMembers as MaxRows
from cteMaxMembers
) tr
ON NOT (
lr.ColumnNo = 1
AND lr.Number = tr.MaxRows
)
) ,
cteCompose
as (
Select ODS.PurchaseOrderID
, ODS.OrderDetailInfo
, C.Store
from cteCompositions C
/* replace spaces with split character */
cross apply master.dbo.fn_ALZDBA_Split(replace(replace(C.Store, ' ', ';'),' ',';'), ';') S
inner join cteOrderDetailSeq ODS
on ODS.SEQNR = S.item
)
Select C.PurchaseOrderID
, STUFF((
SELECT COALESCE(',' + S1.OrderDetailInfo, '')
FROM cteCompose S1
WHERE S1.PurchaseOrderID = C.PurchaseOrderID
and S1.Store = C.Store
FOR
XML PATH('')
), 1, 1, '') as OrderDetails
from cteCompose C
group by C.PurchaseOrderID
, C.Store
) ;
go
Select *
from ufn_GetOrderDetailsCombinations ( 167 )
OPTION (MAXRECURSION 0)
go
/*
(63 row(s) affected)
CPU time = 172 ms, elapsed time = 409 ms.
*/
Select *
from ufn_GetOrderDetailsCombinations ( 455 )
OPTION (MAXRECURSION 0)
go
/*
(1023 row(s) affected)
CPU time = 4766 ms, elapsed time = 8744 ms.
*/
/*
PurchaseOrderIDnDetailsnCombinations
1015301073741823
1352667108863
12552416777215
515201048575
68519524287
76517131071
8351665535
13051532767
20451416383
1905138191
325124095
645112047
455101023
1859511
2958255
27663
8531
11415
737
223
111
*/
Although an enduser may not be very happy if (s)he has to pick one out of 1023 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 14, 2010 at 8:46 am
Thanks a million to all who've helped on this problem.
I've had a very happy conclusion yesterday. After being told but the erp cie that it was impossible to add the field we needed and hence have the correct design, I've found out about 2 other ledgers in the system and if I cross both of them I can get the actual document # for each line of the invoices and shippings. That means that I can simply do a distinct in a derived table + concatenation which gives the results well under 1 sec for even 10 000 documents.
Maybe there's a God after all :w00t:.
April 14, 2010 at 4:33 pm
Always nice to get a happy ending 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 16, 2010 at 6:49 am
I may be way off... but this seems like a simple binary problem to me.
DECLARE @People TABLE (Person VARCHAR(50))
INSERT INTO @People VALUES ('Ted')
INSERT INTO @People VALUES ('Tom')
INSERT INTO @People VALUES ('Mary')
INSERT INTO @People VALUES ('Sue')
--INSERT INTO @People VALUES ('Frank')
; WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).
E1(N) AS ( --=== Create Ten 1's very quickly
SELECT N
FROM (SELECT 1 N0, 1 N1, 1 N2, 1 N3, 1 N4, 1 N5, 1 N6, 1 N7, 1 N8, 1 N9) AS E0
UNPIVOT (N FOR Nx IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt
), --10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
--E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E2)
SELECT N, Person
FROM cteTally
JOIN (SELECT Person, ROW_NUMBER() OVER(ORDER BY Person) R, POWER(2,ROW_NUMBER() OVER(ORDER BY Person)-1) B /* Bitflag - I am sure there is a better way to do this */
FROM @People) P
ON P.B & N = P.B
WHERE N <= POWER(2,(SELECT COUNT(*) FROM @People))
ORDER BY N
And then you have your list. All you would have to do is at your count filter or whatever you needed to do with this data set.
Hope it helps!
(p.s. Just saw a post from Jeff Moden on the tally CTE... had to give it a try... thanks Jeff! 🙂 )
April 16, 2010 at 7:29 am
I tried with those values... and I got only 319 rows back... only 1M short of the correct answer
DECLARE @People TABLE (Person VARCHAR(50))
INSERT INTO @People VALUES ('0')
INSERT INTO @People VALUES ('1')
INSERT INTO @People VALUES ('2')
INSERT INTO @People VALUES ('3')
INSERT INTO @People VALUES ('4')
INSERT INTO @People VALUES ('5')
INSERT INTO @People VALUES ('6')
INSERT INTO @People VALUES ('7')
INSERT INTO @People VALUES ('8')
INSERT INTO @People VALUES ('9')
INSERT INTO @People VALUES ('10')
INSERT INTO @People VALUES ('11')
INSERT INTO @People VALUES ('12')
INSERT INTO @People VALUES ('13')
INSERT INTO @People VALUES ('14')
INSERT INTO @People VALUES ('15')
Viewing 15 posts - 46 through 60 (of 64 total)
You must be logged in to reply to this topic. Login to reply