Crap Query.... Anyone help tune it in better way

Question

Crap Query.... Anyone help tune it in better way

sql_butterfly

Ten Centuries

Points: 1264
More actions
February 4, 2011 at 3:38 am

#97640

We have four tables Table1, Table1, Table3, Table4 in differernt databases. There are common fields Department, ProcessDate and ProcessId i these tables. The processes are getting processed and putting data constantly in these four tables. I need to make a join to these four tables together to get the top 1 processId for all the processid's mentioned in where clause. (One process might have different entries across the four tables.) Right now I'm doing it like this (which is a crap):
select Department, ProcessDate, ProcessId from
(select ROW_NUMBER() OVER (PARTITION BY DomainName ORDER BY COBDate DESC ) AS 'RowNum', A.*
from (select * from
(select ROW_NUMBER() OVER (PARTITION BY COBDate ORDER BY DomainName ) AS 'RowNo', * from
(select distinct A.*
from(select Department, ProcessDate, ProcessId from DatabaseA.dbo.Table1
where ProcessId IN(<List of Processes>)
union all
select Department, ProcessDate, ProcessId from DatabaseB.dbo.Table2
where ProcessId IN(<List of Processes>)
union all
select Department, ProcessDate, ProcessId from DatabaseC.dbo.Table3
where ProcessId IN(<List of Processes>)
union all
select DDepartment, ProcessDate, ProcessId from DatabaseD.dbo.Table4
where ProcessId IN(<List of Processes>)) A
) A
) A
)A
)A
where RowNum = 1
order by Department, ProcessDate desc
Can anyone help to achieve this in efficient way ???

Viewing 3 posts - 1 through 2 (of 2 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide table def and sample data as described in the first link in my signature togeteher with your expected result.

This will help those of us who prefer testing the solution before posting.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

sql_butterfly (2/4/2011)
We have four tables Table1, Table1, Table3, Table4 in differernt databases. There are common fields Department, ProcessDate and ProcessId i these tables. The processes are getting processed and putting data constantly in these four tables. I need to make a join to these four tables together to get the top 1 processId for all the processid's mentioned in where clause. (One process might have different entries across the four tables.) Right now I'm doing it like this (which is a crap):
select Department, ProcessDate, ProcessId from
(select ROW_NUMBER() OVER (PARTITION BY DomainName ORDER BY COBDate DESC ) AS 'RowNum', A.*
from (select * from
(select ROW_NUMBER() OVER (PARTITION BY COBDate ORDER BY DomainName ) AS 'RowNo', * from
(select distinct A.*
from(select Department, ProcessDate, ProcessId from DatabaseA.dbo.Table1
where ProcessId IN(<List of Processes>)
union all
select Department, ProcessDate, ProcessId from DatabaseB.dbo.Table2
where ProcessId IN(<List of Processes>)
union all
select Department, ProcessDate, ProcessId from DatabaseC.dbo.Table3
where ProcessId IN(<List of Processes>)
union all
select DDepartment, ProcessDate, ProcessId from DatabaseD.dbo.Table4
where ProcessId IN(<List of Processes>)) A
) A
) A
)A
)A
where RowNum = 1
order by Department, ProcessDate desc
Can anyone help to achieve this in efficient way ???

Just double checking before I jump in. What defines the correct table you want to get the "latest" ProcessID from for each Department? Would that be the MAX ProcessDate?

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