March 10, 2016 at 7:08 pm
Hello,
have 2 tables from linked server.
result is 1 : n, but in case some issues can be n:n, but this is exception..
1) I used @tables for insert data from linked server ( still without problem )
2) want to get result where create date from second table is higher or same for 48second than create date from first table...
on test what I will have is logical reads
(1 row(s) affected)
Table 'Worktable'. Scan count 10, logical reads 41
Table '#AD157B2E'. Scan count 1, logical reads 1,
Table '#AE099F67'. Scan count 1, logical reads 1,
but on real server is higher
Table 'Worktable'. Scan count 2594, logical reads 127551
Table '#B352D20D'. Scan count 1, logical reads 269,
Table '#B446F646'. Scan count 1, logical reads 16,
anybody has idea how to do better?
DECLARE @tmp_RFI TABLE
(
COMMIT_NO CHAR(4),
BODY_NO VARCHAR(12),
LINE_CODE CHAR(5),
STATION_ID VARCHAR(5),
CREATE_DTTM DATETIME
)
DECLARE @tmp_NG TABLE
(
CommitNo CHAR(4),
BodyNo VARCHAR(12),
LineCode CHAR(5),
StationId CHAR(5),
CreateDttm DATETIME
)
INSERT INTO @tmp_RFI
SELECT '1124','AAA 111111','AAAAA','AA-07','Mar 11 2016 2:48AM' UNION ALL
SELECT '1124','AAA 111111','AAAAA','AA-07','Mar 11 2016 2:48AM'UNION ALL
SELECT '1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:40AM'UNION ALL
SELECT '1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:40AM'UNION ALL
SELECT '1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:40AM'UNION ALL
SELECT '1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:40AM' UNION ALL
SELECT '1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:40AM' UNION ALL
SELECT '1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:40AM' UNION ALL
SELECT '1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:40AM' UNION ALL
SELECT '1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:40AM'
INSERT INTO @tmp_NG
SELECT'1124','AAA 111111','AAAAA','AA-07','Mar 11 2016 2:50AM' UNION ALL
SELECT'1124','AAA 111111','AAAAA','AA-07','Mar 11 2016 2:49AM' UNION ALL
SELECT'1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:42AM' UNION ALL
SELECT'1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:42AM' UNION ALL
SELECT'1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:41AM' UNION ALL
SELECT'1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:41AM' UNION ALL
SELECT'1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:41AM' UNION ALL
SELECT'1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:41AM' UNION ALL
SELECT'1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:41AM' UNION ALL
SELECT'1162','BBB 222222','BBBBB','BB-03','Mar 11 2016 2:41AM'
SELECT COUNT(*) FROM @tmp_RFI AS rf
JOIN @tmp_NG AS ng
ON ng.[BodyNo] = rf.[BODY_NO]
AND ng.[StationId] = rf.[STATION_ID]
AND ng.[LineCode] = rf.[LINE_CODE]
AND ng.[CreateDttm] >= DATEADD(SECOND, 48, rf.[CREATE_DTTM])
I found one solution with INNER HASH
on test query here
(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0,
Table '#B0C8AFDF'. Scan count 1, logical reads 1,
Table '#AFD48BA6'. Scan count 1, logical reads 1,
on real server is like this
Table 'Worktable'. Scan count 0, logical reads 0,
Table '#BAD96EC0'. Scan count 1, logical reads 15,
Table '#B9E54A87'. Scan count 1, logical reads 272
Thanks
March 11, 2016 at 2:08 am
If all of the objects referenced in the query are remote and on the same server, then run the query on the remote server. The two obvious ways to do this are by putting the query into a runnable container object such as a stored procedure on the remote server, or using OPENQUERY in your local query. If you choose the former route then you have the option to tune the query on the remote server.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2016 at 10:23 am
If you decide to run it locally, clustering the tables to match the join logic will help:
IF OBJECT_ID('tempdb.dbop.#RFI') IS NOT NULL
DROP TABLE #RFI
CREATE TABLE #RFI
(
COMMIT_NO CHAR(4),
BODY_NO VARCHAR(12),
LINE_CODE CHAR(5),
STATION_ID VARCHAR(5),
CREATE_DTTM DATETIME
)
CREATE CLUSTERED INDEX RFI__CL ON #RFI ( STATION_ID, BODY_NO, LINE_CODE, CREATE_DTTM ) WITH ( FILLFACTOR = 100 );
IF OBJECT_ID('tempdb.dbop.#NG') IS NOT NULL
DROP TABLE #NG
CREATE TABLE #NG
(
CommitNo CHAR(4),
BodyNo VARCHAR(12),
LineCode CHAR(5),
StationId CHAR(5),
CreateDttm DATETIME
)
CREATE CLUSTERED INDEX NG__CL ON #NG ( StationId, BodyNo, LineCode, CreateDttm ) WITH ( FILLFACTOR = 100 );
INSERT INTO #RFI
...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 11, 2016 at 2:53 pm
.
March 15, 2016 at 12:08 am
Seventy five percent of our skin is comprised of water and collagen. Our skin is exposed to harsh UVA and UVB radiation resulting in age spots, fine lines, and wrinkles. As we age, our bodies produce less and less collagen, leading to the formation of wrinkles and fine lines. Most anti-aging products use fragments of hydrolyzed collagen containing molecules too large for the skin with conventional formulas. Renue Derma[/url] breakthrough formula delivers whole collagen molecules to the skin. The peptide-rich wrinkle serum is applied to the skin, rebuilding and rejuvenating the skin. http://buyskincareproducts.org/renue-derma/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply