November 7, 2007 at 7:27 am
Hey,
I have a query, which when executed in SQL Server 2005 runs in sub-second, but when running same query against SQL Server 2000, it takes approximately 14 seconds. I have researched possible issues with SQL Server 2000 optimizer in terms of queries containing Left or Right Outer Joins against views, but after applying hotfix, the execution plans are still differing. I am executing the query on same machine, but with differing execution plans and execution times.
Please help!
Here is the code:
SELECT
sr.SchemeRoleId,
sr.PSRNumber,
sr.RoleTypeReference,
sr.EffectiveDate,
sr.EndDate,
sr.StartEventReference,
dbo.uvw_MostRecent_RoleDetailsContact.EmailAddress,
dbo.uvw_MostRecent_RoleDetailsContact.TelephoneNumber,
dbo.uvw_MostRecent_RoleDetailsAddress.AddressLine1,
dbo.uvw_MostRecent_RoleDetailsAddress.AddressLine2,
dbo.uvw_MostRecent_RoleDetailsAddress.AddressLine3,
dbo.uvw_MostRecent_RoleDetailsAddress.AddressLine4,
dbo.uvw_MostRecent_RoleDetailsAddress.AddressLine5,
dbo.uvw_MostRecent_RoleDetailsAddress.PostCode,
dbo.uvw_MostRecent_Identifiers.Tax_Reference_No,
dbo.uvw_MostRecent_Identifiers.Companies_House_No,
dbo.uvw_MostRecent_Identifiers.Registered_Charity_No,
dbo.uvw_MostRecent_Identifiers.DUNS_No,
dbo.uvw_MostRecent_OrganisationType.OrganisationTypeReference,
dbo.uvw_MostRecent_OrganisationType.OtherOrganisationTypeDescription,
dbo.uvw_MostRecent_OrganisationName.Name,
dbo.uvw_MostRecent_OrganisationTradingStatus.TradingStatusReference,
dbo.uvw_MostRecent_OrganisationTradingStatus.OtherOrganisationTradingStatusDescription,
dbo.uvw_MostRecent_EmployerMembership.NumberOfDBMembers,
dbo.tbl_RoleType.RolePlayerType,
dbo.uvw_MostRecent_SchemeSubRole.RoleSubTypeReference,
dbo.uvw_MostRecent_RoleDetailsAddress.AddressReference,
dbo.uvw_MostRecent_RoleDetailsAddress.CountryId,
dbo.uvw_MostRecent_RoleDetailsContact.ContactReference
FROM dbo.tbl_SchemeRole AS sr LEFT OUTER JOIN
dbo.uvw_MostRecent_SchemeSubRole ON sr.SchemeRoleId = dbo.uvw_MostRecent_SchemeSubRole.SchemeRoleId LEFT OUTER JOIN
dbo.tbl_RoleType ON sr.RoleTypeReference = dbo.tbl_RoleType.RoleTypeReference LEFT OUTER JOIN
dbo.uvw_MostRecent_EmployerMembership ON
sr.OrganisationReference = dbo.uvw_MostRecent_EmployerMembership.OrganisationReference LEFT OUTER JOIN
dbo.uvw_MostRecent_OrganisationTradingStatus ON
sr.OrganisationReference = dbo.uvw_MostRecent_OrganisationTradingStatus.OrganisationReference LEFT OUTER JOIN
dbo.uvw_MostRecent_OrganisationName ON
sr.OrganisationReference = dbo.uvw_MostRecent_OrganisationName.OrganisationReference LEFT OUTER JOIN
dbo.uvw_MostRecent_OrganisationType ON
sr.OrganisationReference = dbo.uvw_MostRecent_OrganisationType.OrganisationReference LEFT OUTER JOIN
dbo.uvw_MostRecent_Identifiers ON sr.OrganisationReference = dbo.uvw_MostRecent_Identifiers.OrganisationReference LEFT OUTER JOIN
dbo.uvw_MostRecent_RoleDetailsContact ON sr.SchemeRoleId = dbo.uvw_MostRecent_RoleDetailsContact.SchemeRoleId LEFT OUTER JOIN
dbo.uvw_MostRecent_RoleDetailsAddress ON sr.SchemeRoleId = dbo.uvw_MostRecent_RoleDetailsAddress.SchemeRoleId
WHERE sr.PSRNumber = 123456 AND (sr.EndDate IS NOT NULL) AND (sr.EndEventReference IS NULL) AND (sr.RoleTypeReference = 5)
The reason for the left outer joins against views is to get latest values, which the views are performing.
Thanks,
Phillip Cox
November 7, 2007 at 9:35 am
Sorry, question is: Has the query optimizer changed that much in terms of building an execution plan. In addition, the SQL Server 2000 run has book-marks, but none exists in the SQL Server 2005 execution plan.
Thanks,
Phillip Cox
November 7, 2007 at 10:17 am
Answering your second question: They renamed bookmarks to joins in 2005, but essensially it's same thing.
About your execution time: did you compare the number of records, availability if indexes, and when statistics was last updated for each table between your 2000 and 2005 instances?
November 7, 2007 at 3:43 pm
Ah, thanks for update regarding bookmarks!
I have restored the same database to each instance, thus everything is exact same and everything is running on same machine. This is why I am so perplexed with differences between execution plans.
Thanks,
Phillip Cox
November 8, 2007 at 6:56 am
one of the crucial changes in sql2005 was the reworking of the optimiser, like for like sql2005 should be faster than sql2000 and in basic testing I did this was the case - however a lot depends on the quality shall we say of the T SQL. I would expect better performance from 2005. Conversely it's usually the other way around where sql 2000 queries run slower on sql 2005!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 9, 2007 at 3:55 pm
1) different hardware?
2) different memory allocation, server settings?
3) fragmented indexes/data/os files?
4) out of date statistics?
5) bad query plan from parameter sniffing?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 12, 2007 at 9:35 am
Thanks all!
With a slight change of plan in code, by changing from a number of "AND" to "OR", the query now runs in sub-second. We were dealing with a number of scenarios where data may be NULL and after speaking with business to understand how application works, it was possible to OR these checks.
Once again, thanks,
Phillip Cox
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply