Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

Calculating interest query Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 11:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
Paul White (3/2/2010)
Jeff Moden (3/2/2010)
If you keep track of the pennies, you can probably afford to buy a new red stapler.

That would be awesome! I can never find a stapler when I need one.
Just don't get upset and burn the place down if you can't.
Post #876224
Posted Thursday, March 4, 2010 12:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
Ah... good that some have seen the movie.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #876604
Posted Thursday, March 4, 2010 9:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
Jeff Moden (3/4/2010)
Ah... good that some have seen the movie.

To let the rest of the world in on it, we're talking about Office Space. ("It's good to be a gangsta...." - ROFL).
-----
edit: fix link, typo
Post #876920
Posted Thursday, March 4, 2010 5:35 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
John,

That link seems to be broken, which is a shame since I would like to be one of the cool kids that's in on the joke.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #877298
Posted Thursday, March 4, 2010 7:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
This is the link John tried to post...
http://www.imdb.com/title/tt0151804/

It's a cult classic, totally silly, and (unfortunately) sometimes truer than life in a cube.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #877334
Posted Friday, March 5, 2010 10:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
Jeff Moden (3/4/2010)
This is the link John tried to post...
http://www.imdb.com/title/tt0151804/

It's a cult classic, totally silly, and (unfortunately) sometimes truer than life in a cube.

and of course the main plot element is a scheme to skim fractions of pennies from interest rounding.
Post #877787
Posted Friday, October 29, 2010 10:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,789, Visits: 14,000
Paul White NZ (2/27/2010)
...recursive CTEs can be fast, but it is relative. I sometimes use them to seek down the distinct keys of a large index rather than scanning the whole thing and relying on an aggregate, for example...
Paul


I'd really like to see an example of that - in the meantime, here's another simple and obvious use of rCTE's. No timings because I've not yet had the opportunity.

-- Rollup rows, concatenating row values into a new column 
DROP TABLE #Test
CREATE TABLE #Test (TestID INT IDENTITY (1,1), Section INT, Word VARCHAR(20))
INSERT INTO #Test (Section, Word) VALUES
(1, 'A'),(1, 'day'), (1, 'in'),(1, 'the'),(1, 'life'),(1, 'of'),(1, 'Ivan'),(1, 'Denisovich'),
(2, 'Silent'),(2, 'Spring')

;WITH PreparedData AS (
SELECT *,
SectionID = ROW_NUMBER() OVER(PARTITION BY Section ORDER BY TestID), -- sets order of words in sentence
SectionSize = COUNT(*) OVER(PARTITION BY Section) -- used to detect last word of sentence
FROM #Test),

Concatenator AS (
SELECT TestID, Section, Word, -- source columns
SectionID, SectionSize, Sentence = CAST(Word AS VARCHAR(100)) -- "working" columns
FROM PreparedData
WHERE TestID = 1
UNION ALL
SELECT
t.TestID, t.Section, t.Word,
t.SectionID, t.SectionSize, Sentence = CAST(CASE WHEN t.SectionID = 1 THEN ISNULL(t.Word, '')
ELSE l.Sentence + ' ' + ISNULL(t.Word, '') END AS VARCHAR(100))
FROM PreparedData t
INNER JOIN Concatenator l ON l.TestID + 1 = t.TestID
)

SELECT
TestID,
Section,
Sentence
FROM Concatenator
WHERE SectionID = SectionSize




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1013191
Posted Friday, October 29, 2010 9:15 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Chris Morris-439714 (10/29/2010)
I'd really like to see an example of that

I'll post one in a minute.

...in the meantime, here's another simple and obvious use of rCTE's. No timings because I've not yet had the opportunity.

Table Spool mania! It doesn't perform well; I'd stick with the usual XML hack/solution:

SELECT  Sections.Section,
Concatenated.sentence
FROM (
SELECT DISTINCT
Section
FROM #Test
) Sections
CROSS
APPLY (
SELECT SPACE(1) + Word
FROM #Test T
WHERE T.Section = Sections.Section
ORDER BY
T.TestID
FOR XML PATH (''),
TYPE
) Concatenator (xml_string)
CROSS
APPLY (
SELECT STUFF(Concatenator.xml_string.value('(./text())[1]', 'VARCHAR(MAX)'), 1, 1, SPACE(0))
) Concatenated (sentence);





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1013403
Posted Friday, October 29, 2010 9:54 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Super-fast DISTINCT using a recursive CTE:

USE     tempdb;
GO
DROP TABLE dbo.Test;
GO
CREATE TABLE
dbo.Test
(
data INTEGER NOT NULL,
);
GO
CREATE CLUSTERED INDEX c ON dbo.Test (data);
GO
-- Lots of duplicated values
INSERT dbo.Test WITH (TABLOCK)
(data)
SELECT TOP (5000000)
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 117329
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3;
GO

SET     STATISTICS TIME ON;

-- 1591ms CPU
SELECT DISTINCT
data
FROM dbo.Test;

-- 15ms CPU
WITH RecursiveCTE
AS (
SELECT data = MIN(T.data)
FROM dbo.Test T
UNION ALL
SELECT R.data
FROM (
-- A cunning way to use TOP in the recursive part of a CTE :)
SELECT T.data,
rn = ROW_NUMBER() OVER (ORDER BY T.data)
FROM dbo.Test T
JOIN RecursiveCTE R
ON R.data < T.data
) R
WHERE R.rn = 1
)
SELECT *
FROM RecursiveCTE
OPTION (MAXRECURSION 0);

SET STATISTICS TIME OFF;
GO
DROP TABLE dbo.Test;

The recursive CTE is 100 times more efficient




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1013407
Posted Sunday, October 31, 2010 11:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
Paul White NZ (10/29/2010)
Super-fast DISTINCT using a recursive CTE:


Absolutely awesome! What on earth made you look for an alternative to DISTINCT?

On my 8 year old 1.8GHz P4, here's what I get...

(43 row(s) affected)

SQL Server Execution Times:
CPU time = 2938 ms, elapsed time = 3052 ms.

(43 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1013682
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse