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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

181 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

2780 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

181 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
30421 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  
 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.04 seconds. Powered By: Snitz Forums 2000