x002548's Blog - March 2007 Entries

Posts: 83
Comments: 600
Trackbacks: 40

March 2007 Entries

EDIT: This is bizzare..I ran multiple table joins, and reran it ovr and over, and the times are always different and one time one is faster than the othe and other times it's the other wway around.

We were having a discussion over at SQLTeam on whether to use full table name aliases or short aliases to label columns. It always seemed to be a mattter of preference and debate on how self docuenting the code. For m I will always use short aliases and make sure I lable every column, even if it's unique, just so when I come back to the code I don't have to guess or go to the data model. Some suggest that that's not good enough and fully qualify the columns with the name. I think that is overkill, but hey, to each his or her own.

Then we got into a discussion about performance and if there was any. My first thought was, no way...then someone asked if anyone had ever tested it...so I got to thinking...ys, yes, I know, a dangerous proposition. And while this is not diffinitive by any means, and I've seen varying results, but the table without the aliases at all took longer. I have to run some more complicated tests, but this is what we get from the following code.

ShortLabelTime
--------------
93

FullLabelTime
-------------
93

NoLableTime
-----------
126

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()))
GO

DECLARE @x int
SELECT @x = 1
WHILE @x < 10000
BEGIN
INSERT INTO myTable99(Col2) SELECT 'x'
SELECT @x = @x + 1
END
GO

DECLARE @s datetime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT a.Col1, a.Col2, a.Col3 FROM myTable99 a WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS ShortLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT myTable99.Col1, myTable99.Col2, myTable99.Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS FullLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT Col1, Col2, Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS NoLableTime
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

posted @ Tuesday, March 27, 2007 2:59 PM | Feedback (2)

Add Foreign Keys Back to the Database: "OK Brett, Now that I Removed all my Foreign Keys to Truncate the Data, Now What? I'm Hosed! Thanks a bunch"

OK, Well....sorry to keep you hangng out there. But, if you followed the code in the above link you will have all of the RI saved to the work table, so now all you need to do is replay it. The following is the code that will do this for you. Again, sorry for the delay.

CREATE procedure isp_exec_FK_code

AS

DECLARE @FKcode nvarchar(3000)
DECLARE @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @rc int
SET NOCOUNT ON
SELECT @rc = 0
DECLARE FKcode cursor fast_forward read_only for

SELECT * FROM FK_Create_code

OPEN FKcode

FETCH NEXT FROM FKcode
INTO @FKcode

WHILE @@fetch_status = 0
BEGIN
execute sp_executesql @FKcode
Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_exec_FK_code_Error
  END
FETCH NEXT FROM FKcode
INTO @FKcode
END

isp_exec_FK_code_Exit:
CLOSE FKcode
DEALLOCATE FKcode
SET NOCOUNT OFF
RETURN @rc

isp_exec_FK_code_Error:

If @Error_Type = 50001
BEGIN
  Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
          + ',"' + ' @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
          + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
          + ',"' + ' Message: ' + ',"' + RTrim(description)
          From master..sysmessages
        Where error = @error_out)
END

RAISERROR @Error_Type @Error_Message

GOTO isp_exec_FK_code_Exit

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO; posted @ Thursday, March 01, 2007 9:49 AM | Feedback (0)

Article Categories

Archives

Post Categories

Image Galleries

Misc

SQL Server