Solved

Trying to make a query more efficient

Posted on 2007-11-27
3
139 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 52
poor performance from  MySQL stored procedure 6 23
SQL Query with Sum and Detail rows 2 39
Get Duration of last Status Update 4 29
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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

929 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

11 Experts available now in Live!

Get 1:1 Help Now