Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trying to make a query more efficient

Posted on 2007-11-27
3
Medium Priority
?
153 Views
Last Modified: 2010-04-21
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
Comment
Question by:UnderSeven
3 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 1000 total points
ID: 20359274
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
 
LVL 15

Expert Comment

by:JimFive
ID: 20396796
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
 

Author Closing Comment

by:UnderSeven
ID: 31411213
I was sort of already aware of this possibility, but it helps that you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question