Quicker 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 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.

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources