Post reply

Performance question... TSQL streamlining...

  • October 23, 2003 at 8:42 am

    #84629

    Hi there,

    Just going thru some procs and came across this one:

    CREATE PROCEDURE [SP_GetOneCompletedRecord]

    --This Script will get the account that are completed

    @intMasterAcctID int

    AS

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN

    Select Distinct Propertys.intMasterAcctID from Propertys where intMasterAcctID IN

    (Select Distinct Propertys.intMasterAcctID

    from

    Propertys,PropertyFactors,LandSectionSites,BuildingDescriptions,PriceExtensions

    where

    Propertys.intMasterAcctID = PropertyFactors.intMasterAcctID and

    Propertys.intMasterAcctID = landSectionSites.intMasterAcctID and propertys.intMasterAcctID = BuildingDescriptions.intMasterAcctID

    and Propertys.intMasterAcctID = PriceExtensions.intMasterAcctID

    and BuildingDescriptions.intID

    in(Select Distinct intBuildingID from PriceExtensions where

    PriceExtensions.intMasterAcctID = Propertys.intMasterAcctID and Propertys.intMasterAcctID = PropertyFactors.intMasterAcctID and

    PriceExtensions.intBuildingID = BuildingDescriptions.intID and Propertys.intMasterAcctID = @intMasterAcctID)

    and Propertys.intActive = 1 )

    END

    GO

    In a nutshell, I'm going thru ALOT of procs and seeing where I can cleanup code, make it quicker, etc. Unfortunately I'm not terribly certain of how to go about it.

    Any thoughts or suggestions would be appreicated.

    Regards,

    Christopher Klein

  • October 23, 2003 at 12:45 pm

    #479354

    Wow; is it just me or is that some scary code?

    Just this might be equivalent:

    

    CREATE PROCEDURE USP_GetOneCompletedRecord

    --This Stored Procedure will get the accounts that are completed

    @intMasterAcctID int

    AS

    SET NOCOUNT ON

    SELECT intMasterAcctID

    FROM Propertys r

    WHERE EXISTS

    (SELECT * 

     FROM Propertys p JOIN PropertyFactors f ON p.intMasterAcctID = f.intMasterAcctID

      JOIN LandSectionSites l ON p.intMasterAcctID = l.intMasterAcctID

      JOIN BuildingDescriptions b ON p.intMasterAcctID = b.intMasterAcctID

      JOIN PriceExtensions e ON p.intMasterAcctID = e.intMasterAcctID AND e.intBuildingID = b.intID

     WHERE p.intMasterAcctID = @intMasterAcctID AND p.intActive = 1

      AND p.intMasterAcctID = r.intMasterAcctID)

    I changed the name so it cannot be mistaken for a system stored procedure (which would hurt performance by causing the server to always look in the master database).

    It's one statement, so it's already an atomic transaction, hence the removal of your set option which I replaced with one that can help performance.

    The DISTINCT modifiers in the subqueries do nothing but harm performance, and the outer DISTINCT is made unnecessary by the use of a correlated existence subquery instead of IN. I changed the join syntax and used table aliases, obviously.

    I'm not certain that the innermost subquery can be replaced with just a join and predicate, but I did that too.

    Interesting plural form for property...

    --Jonathan



    --Jonathan

  • October 23, 2003 at 2:42 pm

    #479374

    scary, eh?

    I've inherited some of this code since the 'senior' dba left here and now I'm the 'senior' dba. (oh joy, oh rapture)

    Running your version of the proc vs. the original it goes from 63ms processing time to 13ms (using profiler)

    So in savings comes out to around 18minutes on 17500 records.

    Unfortunately that is just one LITTLE proc in a whole family of procs.

    Thanks for the help.

    Chris

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply