SQL Server 2000 Query Slowing Down In SQL Server 2005

Hi everybody,

I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.

Here is the sample:

Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau,
IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' +
ls.currentLevelHIstoric.NoNiveau + ')' As Nom,
ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut,
ls.EmployeNiveau.assignmentReason As AssignmentReason,
ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin,
ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID,
'' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal,
ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id,
ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg,
IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau
+ ')' As NomSup,
ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco +
ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope,
case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as
ContractCategory
From ls.currentEmployeeLevelHistoric
Inner Join ls.EmployeNiveau on
ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID
Inner join ls.currentLevelHistoric On
ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux
Left Outer Join ls.TraductionDefaut On
ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction
Left Outer Join ls.Traduction On
ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and
ls.Traduction.Langue = 1
Left Join ls.MandatsEx on
ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID
Left Join ls.mandatExternalisation on
ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx
left Join ls.Niveaux as NiveauPere on
NiveauPere.niveauID = ls.currentLevelHIstoric.supId
Left Outer Join ls.TraductionDefaut As TradDefautPere On
NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction
Left Outer Join ls.Traduction As TradPere On
TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and
TradPere.Langue = 1
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar,
ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and
(ls.currentEmployeeLevelHistoric.FinAssign is null or
convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))


Thank you

Steve Gadoury