Update query slow - 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 2005 Forums

Transact-SQL (2005)

Update query slow

Author

Topic

iCodehead
Starting Member

3 Posts

Posted - 2007-06-19 : 15:57:41

I'm using SQL Server 2005 Express with SSMS to run a large cross database UPDATE query that runs forever because the query plan shows it is creating a Stream Aggregate. This same query runs on SQL server 2000 in 1 second.

I can work around this by rewriting it into 2 queries using a temp table, both taking 1 second to run (See below).

I would like to force this same behavior using a single query. I have tried using a sub-query in an attempt to influence the query plan but it still creates the Stream Aggregate (See below).

Is there a way to write a single query to force this same behavior?

I find it interesting that if I remove the "AND e.InvalidHistory = 0" it gets rid of the Stream Aggregate and works fine (See below).

Resources
-----------------------------------------------------------
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Operating System 5.1.2600

Original query
-----------------------------------------------------------
UPDATE EDS set EDS.memberid = m.memberid
FROM EDS INNER JOIN Emcee40Mno.dbo.members m ON IsNull(EDS.member_LastName,'') = IsNull(m.lastname,'')
AND IsNull(EDS.member_FirstName,'') = IsNull(m.firstname,'')
AND EDS.member_birthdate = m.birthdate
INNER JOIN Emcee40Mno.dbo.eligibilityhistory e ON e.healthplanmemberid = EDS.healthplanmemberid
AND e.FamilyExt = EDS.FamilyExt
AND e.InvalidHistory = 0
AND e.memberid = m.memberid
INNER JOIN Emcee40Mno.dbo.benefitplans bp ON e.benefitplanid = bp.benefitplanid
AND EDS.healthplanid = bp.healthplanid
WHERE EDS.ExceptionNbr is Null
AND EDS.memberid is Null

|--Clustered Index Update(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), SET:([Exchange].[dbo].[EDS].[MemberID] = [Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid]))
|--Top(ROWCOUNT est 0)
|--Stream Aggregate(GROUP BY:([Exchange].[dbo].[EDS].[EDS_primarykey]) DEFINE:([m].[memberid]=ANY([Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Exchange].[dbo].[EDS].[HealthplanMemberid], [Exchange].[dbo].[EDS].[FamilyExt], [m].[memberid], [bp].[BenefitPlanID], [Expr1025]) WITH ORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1008], [Expr1024]) WITH ORDERED PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Exchange].[dbo].[EDS].[Healthplanid]))
| | |--Sort(ORDER BY:([Exchange].[dbo].[EDS].[EDS_primarykey] ASC))
| | | |--Hash Match(Inner Join, HASH:([Expr1017], [Expr1019], [Expr1021])=([Expr1018], [Expr1020], [m].[birthdate]), RESIDUAL:([Expr1017]=[Expr1018] AND [Expr1019]=[Expr1020] AND [Expr1021]=[Emcee40Mno].[dbo].[members].[birthdate] as [m].[birthdate]))
| | | |--Compute Scalar(DEFINE:([Expr1017]=isnull([Exchange].[dbo].[EDS].[member_LastName],''), [Expr1019]=isnull([Exchange].[dbo].[EDS].[member_FirstName],''), [Expr1021]=CONVERT_IMPLICIT(datetime,[Exchange].[dbo].[EDS].[member_BirthDate],0)))
| | | | |--Clustered Index Scan(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), WHERE:([Exchange].[dbo].[EDS].[ExceptionNbr] IS NULL AND [Exchange].[dbo].[EDS].[MemberID] IS NULL) ORDERED)
| | | |--Compute Scalar(DEFINE:([Expr1018]=isnull([Emcee40Mno].[dbo].[members].[lastname] as [m].[lastname],''), [Expr1020]=isnull([Emcee40Mno].[dbo].[members].[firstname] as [m].[firstname],'')))
| | | |--Index Scan(OBJECT:([Emcee40Mno].[dbo].[members].[MembersNameBithdate] AS [m]))
| | |--Index Seek(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans].[healthplanid] AS [bp]), SEEK:([bp].[HealthPlanID]=[Exchange].[dbo].[EDS].[Healthplanid]) ORDERED FORWARD)
| |--RID Lookup(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans] AS [bp]), SEEK:([Bmk1008]=[Bmk1008]) LOOKUP ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Emcee40Mno].[dbo].[eligibilityhistory].[elighistidx] AS [e]), SEEK:([e].[healthplanmemberid]=[Exchange].[dbo].[EDS].[HealthplanMemberid] AND [e].[familyext]=[Exchange].[dbo].[EDS].[FamilyExt]), WHERE:([Emcee40Mno].[dbo].[eligibilityhistory].[benefitplanid] as [e].[benefitplanid]=[Emcee40Mno].[dbo].[BenefitPlans].[BenefitPlanID] as [bp].[BenefitPlanID] AND [Emcee40Mno].[dbo].[eligibilityhistory].[memberid] as [e].[memberid]=[Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid] AND [Emcee40Mno].[dbo].[eligibilityhistory].[invalidhistory] as [e].[invalidhistory]=(0)) ORDERED FORWARD)

Original query with "AND e.InvalidHistory = 0" removed
-----------------------------------------------------------
UPDATE EDS set EDS.memberid = m.memberid
FROM EDS INNER JOIN Emcee40Mno.dbo.members m ON IsNull(EDS.member_LastName,'') = IsNull(m.lastname,'')
AND IsNull(EDS.member_FirstName,'') = IsNull(m.firstname,'')
AND EDS.member_birthdate = m.birthdate
INNER JOIN Emcee40Mno.dbo.eligibilityhistory e ON e.healthplanmemberid = EDS.healthplanmemberid
AND e.FamilyExt = EDS.FamilyExt
AND e.memberid = m.memberid
INNER JOIN Emcee40Mno.dbo.benefitplans bp ON e.benefitplanid = bp.benefitplanid
AND EDS.healthplanid = bp.healthplanid
WHERE EDS.ExceptionNbr is Null
AND EDS.memberid is Null

|--Clustered Index Update(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), SET:([Exchange].[dbo].[EDS].[MemberID] = [Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Exchange].[dbo].[EDS].[EDS_primarykey] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Exchange].[dbo].[EDS].[Healthplanid], [e].[benefitplanid]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Exchange].[dbo].[EDS].[HealthplanMemberid], [Exchange].[dbo].[EDS].[FamilyExt], [m].[memberid]) OPTIMIZED)
| |--Hash Match(Inner Join, HASH:([Expr1017], [Expr1019], [Expr1021])=([Expr1018], [Expr1020], [m].[birthdate]), RESIDUAL:([Expr1017]=[Expr1018] AND [Expr1019]=[Expr1020] AND [Expr1021]=[Emcee40Mno].[dbo].[members].[birthdate] as [m].[birthdate]))
| | |--Compute Scalar(DEFINE:([Expr1017]=isnull([Exchange].[dbo].[EDS].[member_LastName],''), [Expr1019]=isnull([Exchange].[dbo].[EDS].[member_FirstName],''), [Expr1021]=CONVERT_IMPLICIT(datetime,[Exchange].[dbo].[EDS].[member_BirthDate],0)))
| | | |--Clustered Index Scan(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), WHERE:([Exchange].[dbo].[EDS].[ExceptionNbr] IS NULL AND [Exchange].[dbo].[EDS].[MemberID] IS NULL) ORDERED)
| | |--Compute Scalar(DEFINE:([Expr1018]=isnull([Emcee40Mno].[dbo].[members].[lastname] as [m].[lastname],''), [Expr1020]=isnull([Emcee40Mno].[dbo].[members].[firstname] as [m].[firstname],'')))
| | |--Index Scan(OBJECT:([Emcee40Mno].[dbo].[members].[MembersNameBithdate] AS [m]))
| |--Clustered Index Seek(OBJECT:([Emcee40Mno].[dbo].[eligibilityhistory].[elighistidx] AS [e]), SEEK:([e].[healthplanmemberid]=[Exchange].[dbo].[EDS].[HealthplanMemberid] AND [e].[familyext]=[Exchange].[dbo].[EDS].[FamilyExt]), WHERE:([Emcee40Mno].[dbo].[eligibilityhistory].[memberid] as [e].[memberid]=[Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid]) ORDERED FORWARD)
|--Index Seek(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans].[benplhealthID] AS [bp]), SEEK:([bp].[BenefitPlanID]=[Emcee40Mno].[dbo].[eligibilityhistory].[benefitplanid] as [e].[benefitplanid] AND [bp].[HealthPlanID]=[Exchange].[dbo].[EDS].[Healthplanid]) ORDERED FORWARD)

Sub-query in an attempt to influence query plan and improve performance
-----------------------------------------------------------

UPDATE EDS set memberid = a.memberid
FROM EDS INNER JOIN
(SELECT EDS.EDS_primarykey, m.memberid
FROM EDS INNER JOIN Emcee40Mno.dbo.members m ON IsNull(EDS.member_LastName,'') = IsNull(m.lastname,'')
AND IsNull(EDS.member_FirstName,'') = IsNull(m.firstname,'')
AND EDS.member_birthdate = m.birthdate
INNER JOIN Emcee40Mno.dbo.eligibilityhistory e ON e.healthplanmemberid = EDS.healthplanmemberid
AND e.FamilyExt = EDS.FamilyExt
AND e.InvalidHistory = 0
AND e.memberid = m.memberid
INNER JOIN Emcee40Mno.dbo.benefitplans bp ON e.benefitplanid = bp.benefitplanid
AND EDS.healthplanid = bp.healthplanid
WHERE EDS.ExceptionNbr is Null
AND EDS.memberid is Null
) a ON EDS.EDS_primarykey = a.EDS_primarykey

Work around that works
-----------------------------------------------------------

Drop Table Temp_EDSMembers

SELECT EDS.EDS_primarykey, m.memberid
into Temp_EDSMembers
FROM EDS INNER JOIN Emcee40Mno.dbo.members m ON IsNull(EDS.member_LastName,'') = IsNull(m.lastname,'')
AND IsNull(EDS.member_FirstName,'') = IsNull(m.firstname,'')
AND EDS.member_birthdate = m.birthdate
INNER JOIN Emcee40Mno.dbo.eligibilityhistory e ON e.healthplanmemberid = EDS.healthplanmemberid
AND e.FamilyExt = EDS.FamilyExt
AND e.InvalidHistory = 0
AND e.memberid = m.memberid
INNER JOIN Emcee40Mno.dbo.benefitplans bp ON e.benefitplanid = bp.benefitplanid
AND EDS.healthplanid = bp.healthplanid
WHERE EDS.ExceptionNbr is Null
AND EDS.memberid is Null

UPDATE EDS set EDS.memberid = Temp_EDSMembers.memberid
FROM EDS INNER JOIN Temp_EDSMembers ON EDS.EDS_primarykey = Temp_EDSMembers.EDS_primarykey

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 04:57:31

Well looks like you have done the things that I would be inclined to try.

I'm not very keen on this:


EDS INNER JOIN Emcee40Mno.dbo.members m ON IsNull(EDS.member_LastName,'') = IsNull(m.lastname,'') 
AND IsNull(EDS.member_FirstName,'') = IsNull(m.firstname,'') 
AND EDS.member_birthdate = m.birthdate

Can you avoid a function call on the data columns?

Can you really have a situation where
EDS.member_FirstName IS NULL and m.firstname == ''
or would they in practice BOTH be an empty string, or BOTH be Null?

Because if that is the case you could change that to:

(EDS.member_FirstName = m.firstname OR (EDS.member_FirstName IS NULL AND m.firstname IS NULL))

which may be more efficient (ditto for LastName columns)

"AND e.InvalidHistory = 0"

Might be worth Casting the zero ? (particularly if e.InvalidHistory is a BIT - or anything other than an INT)

And I'm sure you know this, but you should change:


Drop Table Temp_EDSMembers

SELECT EDS.EDS_primarykey, m.memberid
into Temp_EDSMembers

to pre-create the table, and then do


INSERT Temp_EDSMembers(EDS_primarykey, memberid)
SELECT EDS.EDS_primarykey, m.memberid
FROM ...

and make sure you define EDS_primarykey as the PK for Temp_EDSMembers table.

Kristen

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-20 : 05:25:49

Like Kristen, I suspect the ISNULL.
I would be interested in knowing if the following avoids the Stream Aggregate.

UPDATE S
SET memberid = m.memberid 
FROM EDS S
    JOIN (
            SELECT m.memberid
                ,ISNULL(m.lastname,'') AS lastname
                ,ISNULL(m.firstname,'') AS firstname
                ,m.birthdate
                ,e.healthplanmemberid
                ,e.FamilyExt
                ,bp.healthplanid
            FROM Emcee40Mno.dbo.members m
                JOIN Emcee40Mno.dbo.eligibilityhistory e
                    ON e.memberid = m.memberid
                        and e.InvalidHistory = 0
                JOIN Emcee40Mno.dbo.benefitplans bp
                    ON e.benefitplanid = bp.benefitplanid 
        ) D
        ON ISNULL(S.member_LastName,'') = D.lastname
            AND ISNULL(S.member_FirstName,'') = D.firstname
            AND S.member_birthdate = D.birthdate
            AND S.healthplanmemberid = D.healthplanmemberid
            AND S.FamilyExt = D.FamilyExt
            AND S.healthplanid = D.healthplanid
WHERE S.ExceptionNbr IS NULL
    AND S.memberid IS NULL

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 05:27:49

How are you colour-coding that Ifor? (Hopefully not "by hand"

)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-20 : 05:46:58

quote:
Originally posted by Kristen

How are you colour-coding that Ifor? (Hopefully not "by hand" )

I have an editor called NoteTab Pro with a cheap and chearful macro. When I get time I intend to parse correctly in C#. Here is the macro with [] replaced with {}:

^!Replace "LEFT(" >> "{purple}LEFT{/purple}(" HSA
^!Replace "RIGHT(" >> "{purple}RIGHT{/purple}(" HSA
^!Replace "LEFT " >> "{brown}LEFT{/brown} " HSA
^!Replace "RIGHT " >> "{brown}RIGHT{/brown} " HSA

^!Replace "SELECT" >> "{blue}SELECT{/blue}" HSA
;^!Replace "^{^-}*SELECT" >> "\1{blue}SELECT{/blue}" HSAR
^!Replace "FROM" >> "{blue}FROM{/blue}" HSA
^!Replace "WHERE" >> "{blue}WHERE{/blue}" HSA
^!Replace "GROUP BY" >> "{blue}GROUP BY{/blue}" HSA
^!Replace "ORDER BY" >> "{blue}ORDER BY{/blue}" HSA
^!Replace "HAVING" >> "{blue}HAVING{/blue}" HSA
^!Replace "AS" >> "{blue}AS{/blue}" HSA
^!Replace "CASE" >> "{blue}CASE{/blue}" HSA
^!Replace "WHEN" >> "{blue}WHEN{/blue}" HSA
^!Replace "THEN" >> "{blue}THEN{/blue}" HSA
^!Replace "BEGIN" >> "{blue}BEGIN{/blue}" HSA
^!Replace "IF" >> "{blue}IF{/blue}" HSA
^!Replace "WHILE" >> "{blue}WHILE{/blue}" HSA
^!Replace "END" >> "{blue}END{/blue}" HSA
^!Replace "DISTINCT" >> "{blue}DISTINCT{/blue}" HSA
^!Replace "TOP" >> "{blue}TOP{/blue}" HSA
^!Replace "FULL" >> "{blue}FULL{/blue}" HSA
^!Replace "DECLARE" >> "{blue}DECLARE{/blue}" HSA
^!Replace "CREATE" >> "{blue}CREATE{/blue}" HSA
^!Replace "ALTER" >> "{blue}ALTER{/blue}" HSA
^!Replace "PROCEDURE" >> "{blue}PROCEDURE{/blue}" HSA
^!Replace "FUNCTION" >> "{blue}FUNCTION{/blue}" HSA
^!Replace "TABLE" >> "{blue}TABLE{/blue}" HSA
^!Replace "VIEW" >> "{blue}VIEW{/blue}" HSA
^!Replace "INSERT" >> "{blue}INSERT{/blue}" HSA
^!Replace "UPDATE" >> "{blue}UPDATE{/blue}" HSA
^!Replace "DELETE" >> "{blue}DELETE{/blue}" HSA
^!Replace "SET" >> "{blue}SET{/blue}" HSA
^!Replace "INTO" >> "{blue}INTO{/blue}" HSA
^!Replace "MAX" >> "{blue}MAX{/blue}" HSA
^!Replace "MIN" >> "{blue}MIN{/blue}" HSA
^!Replace "UNION" >> "{blue}UNION{/blue}" HSA
^!Replace "VALUES" >> "{blue}VALUES{/blue}" HSA
^!Replace "ON" >> "{blue}ON{/blue}" HSA
^!Replace "ELSE" >> "{blue}ELSE{/blue}" HSA
^!Replace "PRIMARY KEY" >> "{blue}PRIMARY KEY{/blue}" HSA
^!Replace "FOREIGN KEY" >> "{blue}FOREIGN KEY{/blue}" HSA
^!Replace "REFERENCES" >> "{blue}REFERENCES{/blue}" HSA
^!Replace "CONSTRAINT" >> "{blue}CONSTRAINT{/blue}" HSA
^!Replace "DEFAULT" >> "{blue}DEFAULT{/blue}" HSA
^!Replace "FOR" >> "{blue}FOR{/blue}" HSA
^!Replace "CHECK" >> "{blue}CHECK{/blue}" HSA
^!Replace "RESTORE DATABASE" >> "{blue}RESTORE DATABASE{/blue}" HSA
^!Replace "BACKUP DATABASE" >> "{blue}BACKUP DATABASE{/blue}" HSA
^!Replace "DISK" >> "{blue}DISK{/blue}" HSA
^!Replace "MOVE" >> "{blue}MOVE{/blue}" HSA
^!Replace "WITH" >> "{blue}WITH{/blue}" HSA
^!Replace " TO" >> "{blue} TO{/blue}" HSA
^!Replace "ADD" >> "{blue}ADD{/blue}" HSA
^!Replace "DROP" >> "{blue}DROP{/blue}" HSA
^!Replace "EXEC" >> "{blue}EXEC{/blue}" HSA
^!Replace "IS" >> "{blue}IS{/blue}" HSA
^!Replace "RETURN" >> "{blue}RETURN{/blue}" HSA
^!Replace "RETURNS" >> "{blue}RETURNS{/blue}" HSA
^!Replace "INNER" >> "{blue}INNER{/blue}" HSA
^!Replace "LOOP" >> "{blue}LOOP{/blue}" HSA
^!Replace "HASH" >> "{blue}HASH{/blue}" HSA
^!Replace "MERGE" >> "{blue}MERGE{/blue}" HSA
^!Replace "OUTPUT" >> "{blue}OUTPUT{/blue}" HSA
^!Replace "NOCOUNT" >> "{blue}NOCOUNT{/blue}" HSA
^!Replace "UPDLOCK" >> "{blue}UPDLOCK{/blue}" HSA
^!Replace "NOLOCK" >> "{blue}NOLOCK{/blue}" HSA
^!Replace "OVER" >> "{blue}OVER{/blue}" HSA
^!Replace "PARTITION" >> "{blue}PARTITION{/blue}" HSA
^!Replace "BY" >> "{blue}BY{/blue}" HSA
^!Replace "DESC" >> "{blue}DESC{/blue}" HSA

^!Replace "bigint" >> "{blue}bigint{/blue}" HSA
^!Replace "binary" >> "{blue}binary{/blue}" HSA
^!Replace "bit" >> "{blue}bit{/blue}" HSA
^!Replace "char" >> "{blue}char{/blue}" HSA
^!Replace "cursor" >> "{blue}cursor{/blue}" HSA
^!Replace "datetime" >> "{blue}datetime{/blue}" HSA
^!Replace "decimal" >> "{blue}decimal{/blue}" HSA
^!Replace "float" >> "{blue}float{/blue}" HSA
^!Replace "image" >> "{blue}image{/blue}" HSA
^!Replace "int" >> "{blue}int{/blue}" HSA
^!Replace "nchar" >> "{blue}nchar{/blue}" HSA
^!Replace "money" >> "{blue}money{/blue}" HSA
^!Replace "ntext" >> "{blue}ntext{/blue}" HSA
^!Replace "nvarchar" >> "{blue}nvarchar{/blue}" HSA
^!Replace "real" >> "{blue}real{/blue}" HSA
^!Replace "smalldatetime" >> "{blue}smalldatetime{/blue}" HSA
^!Replace "smallint" >> "{blue}smallint{/blue}" HSA
^!Replace "smallmoney" >> "{blue}smallmoney{/blue}" HSA
^!Replace "text" >> "{blue}text{/blue}" HSA
^!Replace "tinyint" >> "{blue}tinyint{/blue}" HSA
^!Replace "timestamp" >> "{blue}timestamp{/blue}" HSA
^!Replace "uniqueidentifier" >> "{blue}uniqueidentifier{/blue}" HSA
^!Replace "varbinary" >> "{blue}varbinary{/blue}" HSA
^!Replace "varchar" >> "{blue}varchar{/blue}" HSA

^!Replace "JOIN" >> "{brown}JOIN{/brown}" HSA
^!Replace "OUTER" >> "{brown}OUTER{/brown}" HSA
^!Replace "CROSS" >> "{brown}CROSS{/brown}" HSA
^!Replace "AND" >> "{brown}AND{/brown}" HSA
^!Replace "OR" >> "{brown}OR{/brown}" HSA
^!Replace "NOT" >> "{brown}NOT{/brown}" HSA
^!Replace "EXISTS" >> "{brown}EXISTS{/brown}" HSA
^!Replace "ALL" >> "{brown}ALL{/brown}" HSA
^!Replace "ANY" >> "{brown}ANY{/brown}" HSA
^!Replace "BETWEEN" >> "{brown}BETWEEN{/brown}" HSA
^!Replace "LIKE" >> "{brown}LIKE{/brown}" HSA
^!Replace "NULL" >> "{brown}NULL{/brown}" HSA
^!Replace "IN" >> "{brown}IN{/brown}" HSA
^!Replace "(" >> "{brown}({/brown}" HSA
^!Replace ")" >> "{brown}){/brown}" HSA

^!Replace "CAST" >> "{purple}CAST{/purple}" HSA
^!Replace "CONVERT" >> "{purple}CONVERT{/purple}" HSA
^!Replace "SUM" >> "{purple}SUM{/purple}" HSA
^!Replace "COUNT" >> "{purple}COUNT{/purple}" HSA
^!Replace "ISNULL" >> "{purple}ISNULL{/purple}" HSA
^!Replace "NULLIF" >> "{purple}NULLIF{/purple}" HSA
^!Replace "ROUND" >> "{purple}ROUND{/purple}" HSA

^!Replace "@@ERROR" >> "{purple}@@ERROR{/purple}" HSA
^!Replace "@@IDENTITY" >> "{purple}@@IDENTITY{/purple}" HSA
^!Replace "@@ROWCOUNT" >> "{purple}@@ROWCOUNT{/purple}" HSA
^!Replace "@@TRANCOUNT" >> "{purple}@@TRANCOUNT{/purple}" HSA
^!Replace "APP_NAME" >> "{purple}APP_NAME{/purple}" HSA
^!Replace "CURRENT_TIMESTAMP" >> "{purple}CURRENT_TIMESTAMP{/purple}" HSA
^!Replace "CURRENT_USER" >> "{purple}CURRENT_USER{/purple}" HSA
^!Replace "DATENAME" >> "{purple}DATENAME{/purple}" HSA

^!Replace "FORMATMESSAGE" >> "{purple}FORMATMESSAGE{/purple}" HSA
^!Replace "GETANSINULL" >> "{purple}GETANSINULL{/purple}" HSA
^!Replace "GETDATE" >> "{purple}GETDATE{/purple}" HSA
^!Replace "HOST_ID" >> "{purple}HOST_ID{/purple}" HSA
^!Replace "HOST_NAME" >> "{purple}HOST_NAME{/purple}" HSA
^!Replace "IDENT_INCR" >> "{purple}IDENT_INCR{/purple}" HSA
^!Replace "IDENT_SEED" >> "{purple}IDENT_SEED{/purple}" HSA

^!Replace "NEWID" >> "{purple}NEWID{/purple}" HSA
^!Replace "PERMISSIONS" >> "{purple}PERMISSIONS{/purple}" HSA
^!Replace "SESSION_USER" >> "{purple}SESSION_USER{/purple}" HSA
^!Replace "STATS_DATE" >> "{purple}STATS_DATE{/purple}" HSA
^!Replace "SYSTEM_USER" >> "{purple}SYSTEM_USER{/purple}" HSA
^!Replace "TEXTPTR" >> "{purple}TEXTPTR{/purple}" HSA
^!Replace "TEXTVALID" >> "{purple}TEXTVALID{/purple}" HSA
^!Replace "USER_NAME" >> "{purple}USER_NAME{/purple}" HSA

^!Replace "DATEADD" >> "{purple}DATEADD{/purple}" HSA
^!Replace "DATEDIFF" >> "{purple}DATEDIFF{/purple}" HSA
^!Replace "DATEPART" >> "{purple}DATEPART{/purple}" HSA
^!Replace "DAY" >> "{purple}DAY{/purple}" HSA
^!Replace "GETUTCDATE" >> "{purple}GETUTCDATE{/purple}" HSA
^!Replace "MONTH" >> "{purple}MONTH{/purple}" HSA
^!Replace "YEAR" >> "{purple}YEAR{/purple}" HSA

