Which Way Is Faster?

  • I'm trying to tweak some of my scripts for increased speed, and I realized there are a few areas that I'm not sure which really is the best way to go. Both methods took ~3 Milliseconds to complete on my system (I ran this several times, and would occasionally have 1 or both take 6 milliseconds, but those were rare variances), but I don't have anything massive to test the scripts against to get a real feel for the different methods. I'm curious if anyone has had the opportunity to test these before.

    You don't have to use the table I created here, but I'm including it so you can see exactly what I'm working with (without HIPPA restricted data being included).

    If Object_ID('dbo.Org_Patient_Info_Sys') Is Not Null

    Drop Table dbo.Org_Patient_Info_Sys

    Go

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

    Create Table dbo.Org_Patient_Info_Sys

    (Monarch_AccountNo Char(1),

    Monarch_Lname Char(1),

    Monarch_Fname Char(1),

    Monarch_MiddleName Char(1),

    Monarch_SSN Char(1),

    Monarch_BirthDate Char(1),

    Monarch_Address1 Char(1),

    Monarch_Address2 Char(1),

    Monarch_City Char(1),

    Monarch_State Char(1),

    Monarch_Zip Char(1),

    ContactName Char(1),

    StreetAddress1 Char(1),

    StreetAddress2 Char(1),

    City2 Char(1),

    State2 Char(1),

    ZipCode2 Char(1),

    DeathDate Char(1),

    Monarch_AdmitDate Char(1),

    Monarch_DischargeDate Char(1),

    Sheet_Name Char(1),

    Monarch_InsCode Char(1),

    Monarch_InsName Char(1),

    Monarch_InsPolicy Char(1),

    Monarch_InsCode2 Char(1),

    Monarch_InsName2 Char(1),

    Monarch_InsPolicy2 Char(1),

    Monarch_InsCode3 Char(1),

    Monarch_InsName3 Char(1),

    Monarch_InsPolicy3 Char(1),

    HPS_ID Char(1),

    Monarch_PatientType Char(1))

    Go

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

    Insert Into dbo.Org_Patient_Info_Sys

    (Monarch_AccountNo,

    Monarch_Lname,

    Monarch_Fname,

    Monarch_MiddleName,

    Monarch_SSN,

    Monarch_BirthDate,

    Monarch_Address1,

    Monarch_Address2,

    Monarch_City,

    Monarch_State,

    Monarch_Zip,

    ContactName,

    StreetAddress1,

    StreetAddress2,

    City2,

    State2,

    ZipCode2,

    DeathDate,

    Monarch_AdmitDate,

    Monarch_DischargeDate,

    Sheet_Name,

    Monarch_InsCode,

    Monarch_InsName,

    Monarch_InsPolicy,

    Monarch_InsCode2,

    Monarch_InsName2,

    Monarch_InsPolicy2,

    Monarch_InsCode3,

    Monarch_InsName3,

    Monarch_InsPolicy3,

    HPS_ID,

    Monarch_PatientType)

    Select 'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    ,'A'

    Go

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

    If Object_ID('dbo.TempFieldNameHolder') Is Not Null

    Drop Table dbo.TempFieldNameHolder

    Go

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

    Declare @StartTime DateTime

    Set @StartTime = GetDate()

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

    Select C.Name

    Into dbo.TempFieldNameHolder

    From Sys.Tables T Inner Join Sys.Columns C

    On T.Object_ID=C.Object_ID

    Where T.Name='Org_Patient_Info_Sys'

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

    Select Str(DateDiff(ms,@StartTime,GetDate())) + ' Milliseconds duration: 1st method'

    Go

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

    If Object_ID('dbo.TempFieldNameHolder') Is Not Null

    Drop Table dbo.TempFieldNameHolder

    Go

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

    Declare @StartTime DateTime

    Set @StartTime = GetDate()

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

    Select Name

    Into dbo.TempFieldNameHolder

    From Sys.Columns

    Where Object_ID=Object_ID('dbo.Org_Patient_Info_Sys')

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

    Select Str(DateDiff(ms,@StartTime,GetDate())) + ' Milliseconds duration: 2nd method'

    Go

    Select *

    From dbo.TempFieldNameHolder

    Go

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • If you look at the actual execution plan, you'll see that the second method is more efficient. However, the execution plan also doesn't show any activity for the OBJECT_ID() call.

    If you include physical IO stats, you'll see that the join takes ~ 250ms (on my system), vs. 0-3 ms for the OBJECT_ID() function call.

    Personally, I like the way that the JOIN looks better, but it's clear to see that the OBJECT_ID() function call is more efficient.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thank you Wayne. I'm looking at the Execution Plan now, and I can see there is a great deal more items in the 1st method's plan than there is in the 2nd method's plan. Beyond that, I still am not really sure how to read these. I think you just gave me my next area to study.

    Thank you again.

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • In that case, you ought to read Grant Fritchey's books. He has another one coming out soon, with updates.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Soonish. 1st draft is done, but my tech editor, Brad McGehee, has probably painted it red with comments & suggestions. I think we're shooting for publishing before the PASS Summit. In the mean time, the current book is absolutely still applicable. Just pay no attention to it when it starts comparing costs on operations. The idiot that wrote that didn't know what he was talking about.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Excellent. I just IM'ed my wife to add it to the list / budget. I'll give it a go soon.

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • jarid.lawson (5/23/2011)


    Excellent. I just IM'ed my wife to add it to the list / budget. I'll give it a go soon.

    I can help you with the wife, it's free unless you buy the dead-tree version.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (5/23/2011)


    jarid.lawson (5/23/2011)


    Excellent. I just IM'ed my wife to add it to the list / budget. I'll give it a go soon.

    I can help you with the wife, it's free unless you buy the dead-tree version.

    That would be for the book on this site

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WOW...THANK YOU!!!

    I will start reading this right away. I'm glad to know you're on the boards here...both of you have already been a huge help!

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

Viewing 9 posts - 1 through 8 (of 8 total)

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