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 Administration
 Hash Match Performance Improve

Author  Topic 

rajadadi
Starting Member

30 Posts
Posted - 2010-05-04 : 03:48:42
please tell me how to improve performance below metion details:

Hash match Excution plan

Argument : HASH:([T144].[C1000000148], [T144].[C2000000544], [STA].[value], [T144].[C1000000150], [T144].[C1000000163], [T144].[C1000000162], [T144].[C1000000628], [T144].[C1000002287], [T144].[C1000005169], [T144].[C1000005170], [T144].[C1000000109], [T144].[C1000000155], [T144].[C1000000198], [T144].[C1000000193], [T144].[C1000000303], [T144].[C1000005509], [T144].[C1000000000], [T144].[C1000001566], [BCI].[value], [BUN].[value], [T144].[C1000000153], [T144].[C1000000154], [T144].[C1000000093], [T144].[C1000000094], [T144].[C1000000195], [T144].[C536871199], [T144].[C1000000016], [T144].[C1000000678], [T144].[C1000000013], [T144].[C1000001213], [T144].[C1000000145], [T134].[C1000000208], [T134].[C1000000206], [T134].[C1000000204], [T134].[C1000000211], [Expr1010], [T144].[C2], [T144].[C5], [Expr1011]), RESIDUAL:(((((((((((((((((((((((((((((((((((((([T144].[C1000000148]=[T144].[C1000000148] AND [T144].[C2000000544]=[T144].[C2000000544]) AND [STA].[value]=[STA].[value]) AND [T144].[C1000000150]=[T144].[C1000000150]) AND [T144].[C1000000163]=[T144].[C1000000163]) AND [T144].[C1000000162]=[T144].[C1000000162]) AND [T144].[C1000000628]=[T144].[C1000000628]) AND [T144].[C1000002287]=[T144].[C1000002287]) AND [T144].[C1000005169]=[T144].[C1000005169]) AND [T144].[C1000005170]=[T144].[C1000005170]) AND [T144].[C1000000109]=[T144].[C1000000109]) AND [T144].[C1000000155]=[T144].[C1000000155]) AND [T144].[C1000000198]=[T144].[C1000000198]) AND [T144].[C1000000193]=[T144].[C1000000193]) AND [T144].[C1000000303]=[T144].[C1000000303]) AND [T144].[C1000005509]=[T144].[C1000005509]) AND [T144].[C1000000000]=[T144].[C1000000000]) AND [T144].[C1000001566]=[T144].[C1000001566]) AND [BCI].[value]=[BCI].[value]) AND [BUN].[value]=[BUN].[value]) AND [T144].[C1000000153]=[T144].[C1000000153]) AND [T144].[C1000000154]=[T144].[C1000000154]) AND [T144].[C1000000093]=[T144].[C1000000093]) AND [T144].[C1000000094]=[T144].[C1000000094]) AND [T144].[C1000000195]=[T144].[C1000000195]) AND [T144].[C536871199]=[T144].[C536871199]) AND [T144].[C1000000016]=[T144].[C1000000016]) AND [T144].[C1000000678]=[T144].[C1000000678]) AND [T144].[C1000000013]=[T144].[C1000000013]) AND [T144].[C1000001213]=[T144].[C1000001213]) AND [T144].[C1000000145]=[T144].[C1000000145]) AND [T134].[C1000000208]=[T134].[C1000000208]) AND [T134].[C1000000206]=[T134].[C1000000206]) AND [T134].[C1000000204]=[T134].[C1000000204]) AND [T134].[C1000000211]=[T134].[C1000000211]) AND [Expr1010]=[Expr1010]) AND [T144].[C2]=[T144].[C2]) AND [T144].[C5]=[T144].[C5]) AND [Expr1011]=[Expr1011])


Defined Values : [T144].[C1000000001]=ANY([T144].[C1000000001]), [T144].[C1000000152]=ANY([T144].[C1000000152])

Description: Use each row from the top input to build a hash table, and each row from the bottom input to probe into the hash table, outputting all matching rows.

Estimated CPU COSt : 166.0935516

Estimated I/0: Cost : 203.6528778

Estimated number of execution : 1

Estimated number of rows: 113037.6484375

estimated operator cost :369.7464294 (71%)

estimated row size:971 B

estimated subtree cost;518.2058105

logical operation: Aggregate

Node id:3

output list : [T144].[C1000000148], [T144].[C2000000544], [T144].[C1000000150], [T144].[C1000000163], [T144].[C1000000162], [T144].[C1000000628], [T144].[C1000002287], [T144].[C1000005169], [T144].[C1000005170], [T144].[C1000000109], [T144].[C1000000155], [T144].[C1000000198], [T144].[C1000000193], [T144].[C1000000303], [T144].[C1000005509], [T144].[C1000000000], [T144].[C1000001566], [T144].[C1000000152], [T144].[C1000000153], [T144].[C1000000154], [T144].[C1000000093], [T144].[C1000000094], [T144].[C1000000195], [T144].[C536871199], [T144].[C1000000001], [T144].[C1000000016], [T144].[C1000000678], [T144].[C1000000013], [T144].[C1000001213], [T144].[C1000000145], [T144].[C2], [T144].[C5], [T134].[C1000000208], [T134].[C1000000206], [T134].[C1000000204], [T134].[C1000000211], [STA].[value], [BCI].[value], [BUN].[value], [Expr1010], [Expr1011]

parallel : false

physical operation; hashmatch



this is the query i am using:


Use ARSystem_dev

SELECT

Distinct AST.Configuration_Item_ID AS [Configuration item ID]

, AST.External_System_Number AS [External system number]

, STA.Value AS [CI Status]

, AST.Status_Reason AS [Status Reason]

, AST.Impact AS [Impact]

, AST.Urgency AS [Urgency]

, AST.On_Loan AS [On Loan]

, AST.Quantity AS [Quantity]

, AST.Quantity_Used AS [Quantity used]

, AST.Quantity_Remaining AS [Quantity remaining]

, AST.Tag_Number AS [Tag Number]

, AST.Serial_Number AS [Serial Number]

, AST.Name AS [CI Name]

, AST.System_Roles AS [CI Class]

, AST.System_Type AS [CI Type]

, AST.Configuration_Item_Role AS [CI Role]

, AST.Description AS [Description]

, AST.Environment AS [Environment]

, BCI.Value AS [Baseline CI]

, BUN.Value AS [Bundle]

, AST.Company AS [Company]

, AST.Product_Categorization_Tier_1 AS [Product Categorization Tier 1]

, AST.Product_Categorization_Tier_2 AS [Product Categorization Tier 2]

, AST.Product_Categorization_Tier_3 AS [Product Categorization Tier 3]

, AST.Product_Name AS [Product Name]

, AST.Product_Model_Version AS [Product Model]

, AST.Manufacturer AS [Manufacturer]

, AST.Specific_SLA_Criteria AS [Specific SLA criteria]

, AST.Company AS [Location Company]

, AST.Region AS [Region]

, AST.Site_Group AS [Site group]

, AST.Site AS [Site]

, AST.Site_Zone AS [Site Zone]

, AST.Additional_Location_Details AS [Additional Location Details]

, D.Association_type01 AS [Contact association type]

, D.Request_description01 AS [Contact name]

, D.Request_ID01 AS [Contact ID]

, D.Request_type01 AS [Contact type]

, dbo.cgi_fn_ConvertDateTime(AST.Request_Date, 120) AS [Requisition date]

, dbo.cgi_fn_ConvertDateTime(AST.PO_Date, 120) AS [Purchase date]

, dbo.cgi_fn_ConvertDateTime(AST.Acquisition_Date, 120) AS [Acquisition date]

, dbo.cgi_fn_ConvertDateTime(AST.Submit_Date, 120) AS [CMDB Submit date]

, dbo.cgi_fn_ConvertDateTime(AST.Configuration_Date, 120) AS [Configuration date]

, dbo.cgi_fn_ConvertDateTime(AST.Inventory_Date, 120) AS [Inventory date]

, dbo.cgi_fn_ConvertDateTime(AST.In_Service_Date, 120) AS [In Service date]

, dbo.cgi_fn_ConvertDateTime(AST.Out_of_Service_Date, 120) AS [Out of Service date]

, dbo.cgi_fn_ConvertDateTime(AST.Retired_Date, 120) AS [Retired date]

, dbo.cgi_fn_ConvertDateTime(AST.Disposal_Date, 120) AS [Disposed date]

, dbo.cgi_fn_ConvertDateTime(AST.Last_Audit_Date, 120) AS [Last Audit Date]

, dbo.cgi_fn_ConvertDateTime(AST.Next_Audit_Date, 120) AS [Next Audit Date]

, AST.Submitter AS [Submitter]

, AST.Last_Modified_By AS [Last Modified By]

, dbo.cgi_fn_ConvertDateTime(AST.Last_Modified_Date, 120) AS [Last modified date]

FROM dbo.AST_Configuration_Item__CI_ AST (nolock)

LEFT OUTER JOIN dbo.AST_CTM_Associations D (nolock) ON D.Request_ID02 = AST.Configuration_Item_ID

LEFT OUTER JOIN dbo.ARSchema SST (NOLOCK) ON SST.Name = 'AST:Configuration Item (CI)'

LEFT OUTER JOIN dbo.field_enum_values STA (NOLOCK)

ON STA.schemaid = sst.schemaid AND STA.fieldid = 7 AND AST.CI_Status = STA.enumID

LEFT OUTER JOIN dbo.field_enum_values BCI (NOLOCK)

ON BCI.schemaid = sst.schemaid AND BCI.fieldid = 1000001592 AND AST.Baseline_CI = BCI.enumID

LEFT OUTER JOIN dbo.field_enum_values BUN (NOLOCK)

ON BUN.schemaid = sst.schemaid AND BUN.fieldid = 1000004090 AND AST.Bundle = BUN.enumID

WHERE 1=1

AND AST.Company = 'CGI.CANRGM'

AND AST.Product_Categorization_Tier_1 = 'Hardware'




rajesh
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site