^!Replace "ASCII" >> "{purple}ASCII{/purple}" HSA
^!Replace "CHAR" >> "{purple}CHAR{/purple}" HSA
^!Replace "CHARINDEX" >> "{purple}CHARINDEX{/purple}" HSA
^!Replace "DIFFERENCE" >> "{purple}DIFFERENCE{/purple}" HSA
^!Replace "LEN" >> "{purple}LEN{/purple}" HSA
^!Replace "LOWER" >> "{purple}LOWER{/purple}" HSA
^!Replace "LTRIM" >> "{purple}LTRIM{/purple}" HSA
^!Replace "NCHAR" >> "{purple}NCHAR{/purple}" HSA
^!Replace "PATINDEX" >> "{purple}PATINDEX{/purple}" HSA
^!Replace "REPLACE" >> "{purple}REPLACE{/purple}" HSA
^!Replace "QUOTENAME" >> "{purple}QUOTENAME{/purple}" HSA
^!Replace "REPLICATE" >> "{purple}REPLICATE{/purple}" HSA
^!Replace "REVERSE" >> "{purple}REVERSE{/purple}" HSA
^!Replace "RTRIM" >> "{purple}RTRIM{/purple}" HSA
^!Replace "SOUNDEX" >> "{purple}SOUNDEX{/purple}" HSA
^!Replace "SPACE" >> "{purple}SPACE{/purple}" HSA
^!Replace "STR" >> "{purple}STR{/purple}" HSA
^!Replace "STUFF" >> "{purple}STUFF{/purple}" HSA
^!Replace "SUBSTRING" >> "{purple}SUBSTRING{/purple}" HSA
^!Replace "UNICODE" >> "{purple}UNICODE{/purple}" HSA
^!Replace "UPPER" >> "{purple}UPPER{/purple}" HSA

^!Replace "'{{^'}*}'" >> "{red}'\1'{/red}" HSAR

^!Replace "--{.*}" >> "{green}--\1{/green}" HSAR
^!Replace "/*" >> "{green}/*" HSA
^!Replace "*/" >> "*/{/green}" HSA

^!Replace "\t" >> " " HSAR

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 07:24:43

Converted to a Macro for my editor, testing and working nicely. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 10:35:50

me too. Thanks

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 10:45:17

Which one do you use khtan?

iCodehead
Starting Member

3 Posts

Posted - 2007-06-20 : 13:31:40

Thanks for all your replies.

Kristen:
The IsNull() is a hold over from Sql 6.5 because some older databases may have a space in the field instead of a Null. Your example is also widely used in many queries and I would have alternatively used it if it made a difference. As a test I have removed the IsNull() (See below) but it does not get rid of the Stream Aggregate.

e.InvalidHistory is an Integer so no help there.

The CREATE Table Temp_EDSMembers would be the way to go as the final cut but I am avoiding using 2 queries. I am updating some VB code the easiest solution would be to substitute just the queries and not have to add new code. I still feel that there must be a way to influence the query plan so I can do a single UPDATE.

Ifor:
Ran your query but still no joy. (See below)

Here is something very interesting. Below is the "Query plan from Sql server 2000". This query plan looks identical to the "Original query with 'AND e.InvalidHistory = 0' removed" posted earlier. This is what I would like the query plan to execute. The adding of the "AND e.InvalidHistory = 0" condition should not be causing the query optimizer to create a Stream Aggregate. Its like it is compensating for some lack of resources. I definitely think that SQL server 2005 is a step backward when it comes to the query optimizer.

Any other ideas?

Query plan from Sql server 2000
-----------------------------------------------------------
UPDATE EDS set EDS.memberid = m.memberid
FROM EDS INNER JOIN Emcee40Mno.dbo.members m ON IsNull(EDS.member_LastName,'') = IsNull(m.lastname,'')
AND IsNull(EDS.member_FirstName,'') = IsNull(m.firstname,'')
AND EDS.member_birthdate = m.birthdate
INNER JOIN Emcee40Mno.dbo.eligibilityhistory e ON e.healthplanmemberid = EDS.healthplanmemberid
AND e.FamilyExt = EDS.FamilyExt
AND e.InvalidHistory = 0
AND e.memberid = m.memberid
INNER JOIN Emcee40Mno.dbo.benefitplans bp ON e.benefitplanid = bp.benefitplanid
AND EDS.healthplanid = bp.healthplanid
WHERE EDS.ExceptionNbr is Null
AND EDS.memberid is Null

|--Clustered Index Update(OBJECT:([ExchangeMNO].[dbo].[EDS].[PK__EDS__6A1C8307]), SET:([EDS].[MemberID]=[m].[memberid]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([EDS].[Healthplanid], [e].[benefitplanid]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([EDS].[FamilyExt], [EDS].[HealthplanMemberid], [m].[memberid]))
| |--Hash Match(Inner Join, HASH:([Expr1007], [Expr1009], [Expr1011])=([Expr1008], [Expr1010], [m].[birthdate]), RESIDUAL:(([Expr1007]=[Expr1008] AND [Expr1009]=[Expr1010]) AND [Expr1011]=[m].[birthdate]))
| | |--Compute Scalar(DEFINE:([Expr1007]=isnull([EDS].[member_LastName], ''), [Expr1009]=isnull([EDS].[member_FirstName], ''), [Expr1011]=Convert([EDS].[member_BirthDate])))
| | | |--Clustered Index Scan(OBJECT:([ExchangeMNO].[dbo].[EDS].[PK__EDS__6A1C8307]), WHERE:([EDS].[ExceptionNbr]=NULL AND [EDS].[MemberID]=NULL) ORDERED)
| | |--Compute Scalar(DEFINE:([Expr1008]=isnull([m].[lastname], ''), [Expr1010]=isnull([m].[firstname], '')))
| | |--Index Scan(OBJECT:([Emcee40Mno].[dbo].[members].[MembersNameBithdate] AS [m]))
| |--Clustered Index Seek(OBJECT:([Emcee40Mno].[dbo].[eligibilityhistory].[elighistidx] AS [e]), SEEK:([e].[healthplanmemberid]=[EDS].[HealthplanMemberid] AND [e].[familyext]=[EDS].[FamilyExt]), WHERE:([m].[memberid]=[e].[memberid] AND [e].[invalidhistory]=0) ORDERED FORWARD)
|--Index Seek(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans].[benplhealthID] AS [bp]), SEEK:([bp].[BenefitPlanID]=[e].[benefitplanid] AND [bp].[HealthPlanID]=[EDS].[Healthplanid]) ORDERED FORWARD)

Original query with IsNull() removed
-----------------------------------------------------------
UPDATE EDS set EDS.memberid = m.memberid
FROM EDS INNER JOIN Emcee40Mno.dbo.members m ON EDS.member_LastName = m.lastname
AND EDS.member_FirstName = m.firstname
AND EDS.member_birthdate = m.birthdate
INNER JOIN Emcee40Mno.dbo.eligibilityhistory e ON e.healthplanmemberid = EDS.healthplanmemberid
AND e.FamilyExt = EDS.FamilyExt
AND e.InvalidHistory = 0
AND e.memberid = m.memberid
INNER JOIN Emcee40Mno.dbo.benefitplans bp ON e.benefitplanid = bp.benefitplanid
AND EDS.healthplanid = bp.healthplanid
WHERE EDS.ExceptionNbr is Null
AND EDS.memberid is Null

|--Clustered Index Update(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), SET:([Exchange].[dbo].[EDS].[MemberID] = [Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid]))
|--Top(ROWCOUNT est 0)
|--Stream Aggregate(GROUP BY:([Exchange].[dbo].[EDS].[EDS_primarykey]) DEFINE:([m].[memberid]=ANY([Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Exchange].[dbo].[EDS].[HealthplanMemberid], [Exchange].[dbo].[EDS].[FamilyExt], [m].[memberid], [bp].[BenefitPlanID], [Expr1021]) WITH ORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1008], [Expr1020]) WITH ORDERED PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Exchange].[dbo].[EDS].[Healthplanid]))
| | |--Sort(ORDER BY:([Exchange].[dbo].[EDS].[EDS_primarykey] ASC))
| | | |--Hash Match(Inner Join, HASH:([Exchange].[dbo].[EDS].[member_LastName], [Exchange].[dbo].[EDS].[member_FirstName], [Expr1017])=([m].[lastname], [m].[firstname], [m].[birthdate]), RESIDUAL:([Exchange].[dbo].[EDS].[member_LastName]=[Emcee40Mno].[dbo].[members].[lastname] as [m].[lastname] AND [Exchange].[dbo].[EDS].[member_FirstName]=[Emcee40Mno].[dbo].[members].[firstname] as [m].[firstname] AND [Expr1017]=[Emcee40Mno].[dbo].[members].[birthdate] as [m].[birthdate]))
| | | |--Compute Scalar(DEFINE:([Expr1017]=CONVERT_IMPLICIT(datetime,[Exchange].[dbo].[EDS].[member_BirthDate],0)))
| | | | |--Clustered Index Scan(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), WHERE:([Exchange].[dbo].[EDS].[ExceptionNbr] IS NULL AND [Exchange].[dbo].[EDS].[MemberID] IS NULL) ORDERED)
| | | |--Index Scan(OBJECT:([Emcee40Mno].[dbo].[members].[MembersNameBithdate] AS [m]))
| | |--Index Seek(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans].[healthplanid] AS [bp]), SEEK:([bp].[HealthPlanID]=[Exchange].[dbo].[EDS].[Healthplanid]) ORDERED FORWARD)
| |--RID Lookup(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans] AS [bp]), SEEK:([Bmk1008]=[Bmk1008]) LOOKUP ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Emcee40Mno].[dbo].[eligibilityhistory].[elighistidx] AS [e]), SEEK:([e].[healthplanmemberid]=[Exchange].[dbo].[EDS].[HealthplanMemberid] AND [e].[familyext]=[Exchange].[dbo].[EDS].[FamilyExt]), WHERE:([Emcee40Mno].[dbo].[eligibilityhistory].[benefitplanid] as [e].[benefitplanid]=[Emcee40Mno].[dbo].[BenefitPlans].[BenefitPlanID] as [bp].[BenefitPlanID] AND [Emcee40Mno].[dbo].[eligibilityhistory].[memberid] as [e].[memberid]=[Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid] AND [Emcee40Mno].[dbo].[eligibilityhistory].[invalidhistory] as [e].[invalidhistory]=(0)) ORDERED FORWARD)

Ifor's query
-----------------------------------------------------------
UPDATE S
SET memberid = D.memberid
FROM EDS S
JOIN (
SELECT m.memberid
,ISNULL(m.lastname,'') AS lastname
,ISNULL(m.firstname,'') AS firstname
,m.birthdate
,e.healthplanmemberid
,e.FamilyExt
,bp.healthplanid
FROM Emcee40Mno.dbo.members m
JOIN Emcee40Mno.dbo.eligibilityhistory e
ON e.memberid = m.memberid
and e.InvalidHistory = 0
JOIN Emcee40Mno.dbo.benefitplans bp
ON e.benefitplanid = bp.benefitplanid
) D
ON ISNULL(S.member_LastName,'') = D.lastname
AND ISNULL(S.member_FirstName,'') = D.firstname
AND S.member_birthdate = D.birthdate
AND S.healthplanmemberid = D.healthplanmemberid
AND S.FamilyExt = D.FamilyExt
AND S.healthplanid = D.healthplanid
WHERE S.ExceptionNbr IS NULL
AND S.memberid IS NULL

|--Clustered Index Update(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), SET:([Exchange].[dbo].[EDS].[MemberID] = [Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid]))
|--Top(ROWCOUNT est 0)
|--Stream Aggregate(GROUP BY:([S].[EDS_primarykey]) DEFINE:([m].[memberid]=ANY([Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([S].[HealthplanMemberid], [S].[FamilyExt], [m].[memberid], [bp].[BenefitPlanID], [Expr1025]) WITH ORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1007], [Expr1024]) WITH ORDERED PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([S].[Healthplanid]))
| | |--Sort(ORDER BY:([S].[EDS_primarykey] ASC))
| | | |--Hash Match(Inner Join, HASH:([Expr1017], [Expr1019], [Expr1021])=([Expr1018], [Expr1020], [m].[birthdate]), RESIDUAL:([Expr1017]=[Expr1018] AND [Expr1019]=[Expr1020] AND [Expr1021]=[Emcee40Mno].[dbo].[members].[birthdate] as [m].[birthdate]))
| | | |--Compute Scalar(DEFINE:([Expr1017]=isnull([Exchange].[dbo].[EDS].[member_LastName] as [S].[member_LastName],''), [Expr1019]=isnull([Exchange].[dbo].[EDS].[member_FirstName] as [S].[member_FirstName],''), [Expr1021]=CONVERT_IMPLICIT(datetime,[Exchange].[dbo].[EDS].[member_BirthDate] as [S].[member_BirthDate],0)))
| | | | |--Clustered Index Scan(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3] AS [S]), WHERE:([Exchange].[dbo].[EDS].[ExceptionNbr] as [S].[ExceptionNbr] IS NULL AND [Exchange].[dbo].[EDS].[MemberID] as [S].[MemberID] IS NULL) ORDERED)
| | | |--Compute Scalar(DEFINE:([Expr1018]=isnull([Emcee40Mno].[dbo].[members].[lastname] as [m].[lastname],''), [Expr1020]=isnull([Emcee40Mno].[dbo].[members].[firstname] as [m].[firstname],'')))
| | | |--Index Scan(OBJECT:([Emcee40Mno].[dbo].[members].[MembersNameBithdate] AS [m]))
| | |--Index Seek(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans].[healthplanid] AS [bp]), SEEK:([bp].[HealthPlanID]=[Exchange].[dbo].[EDS].[Healthplanid] as [S].[Healthplanid]) ORDERED FORWARD)
| |--RID Lookup(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans] AS [bp]), SEEK:([Bmk1007]=[Bmk1007]) LOOKUP ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Emcee40Mno].[dbo].[eligibilityhistory].[elighistidx] AS [e]), SEEK:([e].[healthplanmemberid]=[Exchange].[dbo].[EDS].[HealthplanMemberid] as [S].[HealthplanMemberid] AND [e].[familyext]=[Exchange].[dbo].[EDS].[FamilyExt] as [S].[FamilyExt]), WHERE:([Emcee40Mno].[dbo].[eligibilityhistory].[benefitplanid] as [e].[benefitplanid]=[Emcee40Mno].[dbo].[BenefitPlans].[BenefitPlanID] as [bp].[BenefitPlanID] AND [Emcee40Mno].[dbo].[eligibilityhistory].[memberid] as [e].[memberid]=[Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid] AND [Emcee40Mno].[dbo].[eligibilityhistory].[invalidhistory] as [e].[invalidhistory]=(0)) ORDERED FORWARD)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-21 : 05:05:32

Umm...
Maybe the optimizer is having problems with the t-sql style UPDATE.
Try the following ANSI style UPDATEs. Perhaps your data will never return more than one m.memberid so the GROUP BYs can be removed.

UPDATE EDS
SET memberid = (
        SELECT MAX(m.memberid)
        FROM Emcee40Mno.dbo.members m
            JOIN Emcee40Mno.dbo.eligibilityhistory e
                ON e.memberid = m.memberid
                    AND e.InvalidHistory = 0
            JOIN Emcee40Mno.dbo.benefitplans bp
                ON e.benefitplanid = bp.benefitplanid 
        WHERE COALESCE(EDS.member_LastName,'') = COALESCE(m.lastname,'')
            AND COALESCE(EDS.member_FirstName,'') = COALESCE(m.firstname,'')
            AND EDS.member_birthdate = m.birthdate
            AND EDS.healthplanmemberid = e.healthplanmemberid
            AND EDS.FamilyExt = e.FamilyExt
            AND EDS.healthplanid = bp.healthplanid
        GROUP BY
            COALESCE(m.lastname,'')
            ,COALESCE(m.firstname,'')
            ,m.birthdate
            ,e.healthplanmemberid
            ,e.FamilyExt
            ,bp.healthplanid
    )
WHERE ExceptionNbr IS NULL
    AND memberid IS NULL

UPDATE EDS
SET memberid = (
        SELECT MAX(m.memberid)
        FROM Emcee40Mno.dbo.members m
        WHERE COALESCE(EDS.member_LastName,'') = COALESCE(m.lastname,'')
            AND COALESCE(EDS.member_FirstName,'') = COALESCE(m.firstname,'')
            AND EDS.member_birthdate = m.birthdate
            AND EXISTS (
                    SELECT *
                    FROM Emcee40Mno.dbo.eligibilityhistory e
                    WHERE e.memberid = m.memberid
                        AND e.InvalidHistory = 0
                        AND EDS.healthplanmemberid = e.healthplanmemberid
                        AND EDS.FamilyExt = e.FamilyExt
                        AND EXISTS (
                                SELECT *
                                FROM Emcee40Mno.dbo.benefitplans bp
                                WHERE e.benefitplanid = bp.benefitplanid
                                    AND EDS.healthplanid = bp.healthplanid
                            )
                )
        GROUP BY
            COALESCE(m.lastname,'')
            ,COALESCE(m.firstname,'')
            ,m.birthdate
    )
WHERE ExceptionNbr IS NULL
    AND memberid IS NULL

iCodehead
Starting Member

3 Posts

Posted - 2007-06-21 : 13:53:42

IFor:
Thanks for your reply.
Your sub-queries do the job(See plan below). They move the Stream Aggregate so the queries ran more efficiently but unfortunately do not get rid of the Stream Aggregate.
I eventually came up with a similar sub-query (after realizing my first attempt was complete crap) but did not have the MAX() function since all the multiple memberids where duplicates. This got rid of the second Stream Aggregate. To be safe, however, I like your idea better using the MAX() function and got rid of the GROUP BY.

Clearly the Query planner is not calculating something correctly because the most efficient plan is the one it produces when the "invalidhistory = 0" condition is removed which is the same query plan produces by Sql server 2000. Below is what the query plan should be when the "invalidhistory = 0" condition exists. Adding this condition should no way cause the query plan to do something completely different and totally inefficient.

After digging deeper I have found that many developers are have issues with the SQL Server 2005 query optimizer producing inefficient (incorrect) query plans. There is finally hope that Microsoft is responding to reported issues regarding the query optimizer because they have released a hot fix to address problems with the index strategy code. I'm not very hopeful this will correct my issue. I am still waiting for there reply with the hot fix and will reply back if my issue is corrected.

