?
Solved

Trying to make a query more efficient

Posted on 2007-11-27
3
Medium Priority
?
149 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

719 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