Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

Trying to make a query more efficient

This is running off query analyzer, all fields checked against a where statement are indexed.  The db is sqlserver 2005.  The reason I am using the top 1 statements is because I will get indistinct results since two of the col's labeled accession can be different per distinct Fin_nbr, despite the rest of the information being the same.  This results in duplicate results.  The query takes about a half hour to run however.  Any suggestions on what could speed this up wouuld be appreciated.
USE all_tat
 
declare @start as datetime
declare @end as datetime
 
set @start = '10/01/07'
set @end = '10/31/07'
 
select distinct FIN_NBR, 
	(Select top 1 REGNUM from ALL_TAT B1 Where A1.FIN_NBR = B1.FIN_NBR AND (DRAWN_ID = 'H' OR DRAWN_ID = 'C' OR DRAWN_ID = 'G' Or DRAWN_ID = 'B') AND ORDER_DATE >= @start and ORDER_DATE <= @end) AS Regnum,
	(Select top 1 ACCESSION from ALL_TAT B1 Where A1.FIN_NBR = B1.FIN_NBR AND (DRAWN_ID = 'H' OR DRAWN_ID = 'C' OR DRAWN_ID = 'G' Or DRAWN_ID = 'B') AND ORDER_DATE >= @start and ORDER_DATE <= @end) AS Accession,
	(Select top 1 DRAWN_ID from ALL_TAT B1 Where A1.FIN_NBR = B1.FIN_NBR AND (DRAWN_ID = 'H' OR DRAWN_ID = 'C' OR DRAWN_ID = 'G' Or DRAWN_ID = 'B') AND ORDER_DATE >= @start and ORDER_DATE <= @end) AS Drawn_ID,
	(Select top 1 Name from ALL_TAT B1 Where A1.FIN_NBR = B1.FIN_NBR AND (DRAWN_ID = 'H' OR DRAWN_ID = 'C' OR DRAWN_ID = 'G' Or DRAWN_ID = 'B') AND ORDER_DATE >= @start and ORDER_DATE <= @end) AS Name,
	(Select top 1 ORDER_DATE from ALL_TAT B1 Where A1.FIN_NBR = B1.FIN_NBR AND (DRAWN_ID = 'H' OR DRAWN_ID = 'C' OR DRAWN_ID = 'G' Or DRAWN_ID = 'B') AND ORDER_DATE >= @start and ORDER_DATE <= @end) As Order_Date,
	(Select top 1 ORDER_TIME from ALL_TAT B1 Where A1.FIN_NBR = B1.FIN_NBR AND (DRAWN_ID = 'H' OR DRAWN_ID = 'C' OR DRAWN_ID = 'G' Or DRAWN_ID = 'B') AND ORDER_DATE >= @start and ORDER_DATE <= @end) As Order_Time
 
 
from ALL_TAT A1 where (DRAWN_ID = 'H' OR DRAWN_ID = 'C' OR DRAWN_ID = 'G' Or DRAWN_ID = 'B') AND ORDER_DATE >= @start and ORDER_DATE <= @end
ORDER BY ORDER_DATE, ORDER_TIME

Open in new window

0
UnderSeven
Asked:
UnderSeven
1 Solution
 
MikeTooleCommented:
Since the TOP 1 doesn't guarantee which of multiple records it will get the value from, you could just as well use Max() in a Select ... Group By

select FIN_NBR,
    Max(REGNUM) as Regnum,
    Max(ACCESSION) AS Accession,
...
    Max(ORDER_TIME) As ORDER_TIME
from ALL_TAT A1 where (DRAWN_ID = 'H' OR DRAWN_ID = 'C' OR DRAWN_ID = 'G' Or DRAWN_ID = 'B') AND ORDER_DATE >= @start and ORDER_DATE <= @end
Group By Fin_Nbr
ORDER BY ORDER_DATE, ORDER_TIME
 
0
 
JimFiveCommented:
IF Accession is the only one that can be different:

Select Fin_NBR, ReNum, Max(Accession) as MaxAccession, Drawn_id,
           Name, Order_Date, Order_Time
FROM All_TAT
WHERE Drawn_Id IN ('H','C','G','B')
AND ORDER_DATE >=@Start and ORDER_Date <= @End
GROUP BY Fin_NBR, Drawn_ID, Name, Order_Date, OrderTime
ORDR BY ORDER_DATE, ORDER_TIME
0
 
UnderSevenAuthor Commented:
I was sort of already aware of this possibility, but it helps that you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now