speed up the query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

speed up the query

Author

Topic

lukasz
Starting Member

1 Post

Posted - 2011-01-28 : 20:02:07

Hey guys,
Is anyone have any suggestions on how can I speed up my query

declare @file varchar(50) ='5860'
select count(SUBSTRING(ballotbc,13,12)) from Elect_Automation.dbo.LockDown where SUBSTRING(ballotbc,13,12) in
--first exit scan
(select distinct mailPiece from Buhrs.dbo.LogFileInfo where SUBSTRING(mailpiece,9,4) = @file
union
--second exit scan
select substring(ballotbc,13,12) as mailPiece from Elect_Automation.dbo.LockDown where IMB in (select mailPiece from Buhrs.dbo.LogFileInfo2 where fileNo = @file)
union
--now to be even more bullet proof we are going to add pieces from mailpiece file that mailpiece state = 0
select mailpiece from Buhrs.dbo.mailPieces where digits_4=@file and mailPieceState='0' and LEN(mailpiece)=12
)
and SUBSTRING(ballotbc,13,12) not in
--asyncs that did not divert and did got scanned by operator
((SELECT distinct mailPiece FROM Buhrs.dbo.mailPieces where digits_4 = @file and subset_target=0 and error_spec_async !=0 )
union
--diverts that did not divert and did got scanned by operator
(SELECT distinct divertBC FROM Buhrs.dbo.Diverts where SUBSTRING(DivertBC,9,4)=@file)
union
--minus exit scan hits
(select distinct mailPiece as Hits from Buhrs.dbo.Reprints where mailpiece in ((select mailPiece from Buhrs.dbo.LogFileInfo where LogfileInfo.fileNo=@file and mailPiece not in (select mailPiece from Buhrs.dbo.mailPieces where digits_4=@file and error_spec_async<>'0')) union(SELECT distinct substring([BallotBC],13,12) as mail FROM [Elect_Automation].[dbo].[LockDown] where substring(BallotBC,21,4)=@file and imb is not null and imb in (SELECT [mailPiece] FROM [Buhrs].[dbo].[LogFileInfo2] where fileno=@file)
))))

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-28 : 21:20:24

I'm a little too tired to tackle this now, but I can tell you what's slowing it down:

- Substring() in WHERE clause
- COUNT(Substring()), use COUNT(*) unless the columns contain nulls
- DISTINCT
- UNION (especially if it's of 2 DISTINCT queries, UNION -> DISTINCT)
- IN() lists of subqueries, try rewriting as JOINs or EXISTS()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources