March 27, 2018 at 8:42 am
I have 3 source linked server tables and one target table in sql server which contains following number of records. Stored procedure scheduled every 30 min to run. It's executing for very long hours and locking other jobs. I need to improve the performance. Please advise if you have any suggestions.
src_tab1 458245 records
src_tab2 74592 records
src_tab3 72131 records
tv_target_tab 232306 records
DECLARE @tab1 TABLE (
ITEMID VARCHAR(50),
CONTAINERDATANAME VARCHAR(50),
CONTAINERDATAVAL VARCHAR(50),
EVENTCODE INT,
CREATED DATETIME,
USERID VARCHAR(50),
EVENTDATA1 VARCHAR(50),
EVENTDATA2 VARCHAR(50),
EVENTDATA3 VARCHAR(50),
EVENTDATA4 VARCHAR(50),
EVENTDATA5 VARCHAR(50),
PARENTCOMPID VARCHAR(50)
)
declare @tab2 table (
componentid varchar(50),
rtargetitemid varchar(50)
)
insert into @tab2
select COMPONENTID, RTARGETITEMID
FROM [LinkserverDB].dbo.[src_tab2]
DECLARE @tv_target_tab TABLE (
itemid varchar(50),
eventid varchar(50),
date2 datetime,
stocknumber varchar(50),
customernumber varchar(50),
lastname varchar(50),
location varchar(50),
region varchar(50),
userid varchar(50),
durationminutes int
)
declare @tab3 TABLE (
ItemId varchar(50),
ATTR0000001071 varchar(50),
ATTR0000001089 varchar(50),
ATTR0000001090 varchar(50),
ATTR0000001077 varchar(50),
ATTR0000001134 varchar(50)
)
insert into @tab3
select ItemID,
LTRIM(RTRIM(ATTR0000001071)) AS ATTR0000001071,
LTRIM(RTRIM(ATTR0000001089)) AS ATTR0000001089,
LTRIM(RTRIM(ATTR0000001090)) AS ATTR0000001090,
LTRIM(RTRIM(ATTR0000001077)) AS ATTR0000001077,
LTRIM(RTRIM(ATTR0000001134)) AS ATTR0000001134
from [LinkserverDB].dbo.[src_tab3]
INSERT INTO @tab1
(ITEMID, CONTAINERDATANAME, CONTAINERDATAVAL,
EVENTCODE, CREATED, USERID,
EVENTDATA1, EVENTDATA2, EVENTDATA3,
EVENTDATA4, EVENTDATA5, PARENTCOMPID)
SELECT
ITEMID, CONTAINERDATANAME, CONTAINERDATAVAL,
EVENTCODE, CREATED, USERID,
EVENTDATA1, EVENTDATA2, EVENTDATA3,
EVENTDATA4, EVENTDATA5, PARENTCOMPID
FROM [LinkserverDB].dbo.[src_tab1]
--Insert new Keys for Events we care about.
INSERT INTO @tv_target_tab
(itemid, eventid, date2, stocknumber,
customernumber, lastname, location,
region, userid, durationminutes)
SELECT
DISTINCT
LTRIM(RTRIM(WP.ITEMID)) AS ItemID,
LTRIM(RTRIM(R.EVENTDATA2)) AS EventID,
CAST(R.CREATED AS DATETIME) AS Date2,
LTRIM(RTRIM(WP.ATTR0000001071)) AS StockNumber,
LTRIM(RTRIM(WP.ATTR0000001089)) AS CustomerNumber,
LTRIM(RTRIM(WP.ATTR0000001090)) AS LastName,
LTRIM(RTRIM(WP.ATTR0000001077)) AS Location,
LTRIM(RTRIM(WP.ATTR0000001134)) AS Region,
LTRIM(RTRIM(R.USERID)) AS UserID,
NULL as DurationMinutes
FROM @tab1 AS R
INNER JOIN @tab3 AS WP
ON R.ITEMID=WP.ITEMID
AND R.EVENTCODE = 600
INNER JOIN Configuration AS C
ON LTRIM(RTRIM(R.EVENTDATA2))=C.EventID
WHERE NOT EXISTS (SELECT * FROM tv_target_tab WHERE ItemID=LTRIM(RTRIM(WP.ITEMID)) AND EventID=LTRIM(RTRIM(R.EVENTDATA2)) AND Date=CAST(R.CREATED AS DATETIME)) --Only new items
INSERT INTO @tv_target_tab
(itemid, eventid, date2, stocknumber,
customernumber, lastname, location,
region, userid, durationminutes)
SELECT
DISTINCT
LTRIM(RTRIM(WP.ITEMID)) AS ItemID,
LTRIM(RTRIM(R.CONTAINERDATAVAL)) AS EventID, --Hold Codes
CAST(DATEADD(hour, 4, R.CREATED) AS DATETIME) AS Date2, --TODO: Remove this once Charles has fixed the trigger and the data.
LTRIM(RTRIM(WP.ATTR0000001071)) AS StockNumber,
LTRIM(RTRIM(WP.ATTR0000001089)) AS CustomerNumber,
LTRIM(RTRIM(WP.ATTR0000001090)) AS LastName,
LTRIM(RTRIM(WP.ATTR0000001077)) AS Location,
LTRIM(RTRIM(WP.ATTR0000001134)) AS Region,
LTRIM(RTRIM(R.USERID)) AS UserID,
NULL as DurationMinutes
FROM @tab1 AS R
INNER JOIN @tab2 AS R2
ON LTRIM(RTRIM(R.PARENTCOMPID))=LTRIM(RTRIM(R2.COMPONENTID))
AND R.CONTAINERDATANAME='Go to Hold'
INNER JOIN @tab3 AS WP
ON LTRIM(RTRIM(R2.RTARGETITEMID))=LTRIM(RTRIM(WP.ITEMID))
INNER JOIN tv_target_tab AS K
ON LTRIM(RTRIM(WP.ITEMID)) = K.ItemID
INNER JOIN Configuration AS C
ON LTRIM(RTRIM(R.CONTAINERDATAVAL))=C.EventID
WHERE NOT EXISTS (SELECT * FROM tv_target_tab WHERE ItemID=LTRIM(RTRIM(WP.ITEMID)) AND EventID=LTRIM(RTRIM(R.CONTAINERDATAVAL)) AND Date=CAST(DATEADD(hour, 4, R.CREATED) AS DATETIME))
--Then, add the other data, joined on all key fields (all but Date and EventID)
INSERT INTO @tv_target_tab
(itemid, eventid, date2, stocknumber,
customernumber, lastname, location,
region, userid, durationminutes)
SELECT
DISTINCT
LTRIM(RTRIM(WP.ITEMID)) AS ItemID,
LTRIM(RTRIM(R.EVENTDATA3)) AS EventID,
CAST(R.CREATED AS DATETIME) AS Date2,
LTRIM(RTRIM(WP.ATTR0000001071)) AS StockNumber,
LTRIM(RTRIM(WP.ATTR0000001089)) AS CustomerNumber,
LTRIM(RTRIM(WP.ATTR0000001090)) AS LastName,
LTRIM(RTRIM(WP.ATTR0000001077)) AS Location,
LTRIM(RTRIM(WP.ATTR0000001134)) AS Region,
LTRIM(RTRIM(R.USERID)) AS UserID,
NULL as DurationMinutes
FROM @tab1 AS R
INNER JOIN @tab3 AS WP -- Deal Folder
ON R.ITEMID=WP.ITEMID
AND R.EVENTCODE > '600' -- AND R.EVENTCODE <> '606'
INNER JOIN tv_target_tab AS K --Filter by the keys
ON LTRIM(RTRIM(WP.ITEMID)) = K.ItemID
INNER JOIN Configuration AS C
ON LTRIM(RTRIM(R.EVENTDATA3))=C.EventID
WHERE NOT EXISTS (SELECT * FROM tv_target_tab WHERE ItemID=LTRIM(RTRIM(WP.ITEMID)) AND EventID=LTRIM(RTRIM(R.EVENTDATA3)) AND Date=CAST(R.CREATED AS DATETIME)) --Only new items
-- Add the final records (606)
INSERT INTO @tv_target_tab
(itemid, eventid, date2, stocknumber,
customernumber, lastname, location,
region, userid, durationminutes)
SELECT
DISTINCT
LTRIM(RTRIM(WP.ITEMID)) AS ItemID,
LTRIM(RTRIM(R.EVENTCODE)) AS EventID,
CAST(R.CREATED AS DATETIME) AS Date2,
LTRIM(RTRIM(WP.ATTR0000001071)) AS StockNumber,
LTRIM(RTRIM(WP.ATTR0000001089)) AS CustomerNumber,
LTRIM(RTRIM(WP.ATTR0000001090)) AS LastName,
LTRIM(RTRIM(WP.ATTR0000001077)) AS Location,
LTRIM(RTRIM(WP.ATTR0000001134)) AS Region,
LTRIM(RTRIM(R.USERID)) AS UserID,
NULL as DurationMinutes
FROM @tab1 AS R
INNER JOIN @tab3 AS WP
ON R.ITEMID=WP.ITEMID
AND R.EVENTCODE = '606'
INNER JOIN tv_target_tab AS K
ON LTRIM(RTRIM(WP.ITEMID)) = K.ItemID
INNER JOIN Configuration AS C
ON LTRIM(RTRIM(R.EVENTCODE))=C.EventID
WHERE NOT EXISTS (SELECT * FROM tv_target_tab WHERE ItemID=LTRIM(RTRIM(WP.ITEMID)) AND EventID=LTRIM(RTRIM(R.EVENTCODE)) AND Date=CAST(R.CREATED AS DATETIME)) --Only new items
-- Add resulting rows into tv_target_tab table
INSERT INTO tv_target_tab
(itemid, eventid, [date], stocknumber,
customernumber, lastname, location,
region, userid, durationminutes)
SELECT itemid, eventid, [date2], stocknumber,
customernumber, lastname, location,
region, userid, durationminutes
FROM @tv_target_tab
--Do some cleanup, since 6XX records are coming in with Hold entries marked for the exact same time.
DELETE H
FROM tv_target_tab AS H --Hold record
INNER JOIN Configuration AS HC
ON H.EventID=HC.EventID AND (HC.IsHeld=1 OR HC.EventID='0')
INNER JOIN tv_target_tab AS S --Six-hundred series
ON H.ItemID=S.ItemID --Same Item and exact same date
AND H.Date=S.Date
AND H.EventID <> S.EventID
-- INNER JOIN Configuration AS HS
-- ON S.EventID=HC.EventID AND (HS.IsHeld=0 OR HS.EventID <> '0')
--Fix this so it doesn't calculate for EventID indicating the last normal event.
UPDATE L
SET DurationMinutes=
DATEDIFF(
minute,L.Date,
(SELECT TOP 1 Date --First Future Record
FROM tv_target_tab AS F
WHERE F.ItemID=L.ItemID
AND F.Date > L.Date --Future record
ORDER BY Date)
)
FROM tv_target_tab AS L
WHERE DurationMinutes IS NULL
--DELETE FROM tv_target_tab WHERE ITEMID NOT IN (SELECT ITEMID FROM @tab3)
DELETE wl
FROM tv_target_tab wl
LEFT JOIN @tab3 df
ON wl.ITEMID = df.ITEMID
WHERE wl.ItemID IS NULL
March 27, 2018 at 8:59 am
Why not put some debug logic in your stored procedure so that you can see what's taking the most time?
Here's what sticks out to me:
(1) No WHERE clause in any of your INSERT statements from linked servers. Do you really need every row?
(2) Using table variables instead of temp tables. That's OK for a small number of rows, but for the volumes you have, you're going to get bitten.
(3) The correlated subquery in the UPDATE statement looks as if it may hurt you.
(4) Using functions in join predicates. If you're concerned about leading and trailing spaces, consider removing them as soon as you import the data.
John
March 27, 2018 at 9:51 am
John Mitchell-245523 - Tuesday, March 27, 2018 8:59 AMWhy not put some debug logic in your stored procedure so that you can see what's taking the most time?Here's what sticks out to me:
(1) No WHERE clause in any of your INSERT statements from linked servers. Do you really need every row?
(2) Using table variables instead of temp tables. That's OK for a small number of rows, but for the volumes you have, you're going to get bitten.
(3) The correlated subquery in the UPDATE statement looks as if it may hurt you.
(4) Using functions in join predicates. If you're concerned about leading and trailing spaces, consider removing them as soon as you import the data.John
Thank you. What about creating view instead table variables?
March 27, 2018 at 9:57 am
saptek9 - Tuesday, March 27, 2018 9:51 AMThank you. What about creating view instead table variables?
I think you're just guessing. Find out what the most expensive parts of your code are, and tune them first.
John
March 27, 2018 at 11:01 am
I agree that getting some timings across steps would help you isolate the problem.
IMO, you do NOT want to try to join to a view on a linked server. Cross server joins can be fatally slow. If you are trying to pull thousands of rows into a table variable and then join to it, you should consider using a #temp table instead.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 27, 2018 at 12:19 pm
saptek9 - Tuesday, March 27, 2018 9:51 AMJohn Mitchell-245523 - Tuesday, March 27, 2018 8:59 AMWhy not put some debug logic in your stored procedure so that you can see what's taking the most time?Here's what sticks out to me:
(1) No WHERE clause in any of your INSERT statements from linked servers. Do you really need every row?
(2) Using table variables instead of temp tables. That's OK for a small number of rows, but for the volumes you have, you're going to get bitten.
(3) The correlated subquery in the UPDATE statement looks as if it may hurt you.
(4) Using functions in join predicates. If you're concerned about leading and trailing spaces, consider removing them as soon as you import the data.John
Thank you. What about creating view instead table variables?
Don;t even consider it. A view will just make the problem worse. You DEFINITELY want #TEMP tables instead of table variables. Understand that you're bringing entire tables across the network between the servers, so that will take some time, so once you get them across, you need to get the data into temp tables that you can index, and that have statistics - table variables don't get stats and you can't make them, so query optimizer row estimates against table variables are always way off when you have more than just a couple of records, which will almost always cause a bad execution plan. At that point, kiss performance good bye.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 27, 2018 at 1:00 pm
As others have mentioned, use #temp tables. A general rule of thumb I go with is 1000 records. If it's considerably more than 1000 rows, a #temp table is generally better.
Also, when querying linked servers, you get much better performance if you can do the processing on the remote server instead of pulling all the records over the wire just to filter locally.
Instead of this:
SELECT ...
FROM LinkserverDB.dbo.src_tab3;
Try this:
SELECT ...
FROM (SELECT * FROM OPENQUERY(LinkedServer,'SELECT col1, col2, col3 FROM LindServerDB.dbo.src_tab3')) t
March 27, 2018 at 1:00 pm
What does memory pressure look like on your server? Unless your server can handle the whole record set in memory, I would consider using permanent staging tables indexed to support your workload instead of table variables or temp tables. Using SSIS to load the staging tables may improve performance of the whole process depending on the resources available.
March 28, 2018 at 4:27 am
saptek9 - Tuesday, March 27, 2018 9:51 AMThank you. What about creating view instead table variables?
A view is nothing but a query. It doesn't store data (except materialized views, but those are different critters, note the different name), so it's going to act as a query, not as a temporary data store as table variables do. You need to determine if you need temporary storage or something else. If it's temporary storage, a view can't, doesn't, won't, replace that.
"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
March 28, 2018 at 4:30 am
Things like this destroy the possibility of performance enhancement
ON LTRIM(RTRIM(WP.ITEMID))
You can't use functions on columns like this and expect to see statistics use or index use because you have to perform the function on every single row in order to determine if the values match. If the data is this dirty, clean it.
"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
March 28, 2018 at 4:36 am
So, if we have to have linked queries (and eliminating these ought to be a goal), then we should at least push the filtering down to the linked servers. See all this stuff:
ROM @tab1 AS R
INNER JOIN @tab3 AS WP
ON R.ITEMID=WP.ITEMID
AND R.EVENTCODE = 600
INNER JOIN Configuration AS C
ON LTRIM(RTRIM(R.EVENTDATA2))=C.EventID
WHERE NOT EXISTS (SELECT * FROM tv_target_tab WHERE ItemID=LTRIM(RTRIM(WP.ITEMID)) AND EventID=LTRIM(RTRIM(R.EVENTDATA2)) AND Date=CAST(R.CREATED AS DATETIME)) --Only new items
That's filtering the data after you've moved it over to the table variables (and I agree with comments earlier, temporary tables, which have statistics, better support this type of behavior). Put those filters into your linked queries by using OPENQUERY instead of four part names.
"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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply