Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

T-SQL Performance tuning on Aggregates Expand / Collapse
Author
Message
Posted Monday, November 11, 2013 4:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
d_uvarajan (11/11/2013)
As requested attached the "Actual Execution Plan".


Attached where?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1513064
Posted Monday, November 11, 2013 5:23 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
Can I suggest you start with the obvious and chose appropriate data types for your columns?

CREATE TABLE #tbl_ChannelViewCost 
(
rcc_id NUMERIC(10,0),
activity_dataset_id NUMERIC(10,0),
profit_centre_id NUMERIC(10,0),
channel_dataset_id NUMERIC(10,0),
customer_dataset_id NUMERIC(10,0),
product_category_dataset_id NUMERIC(10,0),
channel_cost NUMERIC(22,8),
driver_id NUMERIC(5,0),
)


Why are you using NUMERIC with 0 scale? That's 9 bytes for each one.

6 columns x 9bytes that's 54 bytes + 5 for the smaller NUMERIC

3559048 rows X 59 bytes = 231338120 bytes = 200MB

Changing these columns to INTs will cut the size in half:

3559048 rows X 28 = 99653344 bytes = 95MB

It's a start....


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1513083
Posted Monday, November 11, 2013 5:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:53 AM
Points: 108, Visits: 50
I have attached in my original post. Please check it!
For your quick reference let me attach it here again.
Please refer the "Query 19" and it's cost is 75%.


  Post Attachments 
Plan4.zip (4 views, 90.10 KB)
Post #1513089
Posted Monday, November 11, 2013 5:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:53 AM
Points: 108, Visits: 50
I use Numeric(10,0) for all Ids in the temeperary tables because the same was defined earlier in their corresponding original tables.
Post #1513091
Posted Monday, November 11, 2013 3:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
d_uvarajan (11/11/2013)
I use Numeric(10,0) for all Ids in the temeperary tables because the same was defined earlier in their corresponding original tables.


Ya just gotta love tables developed by tools (human or otherwise).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1513278
Posted Wednesday, November 13, 2013 4:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 43, Visits: 482
d_uvarajan (11/11/2013)
I have attached in my original post. Please check it!
For your quick reference let me attach it here again.


HI,
Just add appropriate indexes on your temp table involving in the query.,

Please refer the "Query 19" and it's cost is 75%.

Cost to the batch or operator cost does`t always means this most expensive thing, because those are based on estimations, so if the estimation is wrong then cost of operator(s) / batch would be wrong.


===========================================
performance issue:
(1) Have you update the statistics on all tables of underling query?
(2) Are you using index well on the tables?
(3) Can you simplify your Query by re-writing it .
(4) are indexes are Defragmented well ?

Post #1513804
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse