Query Performance - 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 2008 Forums

Transact-SQL (2008)

Query Performance

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-14 : 14:15:44

Is this query correctly? How do I change the.
I want to give the same result.i want to modify this query for performance.

SELECT
0 AS A,
0 AS A_2,
SUM(TEST_ROWS.AMOUNT) AS B,
SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) AS B_2,
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
FROM
TEST_ROWS,TEST
WHERE
C = 0 AND TEST.CARD_ID=TEST_ROWS.CARD_ID
GROUP BY
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
UNION
SELECT
SUM(TEST_ROWS.AMOUNT) AS A,
SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) AS A_2,
0 AS B,
0 AS B_2,
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
FROM
TEST_ROWS,
TEST
WHERE
C = 1 AND
TEST.CARD_ID = TEST_ROWS.CARD_ID
GROUP BY
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 14:48:52

what you've currently can be simplified as


SELECT
CASE WHEN C=1 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS A,
CASE WHEN C=1 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS A_2,
CASE WHEN C=0 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS B, 
CASE WHEN C=0 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS B_2,
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
FROM
TEST_ROWS,TEST
WHERE
C IN (0,1) AND TEST.CARD_ID=TEST_ROWS.CARD_ID
GROUP BY
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-14 : 15:07:16

thank for answer.
TEST_ROWS.C
is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

given error.

quote:
Originally posted by visakh16

what you've currently can be simplified as

SELECT
CASE WHEN C=1 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS A,
CASE WHEN C=1 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS A_2,
CASE WHEN C=0 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS B,
CASE WHEN C=0 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS B_2,
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
FROM
TEST_ROWS,TEST
WHERE
C IN (0,1) AND TEST.CARD_ID=TEST_ROWS.CARD_ID
GROUP BY
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 15:18:34

tht was a typo


SELECT
SUM(CASE WHEN C=1 THEN TEST_ROWS.AMOUNT ELSE 0 END) AS A,
SUM(CASE WHEN C=1 THEN ISNULL(TEST_ROWS.AMOUNT_2,0) ELSE 0 END) AS A_2,
SUM(CASE WHEN C=0 THEN TEST_ROWS.AMOUNT ELSE 0 END) AS B, 
SUM(CASE WHEN C=0 THEN ISNULL(TEST_ROWS.AMOUNT_2,0) ELSE 0 END) AS B_2,
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
FROM
TEST_ROWS,TEST
WHERE
C IN (0,1) AND TEST.CARD_ID=TEST_ROWS.CARD_ID
GROUP BY
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-14 : 15:38:08

i think this correct query cost relative batch:36% - 64%
thank sir everything

SELECT
CASE WHEN C=1 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS A,
CASE WHEN C=1 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS A_2,
CASE WHEN C=0 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS B,
CASE WHEN C=0 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS B_2,
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
FROM
TEST_ROWS,TEST
WHERE
C IN (0,1) AND TEST.CARD_ID=TEST_ROWS.CARD_ID
GROUP BY
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
BA

quote:

Originally posted by visakh16

tht was a typo


SELECT
SUM(CASE WHEN C=1 THEN TEST_ROWS.AMOUNT ELSE 0 END) AS A,
SUM(CASE WHEN C=1 THEN ISNULL(TEST_ROWS.AMOUNT_2,0) ELSE 0 END) AS A_2,
SUM(CASE WHEN C=0 THEN TEST_ROWS.AMOUNT ELSE 0 END) AS B, 
SUM(CASE WHEN C=0 THEN ISNULL(TEST_ROWS.AMOUNT_2,0) ELSE 0 END) AS B_2,
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
FROM
TEST_ROWS,TEST
WHERE
C IN (0,1) AND TEST.CARD_ID=TEST_ROWS.CARD_ID
GROUP BY
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 15:40:47

so is it better to original query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-14 : 15:47:37

yes sir this query better than orginal query.

quote:
Originally posted by visakh16

so is it better to original query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources