Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Opinion


Performance Opinion

Author
Message
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
Hi all,

I have created a SP in which frequentylr transaction will occur..
Can you please look at it and tell me if its fine or need some changes in performance point of view...


ALTER PROCEDURE [dbo].[usp_Job_Inbound_BuildingNote_Working]
   @InstanceID nchar(3),
   @SiteID nchar(3)
AS

BEGIN

SET NOCOUNT ON;

CREATE TABLE #TEMP
(
   [InBound_BuildingNoteId] [int] NOT NULL,
   [InstanceId] [nchar](3) NOT NULL,
   [SiteId] [nchar](3) NOT NULL,
   [LocalBuildingNoteId] [nvarchar](20) NOT NULL,
   [LocalBuildingId] [nvarchar](20) NOT NULL,
   [LocalEmployeeId] [nvarchar](20) NULL,
   [Note] [nvarchar](500) NOT NULL,
   [NoteCategoryCode] [nchar](3) NOT NULL,
   [SequenceNumber] [int] NOT NULL,
   [NoteAddedDate] [datetime] NOT NULL,
   [NoteExpirationDate] [datetime] NULL,
   [StatusFlag] [bit] NOT NULL,
   [DateAdded] [datetime] NOT NULL,
   [AddedBy] [nvarchar](75) NOT NULL,
   DateChanged DATETIME NULL,
   ChangedBy nvarchar(75) null,
   [LocalDateChanged] [datetime] NOT NULL,
   [LocalChangedBy] [nvarchar](75) NOT NULL,
   [ProcessedFlag] [bit] NOT NULL,
   [ProcessedDate] [datetime] NULL,
   [ReturnCode] [nchar](6) NULL,
   [EmailSentFlag] [bit] NOT NULL
)
----------------------------------------------------------------------------
BEGIN TRY
---------------CHECK FOREIGN KEY CONSTRAINT---------------------------------
Update IBC
SET IBC.ProcessedFlag = 1,
   IBC.ReturnCode = '2001',
   IBC.EmailSentFlag = 0,
   IBC.ProcessedDate = GETDATE()
FROM InBound_BuildingNote IBC WITH(NOLOCK)
LEFT JOIN Building B WITH(NOLOCK) ON B.InstanceId = IBC.InstanceId
      AND B.SiteId = IBC.SiteId
      AND B.LocalBuildingId = IBC.LocalBuildingId   
WHERE B.LocalBuildingId IS NULL
    AND IBC.ProcessedFlag = 0
    AND IBC.InstanceId = @InstanceID
    AND IBC.SiteId = @SiteID

Update IBC
SET IBC.ProcessedFlag = 1,
   IBC.ReturnCode = '2001',
   IBC.EmailSentFlag = 0,
   IBC.ProcessedDate = GETDATE()
FROM InBound_BuildingNote IBC WITH(NOLOCK)
LEFT JOIN Employee B WITH(NOLOCK) ON B.InstanceId = IBC.InstanceId
      AND B.SiteId = IBC.SiteId
      AND B.LocalEmployeeId = IBC.LocalEmployeeId      
WHERE B.LocalEmployeeId IS NULL
    AND IBC.ProcessedFlag = 0
    AND IBC.InstanceId = @InstanceID
    AND IBC.SiteId = @SiteID

Update IBC
SET IBC.ProcessedFlag = 1,
   IBC.ReturnCode = '2001',
   IBC.EmailSentFlag = 0,
   IBC.ProcessedDate = GETDATE()
FROM InBound_BuildingNote IBC
LEFT JOIN NoteCategory B ON B.NoteCategoryCode = IBC.NoteCategoryCode
      AND B.InstanceId = IBC.InstanceId
      AND B.SiteId = IBC.SiteId
WHERE B.NoteCategoryCode IS NULL
    AND IBC.ProcessedFlag = 0
    AND IBC.InstanceId = @InstanceID
    AND IBC.SiteId = @SiteID
------------------------------------------------------------------------------------------

INSERT INTO #TEMP
   (
      [InBound_BuildingNoteId] ,
      [InstanceId] ,
      [SiteId] ,
      [LocalBuildingNoteId] ,
      [LocalBuildingId] ,
      [LocalEmployeeId] ,
      [Note] ,
      [NoteCategoryCode] ,
      [SequenceNumber] ,
      [NoteAddedDate] ,
      [NoteExpirationDate] ,
      [StatusFlag] ,
      [DateAdded] ,
      [AddedBy] ,
      DateChanged ,
      ChangedBy ,
      [LocalDateChanged] ,
      [LocalChangedBy] ,
      [ProcessedFlag] ,
      [ProcessedDate] ,
      [ReturnCode] ,
      [EmailSentFlag]
   Wink
SELECT
      N.[InBound_BuildingNoteId] ,
      N.[InstanceId] ,
      N.[SiteId] ,
      N.[LocalBuildingNoteId] ,
      N.[LocalBuildingId] ,
      N.[LocalEmployeeId] ,
      N.[Note] ,
      N.[NoteCategoryCode] ,
      N.[SequenceNumber] ,
      N.[NoteAddedDate] ,
      N.[NoteExpirationDate] ,
      N.[StatusFlag] ,
      N.[DateAdded] ,
      N.[AddedBy] ,
      N.DateChanged ,
      N.ChangedBy ,
      N.[LocalDateChanged] ,
      N.[LocalChangedBy] ,
      N.[ProcessedFlag] ,
      N.[ProcessedDate] ,
      N.[ReturnCode] ,
      N.[EmailSentFlag]
FROM InBound_BuildingNote N WITH(NOLOCK)

WHERE N.ProcessedFlag = 0
      AND N.InstanceId = @InstanceID
      AND N.SiteId = @SiteID
------------------------------------------------------------------------------------------
;WITH CTE
AS
(
   SELECT
      N.[InBound_BuildingNoteId] ,
      N.[InstanceId] ,
      N.[SiteId] ,
      N.[LocalBuildingNoteId] ,
      B.BuildingId ,
      E.EmployeeId ,
      N.[Note] ,
      NC.NoteCategoryId ,
      N.[SequenceNumber] ,
      N.[NoteAddedDate] ,
      N.[NoteExpirationDate] ,
      N.[StatusFlag] ,
      N.[DateAdded] ,
      N.[AddedBy] ,
      N.DateChanged ,
      N.ChangedBy ,
      N.[LocalDateChanged] ,
      N.[LocalChangedBy] ,
      N.[ProcessedFlag] ,
      N.[ProcessedDate] ,
      N.[ReturnCode] ,
      N.[EmailSentFlag]
FROM #TEMP N WITH(NOLOCK)
INNER JOIN Building B WITH(NOLOCK) ON B.InstanceId = N.InstanceId
         AND B.SiteId = N.SiteId
         AND B.LocalBuildingId = N.LocalBuildingId
INNER JOIN Employee E WITH(NOLOCK) ON E.InstanceId = N.InstanceId
         AND E.SiteId = N.SiteId
         AND E.LocalEmployeeId = N.LocalEmployeeId
INNER JOIN NoteCategory NC WITH(NOLOCK) ON NC.InstanceId = N.InstanceId
         AND NC.SiteId = N.SiteId
         AND NC.NoteCategoryCode = N.NoteCategoryCode
)

MERGE BuildingNote_Working B
USING CTE C ON B.InstanceID = C.InstanceID
      AND B.SiteID = C.SiteID
      AND B.LocalBuildingNoteID = C.LocalBuildingNoteID
WHEN MATCHED THEN
UPDATE
SET
      B.BuildingId         = C.BuildingId ,
      B.EmployeeId         = C.EmployeeId ,
      B.[Note]            = C.[Note] ,
      B.NoteCategoryId      = C.NoteCategoryId ,
      B.[SequenceNumber]      = C.[SequenceNumber] ,
      B.[NoteAddedDate]      = C.[NoteAddedDate] ,
      B.[NoteExpirationDate]   = C.[NoteExpirationDate] ,
      B.[StatusFlag]         = C.[StatusFlag] ,
      B.[DateAdded]         = C.[DateAdded] ,
      B.[AddedBy]            = C.[AddedBy] ,
      B.DateChanged         = GETDATE(),
      B.ChangedBy            = C.ChangedBy ,
      B.[LocalDateChanged]   = C.[LocalDateChanged] ,
      B.[LocalChangedBy]      = C.[LocalChangedBy]

WHEN NOT MATCHED THEN
INSERT
   (
      [InstanceId] ,
      [SiteId] ,
      [LocalBuildingNoteId] ,
      BuildingId ,
      EmployeeId ,
      [Note] ,
      NoteCategoryId ,
      [SequenceNumber] ,
      [NoteAddedDate] ,
      [NoteExpirationDate] ,
      [StatusFlag] ,
      [DateAdded] ,
      [AddedBy] ,
      DateChanged ,
      ChangedBy ,
      [LocalDateChanged] ,
      [LocalChangedBy]
   Wink
VALUES
   (
      C.[InstanceId] ,
      C.[SiteId] ,
      C.[LocalBuildingNoteId] ,
      C.BuildingId ,
      C.EmployeeId ,
      C.[Note] ,
      C.NoteCategoryId ,
      C.[SequenceNumber] ,
      C.[NoteAddedDate] ,
      C.[NoteExpirationDate] ,
      C.[StatusFlag] ,
      C.[DateAdded] ,
      C.[AddedBy] ,
      C.DateChanged ,
      C.ChangedBy ,
      C.[LocalDateChanged] ,
      C.[LocalChangedBy]
   Wink;

-----------------------------------------------------------------------------------
--------------Changed Process Flag stauts to 1 -------------------------------
UPDATE B
SET   ProcessedFlag = 1,
   ProcessedDate = GETDATE()
FROM InBound_BuildingNote B WITH (NOLOCK)
INNER JOIN #TEMP T WITH (NOLOCK) ON B.LocalBuildingNoteId = T.LocalBuildingNoteId
      AND B.InBound_BuildingNoteId = T.InBound_BuildingNoteId
      AND B.InstanceId = T.InstanceId
      AND B.SiteId = T.SiteId
END TRY

BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);

SELECT @ErrorMessage = ERROR_MESSAGE()
RAISERROR (@ErrorMessage, 16, 1);

Exec [dbo].[usp_Insert_Error] 'StoreProcedure:[usp_Job_Inbound_BuildingnNote]',@@ERROR,@ErrorMessage,'','Stored Procedure';
END CATCH

----------------------------------
DROP TABLE #temp



END



Thanks a lot in advance...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
More nolock abuse. Are the users aware and happy with the fact that their reports could return incorrect data?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
GilaMonster (12/11/2013)
More nolock abuse. Are the users aware and happy with the fact that their reports could return incorrect data?

Ok, I will remove that nolock part from code.......................

Other than that is the code fine?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
I didn't look at it in detail.

Run it against a representative data volume, check the performance. If acceptable great. If not, identify which parts are slowest and ask for help with those.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
I looked through it. I don't see anything egregious. It is doing a lot of work. But you don't have any really obvious issues. At least, none I spotted. I'd need to see the execution plans for the statements to identify if there were better indexes that might help performance.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3938 Visits: 6669
1) Maybe you can combine the three separate UPDATEs into one, as shown below, avoiding repeated joins of the same tables.

2) Depending on the row counts, you might consider creating a filtered index on IBC.ProcessedFlag, including at least InstanceId and SiteId (if they are the clustering keys, of course you won't need to explicitly INCLUDE them, although it doesn't hurt anything to do so).

3) Beyond just this query, if you most often access the listed tables by InstanceId and SiteId, then if possible cluster the tables that way. That should speed up all your processing.
I don't know enough about the data distributions to know whether it should be ( InstanceId, SiteId ) or ( SiteId, InstanceId ), but you can determine that. If the third column is consistently specified in the WHERE, you could specify it in the clus key as well if you want; that is: ( key1, key2, LocalBuildingId | LocalEmployeeId | NoteCategoryCode ), although that lengthens the key so much that it must be weighed carefully.



Update IBC
SET IBC.ProcessedFlag = 1,
   IBC.ReturnCode = '2001',
   IBC.EmailSentFlag = 0,
   IBC.ProcessedDate = GETDATE()
FROM InBound_BuildingNote IBC WITH(NOLOCK)
LEFT JOIN Building B WITH(NOLOCK) ON B.InstanceId = IBC.InstanceId
      AND B.SiteId = IBC.SiteId
      AND B.LocalBuildingId = IBC.LocalBuildingId   
LEFT JOIN Employee E WITH(NOLOCK) ON E.InstanceId = IBC.InstanceId
      AND E.SiteId = IBC.SiteId
      AND E.LocalEmployeeId = IBC.LocalEmployeeId      
LEFT JOIN NoteCategory NC ON NC.NoteCategoryCode = IBC.NoteCategoryCode
      AND NC.InstanceId = IBC.InstanceId
      AND NC.SiteId = IBC.SiteId
WHERE (B.LocalBuildingId IS NULL OR E.LocalEmployeeId IS NULL OR NC.NoteCategoryCode IS NULL)
    AND IBC.ProcessedFlag = 0
    AND IBC.InstanceId = @InstanceID
    AND IBC.SiteId = @SiteID




SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
You should wrap your UPDATEs/INSERT/MERGE in a TRANSACTION and then do COMMIT/ROLLBACK consistent with your error handling.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
dwain.c (12/12/2013)
You should wrap your UPDATEs/INSERT/MERGE in a TRANSACTION and then do COMMIT/ROLLBACK consistent with your error handling.


Thanks Dwain, I will do that :-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
ScottPletcher (12/12/2013)
1) Maybe you can combine the three separate UPDATEs into one, as shown below, avoiding repeated joins of the same tables.

2) Depending on the row counts, you might consider creating a filtered index on IBC.ProcessedFlag, including at least InstanceId and SiteId (if they are the clustering keys, of course you won't need to explicitly INCLUDE them, although it doesn't hurt anything to do so).

3) Beyond just this query, if you most often access the listed tables by InstanceId and SiteId, then if possible cluster the tables that way. That should speed up all your processing.
I don't know enough about the data distributions to know whether it should be ( InstanceId, SiteId ) or ( SiteId, InstanceId ), but you can determine that. If the third column is consistently specified in the WHERE, you could specify it in the clus key as well if you want; that is: ( key1, key2, LocalBuildingId | LocalEmployeeId | NoteCategoryCode ), although that lengthens the key so much that it must be weighed carefully.



Update IBC
SET IBC.ProcessedFlag = 1,
   IBC.ReturnCode = '2001',
   IBC.EmailSentFlag = 0,
   IBC.ProcessedDate = GETDATE()
FROM InBound_BuildingNote IBC WITH(NOLOCK)
LEFT JOIN Building B WITH(NOLOCK) ON B.InstanceId = IBC.InstanceId
      AND B.SiteId = IBC.SiteId
      AND B.LocalBuildingId = IBC.LocalBuildingId   
LEFT JOIN Employee E WITH(NOLOCK) ON E.InstanceId = IBC.InstanceId
      AND E.SiteId = IBC.SiteId
      AND E.LocalEmployeeId = IBC.LocalEmployeeId      
LEFT JOIN NoteCategory NC ON NC.NoteCategoryCode = IBC.NoteCategoryCode
      AND NC.InstanceId = IBC.InstanceId
      AND NC.SiteId = IBC.SiteId
WHERE (B.LocalBuildingId IS NULL OR E.LocalEmployeeId IS NULL OR NC.NoteCategoryCode IS NULL)
    AND IBC.ProcessedFlag = 0
    AND IBC.InstanceId = @InstanceID
    AND IBC.SiteId = @SiteID




Thanks Scott for your suggestion, I will combime multiple update statements into a single update statement..

Below is my table script to review for index point of view if any changes is required:

/****** Object: Table [dbo].[Employee] Script Date: 12/13/2013 12:05:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee](
   [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
   [InstanceId] [nchar](3) NOT NULL,
   [SiteId] [nchar](3) NOT NULL,
   [LocalEmployeeId] [nvarchar](20) NOT NULL,
   [LocalEmployeeNumber] [nvarchar](20) NOT NULL,
   [OfficeId] [int] NOT NULL,
   [Prefix] [nvarchar](10) NULL,
   [FirstName] [nvarchar](50) NULL,
   [MiddleName] [nvarchar](50) NULL,
   [LastName] [nvarchar](50) NULL,
   [DisplayName] [nvarchar](125) NULL,
   [WorkPhoneNumber] [nvarchar](25) NULL,
   [CellPhoneNumber] [nvarchar](25) NULL,
   [EmailAddress] [nvarchar](75) NULL,
   [SupervisorId] [int] NULL,
   [PersonTypeId] [int] NULL,
   [DeviceID] [nvarchar](100) NULL,
   [LocalUnionID] [nvarchar](20) NOT NULL,
   [OT1RateMultiple] [decimal](2, 1) NOT NULL,
   [OT2RateMultiple] [decimal](2, 1) NULL,
   [OT3RateMultiple] [decimal](2, 1) NULL,
   [OldTravelRate] [decimal](6, 4) NULL,
   [CurrentTravelRate] [decimal](6, 4) NULL,
   [CurrentRateStartDate] [datetime] NULL,
   [TemporaryRateCode] [nchar](3) NULL,
   [StatusFlag] [bit] NOT NULL,
   [DateAdded] [datetime] NOT NULL,
   [AddedBy] [nvarchar](75) NOT NULL,
   [DateChanged] [datetime] NULL,
   [ChangedBy] [nvarchar](75) NULL,
   [LocalDateChanged] [datetime] NOT NULL,
   [LocalChangedBy] [nvarchar](75) NOT NULL,
   [SyncTimeStamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
   [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [uq1_Employee] UNIQUE NONCLUSTERED
(
   [InstanceId] ASC,
   [SiteId] ASC,
   [LocalEmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_EmployeeOfficeId] FOREIGN KEY([OfficeId])
REFERENCES [dbo].[Office] ([OfficeId])
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_EmployeeOfficeId]
GO

ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_EmployeeSupervisorId] FOREIGN KEY([SupervisorId])
REFERENCES [dbo].[Supervisor] ([SupervisorId])
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_EmployeeSupervisorId]
GO






_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2596
if there is large data set in the #Temp table, all you find in the query execution plan is "tableScan" for #Temp table, If you need to increase the performance then you need to index the temp table as well.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search