January 31, 2010 at 6:01 pm
I am new to sql server 2005 and I am wondering if you can suggest any alternatives to the sql I have listed below. (Note: The sql does run and it does give me the correct results, but the part I have in question takes along time to run and I get the same results in last left outer join).
The problem that I have with the following sql, is that I need to list the j3.maxReceiveDate for every record that is selected. My problem is the part where I need to determine what the ReceivedRequests count is by comparing the rec_date >= Z.MaxReceiveDate. Thus I am basically determining what the max date is again in the same query. Is there anyway to use the
j3.MaxReceiveDate instead of having to come up with the MaxReceiveDate again in the J2 part of the query? (Also, note all tables I am using are history tables and the contain lots of rows.)
select distinct J1.gnun, j1.MthSinceEffectDate,j2.ReceivedRequests, j3.maxReceiveDate
from
(select distinct P.gnum,
DateDiff(Month, effectivedate,ltrim(rtrim(str(month(dateadd(month,0,getdate()))))) + '/01/' + ltrim(rtrim(str(year(dateadd(year,0,getdate())))))) as MthSinceEffectDate
from dbo.table1 P
group by P.gnum,P.effectivedate
) as J1
left join
(select distinct Z.gnum,count(distinct recs) as ReceivedRequests,
from dbo.table2 r1
LEFT JOIN
(select distinct r2.gnum,
MaxReceiveDate = Max(Received_Date)
from dbo.table3
group by r2.gnum
) Z
On Z.HNumber = r1.Hnumber
where rec_date >= Z.MaxReceiveDate
group by Z.gnum, Z.MaxReceiveDate
) as J2
on J1.gnum =J2.gnum
Left join (select distinct P.gnum, MaxReceiveDate = Max(Received_Date)
from dbo.table4
group by P.gnum
) as J3
on J1.gnum=J3.gnum
order by 1,2,3,4
Thank you very much in advance!
January 31, 2010 at 6:27 pm
I would consider losing the subqueries in favor of an alternative method. The alternative method would depend on the performance gain from testing a few different methods.
Please provide the execution plan and table structures so the best answer can be ascertained.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2010 at 7:43 pm
Thanks!
January 31, 2010 at 9:31 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply