Help with Inefficient SQL code

Morpheus

Anyway to make this running total SELECT more efficient?

Declare @QuoteKey int, @BHKeyGroup varchar(30)
set @QuoteKey = 1845
set @BHKeyGroup = 'Help'
	
if @BHKeyGroup = 'Help'
	BEGIN
		SELECT	total = 
		( 
			SELECT SUM(QdLineItemTbl.LineTotal) 
			
			FROM    QdLangTbl INNER JOIN	
				QdLineItemTbl ON QdLangTbl.QdLangKey = QdLineItemTbl.QdLangKey 
			INNER JOIN
				QuoteTbl ON QdLangTbl.QuoteKey = QuoteTbl.QuoteKey 
			INNER JOIN
				TaskDescriptionTbl ON QdLineItemTbl.TaskKey = TaskDescriptionTbl.TaskKey 
			INNER JOIN
				tblBudgetHeads ON TaskDescriptionTbl.BHKey = tblBudgetHeads.BHKey
			
			WHERE	(QdLineItemTbl.Required = 1) 
				AND (QdLangTbl.Required = 1) 
				AND (QuoteTbl.QuoteKey = @QuoteKey) 
				AND (tblBudgetHeads.BHKey = 2) 
		) 
		+
		( 
			SELECT SUM(QdLineItemTbl.LineTotal) 
			
			FROM    QdLangTbl INNER JOIN	
				QdLineItemTbl ON QdLangTbl.QdLangKey = QdLineItemTbl.QdLangKey 
			INNER JOIN
				QuoteTbl ON QdLangTbl.QuoteKey = QuoteTbl.QuoteKey 
			INNER JOIN
				TaskDescriptionTbl ON QdLineItemTbl.TaskKey = TaskDescriptionTbl.TaskKey 
			INNER JOIN
				tblBudgetHeads ON TaskDescriptionTbl.BHKey = tblBudgetHeads.BHKey
			
			WHERE	(QdLineItemTbl.Required = 1) 
				AND (QdLangTbl.Required = 1) 
				AND (QuoteTbl.QuoteKey = @QuoteKey) 
				AND (tblBudgetHeads.BHKey = 10) 
		) 
		+
		( 
			SELECT SUM(QdLineItemTbl.LineTotal) 
			
			FROM    QdLangTbl INNER JOIN	
				QdLineItemTbl ON QdLangTbl.QdLangKey = QdLineItemTbl.QdLangKey 
			INNER JOIN
				QuoteTbl ON QdLangTbl.QuoteKey = QuoteTbl.QuoteKey 
			INNER JOIN
				TaskDescriptionTbl ON QdLineItemTbl.TaskKey = TaskDescriptionTbl.TaskKey 
			INNER JOIN
				tblBudgetHeads ON TaskDescriptionTbl.BHKey = tblBudgetHeads.BHKey
			
			WHERE	(QdLineItemTbl.Required = 1) 
				AND (QdLangTbl.Required = 1) 
				AND (QuoteTbl.QuoteKey = @QuoteKey) 
				AND (tblBudgetHeads.BHKey = 8) 
		) 


	END

Ive to write a few versions of it depending on which BHKeyGroup string i pass into it.

Mick L

If you can group the BHKey field either with another related table (if there isn't one already) or add another field to the table ( may not be perfectly normalised but normalisation sometimes suffers where performance is an issue anyway) which will allow you to run one select statement rather than 3.

Paladin

Ive not got too much experience with SQL, so Im probably missing something here when I ask why you cant use an OR statement for

((tblBudgetHeads.BHKey = 2) OR (tblBudgetHeads.BHKey = 8) OR(tblBudgetHeads.BHKey = 10))

?

Morpheus

thanks for replying lads...

got it sorted though

SELECT	totalHelp = 
		( 
			SELECT isnull(SUM(QdLineItemTbl.LineTotal),0) 
			
			FROM    QdLangTbl INNER JOIN	
				QdLineItemTbl ON QdLangTbl.QdLangKey = QdLineItemTbl.QdLangKey 
			INNER JOIN
				QuoteTbl ON QdLangTbl.QuoteKey = QuoteTbl.QuoteKey 
			INNER JOIN
				TaskDescriptionTbl ON QdLineItemTbl.TaskKey = TaskDescriptionTbl.TaskKey 
			INNER JOIN
				tblBudgetHeads ON TaskDescriptionTbl.BHKey = tblBudgetHeads.BHKey
			
			WHERE	(QdLineItemTbl.Required = 1) 
				AND (QdLangTbl.Required = 1) 
				AND (QuoteTbl.QuoteKey = @QuoteKey) 
				AND ((tblBudgetHeads.BHKey = 8) OR (tblBudgetHeads.BHKey = 9))
		),
		totalDTP = 	
		( 
			SELECT isnull(SUM(QdLineItemTbl.LineTotal),0)
			
			FROM    QdLangTbl INNER JOIN	
				QdLineItemTbl ON QdLangTbl.QdLangKey = QdLineItemTbl.QdLangKey 
			INNER JOIN
				QuoteTbl ON QdLangTbl.QuoteKey = QuoteTbl.QuoteKey 
			INNER JOIN
				TaskDescriptionTbl ON QdLineItemTbl.TaskKey = TaskDescriptionTbl.TaskKey 
			INNER JOIN
				tblBudgetHeads ON TaskDescriptionTbl.BHKey = tblBudgetHeads.BHKey
			
			WHERE	(QdLineItemTbl.Required = 1) 
				AND (QdLangTbl.Required = 1) 
				AND (QuoteTbl.QuoteKey = @QuoteKey) 
				AND ((tblBudgetHeads.BHKey = 10) OR (tblBudgetHeads.BHKey = 11))
		),
		totalEngineering =	
		( 
			SELECT isnull(SUM(QdLineItemTbl.LineTotal),0)
			
			FROM    QdLangTbl INNER JOIN	
				QdLineItemTbl ON QdLangTbl.QdLangKey = QdLineItemTbl.QdLangKey 
			INNER JOIN
				QuoteTbl ON QdLangTbl.QuoteKey = QuoteTbl.QuoteKey 
			INNER JOIN
				TaskDescriptionTbl ON QdLineItemTbl.TaskKey = TaskDescriptionTbl.TaskKey 
			INNER JOIN
				tblBudgetHeads ON TaskDescriptionTbl.BHKey = tblBudgetHeads.BHKey
			
			WHERE	(QdLineItemTbl.Required = 1) 
				AND (QdLangTbl.Required = 1) 
				AND (QuoteTbl.QuoteKey = @QuoteKey) 
				AND ((tblBudgetHeads.BHKey = 3) 
					OR (tblBudgetHeads.BHKey = 4) 
					OR (tblBudgetHeads.BHKey = 5) 
					OR (tblBudgetHeads.BHKey = 6)
					OR (tblBudgetHeads.BHKey = 7))
		),
		totalLocalization =	
		( 
			SELECT isnull(SUM(QdLineItemTbl.LineTotal),0)
			
			FROM    QdLangTbl INNER JOIN	
				QdLineItemTbl ON QdLangTbl.QdLangKey = QdLineItemTbl.QdLangKey 
			INNER JOIN
				QuoteTbl ON QdLangTbl.QuoteKey = QuoteTbl.QuoteKey 
			INNER JOIN
				TaskDescriptionTbl ON QdLineItemTbl.TaskKey = TaskDescriptionTbl.TaskKey 
			INNER JOIN
				tblBudgetHeads ON TaskDescriptionTbl.BHKey = tblBudgetHeads.BHKey
			
			WHERE	(QdLineItemTbl.Required = 1) 
				AND (QdLangTbl.Required = 1) 
				AND (QuoteTbl.QuoteKey = @QuoteKey) 
				AND ((tblBudgetHeads.BHKey = 1) 
				OR (tblBudgetHeads.BHKey = 2) 
				OR (tblBudgetHeads.BHKey = 13) 
				OR (tblBudgetHeads.BHKey = 15)) 
		),
		totalAccountMngr =	
		( 
			SELECT isnull(SUM(QdLineItemTbl.LineTotal),0)
			
			FROM    QdLangTbl INNER JOIN	
				QdLineItemTbl ON QdLangTbl.QdLangKey = QdLineItemTbl.QdLangKey 
			INNER JOIN
				QuoteTbl ON QdLangTbl.QuoteKey = QuoteTbl.QuoteKey 
			INNER JOIN
				TaskDescriptionTbl ON QdLineItemTbl.TaskKey = TaskDescriptionTbl.TaskKey 
			INNER JOIN
				tblBudgetHeads ON TaskDescriptionTbl.BHKey = tblBudgetHeads.BHKey
			
			WHERE	(QdLineItemTbl.Required = 1) 
				AND (QdLangTbl.Required = 1) 
				AND (QuoteTbl.QuoteKey = @QuoteKey) 
				AND (tblBudgetHeads.BHKey = 14) 
		)
GO

amen

out of interest have a you run a query execution plan ?
how long does it take to run with new sql vs the old ?

Morpheus

Never used Query Execution Plan....

Never been shown before...

Do tell? im reading up on it now!

bonkey

OK....

This can be made way more efficient with a little bit of thought.

Taking a look at it, we can see that every single one of the nested SELECT queries does almost the same thing. It joins the same tables in the same way, and applies some of the same WHERE clause filters.

In fact, the only difference between each total you want to calculate is entirely based on the value of tblBudgetHeads.BHKey.

So...you should be way better off doing the following (if its not clear, go read up on the CASE statement, and then come ask me) :

SELECT  SUM(case 
              when tblBudgetHeads.BHKey = 8 
                or tblBudgetHeads.BHKey = 9   then QdLineItemTbl.LineTotal 
              else 0 
            end)  as totalHelp,
        SUM(case 
              when tblBudgetHeads.BHKey = 10 
                or tblBudgetHeads.BHKey = 11  then QdLineItemTbl.LineTotal 
              else 0 
            end)  as totalDTP,
        SUM(case 
              when tblBudgetHeads.BHKey = 4 
                or tblBudgetHeads.BHKey = 5
                or tblBudgetHeads.BHKey = 6 
                or tblBudgetHeads.BHKey = 7   then QdLineItemTbl.LineTotal 
              else 0 
            end)  as totalEngineering,
        SUM(case 
              when tblBudgetHeads.BHKey = 1 
                or tblBudgetHeads.BHKey = 2
                or tblBudgetHeads.BHKey = 12 
                or tblBudgetHeads.BHKey = 13  then QdLineItemTbl.LineTotal 
              else 0 
            end)  as totalLocalization,
        SUM(case 
              when tblBudgetHeads.BHKey = 14  then QdLineItemTbl.LineTotal 
              else 0 
            end)  as totalAccountMngr,
FROM    QdLangTbl 
INNER JOIN 
        QdLineItemTbl 
  ON    QdLangTbl.QdLangKey = QdLineItemTbl.QdLangKey 
INNER JOIN 
        QuoteTbl 
  ON    QdLangTbl.QuoteKey = QuoteTbl.QuoteKey 
INNER JOIN 
        TaskDescriptionTbl 
  ON    QdLineItemTbl.TaskKey = TaskDescriptionTbl.TaskKey 
INNER JOIN 
        tblBudgetHeads 
  ON    TaskDescriptionTbl.BHKey = tblBudgetHeads.BHKey
WHERE   QdLineItemTbl.Required = 1
  AND   QdLangTbl.Required = 1
  AND   QuoteTbl.QuoteKey = @QuoteKey

GO

You could make this shorter by changing the OR statements to IN statements, but there's no real performance advantage.

You can also use the other form of the CASE statement in each case (which you will see when reading the help on CASE) but I generally prefer this one, and I don't think there's a performance difference.

Hope that helps somewhat.

jc

bonkey

Oh - I should mention that I dropped the ISNULL "wrapper" around each value deliberately. The way the CASE will work (replacing values it doesn't want to SUM) with 0, rather than omitting them as with your WHERE clause implementation) means that you will only get NULL if there are no records at all.

Oh - and I should probably have added the following two lines to the end :

AND  tblBudgetHeads.BHKey >= 1
AND tblBudgetHeads.BHKey <= 14

It won't change the totals, but if there are other values of that field allowed, it may give a performance benefit.

jc

Morpheus

Cheers bonkey,

im the so called resident SQL "expert" here simply because im the only one with SQL experience, with the result that ive no1 to train under or gain experience from. My SQL code works, but im always under the imression that in its simplicity it is usually overkill, inefficient and... well... crap basically

What line of work are you in? im (unfortunately) relegated to a localisation company, building Db's, SQL stored procedures and tables, using VBA VB and c#,javascript with ASP.NET.

im looking for work in a SW company, but now im considering courses or else some self teaching, if you can recommend any good books from amazon id appreciate it?

thanks for the help.

bonkey

Originally posted by Morphéus
What line of work are you in?

IT Consultancy in general, but I used to work for an IT Training company in Ireland for a couple of years too (well, training and consultancy).

Mostly I write apps of varying shapes and sizes. At the moment, my two main projects are for teh Swiss Military and for a Swiss civil-service department.

Databases are just something I like, and decided a long time ago to get good at because in my experience its something most other developers don't get good at which gives me an edge

im looking for work in a SW company,

Working in Switz, as I do, I can't really recommend anything.

but now im considering courses or else some self teaching, if you can recommend any good books from amazon id appreciate it?

Hmm. Books. Not really, TBH. Definitely not for databases....I just learned that through experience (and reading large chunks of the MS documentation and various articles in the MSDN library).

For regular programming, I can't point out much better. I tend to go for the O'Reilly books every time on every subject, and they give me what I need....but whether or not they'll give you what you need....

jc