March 28, 2012 at 6:14 am
MyDoggieJessie (3/27/2012)
I didn't think about the fact that because the file was an attachment it might keep you from opening it
It's not that I couldn't open it. In order to load it, I'd have had to do a BULK INSERT or something else to load the data. It doesn't take an incredible amount of extra time for me to BULK INSERT it or use some other method to load it up but it does take extra. Thank you for saving me the time. It's a whole lot easier for me this way.
I'm on my way to work. I'll see what I can do tonight after I get home.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2012 at 6:43 am
True, if you add in Jeff's optimizations for the splitter portion, the CTEs part will be even faster. Can't hurt.
I use a CLR splitter these days, so forgot about the "even better" version of the T-SQL splitter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 28, 2012 at 8:09 am
Sorry have to edit it, as I did not tested it.
March 28, 2012 at 8:06 pm
MyDoggieJessie (3/27/2012)
Hey Jeff, thanks for that (really)...just not certain how I would work your splitter into what I already have posted a couple posts above...With using the CTE's that GSquared provided I saw excellent performance on a month's worth of data (approx. 300,000 rows) and ran in under 15 secs. When attempting to run it for a year it still performed well, but took nearly 8.5 minutes to complete.
By comparison my method (which uses your splitter) ran a months worth of data in about 4 minutes (versus the 15 seconds), but I'm certain the way I'm using it was a less optimal one :hehe:
All set. Replace your fx_SumUpExtentsFromProgress function with the following.
CREATE FUNCTION dbo.fx_SumUpExtentsFromProgress
(
@pProdCode VARCHAR(8000),
@pProdQty VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH cteCode AS
(
SELECT split.ItemN
FROM dbo.fx_DelimitedSplit8k(@pProdCode,';') split
WHERE split.Item > 0 AND split.Item < 99
)
SELECT TotalQty = SUM(CAST(qty.Item AS DECIMAL(14,3)))
FROM dbo.fx_DelimitedSplit8k(@pProdQty,';') qty
INNER JOIN cteCode code
ON code.ItemN = qty.ItemN
;
Then, modify the code you used to get your output just a wee bit so you can use the new "inline Table Valued Function" instead of the scalar function you had created.
SELECT a.[tc-Number],
ActiveCards = COUNT(DISTINCT a.[tc-card]),
FuelTrans = SUM(CASE WHEN [trans-code] IN (43,53) THEN 0 ELSE 1 END),
Qty = SUM(total.TotalQty)
FROM dbo.[tmpQTY_LocalFleets_WORK] a
CROSS APPLY fx_SumUpExtentsFromProgress(a.[prod-code], a.[prod-qty]) total
WHERE [pos-date] >= '2012/01/01'
AND [pos-date] < '2012/01/02'
AND [system] = 'PR'
GROUP BY a.[tc-Number],
a.[monthName],
a.[Year]
Where you'll see a performance difference is when you need to split more than just 1 or 2 codes per row.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2012 at 8:23 pm
p.s. Almost forgot. Don't forget to add the month and year to your SELECT list if you want to actually show groupings by month and year. After you do that, add WITH ROLLUP after the GROUP BY list for a nice surprise when you select more than 1 month's worth of data.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2012 at 9:00 pm
GOOD LORD JEFF!!! :w00t:
The efficiency of this using the TVF is incredible. I processed a years worth of data in just under 2 minutes...WOW - THANK-YOU!!!
The ROLLUP is not something I've used before but I tried it and read up on it (pretty nifty little sub totaling trick). Because the developer isn't finished with the actual report at this point in the code, it skews the numbers by inserting the sub totals into the grouped by columns (unless I used it incorrectly).
Again, THANKS SO MUCH for your help (and everyone else's on this one) - I believe this is EXACTLY what he needs!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 29, 2012 at 10:45 am
>>God Lord Celko
Good Freudian typo, dude 😉
March 29, 2012 at 11:25 am
MyDoggieJessie (3/28/2012)
GOOD LORD JEFF!!! :w00t:The efficiency of this using the TVF is incredible. I processed a years worth of data in just under 2 minutes...WOW - THANK-YOU!!!
The ROLLUP is not something I've used before but I tried it and read up on it (pretty nifty little sub totaling trick). Because the developer isn't finished with the actual report at this point in the code, it skews the numbers by inserting the sub totals into the grouped by columns (unless I used it incorrectly).
Again, THANKS SO MUCH for your help (and everyone else's on this one) - I believe this is EXACTLY what he needs!
Great solution, isn't it.
It's strength is two part:
1) it is a TVF, so is set based and there lies the strength of our DBMS
2) it is an inline tvf, a single statement with direct return of result. That's when magic occurs :w00t:
Conclusion: If you have to use UDFs, avoid ScalarValuedFunctions !
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
March 29, 2012 at 9:27 pm
MyDoggieJessie (3/28/2012)
GOOD LORD JEFF!!! :w00t:The efficiency of this using the TVF is incredible. I processed a years worth of data in just under 2 minutes...WOW - THANK-YOU!!!
The ROLLUP is not something I've used before but I tried it and read up on it (pretty nifty little sub totaling trick). Because the developer isn't finished with the actual report at this point in the code, it skews the numbers by inserting the sub totals into the grouped by columns (unless I used it incorrectly).
Again, THANKS SO MUCH for your help (and everyone else's on this one) - I believe this is EXACTLY what he needs!
You bet. Thank you very much for your feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply