SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Opinion


Performance Opinion

Author
Message
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5288 Visits: 2767
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]
)
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]
)
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]
);

-----------------------------------------------------------------------------------
--------------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 Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222725 Visits: 46294
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
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5288 Visits: 2767
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 Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222725 Visits: 46294
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
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97871 Visits: 33014
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19509 Visits: 7410
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
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: 17665 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
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5288 Visits: 2767
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
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5288 Visits: 2767
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
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5288 Visits: 2692
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