SUM performance issues - 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 2005 Forums

Transact-SQL (2005)

SUM performance issues

Author

Topic

ias0nas
Starting Member

36 Posts

Posted - 2010-10-13 : 05:37:19

Hello,

Using SQL 2005.

I have a query that looks like that, only with a lot more columns and SUMs:

SELECT table1.col1, table2.col2, table3.col3,
(SELECT SUM(col4) FROM table4) AS sum_col1,
(SELECT SUM(col5) FROM table5) AS sum_col2
FROM table1
INNER JOIN table2 ON table1.id_col = table2.id_col
LEFT JOIN table3 ON table1.id_col = table3.id_col

It takes about 10 minutes to execute and locks tables, thus people yelling.
Is there a better way of writing this kind of query?
I cannot use NOLOCK and the query has to be executed on live data.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-13 : 05:56:00

you are doing a subquery on tables not associated with the main query. This means that you are performing that SUM FOR EVERY ROW in your outer query.

Don't do that!

Do a derived table instead. If the SUM is going to be the same value for every row (as it would in the example you posted do something like this instead


SELECT
	t1.col1
	, t2.col2
	, t3.col3
	, st4.[sum_col] AS [sum_col1]
	, st5.[sum_col] AS [sum_col2]
FROM
	table1 AS t1
	JOIN table2 AS t2 ON t1.id_col = t2.id_col
	LEFT JOIN table3 AS t3 ON t1.id_col = t3.id_col

	CROSS JOIN (
		SELECT SUM([col4]) AS [sum_col] FROM table4
		)
		AS st4

	CROSS JOIN (
		SELECT SUM([col5]) AS [sim_col] FROM table5
		)
		AS st5

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ias0nas
Starting Member

36 Posts

Posted - 2010-10-13 : 06:56:35

Thanks Charlie,

I am having some difficulties doing that in the actual query as I have simplified it a bit in the original post.
The actual query is like that:

SELECT
t1.col1
, t2.col2
, t3.col3
, (SELECT SUM(table4.col4) FROM table4 INNER JOIN table5 ON table4.col_id = table5.col_id AND table5.outer_id = t1.outer_id) AS sum_col1
FROM
table1 AS t1
JOIN table2 AS t2 ON t1.id_col = t2.id_col
LEFT JOIN table3 AS t3 ON t1.id_col = t3.id_col

Could you give me an example of how that would be done using derived tables?

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-13 : 07:00:39

Is this the *actual* query? or a slightly less simplified version........

I'd do something like


SELECT
	t1.col1
	, t2.col2
	, t3.col3
	, sm1.[sumVal] AS [sum_col1]
FROM
	table1 AS t1
	JOIN table2 AS t2 ON t1.id_col = t2.id_col
	LEFT JOIN table3 AS t3 ON t1.id_col = t3.id_col

	LEFT JOIN (
		SELECT
			t5.[outer_id] AS [outer_id]
			, SUM(t4.[col4]) AS [sumVal]
		FROM
			table4 AS t4
			JOIN table5 AS t5 ON t5.[col_id] = t4.[col_Id]
		GROUP BY
			t5.[outer_id]
		)
		AS sm1 ON sm1.[outer_id] = t1.[outer_id]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-10-13 : 07:14:45

You might want to further refine TC's example by putting a WHERE clause in the inner SELECT to restrict it to only t5.[outer_id] that happen to be present in the outer t1.[outer_id] list.

e.g.


SELECT Col1, Col2, ..., TotalColA, ...
FROM Table1 AS T1a
     LEFT OUTER JOIN
     (
         SELECT T2a.ID, SUM(T2a.ColA) AS TotalColA, ...
         FROM Table2 AS T2a
              JOIN Table1 AS T1b
                  ON T1b.ID = T2a.ID
                 AND T1b.ColX = 'FooBar'
         GROUP BY T2a.ID
     ) AS T2b
         ON T2b.ID = T1a.ID
WHERE  T1a.ColX = 'FooBar'

ias0nas
Starting Member

36 Posts

Posted - 2010-10-13 : 07:27:34

Thank you Charlie.

Yes, that was a slightly less simplified version.
It worked for an example so hopefully when I do that for all columns it should dramatically decrease the time needed to execute?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-13 : 08:39:16

We can't say for sure but I can't imagine it being worse than what you posted at first.

Can you abstract all the subqueries into the same derived table? if so you should see a HUGE improvement

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ias0nas
Starting Member

36 Posts

Posted - 2010-10-14 : 04:29:20

Thanks Kristen did that as well.

Charlie, I don't think it's possible but please let me know if I'm wrong because there is no huge difference.
All derived tables use the same actual tables but they all have different where clauses, so it looks like this:

SELECT
t1.col1
, t2.col2
, t3.col3
, sm1.[sumVal] AS [sum_col1]
, sm2.[sumVal2] AS [sum_col2]
FROM
table1 AS t1
JOIN table2 AS t2 ON t1.id_col = t2.id_col
LEFT JOIN table3 AS t3 ON t1.id_col = t3.id_col

LEFT JOIN (
SELECT
t5.[outer_id] AS [outer_id]
, SUM(t4.[col4]) AS [sumVal]
FROM
table4 AS t4
JOIN table5 AS t5 ON t5.[col_id] = t4.[col_Id]
WHERE t4.code = 'a'
GROUP BY
t5.[outer_id]
)
AS sm1 ON sm1.[outer_id] = t1.[outer_id]
LEFT JOIN (
SELECT
t5.[outer_id] AS [outer_id]
, SUM(t4.[col4]) AS [sumVal2]
FROM
table4 AS t4
JOIN table5 AS t5 ON t5.[col_id] = t4.[col_Id]
WHERE t4.code = 'b'
GROUP BY
t5.[outer_id]
)
AS sm2 ON sm2.[outer_id] = t1.[outer_id]

Thanks

Sachin.Nand

2937 Posts

Posted - 2010-10-14 : 05:06:54

quote:
Originally posted by ias0nas

Hello,

Using SQL 2005.

I have a query that looks like that, only with a lot more columns and SUMs:

SELECT table1.col1, table2.col2, table3.col3,
(SELECT SUM(col4) FROM table4) AS sum_col1,
(SELECT SUM(col5) FROM table5) AS sum_col2
FROM table1
INNER JOIN table2 ON table1.id_col = table2.id_col
LEFT JOIN table3 ON table1.id_col = table3.id_col

It takes about 10 minutes to execute and locks tables, thus people yelling.
Is there a better way of writing this kind of query?
I cannot use NOLOCK and the query has to be executed on live data.

Thanks

Isn't it this simple?


declare @sum1 as int
set @sum1=(SELECT SUM(col4) FROM table4)
declare @sum2 as int
set @sum2 =(SELECT SUM(col5) FROM table5)

SELECT table1.col1, table2.col2, table3.col3,
@sum1 AS sum_col1,
@sum2 AS sum_col2
FROM table1
INNER JOIN table2 ON table1.id_col = table2.id_col
LEFT JOIN table3 ON table1.id_col = table3.id_col

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources