SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Transact-SQL

Sascha05


Hi!

I have to migrate an Application from SQL2000 on a SBS2000 to a SQL2005 on a SBS2003-Server (german version) (SP2 for SQL2005, all patches installed)

I made an backup of the SQL2000-database and imported in SQL 2005. Everything looks fine, but some things are look quite weird:

The application runs following SQL-Statement against the Server: (I verified this with the SQLprofiler, and tested it in Microsoft SQL Server Management Studio (SQL2005) and SQL Query Analyzer (SQL2000):

Code Snippet

INSERT INTO Interessent_abgleich_IAB (IdINT_IAB, IdFIR_IAB, IdFAA_IAB, DELETE_IAB, IdMAR_IAB, Datum_IAB)

SELECT

Interessent_INT.Id_INT,

Interessent_INT.IdFIR_INT,

Firma_adress_partner.Id_FAA,

1,

'AM',

CONVERT(DATETIME,'04.24.2007 09:48:23 ', 102)

FROM Interessent_INT LEFT OUTER JOIN Firma_adress_partner ON Interessent_INT.IdFIR_INT = Firma_adress_partner.IdFIR_FAD

WHERE Id_INT =4138

In SQL2000 the Statements takes about 1 sec.

In SQL2005 I get a timeout in my application. So I run this statement from the Management Console against the SQL2005 Server - I brake it up after more than a few minutes.

I split up each SQL Statement in:

Code Snippet

select *

from Interessent_INT

where Id_INT = 4138

select *

from Firma_adress_partner

where Firma_adress_partner.IdFIR_FAD = 10415

Both Statements returns excatly one rowset within 2 secones - on both SQL-Servers. Indices seems to be ok.

So I'm not sure what to do

Thanks

Sascha

Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

hunchback


Did you update statistics in all tables also

AMB



Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Sascha05

Hi,

Yes, I did. I run

Code Snippet

sp_updatestats

and also

Code Snippet

USE xxx

GO

update statistics dbo.Firma_adresse_FAD with fullscan, all;

update statistics dbo.Katalog_einfach_KTE with fullscan, all;

update statistics dbo.Interessent_INT with fullscan, all;

GO

The query takes about 2 seconds on the sql2000 and 6 minutes on the sql2005!

Firma_adress_partner is a (complicated) view, which depends on other views over a few levels; within this views some "subviews" are used more than one time.

I assume it depends on the optimizer of sql2005. Since the company, which has developed this application, does not exists anymore, I have inherited this ingrate project....

Does anybody know if there is perhaps any possibility to tell the optimizer to exeute it in the same way sql2000 does I would be glad to solve this without rewriting the query...

Thanks to all!

Sascha



Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Chris Howarth

You should also run DBCC UPDATEUSAGE in the database, post-upgrade.

Chris





Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Sascha05

HI,

thanks for the tipp. So I run these statements against SQL2005:

Code Snippet

USE xxxx

GO

DBCC UPDATEUSAGE(0)

GO

USE xxxx

GO

DBCC CHECKDB WITH DATA_PURITY

GO

USE xxxx

GO

DBCC CHECKDB

GO

Everything fine, no error message. But the query still takes about 6 min!

Thanks

Sascha


Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Chris Howarth

Could you run the code below and post the results back to the forum

It would also be good if you could run the same code against a copy of your database attached to a SQL Server 2000 instance and again post the results back for comparison.

Chris

Code Snippet

SET SHOWPLAN_TEXT ON

GO

INSERT INTO Interessent_abgleich_IAB

(

IdINT_IAB,

IdFIR_IAB,

IdFAA_IAB,

DELETE_IAB,

IdMAR_IAB,

Datum_IAB

)

SELECT Interessent_INT.Id_INT,

Interessent_INT.IdFIR_INT,

Firma_adress_partner.Id_FAA,

1,

'AM',

CONVERT(DATETIME, '04.24.2007 09:48:23 ', 102)

FROM Interessent_INT

LEFT OUTER JOIN Firma_adress_partner ON Interessent_INT.IdFIR_INT = Firma_adress_partner.IdFIR_FAD

WHERE Id_INT = 4138

GO





Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Madhu K Nair

After migration of database from sql server 2000 to SQL server 2005

(a) Run DBCC DBReindex for each table

(b) Update statistics

and see the result

Madhu





Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Madhu K Nair

Also recompile the stored procedure (if this script is in a sp), it may be because of parameter sniffing

Madhu





Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Sascha05

Hi,

the execution plan is different for both servers.

SQL 2000:

Code Snippet

|--Assert(WHERE:(If (NOT([Pass1118]) AND ([Expr1117] IS NULL)) then 0 else If (NOT([Pass1120]) AND ([Expr1119] IS NULL)) then 1 else If (NOT([Pass1122]) AND ([Expr1121] IS NULL)) then 2 else NULL))
|--Nested Loops(Left Semi Join, WHERE:('AM' IS NULL)OUTER REFERENCES:('AM'), DEFINE:([Expr1121] = [PROBE VALUE]))
|--Nested Loops(Left Semi Join, WHERE:([Interessent_INT].[IdFIR_INT] IS NULL)OUTER REFERENCES:([Interessent_INT].[IdFIR_INT]), DEFINE:([Expr1119] = [PROBE VALUE]))
| |--Nested Loops(Left Semi Join, WHERE:([Interessent_INT].[Id_INT] IS NULL)OUTER REFERENCES:([Interessent_INT].[Id_INT]), DEFINE:([Expr1117] = [PROBE VALUE]))
| | |--Clustered Index Insert(OBJECT:([o3000].[dbo].[Interessent_abgleich_IAB].[PK_Interessent_abgleich_IAB]), SET:([Interessent_abgleich_IAB].[IdFAD_IAB]=NULL, [Interessent_abgleich_IAB].[K2Dat_IAB]=NULL, [Interessent_abgleich_IAB].[K2OK_IAB]=NULL, [Interessent_abgleich_IAB].[Art_IAB]=NULL, [Interessent_abgleich_IAB].[Datum_Fertig_IAB]=NULL, [Interessent_abgleich_IAB].[email_IAB]=NULL, [Interessent_abgleich_IAB].[Kontrolle_IAB]=NULL, [Interessent_abgleich_IAB].[Mappe_IAB]=NULL, [Interessent_abgleich_IAB].[Fertig_IAB]=NULL, [Interessent_abgleich_IAB].[DELETE_IAB]=1, [Interessent_abgleich_IAB].[Gedruckt_IAB]=NULL, [Interessent_abgleich_IAB].[Work_IAB]=NULL, [Interessent_abgleich_IAB].[FAX_IAB]=NULL, [Interessent_abgleich_IAB].[Besichtigt_IAB]=NULL, [Interessent_abgleich_IAB].[IdFAA_IAB]=[Union1085], [Interessent_abgleich_IAB].[Datum_IAB]='Apr 24 2007 9:48AM', [Interessent_abgleich_IAB].[IdFIR_IAB]=[Interessent_INT].[IdFIR_INT], [Interessent_abgleich_IAB].[IdINT_IAB]=[Interessent_INT].[Id_INT], [Interessent_abgleich_IAB].[Id_IAB]=RaiseIfNull([Expr1108]), [Interessent_abgleich_IAB].[Name_IAB]=NULL, [Interessent_abgleich_IAB].[IdMAR_IAB]='AM', [Interessent_abgleich_IAB].[Abgleich_doc_IAB]=NULL, [Interessent_abgleich_IAB].[K2MA_IAB]=NULL, [Interessent_abgleich_IAB].[IdFAX_IAB]=NULL))
| | | |--Top(ROWCOUNT est 0)
| | | |--Compute Scalar(DEFINE:([Expr1108]=getidentity(662293419, 8, NULL)))
| | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Interessent_INT].[IdFIR_INT]))
| | | |--Clustered Index Seek(OBJECT:([o3000].[dbo].[Interessent_INT].[PK_Interessent_INT]), SEEK:([Interessent_INT].[Id_INT]=4138) ORDERED FORWARD)
| | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Firma_adresse_FAD].[Id_FAD]))
| | | |--Hash Match(Aggregate, HASH:([Expr1104], [Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Expr1104]=[Expr1104] AND [Firma_adresse_FAD].[Id_FAD]=[Firma_adresse_FAD].[Id_FAD]))
| | | | |--Compute Scalar(DEFINE:([Expr1104]=isnull([Katalog_einfach_KTE].[Bezeichnung_KTE]+', ', '')+isnull(Convert([Firma_adresse_FAD].[Strasse_Nr_FAD]), '')))
| | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Firma_adresse_FAD].[IdKTE_strasse_FAD]) WITH PREFETCH)
| | | | |--Sort(DISTINCT ORDER BY:([Firma_adresse_FAD].[Id_FAD] ASC, [Union1103] ASC))
| | | | | |--Concatenation
| | | | | |--Hash Match(Inner Join, HASH:([Firma_adresse_FAD].[Id_FAD])=([Firma_adresse_FAD].[Id_FAD]))
| | | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1088]), OBJECT:([o3000].[dbo].[Firma_adresse_FAD]))
| | | | | | | |--Index Seek(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]), SEEK:([Firma_adresse_FAD].[IdFIR_FAD]=[Interessent_INT].[IdFIR_INT]) ORDERED FORWARD)
| | | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Haupt_FAD])=1 AND (Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL)))
| | | | | |--Hash Match(Left Anti Semi Join, HASH:([Firma_adresse_FAD].[IdFIR_FAD])=([Firma_adresse_FAD].[IdFIR_FAD]))
| | | | | |--Hash Match(Inner Join, HASH:([Firma_adresse_FAD].[Id_FAD])=([Expr1023]), RESIDUAL:([Firma_adresse_FAD].[Id_FAD]=[Expr1023]))
| | | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1088]), OBJECT:([o3000].[dbo].[Firma_adresse_FAD]))
| | | | | | | |--Index Seek(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]), SEEK:([Firma_adresse_FAD].[IdFIR_FAD]=[Interessent_INT].[IdFIR_INT]) ORDERED FORWARD)
| | | | | | |--Stream Aggregate(GROUP BY:([Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1023]=MIN([Firma_adresse_FAD].[Id_FAD])))
| | | | | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC, [Firma_adresse_FAD].[Haupt_FAD] DESC, [Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL))
| | | | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC, [Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Haupt_FAD])=1 AND (Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL)))
| | | | |--Clustered Index Seek(OBJECT:([o3000].[dbo].[Katalog_einfach_KTE].[PK_Katalog_einfach_KTE]), SEEK:([Katalog_einfach_KTE].[Id_KTE]=[Firma_adresse_FAD].[IdKTE_strasse_FAD]) ORDERED FORWARD)
| | | |--Table Spool
| | | |--Sort(DISTINCT ORDER BY:([Union1085] ASC, [Union1086] ASC, [Union1087] ASC))
| | | |--Concatenation
| | | |--Filter(WHERE:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[Firma_adresse_FAD].[Id_FAD]))
| | | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC))
| | | | |--Hash Match(Aggregate, HASH:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA], [Firma_adresse_FAD].[IdFIR_FAD]), RESIDUAL:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA] AND [Firma_adresse_FAD].[IdFIR_FAD]=[Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1043]=MIN([Firma_adr_ansprechpartner_FAA].[Id_FAA])))
| | | | |--Hash Match(Inner Join, HASH:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA])=([Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[Firma_adresse_FAD].[Id_FAD]))
| | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adr_ansprechpartner_FAA].[PK_Firma_adr_ansprechpartner_FAA]), WHERE:(Convert([Firma_adr_ansprechpartner_FAA].[Einkauf_FAA])=1))
| | | | |--Hash Match(Inner Join, HASH:([Union1040])=([Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Union1040]=[Firma_adresse_FAD].[Id_FAD]))
| | | | |--Merge Join(Union)
| | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Haupt_FAD])=1 AND (Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL)) ORDERED FORWARD)
| | | | | |--Sort(ORDER BY:([Expr1017] ASC, [Firma_adresse_FAD].[IdFIR_FAD] ASC))
| | | | | |--Hash Match(Right Anti Semi Join, HASH:([Firma_adresse_FAD].[IdFIR_FAD])=([Firma_adresse_FAD].[IdFIR_FAD]))
| | | | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC, [Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Haupt_FAD])=1 AND (Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL)))
| | | | | |--Stream Aggregate(GROUP BY:([Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1017]=MIN([Firma_adresse_FAD].[Id_FAD])))
| | | | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC, [Firma_adresse_FAD].[Haupt_FAD] DESC, [Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL))
| | | | |--Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]))
| | | |--Filter(WHERE:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[Firma_adresse_FAD].[Id_FAD]))
| | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC))
| | | |--Hash Match(Aggregate, HASH:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA], [Firma_adresse_FAD].[IdFIR_FAD]), RESIDUAL:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA] AND [Firma_adresse_FAD].[IdFIR_FAD]=[Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1082]=MIN([Firma_adr_ansprechpartner_FAA].[Id_FAA])))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Firma_adresse_FAD].[Id_FAD]) WITH PREFETCH)
| | | |--Hash Match(Right Anti Semi Join, HASH:([Firma_adresse_FAD].[IdFIR_FAD])=([Firma_adresse_FAD].[IdFIR_FAD]))
| | | | |--Hash Match(Aggregate, HASH:([Firma_adresse_FAD].[IdFIR_FAD]))
| | | | | |--Hash Match(Inner Join, HASH:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA])=([Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[Firma_adresse_FAD].[Id_FAD]))
| | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adr_ansprechpartner_FAA].[PK_Firma_adr_ansprechpartner_FAA]), WHERE:(Convert([Firma_adr_ansprechpartner_FAA].[Einkauf_FAA])=1))
| | | | | |--Hash Match(Inner Join, HASH:([Union1079])=([Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Union1079]=[Firma_adresse_FAD].[Id_FAD]))
| | | | | |--Merge Join(Union)
| | | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Haupt_FAD])=1 AND (Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL)) ORDERED FORWARD)
| | | | | | |--Sort(ORDER BY:([Expr1005] ASC, [Firma_adresse_FAD].[IdFIR_FAD] ASC))
| | | | | | |--Hash Match(Right Anti Semi Join, HASH:([Firma_adresse_FAD].[IdFIR_FAD])=([Firma_adresse_FAD].[IdFIR_FAD]))
| | | | | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC, [Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Haupt_FAD])=1 AND (Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL)))
| | | | | | |--Stream Aggregate(GROUP BY:([Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1005]=MIN([Firma_adresse_FAD].[Id_FAD])))
| | | | | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC, [Firma_adresse_FAD].[Haupt_FAD] DESC, [Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL))
| | | | | |--Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]))
| | | | |--Hash Match(Inner Join, HASH:([Union1060])=([Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Union1060]=[Firma_adresse_FAD].[Id_FAD]))
| | | | |--Merge Join(Union)
| | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Haupt_FAD])=1 AND (Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL)) ORDERED FORWARD)
| | | | | |--Sort(ORDER BY:([Expr1011] ASC, [Firma_adresse_FAD].[IdFIR_FAD] ASC))
| | | | | |--Hash Match(Right Anti Semi Join, HASH:([Firma_adresse_FAD].[IdFIR_FAD])=([Firma_adresse_FAD].[IdFIR_FAD]))
| | | | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC, [Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Haupt_FAD])=1 AND (Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL)))
| | | | | |--Stream Aggregate(GROUP BY:([Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1011]=MIN([Firma_adresse_FAD].[Id_FAD])))
| | | | | |--Sort(ORDER BY:([Firma_adresse_FAD].[IdFIR_FAD] ASC, [Firma_adresse_FAD].[Haupt_FAD] DESC, [Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | |--Clustered Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(Convert([Firma_adresse_FAD].[Alte_FAD])=0 OR [Firma_adresse_FAD].[Alte_FAD]=NULL))
| | | | |--Index Scan(OBJECT:([o3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]))
| | | |--Index Seek(OBJECT:([o3000].[dbo].[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]), SEEK:([Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[Firma_adresse_FAD].[Id_FAD]) ORDERED FORWARD)
| | |--Row Count Spool
| | |--Clustered Index Seek(OBJECT:([o3000].[dbo].[Interessent_INT].[PK_Interessent_INT]), SEEK:([Interessent_INT].[Id_INT]=[Interessent_INT].[Id_INT]) ORDERED FORWARD)
| |--Row Count Spool
| |--Clustered Index Seek(OBJECT:([o3000].[dbo].[Firma_FIR].[PK_Firma_FIR]), SEEK:([Firma_FIR].[Id_FIR]=[Interessent_INT].[IdFIR_INT]) ORDERED FORWARD)
|--Row Count Spool
|--Clustered Index Seek(OBJECT:([o3000].[dbo].[Mitarbeiter_MAR].[PK_Mitarbeiter_MAR]), SEEK:([Mitarbeiter_MAR].[Kurzziechen_MAR]='AM') ORDERED FORWARD)

and for sql2005:

Code Snippet

|--Assert(WHERE:(CASE WHEN [Expr1183] IS NULL THEN (0) ELSE CASE WHEN [Expr1184] IS NULL THEN (1) ELSE CASE WHEN [Expr1185] IS NULL THEN (2) ELSE NULL END END END))
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([O3000].[dbo].[Interessent_abgleich_IAB].[IdMAR_IAB]), DEFINE:([Expr1185] = [PROBE VALUE]))
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([O3000].[dbo].[Interessent_abgleich_IAB].[IdFIR_IAB], [Expr1199]) WITH UNORDERED PREFETCH, DEFINE:([Expr1184] = [PROBE VALUE]))
| |--Nested Loops(Left Semi Join, OUTER REFERENCES:([O3000].[dbo].[Interessent_abgleich_IAB].[IdINT_IAB], [Expr1198]) WITH UNORDERED PREFETCH, DEFINE:([Expr1183] = [PROBE VALUE]))
| | |--Clustered Index Insert(OBJECT:([O3000].[dbo].[Interessent_abgleich_IAB].[PK_Interessent_abgleich_IAB]), OBJECT:([O3000].[dbo].[Interessent_abgleich_IAB].[IdINT_IAB]), OBJECT:([O3000].[dbo].[Interessent_abgleich_IAB].[IdFIR_IAB]), OBJECT:([O3000].[dbo].[Interessent_abgleich_IAB].[IdMAR_IAB]), OBJECT:([O3000].[dbo].[Interessent_abgleich_IAB].[IdFAA_IAB]), OBJECT:([O3000].[dbo].[Interessent_abgleich_IAB].[K2MA_IAB]), SET:([O3000].[dbo].[Interessent_abgleich_IAB].[IdINT_IAB] = [O3000].[dbo].[Interessent_INT].[Id_INT],[O3000].[dbo].[Interessent_abgleich_IAB].[IdFIR_IAB] = [O3000].[dbo].[Interessent_INT].[IdFIR_INT],[O3000].[dbo].[Interessent_abgleich_IAB].[IdFAA_IAB] = [Union1110],[O3000].[dbo].[Interessent_abgleich_IAB].[DELETE_IAB] = [Expr1145],[O3000].[dbo].[Interessent_abgleich_IAB].[IdMAR_IAB] = [Expr1146],[O3000].[dbo].[Interessent_abgleich_IAB].[Datum_IAB] = [Expr1147],[O3000].[dbo].[Interessent_abgleich_IAB].[Id_IAB] = [Expr1144],[O3000].[dbo].[Interessent_abgleich_IAB].[Name_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[Abgleich_doc_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[Besichtigt_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[Gedruckt_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[Fertig_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[Kontrolle_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[Datum_Fertig_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[Art_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[Mappe_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[FAX_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[email_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[Work_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[K2OK_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[K2Dat_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[K2MA_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[IdFAD_IAB] = NULL,[O3000].[dbo].[Interessent_abgleich_IAB].[IdFAX_IAB] = NULL) WITH UNORDERED PREFETCH)
| | | |--Compute Scalar(DEFINE:([Expr1145]=(1), [Expr1146]=N'AM', [Expr1147]='2007-04-24 09:48:00.000'))
| | | |--Compute Scalar(DEFINE:([Expr1144]=getidentity((662293419),(9),NULL)))
| | | |--Top(ROWCOUNT est 0)
| | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([O3000].[dbo].[Interessent_INT].[IdFIR_INT]))
| | | |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Interessent_INT].[PK_Interessent_INT]), SEEK:([O3000].[dbo].[Interessent_INT].[Id_INT]=(4138)) ORDERED FORWARD)
| | | |--Hash Match(Right Outer Join, HASH:([Union1111])=([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | |--Sort(DISTINCT ORDER BY:([Union1110] ASC, [Union1111] ASC, [Union1112] ASC))
| | | | |--Concatenation
| | | | |--Stream Aggregate(GROUP BY:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD], [O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]) DEFINE:([Expr1040]=MIN([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Id_FAA])))
| | | | | |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Einkauf_FAA],0)=(1)))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Id_FAA], [Expr1191]) WITH ORDERED PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD], [Expr1190]) WITH ORDERED PREFETCH)
| | | | | | |--Sort(ORDER BY:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD] ASC, [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | | | |--Hash Match(Aggregate, HASH:([Expr1039], [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Expr1039] = [Expr1039] AND [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD] = [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) DEFINE:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]=ANY([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD])))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1039]=isnull([O3000].[dbo].[Katalog_einfach_KTE].[Bezeichnung_KTE]+N', ',N'')+[Expr1164]))
| | | | | | | |--Hash Match(Left Outer Join, HASH:([O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD])=([O3000].[dbo].[Katalog_einfach_KTE].[Id_KTE]), RESIDUAL:([O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD]=[O3000].[dbo].[Katalog_einfach_KTE].[Id_KTE]))
| | | | | | | |--Hash Match(Inner Join, HASH:([Union1034])=([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Union1034]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | | | | | |--Merge Join(Union)
| | | | | | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Haupt_FAD],0)=(1) AND (CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL)) ORDERED FORWARD)
| | | | | | | | | |--Sort(ORDER BY:([Expr1026] ASC, [O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD] ASC))
| | | | | | | | | |--Hash Match(Right Anti Semi Join, HASH:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD])=([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]))
| | | | | | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Haupt_FAD],0)=(1) AND (CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL)))
| | | | | | | | | |--Hash Match(Aggregate, HASH:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1026]=MIN([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD])))
| | | | | | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL))
| | | | | | | | |--Compute Scalar(DEFINE:([Expr1164]=isnull(CONVERT(nvarchar(30),[O3000].[dbo].[Firma_adresse_FAD].[Strasse_Nr_FAD],0),N'')))
| | | | | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]))
| | | | | | | |--Index Scan(OBJECT:([O3000].[dbo].[Katalog_einfach_KTE].[uniq]))
| | | | | | |--Index Seek(OBJECT:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]), SEEK:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[PK_Firma_adr_ansprechpartner_FAA]), SEEK:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Id_FAA]=[O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Id_FAA]) LOOKUP ORDERED FORWARD)
| | | | |--Stream Aggregate(GROUP BY:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD], [O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]) DEFINE:([Expr1109]=MIN([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Id_FAA])))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | |--Sort(ORDER BY:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD] ASC, [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD] ASC))
| | | | | |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]))
| | | | | |--Hash Match(Aggregate, HASH:([Expr1074], [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Expr1074] = [Expr1074] AND [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD] = [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) DEFINE:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]=ANY([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD])))
| | | | | | |--Compute Scalar(DEFINE:([Expr1074]=isnull([O3000].[dbo].[Katalog_einfach_KTE].[Bezeichnung_KTE]+N', ',N'')+[Expr1170]))
| | | | | | |--Hash Match(Left Outer Join, HASH:([O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD])=([O3000].[dbo].[Katalog_einfach_KTE].[Id_KTE]), RESIDUAL:([O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD]=[O3000].[dbo].[Katalog_einfach_KTE].[Id_KTE]))
| | | | | | |--Hash Match(Inner Join, HASH:([Union1069])=([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Union1069]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | | | | |--Merge Join(Union)
| | | | | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Haupt_FAD],0)=(1) AND (CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL)) ORDERED FORWARD)
| | | | | | | | |--Sort(ORDER BY:([Expr1061] ASC, [O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD] ASC))
| | | | | | | | |--Hash Match(Right Anti Semi Join, HASH:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD])=([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]))
| | | | | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Haupt_FAD],0)=(1) AND (CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL)))
| | | | | | | | |--Hash Match(Aggregate, HASH:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1061]=MIN([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD])))
| | | | | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1170]=isnull(CONVERT(nvarchar(30),[O3000].[dbo].[Firma_adresse_FAD].[Strasse_Nr_FAD],0),N'')))
| | | | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]))
| | | | | | |--Index Scan(OBJECT:([O3000].[dbo].[Katalog_einfach_KTE].[uniq]))
| | | | | |--Index Spool(SEEK:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]=[O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]))
| | | | | |--Top(TOP EXPRESSION:((1)))
| | | | | |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Einkauf_FAA],0)=(1)))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Id_FAA], [Expr1196]) WITH UNORDERED PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD], [Expr1195]) WITH UNORDERED PREFETCH)
| | | | | | |--Hash Match(Flow Distinct, HASH:([Expr1108], [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Expr1108] = [Expr1108] AND [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD] = [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1108]=isnull([O3000].[dbo].[Katalog_einfach_KTE].[Bezeichnung_KTE]+N', ',N'')+[Expr1177]))
| | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD], [Expr1194]) WITH UNORDERED PREFETCH)
| | | | | | | |--Hash Match(Inner Join, HASH:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD])=([Union1103]), RESIDUAL:([Union1103]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | | | | | |--Compute Scalar(DEFINE:([Expr1177]=isnull(CONVERT(nvarchar(30),[O3000].[dbo].[Firma_adresse_FAD].[Strasse_Nr_FAD],0),N'')))
| | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | | | | | | |--Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]=[O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]) ORDERED FORWARD)
| | | | | | | | | |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) LOOKUP ORDERED FORWARD)
| | | | | | | | |--Hash Match(Flow Distinct, HASH:([Union1103], [Union1104]), RESIDUAL:([Union1103] = [Union1103] AND [Union1104] = [Union1104]))
| | | | | | | | |--Concatenation
| | | | | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Haupt_FAD],0)=(1) AND (CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL)))
| | | | | | | | |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD], [Expr1193]) WITH UNORDERED PREFETCH)
| | | | | | | | |--Stream Aggregate(GROUP BY:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1095]=MIN([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD])))
| | | | | | | | | |--Filter(WHERE:([Expr1180]=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL))
Compute Scalar(DEFINE:([Expr1180]=CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)))
Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD], [Expr1192]) WITH ORDERED PREFETCH)
|--Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) LOOKUP ORDERED FORWARD)
| | | | | | | | |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Haupt_FAD],0)=(1) AND (CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL)))
| | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]=[O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]) ORDERED FORWARD)
Clustered Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) LOOKUP ORDERED FORWARD)
| | | | | | | |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Katalog_einfach_KTE].[PK_Katalog_einfach_KTE]), SEEK:([O3000].[dbo].[Katalog_einfach_KTE].[Id_KTE]=[O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD]) ORDERED FORWARD)
| | | | | | |--Index Seek(OBJECT:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]), SEEK:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[PK_Firma_adr_ansprechpartner_FAA]), SEEK:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Id_FAA]=[O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[Id_FAA]) LOOKUP ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]), SEEK:([O3000].[dbo].[Firma_adr_ansprechpartner_FAA].[IdFAD_FAA]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) ORDERED FORWARD)
| | | |--Hash Match(Aggregate, HASH:([Expr1143], [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]), RESIDUAL:([Expr1143] = [Expr1143] AND [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD] = [O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | |--Compute Scalar(DEFINE:([Expr1143]=isnull([O3000].[dbo].[Katalog_einfach_KTE].[Bezeichnung_KTE]+N', ',N'')+[Expr1157]))
| | | |--Hash Match(Left Outer Join, HASH:([O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD])=([O3000].[dbo].[Katalog_einfach_KTE].[Id_KTE]), RESIDUAL:([O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD]=[O3000].[dbo].[Katalog_einfach_KTE].[Id_KTE]))
| | | |--Stream Aggregate(GROUP BY:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD], [Union1139]) DEFINE:([O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD]=ANY([O3000].[dbo].[Firma_adresse_FAD].[IdKTE_strasse_FAD]), [Expr1157]=ANY([Expr1157])))
| | | | |--Merge Join(Concatenation)
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | | |--Compute Scalar(DEFINE:([Expr1157]=isnull(CONVERT(nvarchar(30),[O3000].[dbo].[Firma_adresse_FAD].[Strasse_Nr_FAD],0),N'')))
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | | | |--Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]=[O3000].[dbo].[Interessent_INT].[IdFIR_INT]) ORDERED FORWARD)
| | | | | | |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) LOOKUP ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Haupt_FAD],0)=(1) AND (CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL)) ORDERED FORWARD)
| | | | |--Sort(ORDER BY:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD] ASC, [O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD] ASC))
| | | | |--Hash Match(Left Anti Semi Join, HASH:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD])=([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]))
| | | | |--Hash Match(Inner Join, HASH:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD])=([Expr1130]), RESIDUAL:([Expr1130]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | | |--Compute Scalar(DEFINE:([Expr1157]=isnull(CONVERT(nvarchar(30),[O3000].[dbo].[Firma_adresse_FAD].[Strasse_Nr_FAD],0),N'')))
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]))
| | | | | | |--Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]=[O3000].[dbo].[Interessent_INT].[IdFIR_INT]) ORDERED FORWARD)
| | | | | | |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), SEEK:([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]=[O3000].[dbo].[Firma_adresse_FAD].[Id_FAD]) LOOKUP ORDERED FORWARD)
| | | | | |--Hash Match(Aggregate, HASH:([O3000].[dbo].[Firma_adresse_FAD].[IdFIR_FAD]) DEFINE:([Expr1130]=MIN([O3000].[dbo].[Firma_adresse_FAD].[Id_FAD])))
| | | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL))
| | | | |--Clustered Index Scan(OBJECT:([O3000].[dbo].[Firma_adresse_FAD].[PK_Firma_adresse_FAD]), WHERE:(CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Haupt_FAD],0)=(1) AND (CONVERT_IMPLICIT(tinyint,[O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD],0)=(0) OR [O3000].[dbo].[Firma_adresse_FAD].[Alte_FAD] IS NULL)))
| | | |--Index Scan(OBJECT:([O3000].[dbo].[Katalog_einfach_KTE].[uniq]))
| | |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Interessent_INT].[PK_Interessent_INT]), SEEK:([O3000].[dbo].[Interessent_INT].[Id_INT]=[O3000].[dbo].[Interessent_abgleich_IAB].[IdINT_IAB]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([O3000].[dbo].[Firma_FIR].[PK_Firma_FIR]), SEEK:([O3000].[dbo].[Firma_FIR].[Id_FIR]=[O3000].[dbo].[Interessent_abgleich_IAB].[IdFIR_IAB]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([O3000].[dbo].[Mitarbeiter_MAR].[PK_Mitarbeiter_MAR]), SEEK:([O3000].[dbo].[Mitarbeiter_MAR].[Kurzziechen_MAR]=[O3000].[dbo].[Interessent_abgleich_IAB].[IdMAR_IAB]) ORDERED FORWARD)

I hoped there would be "an easy" way to tell the sql2005 to execute in the same way sql2000 does...

Thanks,

Sascha


Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Chris Howarth

Presumably you want to avoid amending the Views, so in the query you initially provided try replacing LEFT OUTER JOIN with LEFT OUTER LOOP JOIN.

SQL Server 2000 is using a Nested Loop join method to process the join where SQL Server 2005 is using a Hash Match - the LOOP join hint will force a Nested Loop. Note that the use of this hint this will probably have an effect on the rest of the execution plan so may or may not improve overall performance - the only way to find out is to try it.

Chris





Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Sascha05

Hi,

-) LEFT OUTER LOOP JOIN didn't work

-) swapping the tables and making a right outer join didn't work either

-) I considered the tipps above and made a statement with all of them:

Code Snippet

USE o3000

GO

DBCC FLUSHPROCINDB(9)

GO

USE o3000

GO

DBCC UPDATEUSAGE(0);

GO

USE o3000

GO

DBCC CHECKDB WITH DATA_PURITY;

GO

USE o3000

GO

DBCC CHECKDB;

GO

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

USE o3000

GO

DBCC DBReindex ('Geschaftsfall_fir_GRI');

DBCC DBReindex ('Objekt_park_hauseigene_OPH');

[... all tables...]

GO

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

USE o3000

GO

sp_updatestats

GO

USE o3000

GO

update statistics Geschaftsfall_fir_GRI with fullscan, all;

update statistics Objekt_park_hauseigene_OPH with fullscan, all;

[.. all tables...]

GO

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

USE o3000

GO

sp_recompile '__top_automatik_ausstatung'

GO

sp_recompile '__top_automatik_zustand'

GO

[... all stored procs...]

The query still takes about 6 min....

I don't know now what to do but rewriting the query




Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Chris Howarth

Would you be able to post the DDL scripts for the Views and Tables (including constraints and indexes) that partake in the query

I'm a bit concerned by the number of table scans taking place and the number of implicit datatype conversions that are occuring on columns in your [Firma_adresse_FAD] table (these conversions are likely to be causing index scans rather than seeks). I appreciate that this implicit conversion occurred in the SQL Server 2000 version of the execution plan, however if you can eliminate the implicit conversions (and in doing so cause seeks rather than scans) then performance may improve. If you could post the DDL then we can try and identify where and why the conversions are occuring.

Chris





Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Ennor

Sascha,

Please post here the result of this:

select @@version

for your SQL2000 installation. It seems that you don't have MSSQL 2000 SP4 installed.


Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Sascha05

HI,

the SQL2000 Server has SP4:

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I would have been glad, if it were not necessary to post the sql-scripts... The view is the hell... And as I didn't devloped this application, I tried everything not to rewrite it....

1.) It is not the INSERT Statement, the view itself without the INSERT-Statement is the problem

2.) The first part of the view

Code Snippet

SELECT

Interessent_INT.Id_INT,

Interessent_INT.IdFIR_INT,

Firma_adress_partner.Id_FAA,

1 AS Expr1,

'AM' AS Expr2,

CONVERT(DATETIME,'04.24.2007 09:48:23 ', 102) AS Expr3,

Firma_adress_partner.IdFIR_FAD

FROM Interessent_INT LEFT OUTER LOOP JOIN Firma_adress_partner ON Interessent_INT.IdFIR_INT = Firma_adress_partner.IdFIR_FAD

WHERE Id_INT =4138

(I trie to post this now in a drop-down order)

is the table Interessent_INT:

Code Snippet

/****** Objekt: Tabelle [dbo].[Interessent_INT] Skriptdatum: 03.05.2007 13:22:25 ******/
CREATE TABLE [dbo].[Interessent_INT] (
[Id_INT] [int] IDENTITY (1, 1) NOT NULL ,
[IdFIR_INT] [int] NOT NULL ,
[IdKTE_status_INT] [int] NULL ,
[IdMAR_INT] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Lagebeschreibung_INT] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdKTE_typ_zustand_INT] [int] NULL ,
[IdKTE_typ_lage_INT] [int] NULL ,
[IdFAD_INT] [int] NULL ,
[IdFAA_INT] [int] NULL ,
[IdKTE_akquisitionsart_INT] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Baujahr_von_INT] [int] NULL ,
[Baujahr_bis_INT] [int] NULL ,
[Gesflache_von_INT] [int] NULL ,
[Gesflache_bis_INT] [int] NULL ,
[Park_von_INT] [int] NULL ,
[Park_bis_INT] [int] NULL ,
[Datum_hinzugefugt_INT] [smalldatetime] NULL ,
[IdMAR_charge_INT] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Datum_modif_INT] [smalldatetime] NULL ,
[IdMAR_modif_INT] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Priority_INT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sucher_Int] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Interessent_INT_Sucher_Int] DEFAULT (''),
CONSTRAINT [PK_Interessent_INT] PRIMARY KEY CLUSTERED
(
[Id_INT]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE INDEX [IdFIR_INT] ON [dbo].[Interessent_INT]([IdFIR_INT]) ON [PRIMARY]
GO

CREATE INDEX [IdKTE_status_INT] ON [dbo].[Interessent_INT]([IdKTE_status_INT]) ON [PRIMARY]
GO

CREATE INDEX [IdMAR_INT] ON [dbo].[Interessent_INT]([IdMAR_INT]) ON [PRIMARY]
GO

CREATE INDEX [IdKTE_typ_zustand_INT] ON [dbo].[Interessent_INT]([IdKTE_typ_zustand_INT]) ON [PRIMARY]
GO

CREATE INDEX [IdKTE_typ_lage_INT] ON [dbo].[Interessent_INT]([IdKTE_typ_lage_INT]) ON [PRIMARY]
GO

CREATE INDEX [IdMAR_modif_INT] ON [dbo].[Interessent_INT]([IdMAR_modif_INT]) ON [PRIMARY]
GO

CREATE INDEX [IdFAA_INT] ON [dbo].[Interessent_INT]([IdFAA_INT]) ON [PRIMARY]
GO

CREATE INDEX [IdKTE_akquisitionsart_INT] ON [dbo].[Interessent_INT]([IdKTE_akquisitionsart_INT]) ON [PRIMARY]
GO

CREATE INDEX [IdMAR_charge_INT] ON [dbo].[Interessent_INT]([IdMAR_charge_INT]) ON [PRIMARY]
GO


exec sp_addextendedproperty N'MS_DefaultView', 2, N'user', N'dbo', N'table', N'Interessent_INT'
GO
exec sp_addextendedproperty N'MS_Filter', N'((Interessent_INT.Id_INT=1470))', N'user', N'dbo', N'table', N'Interessent_INT'
GO
exec sp_addextendedproperty N'MS_OrderBy', null, N'user', N'dbo', N'table', N'Interessent_INT'
GO
exec sp_addextendedproperty N'MS_OrderByOn', 0x0, N'user', N'dbo', N'table', N'Interessent_INT'
GO
exec sp_addextendedproperty N'MS_Orientation', 0, N'user', N'dbo', N'table', N'Interessent_INT'
GO
exec sp_addextendedproperty N'MS_TableMaxRecords', 10000, N'user', N'dbo', N'table', N'Interessent_INT'

GO

exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Id_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Id_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdFAA_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdFAA_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdFAA_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_akquisitionsart_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_akquisitionsart_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_akquisitionsart_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'Stored as text in order to be able to input other values than those in the catalogue without adding them to the catalogue', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_akquisitionsart_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Baujahr_von_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Baujahr_von_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Baujahr_von_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'Baujahr from', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Baujahr_von_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Baujahr_bis_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Baujahr_bis_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Baujahr_bis_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'Baujahr to', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Baujahr_bis_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Gesflache_von_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Gesflache_von_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Gesflache_von_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'Total surface from', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Gesflache_von_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Gesflache_bis_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Gesflache_bis_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Gesflache_bis_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'Total surface to', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Gesflache_bis_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Park_von_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Park_von_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Park_von_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'Number of parkings from', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Park_von_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Park_bis_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Park_bis_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Park_bis_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'Number of parkings to', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Park_bis_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Datum_hinzugefugt_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Datum_hinzugefugt_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Datum_hinzugefugt_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_charge_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_charge_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_charge_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Datum_modif_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Datum_modif_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Datum_modif_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_modif_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_modif_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_modif_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'priority', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Priority_INT'
GO
exec sp_addextendedproperty N'MS_DisplayControl', N'109', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Priority_INT'
GO
exec sp_addextendedproperty N'MS_Format', null, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Priority_INT'
GO
exec sp_addextendedproperty N'MS_IMEMode', N'0', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Priority_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Id_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdFIR_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdFIR_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdFIR_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_status_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_status_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_status_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'Betreuer', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdMAR_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Lagebeschreibung_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Lagebeschreibung_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Lagebeschreibung_INT'
GO
exec sp_addextendedproperty N'MS_Description', N'Lagebeschreibung des gesuchten Objektes', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'Lagebeschreibung_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_typ_zustand_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_typ_zustand_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_typ_zustand_INT'
GO
exec sp_addextendedproperty N'MS_ColumnHidden', 0x0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_typ_lage_INT'
GO
exec sp_addextendedproperty N'MS_ColumnOrder', 0, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_typ_lage_INT'
GO
exec sp_addextendedproperty N'MS_ColumnWidth', 65535, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdKTE_typ_lage_INT'
GO
exec sp_addextendedproperty N'MS_DisplayControl', N'109', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdFAD_INT'
GO
exec sp_addextendedproperty N'MS_Format', null, N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdFAD_INT'
GO
exec sp_addextendedproperty N'MS_IMEMode', N'0', N'user', N'dbo', N'table', N'Interessent_INT', N'column', N'IdFAD_INT'


GO


The second part is a view Firma_adress_partner; which depends on FAA_haupt and FIR_haupt_address; now hell starts...


Code Snippet

CREATE VIEW dbo.Firma_adress_partner
AS
SELECT dbo.FIR_haupt_address.IdFIR_FAD, dbo.FIR_haupt_address.Id_FAD, dbo.FAA_haupt.IdFAA AS Id_FAA
FROM dbo.FAA_haupt RIGHT OUTER JOIN
dbo.FIR_haupt_address ON dbo.FAA_haupt.IdFAD_FAA = dbo.FIR_haupt_address.Id_FAD

FAA_haupt_adress is a view, that depends on the table Firma_adresse_FAD, on the view FIR_haupt_address_intermediar and the table Katalog_einfach_KTE:

Code Snippet

CREATE VIEW dbo.FIR_haupt_address
AS
SELECT DISTINCT
dbo.Firma_adresse_FAD.IdFIR_FAD, ISNULL(dbo.Katalog_einfach_KTE.Bezeichnung_KTE + N', ', N'')
+ ISNULL(CAST(dbo.Firma_adresse_FAD.Strasse_Nr_FAD AS nvarchar), N'') AS address, dbo.Firma_adresse_FAD.Id_FAD,
dbo.Firma_adresse_FAD.IdKTE_strasse_FAD, dbo.Firma_adresse_FAD.Strasse_Nr_FAD, dbo.Firma_adresse_FAD.Strasse_Nr_nummer_FAD,
dbo.Firma_adresse_FAD.IdPLZ_FAD, dbo.Firma_adresse_FAD.IdKTE_art_FAD, dbo.Firma_adresse_FAD.Briefkopf_FAD,
dbo.Firma_adresse_FAD.Tel_FAD, dbo.Firma_adresse_FAD.Fax_FAD, dbo.Firma_adresse_FAD.[E-mail_FAD],
dbo.Firma_adresse_FAD.Homepage_FAD, dbo.Firma_adresse_FAD.Haupt_FAD, dbo.Firma_adresse_FAD.Alte_FAD,
dbo.Firma_adresse_FAD.Nutzung_FAD, dbo.Firma_adresse_FAD.Size_FAD, dbo.Firma_adresse_FAD.Mietdauer_von_FAD,
dbo.Firma_adresse_FAD.Mietdauer_bis_FAD, dbo.Firma_adresse_FAD.IdMAR_modif_FAD, dbo.Firma_adresse_FAD.Datum_modif_FAD
FROM dbo.Firma_adresse_FAD INNER JOIN
dbo.FIR_haupt_address_intermediar ON dbo.Firma_adresse_FAD.Id_FAD = dbo.FIR_haupt_address_intermediar.Id_FAd LEFT OUTER JOIN
dbo.Katalog_einfach_KTE ON dbo.Firma_adresse_FAD.IdKTE_strasse_FAD = dbo.Katalog_einfach_KTE.Id_KTE

The table Firma_adresse_FAD:

Code Snippet

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Firma_adr_ansprechpartner_FAA_Firma_adresse_FAD]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Firma_adr_ansprechpartner_FAA] DROP CONSTRAINT FK_Firma_adr_ansprechpartner_FAA_Firma_adresse_FAD
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Geschaftsfall_fir_GRI_Firma_adresse_FAD]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Geschaftsfall_fir_GRI] DROP CONSTRAINT FK_Geschaftsfall_fir_GRI_Firma_adresse_FAD
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Geschaftsfall_fir_GRI_Firma_adresse_FAD1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Geschaftsfall_fir_GRI] DROP CONSTRAINT FK_Geschaftsfall_fir_GRI_Firma_adresse_FAD1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Firma_adresse_FAD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Firma_adresse_FAD]
GO

CREATE TABLE [dbo].[Firma_adresse_FAD] (
[Id_FAD] [int] IDENTITY (1, 1) NOT NULL ,
[IdFIR_FAD] [int] NOT NULL ,
[IdKTE_strasse_FAD] [int] NULL ,
[Strasse_Nr_FAD] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Strasse_Nr_nummer_FAD] [int] NULL ,
[IdPLZ_FAD] [int] NULL ,
[IdKTE_art_FAD] [int] NULL ,
[Briefkopf_FAD] [bit] NULL ,
[Tel_FAD] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax_FAD] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[E-mail_FAD] [nvarchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Homepage_FAD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Haupt_FAD] [bit] NULL ,
[Alte_FAD] [bit] NULL ,
[Nutzung_FAD] [int] NULL ,
[Size_FAD] [int] NULL ,
[Mietdauer_von_FAD] [smalldatetime] NULL ,
[Mietdauer_bis_FAD] [smalldatetime] NULL ,
[IdMAR_modif_FAD] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Datum_modif_FAD] [smalldatetime] NULL ,
[Harold_SID_FAD] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Firma_adresse_FAD] WITH NOCHECK ADD
CONSTRAINT [PK_Firma_adresse_FAD] PRIMARY KEY CLUSTERED
(
[Id_FAD]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Firma_adresse_FAD] ADD
CONSTRAINT [DF_Firma_adresse_FAD_Briefkopf_FAD] DEFAULT (0) FOR [Briefkopf_FAD],
CONSTRAINT [DF_Firma_adresse_FAD_Haupt_FAD] DEFAULT (0) FOR [Haupt_FAD],
CONSTRAINT [DF_Firma_adresse_FAD_Alte_FAD] DEFAULT (0) FOR [Alte_FAD]
GO

CREATE INDEX [IdFIR_FAD] ON [dbo].[Firma_adresse_FAD]([IdFIR_FAD]) ON [PRIMARY]
GO

CREATE INDEX [IdKTE_strasse_FAD] ON [dbo].[Firma_adresse_FAD]([IdKTE_strasse_FAD]) ON [PRIMARY]
GO

CREATE INDEX [IdPLZ_FAD] ON [dbo].[Firma_adresse_FAD]([IdPLZ_FAD]) ON [PRIMARY]
GO

CREATE INDEX [IdKTE_art_FAD] ON [dbo].[Firma_adresse_FAD]([IdKTE_art_FAD]) ON [PRIMARY]
GO

CREATE INDEX [IX_Firma_adresse_FAD] ON [dbo].[Firma_adresse_FAD]([Id_FAD]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Firma_adresse_FAD] ADD
CONSTRAINT [FK_Firma_adresse_FAD_Firma_FIR] FOREIGN KEY
(
[IdFIR_FAD]
) REFERENCES [dbo].[Firma_FIR] (
[Id_FIR]
) ON DELETE CASCADE ,
CONSTRAINT [FK_Firma_adresse_FAD_Katalog_einfach_KTE] FOREIGN KEY
(
[IdKTE_strasse_FAD]
) REFERENCES [dbo].[Katalog_einfach_KTE] (
[Id_KTE]
),
CONSTRAINT [FK_Firma_adresse_FAD_PLZ] FOREIGN KEY
(
[IdPLZ_FAD]
) REFERENCES [dbo].[PLZ] (
[Id_PLZ]
)
GO

The table Katalog_einfach_KTE:

Code Snippet

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Aktivitaten_AKT_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Aktivitaten_AKT] DROP CONSTRAINT FK_Aktivitaten_AKT_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Firma_adr_ansprechpartner_FAA_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Firma_adr_ansprechpartner_FAA] DROP CONSTRAINT FK_Firma_adr_ansprechpartner_FAA_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Firma_adresse_FAD_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Firma_adresse_FAD] DROP CONSTRAINT FK_Firma_adresse_FAD_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Firma_branche_FBR_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Firma_branche_FBR] DROP CONSTRAINT FK_Firma_branche_FBR_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Geschaftsfall_GNR_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Geschaftsfall_GNR] DROP CONSTRAINT FK_Geschaftsfall_GNR_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Geschaftsfall_others_GRO_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Geschaftsfall_others_GRO] DROP CONSTRAINT FK_Geschaftsfall_others_GRO_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Interessent_ausstatung_IAS_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Interessent_ausstatung_IAS] DROP CONSTRAINT FK_Interessent_ausstatung_IAS_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Interessent_kriterien_IKR_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Interessent_kriterien_IKR] DROP CONSTRAINT FK_Interessent_kriterien_IKR_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Interessent_objekttyp_IOT_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Interessent_objekttyp_IOT] DROP CONSTRAINT FK_Interessent_objekttyp_IOT_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Interessent_rechtsform_IRF_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Interessent_rechtsform_IRF] DROP CONSTRAINT FK_Interessent_rechtsform_IRF_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Interessent_topausstatung_ITA_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Interessent_topausstatung_ITA] DROP CONSTRAINT FK_Interessent_topausstatung_ITA_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Interessent_verhandlungsstande_IVS_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Interessent_verhandlungsstande_IVS] DROP CONSTRAINT FK_Interessent_verhandlungsstande_IVS_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_ausstatung_OAS_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_ausstatung_OAS] DROP CONSTRAINT FK_Objekt_ausstatung_OAS_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_bauklasse_OBA_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_bauklasse_OBA] DROP CONSTRAINT FK_Objekt_bauklasse_OBA_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_dokument_ODK_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_dokument_ODK] DROP CONSTRAINT FK_Objekt_dokument_ODK_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_flachenwidmung_OFW_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_flachenwidmung_OFW] DROP CONSTRAINT FK_Objekt_flachenwidmung_OFW_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_OBJ_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_OBJ] DROP CONSTRAINT FK_Objekt_OBJ_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_OBJ_Katalog_einfach_KTE1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_OBJ] DROP CONSTRAINT FK_Objekt_OBJ_Katalog_einfach_KTE1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_OBJ_Katalog_einfach_KTE2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_OBJ] DROP CONSTRAINT FK_Objekt_OBJ_Katalog_einfach_KTE2
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_OBJ_Katalog_einfach_KTE3]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_OBJ] DROP CONSTRAINT FK_Objekt_OBJ_Katalog_einfach_KTE3
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_OBJ_Katalog_einfach_KTE4]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_OBJ] DROP CONSTRAINT FK_Objekt_OBJ_Katalog_einfach_KTE4
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_OBJ_Katalog_einfach_KTE5]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_OBJ] DROP CONSTRAINT FK_Objekt_OBJ_Katalog_einfach_KTE5
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_off_beforderung_OOB_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_off_beforderung_OOB] DROP CONSTRAINT FK_Objekt_off_beforderung_OOB_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Objekt_typ_OTY_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Objekt_typ_OTY] DROP CONSTRAINT FK_Objekt_typ_OTY_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PLZ_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PLZ] DROP CONSTRAINT FK_PLZ_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PLZ_Katalog_einfach_KTE1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PLZ] DROP CONSTRAINT FK_PLZ_Katalog_einfach_KTE1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_ausstatung_TAU_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_ausstatung_TAU] DROP CONSTRAINT FK_Top_ausstatung_TAU_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_betriebskosten_inkl_TBI_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_betriebskosten_inkl_TBI] DROP CONSTRAINT FK_Top_betriebskosten_inkl_TBI_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_dokument_TDK_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_dokument_TDK] DROP CONSTRAINT FK_Top_dokument_TDK_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_mittler_TML_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_mittler_TML] DROP CONSTRAINT FK_Top_mittler_TML_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_nutzung_TNZ_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_nutzung_TNZ] DROP CONSTRAINT FK_Top_nutzung_TNZ_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_rechtsform_TRF_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_rechtsform_TRF] DROP CONSTRAINT FK_Top_rechtsform_TRF_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_TOP_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_TOP] DROP CONSTRAINT FK_Top_TOP_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_TOP_Katalog_einfach_KTE2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_TOP] DROP CONSTRAINT FK_Top_TOP_Katalog_einfach_KTE2
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_TOP_Katalog_einfach_KTE3]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_TOP] DROP CONSTRAINT FK_Top_TOP_Katalog_einfach_KTE3
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_TOP_Katalog_einfach_KTE4]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_TOP] DROP CONSTRAINT FK_Top_TOP_Katalog_einfach_KTE4
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_TOP_Katalog_einfach_KTE5]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_TOP] DROP CONSTRAINT FK_Top_TOP_Katalog_einfach_KTE5
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_TOP_Katalog_einfach_KTE6]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_TOP] DROP CONSTRAINT FK_Top_TOP_Katalog_einfach_KTE6
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_TOP_Katalog_einfach_KTE7]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_TOP] DROP CONSTRAINT FK_Top_TOP_Katalog_einfach_KTE7
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Top_zustand_TPZ_Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Top_zustand_TPZ] DROP CONSTRAINT FK_Top_zustand_TPZ_Katalog_einfach_KTE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Katalog_einfach_KTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Katalog_einfach_KTE]
GO

CREATE TABLE [dbo].[Katalog_einfach_KTE] (
[Id_KTE] [int] IDENTITY (1, 1) NOT NULL ,
[IdKGR_KTE] [int] NULL ,
[Sort_KTE] [int] NULL ,
[Bezeichnung_KTE] [nvarchar] (440) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Alte_cod_KTE] [int] NULL ,
[Alte_cod_txt_KTE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[imlart_KTE] [int] NULL ,
[imlenr_KTE] [int] NULL ,
[Kurzbez_KTE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Value_KTE] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Katalog_einfach_KTE] WITH NOCHECK ADD
CONSTRAINT [PK_Katalog_einfach_KTE] PRIMARY KEY CLUSTERED
(
[Id_KTE]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Katalog_einfach_KTE] ADD
CONSTRAINT [uniq] UNIQUE NONCLUSTERED
(
[IdKGR_KTE],
[Bezeichnung_KTE]
) ON [PRIMARY]
GO

CREATE INDEX [IdKGR_KTE] ON [dbo].[Katalog_einfach_KTE]([IdKGR_KTE]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Katalog_einfach_KTE] ADD
CONSTRAINT [FK_Katalog_einfach_KTE_Katalog_gruppe_KGR] FOREIGN KEY
(
[IdKGR_KTE]
) REFERENCES [dbo].[Katalog_gruppe_KGR] (
[Id_KGR]
)
GO

The view FIR_haupt_address_intermediar depends on the view SELECT_FAD_ALL:

Code Snippet
CREATE VIEW dbo.FIR_haupt_address_intermediar
AS
SELECT Id_FAd, IdFIR_FAD
FROM SELECT_FAD_all
WHERE haupt_fad = 1
UNION
SELECT MIN(Id_FAD) AS Expr1, IdFIR_FAD
FROM dbo.select_FAD_all
GROUP BY IdFIR_FAD
HAVING (Idfir_FAD) <> ALL
(SELECT Idfir_FAd
FROM SELECT_FAD_all
WHERE haupt_fad = 1)

The View SELECT_FAD_ALL depends on the table Firma_adresse_FAD (definitoin above) and on the table Katalog_einfach_KTE (definition above)

Code Snippet
CREATE VIEW dbo.select_FAD_all
AS
SELECT TOP 100 PERCENT dbo.Firma_adresse_FAD.Id_FAD, dbo.Firma_adresse_FAD.Haupt_FAD, dbo.Firma_adresse_FAD.Alte_FAD,
dbo.Firma_adresse_FAD.IdFIR_FAD, ISNULL(dbo.Katalog_einfach_KTE.Bezeichnung_KTE + N', ', '')
+ ISNULL(CAST(dbo.Firma_adresse_FAD.Strasse_Nr_FAD AS nvarchar), '') AS address
FROM dbo.Firma_adresse_FAD LEFT OUTER JOIN
dbo.Katalog_einfach_KTE ON dbo.Firma_adresse_FAD.IdKTE_strasse_FAD = dbo.Katalog_einfach_KTE.Id_KTE
WHERE (dbo.Firma_adresse_FAD.Alte_FAD = 0) OR
(dbo.Firma_adresse_FAD.Alte_FAD IS NULL)
ORDER BY dbo.Firma_adresse_FAD.IdFIR_FAD, dbo.Firma_adresse_FAD.Haupt_FAD DESC, dbo.Firma_adresse_FAD.Id_FAD

FAA_haupt is a view, which depends also on FAA_haupt_adress (definition above) and on the table Firma_adr_ansprechpartner_FAA:


Code Snippet
CREATE VIEW dbo.FAA_haupt
AS
SELECT TOP 100 PERCENT MIN(DISTINCT dbo.Firma_adr_ansprechpartner_FAA.Id_FAA) AS IdFAA, dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA,
dbo.FIR_haupt_address.IdFIR_FAD
FROM dbo.Firma_adr_ansprechpartner_FAA INNER JOIN
dbo.FIR_haupt_address ON dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA = dbo.FIR_haupt_address.Id_FAD
WHERE (dbo.Firma_adr_ansprechpartner_FAA.Einkauf_FAA = 1)
GROUP BY dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA, dbo.FIR_haupt_address.IdFIR_FAD
ORDER BY dbo.FIR_haupt_address.IdFIR_FAD
UNION
SELECT TOP 100 PERCENT MIN(dbo.Firma_adr_ansprechpartner_FAA.Id_FAA) AS IdFAA, dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA,
dbo.FIR_haupt_address.IdFIR_FAD
FROM dbo.Firma_adr_ansprechpartner_FAA INNER JOIN
dbo.FIR_haupt_address ON dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA = dbo.FIR_haupt_address.Id_FAD
WHERE (dbo.FIR_haupt_address.IdFIR_FAD <> ALL
(SELECT dbo.FIR_haupt_address.IdFIR_FAD
FROM dbo.Firma_adr_ansprechpartner_FAA INNER JOIN
dbo.FIR_haupt_address ON dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA = dbo.FIR_haupt_address.Id_FAD
WHERE (dbo.Firma_adr_ansprechpartner_FAA.Einkauf_FAA = 1)))
GROUP BY dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA, dbo.FIR_haupt_address.IdFIR_FAD
ORDER BY FIR_haupt_address.IdFIR_FAD

The table Firma_adr_ansprechpartner_FAA:

Code Snippet

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Firma_adr_ansprechpartner_mailmerge_FMM_Firma_adr_ansprechpartner_FAA]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Firma_adr_ansprechpartner_mailmerge_FMM] DROP CONSTRAINT FK_Firma_adr_ansprechpartner_mailmerge_FMM_Firma_adr_ansprechpartner_FAA
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[del_FAA]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[del_FAA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Firma_adr_ansprechpartner_FAA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Firma_adr_ansprechpartner_FAA]
GO