[FIX: The SQL Server 2005 query optimizer may incorrectly estimate the cardinality for a query that has a predicate that contains an index union alternative][/http://support.microsoft.com/kb/920347]

This is what the query planner should produce when the "invalidhistory = 0" condition exists
-----------------------------------------------------------
|--Clustered Index Update(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), SET:([Exchange].[dbo].[EDS].[MemberID] = [Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Exchange].[dbo].[EDS].[EDS_primarykey] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Exchange].[dbo].[EDS].[Healthplanid], [e].[benefitplanid]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Exchange].[dbo].[EDS].[HealthplanMemberid], [Exchange].[dbo].[EDS].[FamilyExt], [m].[memberid]) OPTIMIZED)
| |--Hash Match(Inner Join, HASH:([Expr1017], [Expr1019], [Expr1021])=([Expr1018], [Expr1020], [m].[birthdate]), RESIDUAL:([Expr1017]=[Expr1018] AND [Expr1019]=[Expr1020] AND [Expr1021]=[Emcee40Mno].[dbo].[members].[birthdate] as [m].[birthdate]))
| | |--Compute Scalar(DEFINE:([Expr1017]=isnull([Exchange].[dbo].[EDS].[member_LastName],''), [Expr1019]=isnull([Exchange].[dbo].[EDS].[member_FirstName],''), [Expr1021]=CONVERT_IMPLICIT(datetime,[Exchange].[dbo].[EDS].[member_BirthDate],0)))
| | | |--Clustered Index Scan(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), WHERE:([Exchange].[dbo].[EDS].[ExceptionNbr] IS NULL AND [Exchange].[dbo].[EDS].[MemberID] IS NULL) ORDERED)
| | |--Compute Scalar(DEFINE:([Expr1018]=isnull([Emcee40Mno].[dbo].[members].[lastname] as [m].[lastname],''), [Expr1020]=isnull([Emcee40Mno].[dbo].[members].[firstname] as [m].[firstname],'')))
| | |--Index Scan(OBJECT:([Emcee40Mno].[dbo].[members].[MembersNameBithdate] AS [m]))
| |--Clustered Index Seek(OBJECT:([Emcee40Mno].[dbo].[eligibilityhistory].[elighistidx] AS [e]), SEEK:([e].[healthplanmemberid]=[Exchange].[dbo].[EDS].[HealthplanMemberid] AND [e].[familyext]=[Exchange].[dbo].[EDS].[FamilyExt]), WHERE:([Emcee40Mno].[dbo].[eligibilityhistory].[memberid] as [e].[memberid]=[Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid] AND [Emcee40Mno].[dbo].[eligibilityhistory].[memberid] as [e].[invalidhistory]=0) ORDERED FORWARD)
|--Index Seek(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans].[benplhealthID] AS [bp]), SEEK:([bp].[BenefitPlanID]=[Emcee40Mno].[dbo].[eligibilityhistory].[benefitplanid] as [e].[benefitplanid] AND [bp].[HealthPlanID]=[Exchange].[dbo].[EDS].[Healthplanid]) ORDERED FORWARD)

Ifor's sub-query plan
-----------------------------------------------------------
UPDATE EDS
SET memberid = (
SELECT MAX(m.memberid)
FROM Emcee40Mno.dbo.members m
JOIN Emcee40Mno.dbo.eligibilityhistory e
ON e.memberid = m.memberid
AND e.InvalidHistory = 0
JOIN Emcee40Mno.dbo.benefitplans bp
ON e.benefitplanid = bp.benefitplanid
WHERE COALESCE(EDS.member_LastName,'') = COALESCE(m.lastname,'')
AND COALESCE(EDS.member_FirstName,'') = COALESCE(m.firstname,'')
AND EDS.member_birthdate = m.birthdate
AND EDS.healthplanmemberid = e.healthplanmemberid
AND EDS.FamilyExt = e.FamilyExt
AND EDS.healthplanid = bp.healthplanid
GROUP BY
COALESCE(m.lastname,'')
,COALESCE(m.firstname,'')
,m.birthdate
,e.healthplanmemberid
,e.FamilyExt
,bp.healthplanid
)
WHERE ExceptionNbr IS NULL
AND memberid IS NULL

|--Clustered Index Update(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), SET:([Exchange].[dbo].[EDS].[MemberID] = [Expr1021]))
|--Compute Scalar(DEFINE:([Expr1021]=[Expr1021]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([Exchange].[dbo].[EDS].[member_LastName], [Exchange].[dbo].[EDS].[member_FirstName], [Exchange].[dbo].[EDS].[member_BirthDate], [Exchange].[dbo].[EDS].[HealthplanMemberid], [Exchange].[dbo].[EDS].[FamilyExt], [Exchange].[dbo].[EDS].[Healthplanid]))
|--Top(ROWCOUNT est 0)
| |--Clustered Index Scan(OBJECT:([Exchange].[dbo].[EDS].[PK__EDS__379037E3]), WHERE:([Exchange].[dbo].[EDS].[ExceptionNbr] IS NULL AND [Exchange].[dbo].[EDS].[MemberID] IS NULL) ORDERED FORWARD)
|--Assert(WHERE:(CASE WHEN [Expr1020]>(1) THEN (0) ELSE NULL END))
|--Stream Aggregate(DEFINE:([Expr1020]=Count(*), [Expr1021]=ANY([Expr1013])))
|--Stream Aggregate(DEFINE:([Expr1013]=MAX([Emcee40Mno].[dbo].[members].[memberid] as [m].[memberid])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[benefitplanid]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[memberid]) OPTIMIZED)
| |--Clustered Index Seek(OBJECT:([Emcee40Mno].[dbo].[eligibilityhistory].[elighistidx] AS [e]), SEEK:([e].[healthplanmemberid]=[Exchange].[dbo].[EDS].[HealthplanMemberid] AND [e].[familyext]=[Exchange].[dbo].[EDS].[FamilyExt]), WHERE:([Emcee40Mno].[dbo].[eligibilityhistory].[invalidhistory] as [e].[invalidhistory]=(0)) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([Emcee40Mno].[dbo].[members].[PK_members_1__10] AS [m]), SEEK:([m].[memberid]=[Emcee40Mno].[dbo].[eligibilityhistory].[memberid] as [e].[memberid]), WHERE:(CONVERT_IMPLICIT(datetime,[Exchange].[dbo].[EDS].[member_BirthDate],0)=[Emcee40Mno].[dbo].[members].[birthdate] as [m].[birthdate] AND CASE WHEN [Exchange].[dbo].[EDS].[member_LastName] IS NOT NULL THEN [Exchange].[dbo].[EDS].[member_LastName] ELSE '' END=CASE WHEN [Emcee40Mno].[dbo].[members].[lastname] as [m].[lastname] IS NOT NULL THEN [Emcee40Mno].[dbo].[members].[lastname] as [m].[lastname] ELSE '' END AND CASE WHEN [Exchange].[dbo].[EDS].[member_FirstName] IS NOT NULL THEN [Exchange].[dbo].[EDS].[member_FirstName] ELSE '' END=CASE WHEN [Emcee40Mno].[dbo].[members].[firstname] as [m].[firstname] IS NOT NULL THEN [Emcee40Mno].[dbo].[members].[firstname] as [m].[firstname] ELSE '' END) ORDERED FORWARD)
|--Index Seek(OBJECT:([Emcee40Mno].[dbo].[BenefitPlans].[benplhealthID] AS [bp]), SEEK:([bp].[BenefitPlanID]=[Emcee40Mno].[dbo].[eligibilityhistory].[benefitplanid] as [e].[benefitplanid] AND [bp].[HealthPlanID]=[Exchange].[dbo].[EDS].[Healthplanid]) ORDERED FORWARD)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-22 : 05:38:38

iCodehead:
Thanks for detailed response.
Despite there being a plan to move all our dbs to 2005 by the end of the year, we still do not have a decent machine to test SQL2005 on. Your feedback is much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 05:44:15

quote:
Originally posted by Kristen

Which one do you use khtan?

Ultraedit.

I also change to auto add {code} and {/code} tag

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources