SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Quicker Query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

176 Posts

Posted - 08/27/2014 :  09:03:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1130 Posts

Posted - 08/27/2014 :  09:11:24  Show Profile  Reply with Quote
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

176 Posts

Posted - 08/27/2014 :  09:24:09  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 08/27/2014 :  11:47:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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';



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000