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 2012 Forums
 Transact-SQL (2012)
 Quicker Query?

Author  Topic 

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-08-27 : 09:03:50
Can anyone suggest a better way to write this query to speed it up? This query is pulling up 92000 + records

SELECT CONVERT(varchar(50), LedgerKey) AS LedgerKey, TransactionID, DAccountID, CreatedDate, CreatedUserID, ModifiedDate, ModifiedUserID, DestSortCode, DestAccountNumber, DestAccountName, DestBankRef,
TransValue, CASE [TransCode] WHEN '99' THEN 1 WHEN '01' THEN 2 WHEN '17' THEN 3 WHEN '19' THEN 4 WHEN '0S' THEN 5 WHEN '0C' THEN 6 WHEN '0N' THEN 7 END AS TransCode,
CASE [TransType] WHEN 'IMPORTED' THEN 1 ELSE 2 END AS TransType, Archived, RTICode
FROM tbBOSS_Transaction
WHERE Month((Select SubmittedDate FROM dbo.tbBOSS_TranSet WHERE tbBOSS_TranSet.LedgerKey = tbBOSS_Transaction.LedgerKey )) = '08'
AND Year((Select SubmittedDate FROM dbo.tbBOSS_TranSet WHERE tbBOSS_TranSet.LedgerKey = tbBOSS_Transaction.LedgerKey )) = '2014'
AND (Select SubmissionStatus FROM dbo.tbBOSS_TranSet WHERE tbBOSS_TranSet.LedgerKey = tbBOSS_Transaction.LedgerKey ) = 'Submitted'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-27 : 09:11:24
I had to reformat your query to make any sense of it:


SELECT CONVERT(VARCHAR(50), LedgerKey) AS LedgerKey
,TransactionID
,DAccountID
,CreatedDate
,CreatedUserID
,ModifiedDate
,ModifiedUserID
,DestSortCode
,DestAccountNumber
,DestAccountName
,DestBankRef
,TransValue
,CASE [TransCode]
WHEN '99'
THEN 1
WHEN '01'
THEN 2
WHEN '17'
THEN 3
WHEN '19'
THEN 4
WHEN '0S'
THEN 5
WHEN '0C'
THEN 6
WHEN '0N'
THEN 7
END AS TransCode
,CASE [TransType]
WHEN 'IMPORTED'
THEN 1
ELSE 2
END AS TransType
,Archived
,RTICode
FROM tbBOSS_Transaction
WHERE Month((
SELECT SubmittedDate
FROM dbo.tbBOSS_TranSet
WHERE tbBOSS_TranSet.LedgerKey = tbBOSS_Transaction.LedgerKey
)) = '08'
AND Year((
SELECT SubmittedDate
FROM dbo.tbBOSS_TranSet
WHERE tbBOSS_TranSet.LedgerKey = tbBOSS_Transaction.LedgerKey
)) = '2014'
AND (
SELECT SubmissionStatus
FROM dbo.tbBOSS_TranSet
WHERE tbBOSS_TranSet.LedgerKey = tbBOSS_Transaction.LedgerKey
) = 'Submitted'


Now that I can read it, the first question is, how long does it run and do you think that is too long?

Second question: Why not replace those subqueries in the Where clause with joins on the dbo.tbBOSS_TranSet table? It may not affect the run time, but it should result in a smaller, easier-to-read query.
Go to Top of Page

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-08-27 : 09:24:09
Hi

after a bit of head scratching and trial and error, I have managed to get it down from 1 minute 20 to 36 seconds which is fine for the task in hand

You were right replacing the subqueries with joins made a big difference
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-27 : 11:47:04
[code]SELECT CONVERT(VARCHAR(50), t.LedgerKey) AS LedgerKey,
t.TransactionID,
t.DAccountID,
t.CreatedDate,
t.CreatedUserID,
t.ModifiedDate,
t.ModifiedUserID,
t.DestSortCode,
t.DestAccountNumber,
t.DestAccountName,
t.DestBankRef,
t.TransValue,
CASE t.[TransCode]
WHEN '99' THEN 1
WHEN '01' THEN 2
WHEN '17' THEN 3
WHEN '19' THEN 4
WHEN '0S' THEN 5
WHEN '0C' THEN 6
WHEN '0N' THEN 7
END AS TransCode,
CASE t.TransType
WHEN 'IMPORTED' THEN 1
ELSE 2
END AS TransType,
t.Archived,
t.RTICode
FROM dbo.tbBOSS_Transaction AS t
INNER JOIN dbo.tbBOSS_TranSet AS s ON s.LedgerKey = t.LedgerKey
WHERE s.SubmittedDate >= '20140801'
AND s.SubmittedDate < '20140901'
AND s.SubmissionStatus = 'Submitted';[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -