May 23, 2011 at 9:35 am
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]
May 23, 2011 at 10:57 am
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
May 23, 2011 at 11:28 am
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]
May 23, 2011 at 12:25 pm
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
May 23, 2011 at 12:29 pm
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
May 23, 2011 at 12:46 pm
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]
May 23, 2011 at 1:17 pm
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
May 23, 2011 at 1:30 pm
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
May 23, 2011 at 2:13 pm
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