January 10, 2019 at 1:03 pm
Hi,
I have a stored procedure that runs for approx 5 mins , its developed by our developers.
I need some suggestions that could improve the performance of this SP and probably reduce its time to run, this SP also causes blocking to other processes.
Stored Procedure:
CREATE PROCEDURE [dbo].[sp_validate]
@SaveTables nvarchar(MAX),
@UserID uniqueidentifier,
@UserName nvarchar(50)
AS
BEGIN
declare @idRange as bigint
set @idRange = 1000000000000
create table #tmpObjectSessions (OBJECT_ID bigint, SESSION_ID bigint);
create table #tmpObjectSessionsToParty (OBJECT_ID bigint, SESSION_ID bigint);
create table #tmpCacheUpdateRequest (CACHE_NAME VARCHAR(50), CACHE_OBJECT_ID bigint);
IF (@SaveTables IS NULL or CHARINDEX('Party=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select PARTY_ID, SESSION_ID from #TMP_Core_PARTY
where isnull(DBAction, '') != '' and PARTY_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Event=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select EVENT_ID, SESSION_ID from #TMP_Core_EVENT
where isnull(DBAction, '') != '' and EVENT_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Image=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select IMAGE_ID, SESSION_ID from #TMP_Core_IMAGE
where isnull(DBAction, '') != '' and IMAGE_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Location=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select LOCATION_ID, SESSION_ID from #TMP_Core_LOCATION
where isnull(DBAction, '') != '' and LOCATION_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('M_Album=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select ALBUM_ID, SESSION_ID from #TMP_Core_M_ALBUM
where isnull(DBAction, '') != '' and ALBUM_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Product=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select PRODUCT_ID, SESSION_ID from #TMP_Core_PRODUCT
where isnull(DBAction, '') != '' and PRODUCT_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select ALBUM_ID, SESSION_ID from #TMP_Core_PRODUCT where ALBUM_ID > 0
and isnull(DBAction, '') != '' and PRODUCT_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.ALBUM_ID, c.SESSION_ID from #TMP_Core_PRODUCT c
cross apply (select top 1 * from CoreHistory..PRODUCT where PRODUCT_ID = c.PRODUCT_ID order by SESSION_ID desc) as history
where history.ALBUM_ID > 0
and isnull(c.DBAction, '') != '' and c.PRODUCT_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('M_Composition=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select COMPOSITION_ID, SESSION_ID from #TMP_Core_M_COMPOSITION
where isnull(DBAction, '') != '' and COMPOSITION_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('M_Performance=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select PERFORMANCE_ID, SESSION_ID from #TMP_Core_PERFORMANCE
where isnull(DBAction, '') != '' and PERFORMANCE_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('M_Recording=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select RECORDING_ID, SESSION_ID from #TMP_Core_M_RECORDING
where isnull(DBAction, '') != '' and RECORDING_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Media=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MEDIA_ID, SESSION_ID from #TMP_Core_MEDIA
where isnull(DBAction, '') != '' and MEDIA_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select PRODUCT_ID, SESSION_ID from #TMP_Core_MEDIA WHERE PRODUCT_ID > 0
and isnull(DBAction, '') != '' and MEDIA_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.PRODUCT_ID, c.SESSION_ID from #TMP_Core_MEDIA c
cross apply (select top 1 * from CoreHistory..MEDIA where MEDIA_ID = c.MEDIA_ID order by SESSION_ID desc) as history
where history.PRODUCT_ID > 0
and isnull(c.DBAction, '') != '' and c.MEDIA_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Media_Index=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MEDIA_INDEX_ID, SESSION_ID from #TMP_Core_MEDIA_INDEX
where isnull(DBAction, '') != '' and MEDIA_INDEX_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MEDIA_ID, SESSION_ID from #TMP_Core_MEDIA_INDEX WHERE MEDIA_ID > 0
and isnull(DBAction, '') != '' and MEDIA_INDEX_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.MEDIA_ID, c.SESSION_ID from #TMP_Core_MEDIA_INDEX c
cross apply (select top 1 * from CoreHistory..MEDIA_INDEX where MEDIA_INDEX_ID = c.MEDIA_INDEX_ID order by SESSION_ID desc) as history
where history.MEDIA_ID > 0
and isnull(c.DBAction, '') != '' and c.MEDIA_INDEX_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Media_Index_Segment=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MEDIA_INDEX_SEGMENT_ID, SESSION_ID from #TMP_Core_MEDIA_INDEX_SEGMENT
where isnull(DBAction, '') != '' and MEDIA_INDEX_SEGMENT_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MEDIA_INDEX_ID, SESSION_ID from #TMP_Core_MEDIA_INDEX_SEGMENT WHERE MEDIA_INDEX_ID > 0
and isnull(DBAction, '') != '' and MEDIA_INDEX_SEGMENT_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.MEDIA_INDEX_ID, c.SESSION_ID from #TMP_Core_MEDIA_INDEX_SEGMENT c
cross apply (select top 1 * from CoreHistory..MEDIA_INDEX_SEGMENT where MEDIA_INDEX_ID = c.MEDIA_INDEX_ID order by SESSION_ID desc) as history
where history.MEDIA_INDEX_ID > 0
and isnull(c.DBAction, '') != '' and c.MEDIA_INDEX_SEGMENT_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('M_Track=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select TRACK_ID, SESSION_ID from #TMP_Core_M_TRACK
where isnull(DBAction, '') != '' and TRACK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select ALBUM_ID, SESSION_ID from #TMP_Core_M_TRACK WHERE ALBUM_ID > 0
and isnull(DBAction, '') != '' and TRACK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MEDIA_ID, SESSION_ID from #TMP_Core_M_TRACK WHERE MEDIA_ID > 0
and isnull(DBAction, '') != '' and TRACK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select COMPOSITION_ID, SESSION_ID from #TMP_Core_M_TRACK WHERE COMPOSITION_ID > 0
and isnull(DBAction, '') != '' and TRACK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.ALBUM_ID, c.SESSION_ID from #TMP_Core_M_TRACK c
cross apply (select top 1 * from CoreHistory..M_TRACK where TRACK_ID = c.TRACK_ID order by SESSION_ID desc) as history
where history.ALBUM_ID > 0
and isnull(c.DBAction, '') != '' and c.TRACK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.ALBUM_ID, c.SESSION_ID from #TMP_Core_M_TRACK c
cross apply (select top 1 * from CoreHistory..M_TRACK where TRACK_ID = c.TRACK_ID order by SESSION_ID desc) as history
where history.ALBUM_ID > 0
and isnull(c.DBAction, '') != '' and c.TRACK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.MEDIA_ID, c.SESSION_ID from #TMP_Core_M_TRACK c
cross apply (select top 1 * from CoreHistory..M_TRACK where TRACK_ID = c.TRACK_ID order by SESSION_ID desc) as history
where history.MEDIA_ID > 0
and isnull(c.DBAction, '') != '' and c.TRACK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.COMPOSITION_ID, c.SESSION_ID from #TMP_Core_M_TRACK c
cross apply (select top 1 * from CoreHistory..M_TRACK where TRACK_ID = c.TRACK_ID order by SESSION_ID desc) as history
where history.COMPOSITION_ID > 0
and isnull(c.DBAction, '') != '' and c.TRACK_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('MProd_Cm=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MPROD_CM_ID, SESSION_ID from #TMP_Core_MPROD_CM
where isnull(DBAction, '') != '' and MPROD_CM_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Object_Set=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_SET_ID, SESSION_ID from #TMP_Core_OBJECT_SET
where isnull(DBAction, '') != '' and OBJECT_SET_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('NON_CORE_OBJECT=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select NON_CORE_OBJECT_ID, SESSION_ID from #TMP_Core_NON_CORE_OBJECT
where isnull(DBAction, '') != '' and NON_CORE_OBJECT_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Text=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select TEXT_ID, SESSION_ID from #TMP_Core_TEXT
where isnull(DBAction, '') != '' and TEXT_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('PHONETIC=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select PHONETIC_ID, SESSION_ID from #TMP_Core_PHONETIC
where isnull(DBAction, '') != '' and PHONETIC_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_ID, SESSION_ID from #TMP_Core_PHONETIC WHERE OBJECT_ID > 0
and isnull(DBAction, '') != '' and PHONETIC_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select ROOT_OBJECT_ID, SESSION_ID from #TMP_Core_PHONETIC WHERE ROOT_OBJECT_ID > 0
and isnull(DBAction, '') != '' and PHONETIC_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.OBJECT_ID, c.SESSION_ID from #TMP_Core_PHONETIC c
cross apply (select top 1 * from CoreHistory..PHONETIC where PHONETIC_ID = c.PHONETIC_ID order by SESSION_ID desc) as history
where history.OBJECT_ID > 0
and isnull(c.DBAction, '') != '' and c.PHONETIC_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.ROOT_OBJECT_ID, c.SESSION_ID from #TMP_Core_PHONETIC c
cross apply (select top 1 * from CoreHistory..PHONETIC where PHONETIC_ID = c.PHONETIC_ID order by SESSION_ID desc) as history
where history.ROOT_OBJECT_ID > 0
and isnull(c.DBAction, '') != '' and c.PHONETIC_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('V_Feature=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select FEATURE_ID, SESSION_ID from #TMP_Core_V_FEATURE
where isnull(DBAction, '') != '' and FEATURE_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MEDIA_ID, SESSION_ID from #TMP_Core_V_FEATURE WHERE MEDIA_ID > 0
and isnull(DBAction, '') != '' and FEATURE_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.MEDIA_ID, c.SESSION_ID from #TMP_Core_V_FEATURE c
cross apply (select top 1 * from CoreHistory..V_FEATURE where FEATURE_ID = c.FEATURE_ID order by SESSION_ID desc) as history
where history.MEDIA_ID > 0
and isnull(c.DBAction, '') != '' and c.FEATURE_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('V_Chapter=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select CHAPTER_ID, SESSION_ID from #TMP_Core_V_CHAPTER
where isnull(DBAction, '') != '' and CHAPTER_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select FEATURE_ID, SESSION_ID from #TMP_Core_V_CHAPTER WHERE FEATURE_ID > 0
and isnull(DBAction, '') != '' and CHAPTER_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.FEATURE_ID, c.SESSION_ID from #TMP_Core_V_CHAPTER c
cross apply (select top 1 * from CoreHistory..V_CHAPTER where CHAPTER_ID = c.CHAPTER_ID order by SESSION_ID desc) as history
where history.FEATURE_ID > 0
and isnull(c.DBAction, '') != '' and c.CHAPTER_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('V_TOC=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select V_TOC_ID, SESSION_ID from #TMP_Core_V_TOC
where isnull(DBAction, '') != '' and V_TOC_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MEDIA_ID, SESSION_ID from #TMP_Core_V_TOC WHERE MEDIA_ID > 0
and isnull(DBAction, '') != '' and V_TOC_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.MEDIA_ID, c.SESSION_ID from #TMP_Core_V_TOC c
cross apply (select top 1 * from CoreHistory..V_TOC where V_TOC_ID = c.V_TOC_ID order by SESSION_ID desc) as history
where history.MEDIA_ID > 0
and isnull(c.DBAction, '') != '' and c.V_TOC_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('V_TOC_OFFSET=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select TOC_OFFSET_ID, SESSION_ID from #TMP_Core_V_TOC_OFFSET
where isnull(DBAction, '') != '' and TOC_OFFSET_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select TOC_ID, SESSION_ID from #TMP_Core_V_TOC_OFFSET WHERE TOC_ID > 0
and isnull(DBAction, '') != '' and TOC_OFFSET_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.TOC_ID, c.SESSION_ID from #TMP_Core_V_TOC_OFFSET c
cross apply (select top 1 * from CoreHistory..V_TOC_OFFSET where TOC_OFFSET_ID = c.TOC_OFFSET_ID order by SESSION_ID desc) as history
where history.TOC_ID > 0
and isnull(c.DBAction, '') != '' and c.TOC_OFFSET_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('V_WORK=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select WORK_ID, SESSION_ID from #TMP_Core_V_WORK
where isnull(DBAction, '') != '' and WORK_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('SCHEDULE=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select V_TOC_ID, SESSION_ID from #TMP_Core_V_TOC
where isnull(DBAction, '') != '' and V_TOC_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('SCHEDULE_LINEUP=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select SCHEDULE_LINEUP_ID, SESSION_ID from #TMP_Core_SCHEDULE_LINEUP
where isnull(DBAction, '') != '' and SCHEDULE_LINEUP_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Vendor_Link=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select VENDOR_LINK_ID, SESSION_ID from #TMP_Core_VENDOR_LINK
where isnull(DBAction, '') != '' and VENDOR_LINK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_ID, SESSION_ID from #TMP_Core_VENDOR_LINK WHERE OBJECT_ID > 0
and isnull(DBAction, '') != '' and VENDOR_LINK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.OBJECT_ID, c.SESSION_ID from #TMP_Core_VENDOR_LINK c
cross apply (select top 1 * from CoreHistory..VENDOR_LINK where VENDOR_LINK_ID = c.VENDOR_LINK_ID order by SESSION_ID desc) as history
where history.OBJECT_ID > 0
and isnull(c.DBAction, '') != '' and c.VENDOR_LINK_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('MProd_Link=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select MPROD_LINK_ID, SESSION_ID from #TMP_Core_MPROD_LINK
where isnull(DBAction, '') != '' and MPROD_LINK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_ID, SESSION_ID from #TMP_Core_MPROD_LINK WHERE OBJECT_ID > 0
and isnull(DBAction, '') != '' and MPROD_LINK_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.OBJECT_ID, c.SESSION_ID from #TMP_Core_MPROD_LINK c
cross apply (select top 1 * from CoreHistory..MPROD_LINK where MPROD_LINK_ID = c.MPROD_LINK_ID order by SESSION_ID desc) as history
where history.OBJECT_ID > 0
and isnull(c.DBAction, '') != '' and c.MPROD_LINK_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('SERVICE_TUI=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select SERVICE_TUI_ID, SESSION_ID from #TMP_Core_SERVICE_TUI
where isnull(DBAction, '') != '' and SERVICE_TUI_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_ID, SESSION_ID from #TMP_Core_SERVICE_TUI WHERE OBJECT_ID > 0
and isnull(DBAction, '') != '' and SERVICE_TUI_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.OBJECT_ID, c.SESSION_ID from #TMP_Core_SERVICE_TUI c
cross apply (select top 1 * from CoreHistory..SERVICE_TUI where SERVICE_TUI_ID = c.SERVICE_TUI_ID order by SESSION_ID desc) as history
where history.OBJECT_ID > 0
and isnull(c.DBAction, '') != '' and c.SERVICE_TUI_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Object_To_Attribute=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_TO_ATTRIBUTE_ID, SESSION_ID from #TMP_Core_OBJECT_TO_ATTRIBUTE
where isnull(DBAction, '') != '' and OBJECT_TO_ATTRIBUTE_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select ROOT_OBJECT_ID, SESSION_ID from #TMP_Core_OBJECT_TO_ATTRIBUTE WHERE ROOT_OBJECT_ID > 0
and isnull(DBAction, '') != '' and OBJECT_TO_ATTRIBUTE_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_ID, SESSION_ID from #TMP_Core_OBJECT_TO_ATTRIBUTE WHERE OBJECT_ID > 0
and isnull(DBAction, '') != '' and OBJECT_TO_ATTRIBUTE_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.ROOT_OBJECT_ID, c.SESSION_ID from #TMP_Core_OBJECT_TO_ATTRIBUTE c
cross apply (select top 1 * from CoreHistory..OBJECT_TO_ATTRIBUTE where OBJECT_TO_ATTRIBUTE_ID = c.OBJECT_TO_ATTRIBUTE_ID order by SESSION_ID desc) as history
where history.ROOT_OBJECT_ID > 0
and isnull(c.DBAction, '') != '' and c.OBJECT_TO_ATTRIBUTE_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.OBJECT_ID, c.SESSION_ID from #TMP_Core_OBJECT_TO_ATTRIBUTE c
cross apply (select top 1 * from CoreHistory..OBJECT_TO_ATTRIBUTE where OBJECT_TO_ATTRIBUTE_ID = c.OBJECT_TO_ATTRIBUTE_ID order by SESSION_ID desc) as history
where history.OBJECT_ID > 0 and history.OBJECT_ID <> history.ROOT_OBJECT_ID
and isnull(c.DBAction, '') != '' and c.OBJECT_TO_ATTRIBUTE_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('Object_To_Object=', @SaveTables) > 0)
begin
-- Collect O2O Primary Key
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_TO_OBJECT_ID, SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT
where isnull(DBAction, '') != '' and OBJECT_TO_OBJECT_ID > 0
-- Collect O2O.ROOT_OBJECT_ID1 from all
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select ROOT_OBJECT_ID1, SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT WHERE ROOT_OBJECT_ID1 > 0
and isnull(DBAction, '') != '' and OBJECT_TO_OBJECT_ID > 0
-- Collect O2O.OBJECT_ID1 from all
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_ID1, SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT WHERE OBJECT_ID1 > 0
and isnull(DBAction, '') != '' and OBJECT_TO_OBJECT_ID > 0
-- Collect O2O.ROOT_OBJECT_ID2 where ID2 is party but ID1 is not a party, into #tmpObjectSessionsToParty
--Core: credited parties are going into separate list - to trigger cache update only for them
insert into #tmpObjectSessionsToParty (OBJECT_ID, SESSION_ID)
select ROOT_OBJECT_ID2, SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT WHERE ROOT_OBJECT_ID2 > 0
and convert(int, ROOT_OBJECT_ID1 / @idRange) != 6
and convert(int, ROOT_OBJECT_ID2 / @idRange) = 6
and isnull(DBAction, '') != '' and OBJECT_TO_OBJECT_ID > 0
-- Collect O2O.OBJECT_ID2 where ID2 is a party but ID1 is not a party, into tmpObjectSessionsToParty
insert into #tmpObjectSessionsToParty (OBJECT_ID, SESSION_ID)
select OBJECT_ID2, SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT WHERE OBJECT_ID2 > 0
and convert(int, ROOT_OBJECT_ID1 / @idRange) != 6
and convert(int, OBJECT_ID2 / @idRange) = 6
and isnull(DBAction, '') != '' and OBJECT_TO_OBJECT_ID > 0
-- Collect O2O.ROOT_OBJECT_ID2 where ID1 is a party but ID2 is not a party
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select ROOT_OBJECT_ID2, SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT WHERE ROOT_OBJECT_ID2 > 0
and (convert(int, ROOT_OBJECT_ID1 / @idRange) = 6 or convert(int, ROOT_OBJECT_ID2 / @idRange) != 6)
and isnull(DBAction, '') != '' and OBJECT_TO_OBJECT_ID > 0
-- Collect O2O.OBJECT_ID2 where ID1 is a party but ID2 is not a party
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_ID2, SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT WHERE OBJECT_ID2 > 0
and (convert(int, ROOT_OBJECT_ID1 / @idRange) = 6 or convert(int, OBJECT_ID2 / @idRange) != 6)
and isnull(DBAction, '') != '' and OBJECT_TO_OBJECT_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.ROOT_OBJECT_ID1, c.SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT c
cross apply (select top 1 * from CoreHistory..OBJECT_TO_OBJECT where OBJECT_TO_OBJECT_ID = c.OBJECT_TO_OBJECT_ID order by SESSION_ID desc) as history
where history.ROOT_OBJECT_ID1 > 0
and isnull(c.DBAction, '') != '' and c.OBJECT_TO_OBJECT_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.OBJECT_ID1, c.SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT c
cross apply (select top 1 * from CoreHistory..OBJECT_TO_OBJECT where OBJECT_TO_OBJECT_ID = c.OBJECT_TO_OBJECT_ID order by SESSION_ID desc) as history
where history.OBJECT_ID1 > 0 and history.OBJECT_ID1 <> history.ROOT_OBJECT_ID1
and isnull(c.DBAction, '') != '' and c.OBJECT_TO_OBJECT_ID > 0
--History: credited parties are going into separate list - to trigger cache update only for them
insert into #tmpObjectSessionsToParty (OBJECT_ID, SESSION_ID)
select history.ROOT_OBJECT_ID2, c.SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT c
cross apply (select top 1 * from CoreHistory..OBJECT_TO_OBJECT where OBJECT_TO_OBJECT_ID = c.OBJECT_TO_OBJECT_ID order by SESSION_ID desc) as history
where history.ROOT_OBJECT_ID2 > 0
-- and convert(int, history.ROOT_OBJECT_ID1 / @idRange) != 6
and convert(int, history.ROOT_OBJECT_ID2 / @idRange) = 6
and isnull(c.DBAction, '') != '' and c.OBJECT_TO_OBJECT_ID > 0
insert into #tmpObjectSessionsToParty (OBJECT_ID, SESSION_ID)
select history.OBJECT_ID2, c.SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT c
cross apply (select top 1 * from CoreHistory..OBJECT_TO_OBJECT where OBJECT_TO_OBJECT_ID = c.OBJECT_TO_OBJECT_ID order by SESSION_ID desc) as history
where history.OBJECT_ID2 > 0 and history.OBJECT_ID2 <> history.ROOT_OBJECT_ID2
-- and convert(int, history.ROOT_OBJECT_ID1 / @idRange) != 6
and convert(int, history.OBJECT_ID2 / @idRange) = 6
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.ROOT_OBJECT_ID2, c.SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT c
cross apply (select top 1 * from CoreHistory..OBJECT_TO_OBJECT where OBJECT_TO_OBJECT_ID = c.OBJECT_TO_OBJECT_ID order by SESSION_ID desc) as history
where history.ROOT_OBJECT_ID2 > 0
and (convert(int, history.ROOT_OBJECT_ID1 / @idRange) = 6 or convert(int, history.ROOT_OBJECT_ID2 / @idRange) != 6)
and isnull(c.DBAction, '') != '' and c.OBJECT_TO_OBJECT_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.OBJECT_ID2, c.SESSION_ID from #TMP_Core_OBJECT_TO_OBJECT c
cross apply (select top 1 * from CoreHistory..OBJECT_TO_OBJECT where OBJECT_TO_OBJECT_ID = c.OBJECT_TO_OBJECT_ID order by SESSION_ID desc) as history
where history.OBJECT_ID2 > 0 and history.OBJECT_ID2 <> history.ROOT_OBJECT_ID2
and (convert(int, history.ROOT_OBJECT_ID1 / @idRange) = 6 or convert(int, history.OBJECT_ID2 / @idRange) != 6)
and isnull(c.DBAction, '') != '' and c.OBJECT_TO_OBJECT_ID > 0
end
IF (@SaveTables IS NULL or CHARINDEX('String_Version=', @SaveTables) > 0)
begin
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select STRING_VERSION_ID, SESSION_ID from #TMP_Core_STRING_VERSION
where isnull(DBAction, '') != '' and STRING_VERSION_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select ROOT_OBJECT_ID, SESSION_ID from #TMP_Core_STRING_VERSION WHERE ROOT_OBJECT_ID > 0
and isnull(DBAction, '') != '' and STRING_VERSION_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select OBJECT_ID, SESSION_ID from #TMP_Core_STRING_VERSION WHERE OBJECT_ID > 0
and isnull(DBAction, '') != '' and STRING_VERSION_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.ROOT_OBJECT_ID, c.SESSION_ID from #TMP_Core_STRING_VERSION c
cross apply (select top 1 * from CoreHistory..STRING_VERSION where STRING_VERSION_ID = c.STRING_VERSION_ID order by SESSION_ID desc) as history
where history.ROOT_OBJECT_ID > 0
and isnull(c.DBAction, '') != '' and c.STRING_VERSION_ID > 0
insert into #tmpObjectSessions (OBJECT_ID, SESSION_ID)
select history.OBJECT_ID, c.SESSION_ID from #TMP_Core_STRING_VERSION c
cross apply (select top 1 * from CoreHistory..STRING_VERSION where STRING_VERSION_ID = c.STRING_VERSION_ID order by SESSION_ID desc) as history
where history.OBJECT_ID > 0 and history.OBJECT_ID <> history.ROOT_OBJECT_ID
and isnull(c.DBAction, '') != '' and c.STRING_VERSION_ID > 0
end
select s.OBJECT_ID, s.SESSION_ID into #tmpObjectSessionsUnique
from (select OBJECT_ID, SESSION_ID, ROW_NUMBER() over (partition by OBJECT_ID order by SESSION_ID desc) as rank from #tmpObjectSessions) s
where s.rank = 1
select s.OBJECT_ID, s.SESSION_ID into #tmpObjectSessionsUniqueToParty
from (select OBJECT_ID, SESSION_ID, ROW_NUMBER() over (partition by OBJECT_ID order by SESSION_ID desc) as rank from #tmpObjectSessionsToParty) s
where s.rank = 1
update cacheState set cacheState.EXPIRED = 1, cacheState.MAX_SESSION_ID = sessionUpdate.SESSION_ID, cacheState.UDATE = GETDATE()
output 'PARTY_LOADBYID', deleted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
from #tmpObjectSessionsUnique sessionUpdate
join CoreCaches..PARTY_LOADBYID_SESSIONS cacheSessions on sessionUpdate.OBJECT_ID = cacheSessions.OBJECT_ID
join CoreCaches..PARTY_LOADBYID_STATE cacheState with(forceseek) on cacheSessions.CACHE_OBJECT_ID = cacheState.CACHE_OBJECT_ID
where sessionUpdate.SESSION_ID > cacheState.MAX_SESSION_ID
--for records not in state - add the new expired one
insert into CoreCaches..PARTY_LOADBYID_STATE (CACHE_OBJECT_ID, EXPIRED, MAX_SESSION_ID, UDATE, CDATE)
output 'PARTY_LOADBYID', inserted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
select sessionUpdate.OBJECT_ID, 1, sessionUpdate.SESSION_ID, GETDATE(), GETDATE()
from #tmpObjectSessionsUnique sessionUpdate
left outer join CoreCaches..PARTY_LOADBYID_STATE cacheState with(forceseek) on sessionUpdate.OBJECT_ID = cacheState.CACHE_OBJECT_ID
where convert(int, sessionUpdate.OBJECT_ID / @idRange) = 6
and cacheState.CACHE_OBJECT_ID is null
update cacheState set cacheState.EXPIRED = 1, cacheState.MAX_SESSION_ID = sessionUpdate.SESSION_ID, cacheState.UDATE = GETDATE()
output 'PARTY_LOADBYID', deleted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
from #tmpObjectSessionsUniqueToParty sessionUpdate
join CoreCaches..PARTY_LOADBYID_STATE cacheState with(forceseek) on cacheState.CACHE_OBJECT_ID = sessionUpdate.OBJECT_ID
where sessionUpdate.SESSION_ID > cacheState.MAX_SESSION_ID
--for records not in state - add the new expired one
insert into CoreCaches..PARTY_LOADBYID_STATE (CACHE_OBJECT_ID, EXPIRED, MAX_SESSION_ID, UDATE, CDATE)
output 'PARTY_LOADBYID', inserted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
select sessionUpdate.OBJECT_ID, 1, sessionUpdate.SESSION_ID, GETDATE(), GETDATE()
from #tmpObjectSessionsUniqueToParty sessionUpdate
left outer join CoreCaches..PARTY_LOADBYID_STATE cacheState with(forceseek) on sessionUpdate.OBJECT_ID = cacheState.CACHE_OBJECT_ID
where convert(int, sessionUpdate.OBJECT_ID / @idRange) = 6
and cacheState.CACHE_OBJECT_ID is null
update cacheState set cacheState.EXPIRED = 1, cacheState.MAX_SESSION_ID = sessionUpdate.SESSION_ID, cacheState.UDATE = GETDATE()
output 'PRODUCT_LOADBYID', deleted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
from #tmpObjectSessionsUnique sessionUpdate
join CoreCaches..PRODUCT_LOADBYID_SESSIONS cacheSessions on sessionUpdate.OBJECT_ID = cacheSessions.OBJECT_ID
join CoreCaches..PRODUCT_LOADBYID_STATE cacheState with(forceseek) on cacheSessions.CACHE_OBJECT_ID = cacheState.CACHE_OBJECT_ID
where sessionUpdate.SESSION_ID > cacheState.MAX_SESSION_ID
--for records not in state - add the new expired one
insert into CoreCaches..PRODUCT_LOADBYID_STATE (CACHE_OBJECT_ID, EXPIRED, MAX_SESSION_ID, UDATE, CDATE)
output 'PRODUCT_LOADBYID', inserted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
select sessionUpdate.OBJECT_ID, 1, sessionUpdate.SESSION_ID, GETDATE(), GETDATE()
from #tmpObjectSessionsUnique sessionUpdate
left outer join CoreCaches..PRODUCT_LOADBYID_STATE cacheState with(forceseek) on sessionUpdate.OBJECT_ID = cacheState.CACHE_OBJECT_ID
where convert(int, sessionUpdate.OBJECT_ID / @idRange) = 7
and cacheState.CACHE_OBJECT_ID is null
update cacheState set cacheState.EXPIRED = 1, cacheState.MAX_SESSION_ID = sessionUpdate.SESSION_ID, cacheState.UDATE = GETDATE()
output 'VWORK_LOADBYID', deleted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
from #tmpObjectSessionsUnique sessionUpdate
join CoreCaches..VWORK_LOADBYID_SESSIONS cacheSessions on sessionUpdate.OBJECT_ID = cacheSessions.OBJECT_ID
join CoreCaches..VWORK_LOADBYID_STATE cacheState with(forceseek) on cacheSessions.CACHE_OBJECT_ID = cacheState.CACHE_OBJECT_ID
where sessionUpdate.SESSION_ID > cacheState.MAX_SESSION_ID
--for records not in state - add the new expired one
insert into CoreCaches..VWORK_LOADBYID_STATE (CACHE_OBJECT_ID, EXPIRED, MAX_SESSION_ID, UDATE, CDATE)
output 'VWORK_LOADBYID', inserted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
select sessionUpdate.OBJECT_ID, 1, sessionUpdate.SESSION_ID, GETDATE(), GETDATE()
from #tmpObjectSessionsUnique sessionUpdate
left outer join CoreCaches..VWORK_LOADBYID_STATE cacheState with(forceseek) on sessionUpdate.OBJECT_ID = cacheState.CACHE_OBJECT_ID
where convert(int, sessionUpdate.OBJECT_ID / @idRange) = 19
and cacheState.CACHE_OBJECT_ID is null
update cacheState set cacheState.EXPIRED = 1, cacheState.MAX_SESSION_ID = sessionUpdate.SESSION_ID, cacheState.UDATE = GETDATE()
--output 'VWORK_CANDIDATE_PARTOFBYID', deleted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
from #tmpObjectSessionsUnique sessionUpdate
join CoreCaches..VWORK_CANDIDATE_PARTOFBYID_SESSIONS cacheSessions on sessionUpdate.OBJECT_ID = cacheSessions.OBJECT_ID
join CoreCaches..VWORK_CANDIDATE_PARTOFBYID_STATE cacheState with(forceseek) on cacheSessions.CACHE_OBJECT_ID = cacheState.CACHE_OBJECT_ID
where sessionUpdate.SESSION_ID > cacheState.MAX_SESSION_ID
--for records not in state - add the new expired one
insert into CoreCaches..VWORK_CANDIDATE_PARTOFBYID_STATE (CACHE_OBJECT_ID, EXPIRED, MAX_SESSION_ID, UDATE, CDATE)
--output 'VWORK_LOADBYID', inserted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
select sessionUpdate.OBJECT_ID, 1, sessionUpdate.SESSION_ID, GETDATE(), GETDATE()
from #tmpObjectSessionsUnique sessionUpdate
left outer join CoreCaches..VWORK_CANDIDATE_PARTOFBYID_STATE cacheState with(forceseek) on sessionUpdate.OBJECT_ID = cacheState.CACHE_OBJECT_ID
where convert(int, sessionUpdate.OBJECT_ID / @idRange) = 19
and cacheState.CACHE_OBJECT_ID is null
update cacheState set cacheState.EXPIRED = 1, cacheState.MAX_SESSION_ID = sessionUpdate.SESSION_ID, cacheState.UDATE = GETDATE()
--output 'PRODUCT_CANDIDATE_WORKISONPRODUCT_LOADBYID', deleted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
from #tmpObjectSessionsUnique sessionUpdate
join CoreCaches..PRODUCT_CANDIDATE_WORKISONPRODUCT_LOADBYID_SESSIONS cacheSessions on sessionUpdate.OBJECT_ID = cacheSessions.OBJECT_ID
join CoreCaches..PRODUCT_CANDIDATE_WORKISONPRODUCT_LOADBYID_STATE cacheState with(forceseek) on cacheSessions.CACHE_OBJECT_ID = cacheState.CACHE_OBJECT_ID
where sessionUpdate.SESSION_ID > cacheState.MAX_SESSION_ID
--for records not in state - add the new expired one
insert into CoreCaches..PRODUCT_CANDIDATE_WORKISONPRODUCT_LOADBYID_STATE (CACHE_OBJECT_ID, EXPIRED, MAX_SESSION_ID, UDATE, CDATE)
--output 'VWORK_LOADBYID', inserted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
select sessionUpdate.OBJECT_ID, 1, sessionUpdate.SESSION_ID, GETDATE(), GETDATE()
from #tmpObjectSessionsUnique sessionUpdate
left outer join CoreCaches..PRODUCT_CANDIDATE_WORKISONPRODUCT_LOADBYID_STATE cacheState with(forceseek) on sessionUpdate.OBJECT_ID = cacheState.CACHE_OBJECT_ID
where convert(int, sessionUpdate.OBJECT_ID / @idRange) = 19
and cacheState.CACHE_OBJECT_ID is null
update cacheState set cacheState.EXPIRED = 1, cacheState.MAX_SESSION_ID = sessionUpdate.SESSION_ID, cacheState.UDATE = GETDATE()
output 'MALBUM_LOADBYID', deleted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
from #tmpObjectSessionsUnique sessionUpdate
join CoreCaches..MALBUM_LOADBYID_SESSIONS cacheSessions on sessionUpdate.OBJECT_ID = cacheSessions.OBJECT_ID
join CoreCaches..MALBUM_LOADBYID_STATE cacheState with(forceseek) on cacheSessions.CACHE_OBJECT_ID = cacheState.CACHE_OBJECT_ID
where sessionUpdate.SESSION_ID > cacheState.MAX_SESSION_ID
--for records not in state - add the new expired one
insert into CoreCaches..MALBUM_LOADBYID_STATE (CACHE_OBJECT_ID, EXPIRED, MAX_SESSION_ID, UDATE, CDATE)
output 'MALBUM_LOADBYID', inserted.CACHE_OBJECT_ID into #tmpCacheUpdateRequest
select sessionUpdate.OBJECT_ID, 1, sessionUpdate.SESSION_ID, GETDATE(), GETDATE()
from #tmpObjectSessionsUnique sessionUpdate
left outer join CoreCaches..MALBUM_LOADBYID_STATE cacheState with(forceseek) on sessionUpdate.OBJECT_ID = cacheState.CACHE_OBJECT_ID
where convert(int, sessionUpdate.OBJECT_ID / @idRange) = 9
and cacheState.CACHE_OBJECT_ID is null
insert into CoreCaches..CACHE_UPDATE_REQUEST (CACHE_NAME, CACHE_OBJECT_ID, CDATE)
select CACHE_NAME, CACHE_OBJECT_ID, GETDATE()
from #tmpCacheUpdateRequest cur
where not exists (select top 1 * from CoreCaches..CACHE_UPDATE_REQUEST where CACHE_NAME = cur.CACHE_NAME and CACHE_OBJECT_ID = cur.CACHE_OBJECT_ID and LDATE IS NULL);
return 0
END
GO
January 10, 2019 at 1:14 pm
without any ddl and sample data or an execution plan, we can't help at all. what performance issues are you having? How long does it take?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 10, 2019 at 1:19 pm
Nearly 650 lines of code and no execution plan, nor any suggestion that you've attempted to fix this yourself?
We're unpaid volunteers and you've been around long enough to know better.
You should at least take the time to identify which parts of the script are problematic. I doubt that it's all bad.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 10, 2019 at 1:26 pm
Phil Parkin - Thursday, January 10, 2019 1:19 PMNearly 650 lines of code and no execution plan, nor any suggestion that you've attempted to fix this yourself?We're unpaid volunteers and you've been around long enough to know better.
You should at least take the time to identify which parts of the script are problematic. I doubt that it's all bad.
I understand your concern, also I am just looking into it myself, I just needed some quick suggestions like, 1) Recompile the SP 2) Remove usage of TEMPDB etc...not asking for complete explanation and dedication to it. thanks
January 10, 2019 at 1:27 pm
Mike01 - Thursday, January 10, 2019 1:14 PMwithout any ddl and sample data or an execution plan, we can't help at all. what performance issues are you having? How long does it take?
Its actually taking around 5 mins, I will provide more details soon. thanks
January 10, 2019 at 1:44 pm
I would start with the parts that are reaching across databases. Here's a few:
select top 1 * from CoreHistory..PRODUCT where PRODUCT_ID = c.PRODUCT_ID order by SESSION_ID desc
select top 1 * from CoreHistory..MEDIA where MEDIA_ID = c.MEDIA_ID order by SESSION_ID desc
select top 1 * from CoreHistory..MEDIA_INDEX where MEDIA_INDEX_ID = c.MEDIA_INDEX_ID order by SESSION_ID desc
select top 1 * from CoreHistory..MEDIA_INDEX_SEGMENT where MEDIA_INDEX_ID = c.MEDIA_INDEX_ID order by SESSION_ID desc
select top 1 * from CoreHistory..M_TRACK where TRACK_ID = c.TRACK_ID order by SESSION_ID desc
select top 1 * from CoreHistory..PHONETIC where PHONETIC_ID = c.PHONETIC_ID order by SESSION_ID desc
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 10, 2019 at 1:54 pm
Could step through a statement at a time or insert some logging steps to help identify which statements that are taking the most time and tune them as needed.
January 11, 2019 at 6:32 am
Stuff like this is going to kill performance:
convert(int, sessionUpdate.OBJECT_ID / @idRange) = 6
I didn't read every line or go through it all, but a quick scan showed that. I'll bet it's all over the place and similar code smells will be there too.
"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
January 11, 2019 at 6:42 am
Perfect thanks guys for your responses, I need some tips like these.
January 11, 2019 at 6:48 am
If you run the stored procedure with SET STATISTICS TIME ON you will be able to work out which statement(s) is/are taking up the time.
January 11, 2019 at 7:11 am
One other thing I dislike is differed binding. What is that you ask? This is using differed binding: select top 1 * from CoreHistory..PRODUCT where PRODUCT_ID = c.PRODUCT_ID order by SESSION_ID desc. Instead of specifying the schema of the table PRODUCT in the CoreHistory database you have used the .. notation. This will default to dbo except if the user running this query has a different default schema. Unless you need this functionality you really should explicitly specify the schema.
Other than that, I haven't looked through the code as it is long and I am at work so no time at them moment (maybe later or when I get home). If you could post the actual execution plan for the execution of the procedure (as a .sqlplan file), that I would look at quicker than the code at the moment.
January 11, 2019 at 7:27 am
Lynn Pettis - Friday, January 11, 2019 7:11 AMOne other thing I dislike is differed binding. What is that you ask? This is using differed binding: select top 1 * from CoreHistory..PRODUCT where PRODUCT_ID = c.PRODUCT_ID order by SESSION_ID desc. Instead of specifying the schema of the table PRODUCT in the CoreHistory database you have used the .. notation. This will default to dbo except if the user running this query has a different default schema. Unless you need this functionality you really should explicitly specify the schema.Other than that, I haven't looked through the code as it is long and I am at work so no time at them moment (maybe later or when I get home). If you could post the actual execution plan for the execution of the procedure (as a .sqlplan file), that I would look at quicker than the code at the moment.
Did you mean deferred binding, by any chance?
I definitely agree that it should be avoided unless necessary.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2019 at 8:01 am
Yikes. Just glanced back through and spotted this:isnull(c.DBAction, '') != ''
That's another performance killer. You have a ton of tuning opportunities here without even looking at execution plans for all these queries. I would also examine whether or not all the manipulation of the data in temp tables is necessary. How much of that can be done in one query (note, I'm not advocating for a single query to do this, just some reduction in the quantity) instead of a hundred separate calls. After fixing the bad code smells and reducing the work, I'd break out the execution plans to understand how the optimizer is treating the resulting queries. However, at this point, too much easy work is available to start sweating execution plans.
"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
January 11, 2019 at 9:10 am
I'm also seeing a ton of things like this PARTY_ID > 0 in statements that are creating temp tables. Is that an identity field? If so aren't you basically just selecting the entire table?
January 11, 2019 at 9:24 am
Phil Parkin - Friday, January 11, 2019 7:27 AMLynn Pettis - Friday, January 11, 2019 7:11 AMOne other thing I dislike is differed binding. What is that you ask? This is using differed binding: select top 1 * from CoreHistory..PRODUCT where PRODUCT_ID = c.PRODUCT_ID order by SESSION_ID desc. Instead of specifying the schema of the table PRODUCT in the CoreHistory database you have used the .. notation. This will default to dbo except if the user running this query has a different default schema. Unless you need this functionality you really should explicitly specify the schema.Other than that, I haven't looked through the code as it is long and I am at work so no time at them moment (maybe later or when I get home). If you could post the actual execution plan for the execution of the procedure (as a .sqlplan file), that I would look at quicker than the code at the moment.
Did you mean deferred binding, by any chance?
I definitely agree that it should be avoided unless necessary.
Yes, can't seem to type today.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply