SQL Server Performance

Performance Issue - 2000 Vs 2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by harikrish, Oct 27, 2008.

  1. harikrish New Member

    Hi,
    We are migrating frm 2000 to 2005 and experiencing some performance issues in 2005.
    Example Query:select subcategory_id,subsubcategory,cat_code_3from #tmpsubsubcategory twhere str(subcategory_id) + cat_code_3 not in (select str(subcategory_id) + cat_code3 from stage.stg_sub_subcategory)
    Following is the execution plan in 2000 and 2005:
    2005
    select
    subcategory_id,subsubcategory,cat_code_3
    from #tmpsubsubcategory t
    where str(subcategory_id) + cat_code_3 not in
    (select str(subcategory_id) + cat_code3 from stage.stg_sub_subcategory)
    |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([Expr1008]))
    |--Nested Loops(Left Anti Semi Join)
    | |--Nested Loops(Left Anti Semi Join, WHERE:([Expr1008] IS NULL))
    | | |--Compute Scalar(DEFINE:([Expr1008]=str(CONVERT_IMPLICIT(float(53),[tempdb].[dbo].[#tmpsubsubcategory].[subcategory_id] as [t].[subcategory_id],0))+[tempdb].[dbo].[#tmpsubsubcategory].[cat_code_3] as [t].[cat_code_3]))
    | | | |--Table Scan(OBJECT:([tempdb].[dbo].[#tmpsubsubcategory] AS [t]))
    | | |--Top(TOP EXPRESSION:((1)))
    | | |--Table Scan(OBJECT:([CostcoCanada].[stage].[stg_sub_subcategory]))
    | |--Top(TOP EXPRESSION:((1)))
    | |--Table Scan(OBJECT:([CostcoCanada].[stage].[stg_sub_subcategory]), WHERE:((str(CONVERT_IMPLICIT(float(53),[CostcoCanada].[stage].[stg_sub_subcategory].[subcategory_id],0))+[CostcoCanada].[stage].[stg_sub_subcategory].[cat_code3]) IS NULL))
    |--Top(TOP EXPRESSION:((1)))
    |--Table Scan(OBJECT:([CostcoCanada].[stage].[stg_sub_subcategory]), WHERE:([Expr1008]=(str(CONVERT_IMPLICIT(float(53),[CostcoCanada].[stage].[stg_sub_subcategory].[subcategory_id],0))+[CostcoCanada].[stage].[stg_sub_subcategory].[cat_code3])))
    2000
    select
    subcategory_id,subsubcategory,cat_code_3
    from #tmpsubsubcategory t
    where str(subcategory_id) + cat_code_3 not in
    (select str(subcategory_id) + cat_code3 from stg_sub_subcategory)
    |--Hash Match(Right Anti Semi Join, HASH:([Expr1004])=([Expr1005]), RESIDUAL:([Expr1005]=[Expr1004]))
    |--Compute Scalar(DEFINE:([Expr1004]=str(Convert([stg_sub_subcategory].[subcategory_id]), NULL, NULL)+[stg_sub_subcategory].[cat_code3]))
    | |--Table Scan(OBJECT:([CRXCostcoCanadaStage].[dbo].[stg_sub_subcategory]))
    |--Compute Scalar(DEFINE:([Expr1005]=str(Convert([t].[subcategory_id]), NULL, NULL)+[t].[cat_code_3]))
    |--Nested Loops(Left Anti Semi Join)
    |--Nested Loops(Left Anti Semi Join, WHERE:(str(Convert([t].[subcategory_id]), NULL, NULL)+[t].[cat_code_3]=NULL))
    | |--Table Scan(OBJECT:([tempdb].[dbo].[#tmpsubsubcategory__________________________________________________________________________________________________00000000EDA1] AS [t]))
    | |--Row Count Spool
    | |--Top(1)
    | |--Table Scan(OBJECT:([CRXCostcoCanadaStage].[dbo].[stg_sub_subcategory]))
    |--Row Count Spool
    |--Table Scan(OBJECT:([CRXCostcoCanadaStage].[dbo].[stg_sub_subcategory]), WHERE:(str(Convert([stg_sub_subcategory].[subcategory_id]), NULL, NULL)+[stg_sub_subcategory].[cat_code3]=NULL))

    Here Nested loop is used in 2005 and Hash join is used by default in 2000. Is there any way the database engine can be tuned so that 2005 use Hash Join by default?
    Regards
    Hari
  2. FrankKalis Moderator

    Discussion continues here: http://sql-server-performance.com/Community/forums/t/28587.aspx

Share This Page