CREATE TABLE [dbo].[Firma_adr_ansprechpartner_FAA] (
[Id_FAA] [int] IDENTITY (1, 1) NOT NULL ,
[IdFAD_FAA] [int] NULL ,
[Name_FAA] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vorname_FAA] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdKTE_sex_FAA] [int] NULL ,
[IdKTE_titel_FAA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdKTE_titel_lang_FAA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdKTE_anrede_FAA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notiz_FAA] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdMAR_modif_FAA] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Datum_FAA] [smalldatetime] NULL ,
[IdKTE_position_FAA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdKTE_abteil_FAA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Geburtsdate_FAA] [smalldatetime] NULL ,
[IdKTE_geschenk_FAA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdKTE_informant_FAA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdKTE_provision_FAA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdKTE_typ_FAA] [bigint] NULL ,
[Tel_FAA] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ext_FAA] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mobil_FAA] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax_FAA] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[E-mail_FAA] [nvarchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Einkauf_FAA] [bit] NULL ,
[sort_FAA] [int] NULL ,
[IdMAR_added_FAA] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Datum_added_FAA] [smalldatetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Firma_adr_ansprechpartner_FAA] WITH NOCHECK ADD
CONSTRAINT [PK_Firma_adr_ansprechpartner_FAA] PRIMARY KEY CLUSTERED
(
[Id_FAA]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Firma_adr_ansprechpartner_FAA] ADD
CONSTRAINT [DF_Firma_adr_ansprechpartner_FAA_Einkauf_FAA] DEFAULT (0) FOR [Einkauf_FAA]
GO

CREATE INDEX [IdFAD_FAA] ON [dbo].[Firma_adr_ansprechpartner_FAA]([IdFAD_FAA]) ON [PRIMARY]
GO

CREATE INDEX [IdKTE_sex_FAA] ON [dbo].[Firma_adr_ansprechpartner_FAA]([IdKTE_sex_FAA]) ON [PRIMARY]
GO

CREATE INDEX [IdMAR_modif_FAA] ON [dbo].[Firma_adr_ansprechpartner_FAA]([IdMAR_modif_FAA]) ON [PRIMARY]
GO

CREATE INDEX [IdMAR_added_FAA] ON [dbo].[Firma_adr_ansprechpartner_FAA]([IdMAR_added_FAA]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Firma_adr_ansprechpartner_FAA] ADD
CONSTRAINT [FK_Firma_adr_ansprechpartner_FAA_Firma_adresse_FAD] FOREIGN KEY
(
[IdFAD_FAA]
) REFERENCES [dbo].[Firma_adresse_FAD] (
[Id_FAD]
) ON DELETE CASCADE ,
CONSTRAINT [FK_Firma_adr_ansprechpartner_FAA_Katalog_einfach_KTE] FOREIGN KEY
(
[IdKTE_sex_FAA]
) REFERENCES [dbo].[Katalog_einfach_KTE] (
[Id_KTE]
),
CONSTRAINT [FK_Firma_adr_ansprechpartner_FAA_Mitarbeiter_MAR] FOREIGN KEY
(
[IdMAR_modif_FAA]
) REFERENCES [dbo].[Mitarbeiter_MAR] (
[Kurzziechen_MAR]
),
CONSTRAINT [FK_Firma_adr_ansprechpartner_FAA_Mitarbeiter_MAR1] FOREIGN KEY
(
[IdMAR_added_FAA]
) REFERENCES [dbo].[Mitarbeiter_MAR] (
[Kurzziechen_MAR]
)
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER del_FAA ON dbo.Firma_adr_ansprechpartner_FAA
FOR DELETE
AS
DECLARE @id as int
DECLARE IDFAA CURSOR
FOR
SELECT Id_FAA FROM deleted

OPEN IDFAA

FETCH NEXT FROM IDFAA into @id

WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC ('UPDATE Firma_aktivitaten_FAK SET idFAA_FAK = NULL WHERE idFAA_FAK = '+ @id)
EXEC ('UPDATE Interessent_abgleich_IAB SET idFAA_IAB = NULL WHERE idFAA_IAB = '+ @id)
EXEC ('UPDATE Objekt_firma_OBF SET idFAA_OBF = NULL WHERE idFAA_OBF = '+ @id)
EXEC ('UPDATE Top_firma_TOF SET idFAA_TOF = NULL WHERE idFAA_TOF = '+ @id)
EXEC ('UPDATE Interessent_INT SET idFAA_INT = NULL WHERE idFAA_INT = '+ @id)

FETCH NEXT FROM IDFAA into @id
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I scripted these sql-statements from the original database (sql2000). I also tried the sql-optimizer from sql2005 and added the recommended indexes in sql2005; but this didn't work either....

I think, that the main problem is the second part of the union in the view FAA_haupt:

Code Snippet

UNION
SELECT TOP 100 PERCENT MIN(dbo.Firma_adr_ansprechpartner_FAA.Id_FAA) AS IdFAA, dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA,
dbo.FIR_haupt_address.IdFIR_FAD
FROM dbo.Firma_adr_ansprechpartner_FAA INNER JOIN
dbo.FIR_haupt_address ON dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA = dbo.FIR_haupt_address.Id_FAD
WHERE (dbo.FIR_haupt_address.IdFIR_FAD <> ALL
(SELECT dbo.FIR_haupt_address.IdFIR_FAD
FROM dbo.Firma_adr_ansprechpartner_FAA INNER JOIN
dbo.FIR_haupt_address ON dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA = dbo.FIR_haupt_address.Id_FAD
WHERE (dbo.Firma_adr_ansprechpartner_FAA.Einkauf_FAA = 1)))
GROUP BY dbo.Firma_adr_ansprechpartner_FAA.IdFAD_FAA, dbo.FIR_haupt_address.IdFIR_FAD
ORDER BY FIR_haupt_address.IdFIR_FAD

Thanks!

Sascha


Top

Re: SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN

Tom Phillips

Your query amounts too a self referencing join with RIGHT OUTER and LEFT OUTER joins. This similar situation has caused me many problems in 2005.

My only solution has been to rewrite the query to make it two smaller queries or make the clustered index, non-clustered on the PK of the file. For some reason that helps.


I reported this problem to MS months ago, and it still is not fixed in 2005 SP2.

This is my simple duplication script:

Code Snippet



-- Create Test Data Table CustomerListTom and Views

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewATom'))
DROP VIEW [ViewATom]

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewBTom'))
DROP VIEW [ViewBTom]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'CustomerListTom') AND type in (N'U'))
DROP TABLE CustomerListTom
GO

CREATE TABLE [dbo].[CustomerListTom](
[CustomerID] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RegionID] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] NOT NULL,
[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCountry] [dbo].[Name] NOT NULL,
[Phone] [dbo].[Phone] NULL,
[BillAddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCountry] [dbo].[Name] NOT NULL,
[BillPhone] [dbo].[Phone] NULL,
[ModifiedDate] [datetime] NOT NULL,
)

INSERT INTO CustomerListTom
SELECT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,

BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate
--,*
FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID


-- Create a big enough set of data for testing
DECLARE @i INT
SET @i = 1
WHILE (@i < 30)
BEGIN
INSERT INTO CustomerListTom
SELECT TOP 15 PERCENT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID+@i AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,

BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate + CASE WHEN @i > 3 THEN 10 ELSE -25 END + @i

FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID

SET @i = @i + 1
END


-- Cleanup - Delete Dups for PK
DELETE FROM CustomerListTom
WHERE CustomerID+RegionID IN (
SELECT CustomerID+RegionID
FROM CustomerListTom cu
GROUP BY CustomerID, RegionID
HAVING COUNT(*) > 1)

ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)

GO

-- Create Views
GO
CREATE VIEW ViewATom
AS
SELECT *
FROM CustomerListTom cu
WHERE cu.RegionID = '004'
UNION
SELECT *
FROM CustomerListTom cu
WHERE CustomerID NOT IN
(SELECT CustomerID FROM CustomerListTom c2 WHERE c2.RegionID = '004')
AND (CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID IN
(SELECT MAX(CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID)
FROM CustomerListTom
GROUP BY CustomerID))

GO
CREATE VIEW ViewBTom
AS
SELECT DISTINCT CustomerID
FROM CustomerListTom
GO


USE AdventureWorks

-- FAILURE
-- This query FAILS to return in over 15 mins, cancelled
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- SOLUTIONS
-- Change WHERE TABLEB to WHERE TABLEA, this query returns in less than 1 second
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEA.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Remove RIGHT OUTER on TABLEB, this Query returns in less than 2 seconds
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Drop PK and run ORIGINAL query, returns in less than 4 seconds

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO

SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return


-- Create PK with NONCLUSTERED and run ORIGINAL query, returns in less than 1 second

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO
ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY NONCLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)
GO

SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return


Top