Need query

Question

Need query

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

ss-457805 (12/24/2009)
Jeff,
This is awesome. You are a Genius.:-)

I agree with you ... if you SELECT NAME FROM T-SQL_MASTERS WHERE NAME LIKE 'J%' you will retrieve the only result Jeff Moden.

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

freeman.e.l SSC Veteran Points: 249 More actions · Answer 2

Dugi (12/24/2009)
I agree with you ... if you SELECT NAME FROM T-SQL_MASTERS WHERE NAME LIKE 'J%' you will retrieve the only result Jeff Moden.

Let's not forget Jamie Thomson http://sqlblog.com/blogs/jamie_thomson/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

ss-457805 (12/24/2009)
Jeff,
This is awesome. You are a Genius.:-)

Thanks, SS. Not a genius though... just lucky as all get out. 😉 Give folks a day or two and someone will find a way that will blow this out of the water for performance. :hehe:

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

freeman.e.l (12/24/2009)
Dugi (12/24/2009)
I agree with you ... if you SELECT NAME FROM T-SQL_MASTERS WHERE NAME LIKE 'J%' you will retrieve the only result Jeff Moden.
Let's not forget Jamie Thomson http://sqlblog.com/blogs/jamie_thomson/

Man, do you happen to know Jamie personally or have some way to get a hold of him privately? He's taking a huge amount of heat for one of his blog entries called "Debunking Kimball Effective Dates". He has code that does the following...

CREATE TABLE [Customer] (

[Id] INT PRIMARY KEY

, [NId] NVARCHAR(50)

, [Name] NVARCHAR(50)

, [HomeTown] NVARCHAR(50)

, [MaritalStatus] CHAR(1)

, [NumberOfChildren] INT

, [SCDStartDate] DATETIME --Note only [SCDStartDate], no [SCDEndDate]

, CONSTRAINT Customer_UK UNIQUE

(

[NId]

, [SCDStartDate]

)

); -- Modified the following inserts so they would work in something besides just 2k8

INSERT [Customer] ([Id],[NId],[Name],[HomeTown],[MaritalStatus],[NumberOfChildren],[SCDStartDate])

SELECT 1,'Cust001','Henry','London','S',0,CONVERT(DATETIME,'20050324') UNION ALL

SELECT 2,'Cust001','Henry','London','M',0,CONVERT(DATETIME,'20070726') UNION ALL

SELECT 3,'Cust002','Sarah','Birmingham','M',2,CONVERT(DATETIME,'20060213') UNION ALL

SELECT 4,'Cust001','Henry','London','M',1,CONVERT(DATETIME,'20091127');

GO

CREATE VIEW vCustomer AS

SELECT c.[Id],c.[NId],c.[Name],c.[HomeTown],c.[MaritalStatus]

, c.[NumberOfChildren],c.[SCDStartDate]

, COALESCE(MIN(c2.[ScdStartDate]),CONVERT(DATETIME,'99991231')) AS [SCDEndDate]

, CONVERT(BIT,CASE WHEN MIN(c2.[ScdStartDate]) IS NULL THEN 1 ELSE 0 END) AS [IsLatest]

FROM [Customer] c

LEFT OUTER JOIN [Customer] c2

ON c.[Nid] = c2.[Nid]

AND c.[SCDStartDate] < c2.[SCDStartDate]

GROUP BY c.[Id],c.[NId],c.[Name],c.[HomeTown],c.[MaritalStatus]

, c.[NumberOfChildren],c.[SCDStartDate];

GO

If you run a [font="Arial Black"]SELECT * FROM vCustomer[/font], you end up with the attached Execution Plan... look at the row count for the arrow coming out of the bottom copy of the customer table... it's the square of the number of rows in table. Try it out... add another row to the table and the rowcount for that arrow jumps to 25. I didn't want to say anything on his blog because he's already taking enough heat... if you know how to get a hold of him privately, you've gotta tell him his code needs to be changed. If there are only 10,000 rows in a real table, his code will generate 100,000,000 (+ the original 10,000 but who's counting?) internal rows that will eat the face off a server. He needs to fix that code.

And here's an article that explains why this happens...

http://www.sqlservercentral.com/articles/T-SQL/61539/

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Dugi (12/24/2009)
ss-457805 (12/24/2009)
Jeff,
This is awesome. You are a Genius.:-)
I agree with you ... if you SELECT NAME FROM T-SQL_MASTERS WHERE NAME LIKE 'J%' you will retrieve the only result Jeff Moden.

Dugi, you must be my biggest fan. Thank you for the awesome compliments. I hope I never let folks like you down.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

cthariharan (12/24/2009)
Please share the table structure & some of the datas stored in it. So that others can help u.

cthariharan,

Good tip there but a lot of people just don't know what that means... you may want to take the time to refer people to the following link so the are taught what it means...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

This type of thing comes up more than anyone could imagine... what you may want to do is add it to your signature line like I did in mine (see below). Then, you can say...

Please share the table structure & some of the datas stored in it using the methods in the first link in my signature line below so that others can help you with fully tested code.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Prakash,

Did that do it for you or what?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

freeman.e.l SSC Veteran Points: 249 More actions · Answer 8

Jeff, I have to disagree please see the example below:

Your reebar example does not include the filter nid = nid. Also note, on smaller queries the optimizer will pull all rows from both sides.

use tempdb

go

CREATE TABLE [Customer] (

[Id] INT PRIMARY KEY identity(1,1)

, [NId] int

, [Name] NVARCHAR(50)

, [HomeTown] NVARCHAR(50)

, [MaritalStatus] CHAR(1)

, [NumberOfChildren] INT

, [SCDStartDate] DATETIME --Note only [SCDStartDate], no [SCDEndDate]

, CONSTRAINT Customer_UK UNIQUE

(

[NId]

, [SCDStartDate]

)

); -- Modified the following inserts so they would work in something besides just 2k8

--Load lots of data

declare @counter int

set @counter = 1

while @counter <= 500 BEGIN

INSERT [Customer] ([NId],[Name],[HomeTown],[MaritalStatus],[NumberOfChildren],[SCDStartDate])

SELECT 1,'Henry','London','M',1,dateadd(dd,@counter,'20091127') UNION ALL

SELECT 2,'Sarah','Birmingham','S',2,dateadd(dd,@counter,'20080213') UNION ALL

SELECT 3,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 4,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 5,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 6,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 7,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 8,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 9,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 10,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 11,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 12,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 13,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 14,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 15,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 16,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 17,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 18,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 19,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213') UNION ALL

SELECT 20,'Ree','Bar','S',0,dateadd(dd,@counter,'20071213');

--416534

set @counter = @counter+1

END

--END loads lots of data

go

CREATE VIEW vCustomer AS

SELECT c.[Id],c.[NId], MIN(c2.[ScdStartDate]) ScdEndDate

FROM [Customer] c

LEFT OUTER JOIN [Customer] c2

ON c.[Nid] = c2.[Nid]

AND c.[SCDStartDate] < c2.[SCDStartDate]

GROUP BY c.[Id],c.[NId]

GO

select * from vCustomer

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

freeman.e.l (12/28/2009)
Jeff, I have to disagree please see the example below:
Your reebar example does not include the filter nid = nid. Also note, on smaller queries the optimizer will pull all rows from both sides.

Hmmm.... maybe he fixed it... I copied directly from his blog. I'll revisit.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cthariharan Say Hey Kid Points: 698 More actions · Answer 10

Thank you Jeff. I've added it. Let me know any improvements required.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

cthariharan (12/28/2009)
Thank you Jeff. I've added it. Let me know any improvements required.

Very cool and welcome aboard!

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)