Need Help in a Query. - 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

General SQL Server Forums

New to SQL Server Programming

Need Help in a Query.

Author

Topic

anil2007
Starting Member

5 Posts

Posted - 2007-11-16 : 05:40:54

Hi all ,

I am doing prerformance tuning

I want to run the query with in 10 mints but it takes 19 mint
this is the query

SELECT
US_OMEGA_TRANS_SUM.SOURCE_INDICATOR_CODE,
sum(US_OMEGA_TRANS_SUM.NOR_SPLIT_AMT),
US_OMEGA_TRANS_SUM.CUSTOMER_ID_08,
US_OMEGA_TRANS_SUM.CUSTOMER_ID_14,
US_OMEGA_TRANS_SUM.FIELD_REPORTING_CUSTOMER_NAME,
US_OMEGA_TRANS_SUM.FISCAL_PERIOD,
US_OMEGA_TRANS_SUM.SOURCE_TRANSACTION_ID
FROM
US_OMEGA_TRANS_SUM
WHERE
(
US_OMEGA_TRANS_SUM.FISCAL_PERIOD = '200709'
AND US_OMEGA_TRANS_SUM.CUSTOMER_ID_08 = '3224500007'
AND US_OMEGA_TRANS_SUM.CUSTOMER_ID_14 IN ('US001368083', 'US001794163', 'US005926100', 'US009581091', 'US010850857', 'US014097146', 'US046667523', 'US067381806', 'US121361740', 'US051964047', 'US621411289', 'US014097146', 'US069278570', 'US014127109', 'US031903834', 'US121594832')
AND US_OMEGA_TRANS_SUM.SOURCE_INDICATOR_CODE = 'MP'
AND US_OMEGA_TRANS_SUM.CUSTOMER_TYPE_CODE != 'N'
)
GROUP BY
US_OMEGA_TRANS_SUM.SOURCE_INDICATOR_CODE,
US_OMEGA_TRANS_SUM.CUSTOMER_ID_08,
US_OMEGA_TRANS_SUM.CUSTOMER_ID_14,
US_OMEGA_TRANS_SUM.FIELD_REPORTING_CUSTOMER_NAME,
US_OMEGA_TRANS_SUM.FISCAL_PERIOD,
US_OMEGA_TRANS_SUM.SOURCE_TRANSACTION_ID

Note :- in this index are (SOURCE_INDICATOR_CODE,FISCAL_PERIOD)
can u please help me

Thank & regards
anil

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-16 : 06:03:41

try indexing on
CUSTOMER_ID_08, CUSTOMER_ID_14, FISCAL_PERIOD, SOURCE_INDICATOR_CODE
include
CUSTOMER_TYPE_CODE, FIELD_REPORTING_CUSTOMER_NAME, SOURCE_TRANSACTION_ID, NOR_SPLIT_AMT

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-11-21 : 04:15:10

What sort of index do you have , is it a non-clustered or clustered? Also, are your statistics up to date?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources