Solved

Trying to make a query more efficient

Posted on 2007-11-27
3
135 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 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I was sort of already aware of this possibility, but it helps that you
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now