Post reply

Query Performance SQL Server 2000 vs SQL Server 2005

  • November 7, 2007 at 7:27 am

    #176744

    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

    #749154

    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

    #749167

    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

    #749257

    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

    #749447

    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

    #750005

    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

    #750304

    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