SQL Server Performance

t-sql 2005 alternatives

Discussion in 'SQL Server 2005 General Developer Questions' started by jazzy, Jan 31, 2010.

  1. jazzy New Member

    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!
  2. preethi Member

    Hi Jazzy,
    • I see a lot of DISTINCT AND GROUP BY clauses in this query and most of them are unnecessary. Thre is no point in using SELECT DISTINCT .... and then GROUP BY ( except the part of "count(distinct recs)" I couldn't see any values in distinct and group by
    • DateDiff(Month,effectivedate,ltrim(rtrim(str(month(dateadd(month,0,getdate()))))) +'/01/' + ltrim(rtrim(str(year(dateadd(year,0,getdate())))))) is equivalent to DATEDIFF(MONTH, effectivedate, GETDATE())
    • If you need the sub queries, you better store them in temp tables and then join against temp tables.
    • Try avoiding nested sub queries like below. (Your code below refers P.gnum where no connection between table4 and P are defined. So it creates a Cartesian product!)
    [quote user="jazzy"]
    (select distinct P.gnum, MaxReceiveDate = Max(Received_Date)
    from dbo.table4
    group by P.gnum
    ) as J3
    [/quote]

    Like what I mentioned in the previous post, you better go with temp tables. In your case, it may not only improve performance, but also reduce the debugging headaches.
    You also, need to check whether you have indexes to cover the columns you have referenced in this query.
  3. midnight New Member

Share This Page