How to do a effective Join when querying Huge table with small tableHow to check if a column exists in a SQL Server table?How can a LEFT OUTER JOIN return more records than exist in the left table?SQL update query using joinsWhen should I use cross apply over inner join?How can I do an UPDATE statement with JOIN in SQL?How to 'insert if not exists' in MySQL?Update a table using JOIN in SQL Server?SQL Server: How to Join to first rowWhat are the options for storing hierarchical data in a relational database?How to Delete using INNER JOIN with SQL Server?
What is wrong with Justin Trudeau (or anyone) masquerading as Aladdin?
Is it really necessary to have a four hour meeting in Sprint planning?
Is it impolite to ask for halal food when traveling to and in Thailand?
Hiking with a mule or two?
Way of the bicycle
Performance for simple code that converts a RGB tuple to hex string
Do the villains know Batman has no superpowers?
Why does NASA publish all the results/data it gets?
How does this circuit start up?
Has my MacBook been hacked?
How to manage expenditure when billing cycles and paycheck cycles are not aligned?
Past tense of "greenlight"
Is it right to extend flaps only in the white arc?
Are there non JavaScript ways to hide HTML source code?
What benefits does the Power Word Kill spell have?
How to ask a man to not take up more than one seat on public transport while avoiding conflict?
Two trains move towards each other, a bird moves between them. How many trips can the bird make?
Resolving moral conflict
Guitar tuning (EADGBE), "perfect" fourths?
Creating raster where each cell records distance to sea?
To what extent is it worthwhile to report check fraud / refund scams?
Does Diablo III have a loot filter?
Would Taiwan and China's dispute be solved if Taiwan gave up being the Republic of China?
In a folk jam session, when asked which key my non-transposing chromatic instrument (like a violin) is in, what do I answer?
How to do a effective Join when querying Huge table with small table
How to check if a column exists in a SQL Server table?How can a LEFT OUTER JOIN return more records than exist in the left table?SQL update query using joinsWhen should I use cross apply over inner join?How can I do an UPDATE statement with JOIN in SQL?How to 'insert if not exists' in MySQL?Update a table using JOIN in SQL Server?SQL Server: How to Join to first rowWhat are the options for storing hierarchical data in a relational database?How to Delete using INNER JOIN with SQL Server?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I have 2 tables consider TABLE A and TABLE B .TABLE A have 5 million records and TABLE B have 20 K records . I'm joining the large table with small table where I checking TABLE A record with table B record each by each.
DECLARE @Large TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @Large (INN,OUTT)values (11356686,2101189)
INSERT INTO @Large (INN,OUTT)values (11369336,2101301)
INSERT INTO @Large (INN,OUTT)values (11358687,2101487)
INSERT INTO @Large (INN,OUTT)values (11369337,2101609)
DECLARE @small TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @small (INN,OUTT)values (11356686,2101189)
INSERT INTO @small (INN,OUTT)values (11369337,2101609)
LARGE :
INN OUTT
11356686 2101189
11369336 2101301
11358687 2101487
11369337 2101609
SMALL :
INN OUTT
11356686 2101189
11369337 2101609
My query :
Select CASE WHEN T.INN IS NULL THEN O ELSE 1 END from @LARGE T
LEFT JOIN @SMALLTT
ON T.INN = TT.INN AND T.OUTT = TT.OUTT
Coming to the tables they are well indexed and even no hints are there in Execution plan to add more indexes.
Can any one suggest me what should I do in the query level to get results more quicker like CROSS APPLY,HASH JOIN etc.;
sql
sql-server tsql asked Mar 28 at 16:10
mohan111mohan111
6,5052 gold badges19 silver badges34 bronze badges
|
show 10 more comments
I have 2 tables consider TABLE A and TABLE B .TABLE A have 5 million records and TABLE B have 20 K records . I'm joining the large table with small table where I checking TABLE A record with table B record each by each.
DECLARE @Large TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @Large (INN,OUTT)values (11356686,2101189)
INSERT INTO @Large (INN,OUTT)values (11369336,2101301)
INSERT INTO @Large (INN,OUTT)values (11358687,2101487)
INSERT INTO @Large (INN,OUTT)values (11369337,2101609)
DECLARE @small TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @small (INN,OUTT)values (11356686,2101189)
INSERT INTO @small (INN,OUTT)values (11369337,2101609)
LARGE :
INN OUTT
11356686 2101189
11369336 2101301
11358687 2101487
11369337 2101609
SMALL :
INN OUTT
11356686 2101189
11369337 2101609
My query :
Select CASE WHEN T.INN IS NULL THEN O ELSE 1 END from @LARGE T
LEFT JOIN @SMALLTT
ON T.INN = TT.INN AND T.OUTT = TT.OUTT
Coming to the tables they are well indexed and even no hints are there in Execution plan to add more indexes.
Can any one suggest me what should I do in the query level to get results more quicker like CROSS APPLY,HASH JOIN etc.;
sql
sql-server tsql asked Mar 28 at 16:10
mohan111mohan111
6,5052 gold badges19 silver badges34 bronze badges
If you could edit your question so that the tables will be called@smalland@largeand add a link to paste the plan it could probably help.
– Zohar Peled
Mar 28 at 16:18
Is there a problem here? The SQL Query Optimizer should figure out the best performance option on its own, providing there are adequate indexes and statistics are up to date. You never state that you're actually seeing a performance problem, or what the magnitude of any such problem is, or what your desired performance is.
– pmbAustin
Mar 28 at 16:21
@ZoharPeled I have edited the question as per your suggestion I have given sample records like this we have multiple tables and coming plan it is secured environment I can't able paste the plan in SO
– mohan111
Mar 28 at 16:23
@pmbAustin I have done full scan on the server and stats are upto date ..we are querying on indexed columns only
– mohan111
Mar 28 at 16:24
1
You're still not stating a problem. I don't see any problem. What PROBLEM are you having? Is it taking 5 hours to run? There's nothing wrong with your data or your update statement. SQL Server will "find the most effective way". That's what the query optimizer is for. If you're having an actual ISSUE, we'll need more information... like index definitions and a SQL Plan, to help you diagnose it. But I don'ts see any issues or problems here.
– pmbAustin
Mar 28 at 16:38
|
show 10 more comments
I have 2 tables consider TABLE A and TABLE B .TABLE A have 5 million records and TABLE B have 20 K records . I'm joining the large table with small table where I checking TABLE A record with table B record each by each.
DECLARE @Large TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @Large (INN,OUTT)values (11356686,2101189)
INSERT INTO @Large (INN,OUTT)values (11369336,2101301)
INSERT INTO @Large (INN,OUTT)values (11358687,2101487)
INSERT INTO @Large (INN,OUTT)values (11369337,2101609)
DECLARE @small TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @small (INN,OUTT)values (11356686,2101189)
INSERT INTO @small (INN,OUTT)values (11369337,2101609)
LARGE :
INN OUTT
11356686 2101189
11369336 2101301
11358687 2101487
11369337 2101609
SMALL :
INN OUTT
11356686 2101189
11369337 2101609
My query :
Select CASE WHEN T.INN IS NULL THEN O ELSE 1 END from @LARGE T
LEFT JOIN @SMALLTT
ON T.INN = TT.INN AND T.OUTT = TT.OUTT
Coming to the tables they are well indexed and even no hints are there in Execution plan to add more indexes.
Can any one suggest me what should I do in the query level to get results more quicker like CROSS APPLY,HASH JOIN etc.;
sql
sql-server tsql asked Mar 28 at 16:10
mohan111mohan111
6,5052 gold badges19 silver badges34 bronze badges
I have 2 tables consider TABLE A and TABLE B .TABLE A have 5 million records and TABLE B have 20 K records . I'm joining the large table with small table where I checking TABLE A record with table B record each by each.
DECLARE @Large TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @Large (INN,OUTT)values (11356686,2101189)
INSERT INTO @Large (INN,OUTT)values (11369336,2101301)
INSERT INTO @Large (INN,OUTT)values (11358687,2101487)
INSERT INTO @Large (INN,OUTT)values (11369337,2101609)
DECLARE @small TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @small (INN,OUTT)values (11356686,2101189)
INSERT INTO @small (INN,OUTT)values (11369337,2101609)
LARGE :
INN OUTT
11356686 2101189
11369336 2101301
11358687 2101487
11369337 2101609
SMALL :
INN OUTT
11356686 2101189
11369337 2101609
My query :
Select CASE WHEN T.INN IS NULL THEN O ELSE 1 END from @LARGE T
LEFT JOIN @SMALLTT
ON T.INN = TT.INN AND T.OUTT = TT.OUTT
Coming to the tables they are well indexed and even no hints are there in Execution plan to add more indexes.
Can any one suggest me what should I do in the query level to get results more quicker like CROSS APPLY,HASH JOIN etc.;
sql
sql-server tsql sql
sql-server tsql asked Mar 28 at 16:10
mohan111mohan111
6,5052 gold badges19 silver badges34 bronze badges
asked Mar 28 at 16:10
mohan111mohan111
6,5052 gold badges19 silver badges34 bronze badges
edited Mar 28 at 16:21
mohan111
asked Mar 28 at 16:10
mohan111mohan111
6,5052 gold badges19 silver badges34 bronze badges
asked Mar 28 at 16:10
mohan111mohan111
6,5052 gold badges19 silver badges34 bronze badges
asked Mar 28 at 16:10
mohan111mohan111
6,5052 gold badges19 silver badges34 bronze badges
6,5052 gold badges19 silver badges34 bronze badges
If you could edit your question so that the tables will be called@smalland@largeand add a link to paste the plan it could probably help.
– Zohar Peled
Mar 28 at 16:18
Is there a problem here? The SQL Query Optimizer should figure out the best performance option on its own, providing there are adequate indexes and statistics are up to date. You never state that you're actually seeing a performance problem, or what the magnitude of any such problem is, or what your desired performance is.
– pmbAustin
Mar 28 at 16:21
@ZoharPeled I have edited the question as per your suggestion I have given sample records like this we have multiple tables and coming plan it is secured environment I can't able paste the plan in SO
– mohan111
Mar 28 at 16:23
@pmbAustin I have done full scan on the server and stats are upto date ..we are querying on indexed columns only
– mohan111
Mar 28 at 16:24
1
You're still not stating a problem. I don't see any problem. What PROBLEM are you having? Is it taking 5 hours to run? There's nothing wrong with your data or your update statement. SQL Server will "find the most effective way". That's what the query optimizer is for. If you're having an actual ISSUE, we'll need more information... like index definitions and a SQL Plan, to help you diagnose it. But I don'ts see any issues or problems here.
– pmbAustin
Mar 28 at 16:38
|
show 10 more comments
If you could edit your question so that the tables will be called@smalland@largeand add a link to paste the plan it could probably help.
– Zohar Peled
Mar 28 at 16:18
Is there a problem here? The SQL Query Optimizer should figure out the best performance option on its own, providing there are adequate indexes and statistics are up to date. You never state that you're actually seeing a performance problem, or what the magnitude of any such problem is, or what your desired performance is.
– pmbAustin
Mar 28 at 16:21
@ZoharPeled I have edited the question as per your suggestion I have given sample records like this we have multiple tables and coming plan it is secured environment I can't able paste the plan in SO
– mohan111
Mar 28 at 16:23
@pmbAustin I have done full scan on the server and stats are upto date ..we are querying on indexed columns only
– mohan111
Mar 28 at 16:24
1
You're still not stating a problem. I don't see any problem. What PROBLEM are you having? Is it taking 5 hours to run? There's nothing wrong with your data or your update statement. SQL Server will "find the most effective way". That's what the query optimizer is for. If you're having an actual ISSUE, we'll need more information... like index definitions and a SQL Plan, to help you diagnose it. But I don'ts see any issues or problems here.
– pmbAustin
Mar 28 at 16:38
If you could edit your question so that the tables will be called
@small and @large and add a link to paste the plan it could probably help.– Zohar Peled
Mar 28 at 16:18
If you could edit your question so that the tables will be called
@small and @large and add a link to paste the plan it could probably help.– Zohar Peled
Mar 28 at 16:18
Is there a problem here? The SQL Query Optimizer should figure out the best performance option on its own, providing there are adequate indexes and statistics are up to date. You never state that you're actually seeing a performance problem, or what the magnitude of any such problem is, or what your desired performance is.
– pmbAustin
Mar 28 at 16:21
Is there a problem here? The SQL Query Optimizer should figure out the best performance option on its own, providing there are adequate indexes and statistics are up to date. You never state that you're actually seeing a performance problem, or what the magnitude of any such problem is, or what your desired performance is.
– pmbAustin
Mar 28 at 16:21
@ZoharPeled I have edited the question as per your suggestion I have given sample records like this we have multiple tables and coming plan it is secured environment I can't able paste the plan in SO
– mohan111
Mar 28 at 16:23
@ZoharPeled I have edited the question as per your suggestion I have given sample records like this we have multiple tables and coming plan it is secured environment I can't able paste the plan in SO
– mohan111
Mar 28 at 16:23
@pmbAustin I have done full scan on the server and stats are upto date ..we are querying on indexed columns only
– mohan111
Mar 28 at 16:24
@pmbAustin I have done full scan on the server and stats are upto date ..we are querying on indexed columns only
– mohan111
Mar 28 at 16:24
1
1
You're still not stating a problem. I don't see any problem. What PROBLEM are you having? Is it taking 5 hours to run? There's nothing wrong with your data or your update statement. SQL Server will "find the most effective way". That's what the query optimizer is for. If you're having an actual ISSUE, we'll need more information... like index definitions and a SQL Plan, to help you diagnose it. But I don'ts see any issues or problems here.
– pmbAustin
Mar 28 at 16:38
You're still not stating a problem. I don't see any problem. What PROBLEM are you having? Is it taking 5 hours to run? There's nothing wrong with your data or your update statement. SQL Server will "find the most effective way". That's what the query optimizer is for. If you're having an actual ISSUE, we'll need more information... like index definitions and a SQL Plan, to help you diagnose it. But I don'ts see any issues or problems here.
– pmbAustin
Mar 28 at 16:38
|
show 10 more comments
1 Answer
1
active
oldest
votes
If the goal is to simply identify which records are shared between tables AND you can modify the @Large table, then consider keeping a flag on @Large updated with matches:
DECLARE @Large TABLE (INN BIGINT, OUTT BIGINT, HasMatch BIT DEFAULT(0))
INSERT INTO @Large (INN,OUTT)values (11356686,2101189)
INSERT INTO @Large (INN,OUTT)values (11369336,2101301)
INSERT INTO @Large (INN,OUTT)values (11358687,2101487)
INSERT INTO @Large (INN,OUTT)values (11369337,2101609)
DECLARE @small TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @small (INN,OUTT)values (11356686,2101189)
INSERT INTO @small (INN,OUTT)values (11369337,2101609)
UPDATE @Large SET HasMatch = 0
UPDATE l
SET l.HasMatch = 1
FROM @Large l INNER JOIN @Small s ON l.INN = s.INN AND l.OUTT = s.OUTT
SELECT INN, OUTT, HasMatch FROM @Large
Again, this assumes you have some control over the table structure/data and can execute the UPDATE statements to populate the HasMatch column.
The upside - you get to take advantage of an INNER JOIN and all its inherent benefits (there are inherent benefits, right?).
answered Mar 28 at 16:38
Forty3Forty3
2,0641 gold badge10 silver badges17 bronze badges
Actually I have indicator column to filter the records in Large table
– mohan111
Mar 28 at 16:41
@mohan111 that's not included in the question. Post the actual table schema and query, not a simplified version that may not have any relation to the problem. In any case aBITcolumn is useless. Its selectivity is so bad that including it in an index is meaningless - when half the entries are 1, it's cheaper to just scan the entire table than go through an index.
– Panagiotis Kanavos
Mar 28 at 16:50
add a comment
|
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55402259%2fhow-to-do-a-effective-join-when-querying-huge-table-with-small-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If the goal is to simply identify which records are shared between tables AND you can modify the @Large table, then consider keeping a flag on @Large updated with matches:
DECLARE @Large TABLE (INN BIGINT, OUTT BIGINT, HasMatch BIT DEFAULT(0))
INSERT INTO @Large (INN,OUTT)values (11356686,2101189)
INSERT INTO @Large (INN,OUTT)values (11369336,2101301)
INSERT INTO @Large (INN,OUTT)values (11358687,2101487)
INSERT INTO @Large (INN,OUTT)values (11369337,2101609)
DECLARE @small TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @small (INN,OUTT)values (11356686,2101189)
INSERT INTO @small (INN,OUTT)values (11369337,2101609)
UPDATE @Large SET HasMatch = 0
UPDATE l
SET l.HasMatch = 1
FROM @Large l INNER JOIN @Small s ON l.INN = s.INN AND l.OUTT = s.OUTT
SELECT INN, OUTT, HasMatch FROM @Large
Again, this assumes you have some control over the table structure/data and can execute the UPDATE statements to populate the HasMatch column.
The upside - you get to take advantage of an INNER JOIN and all its inherent benefits (there are inherent benefits, right?).
answered Mar 28 at 16:38
Forty3Forty3
2,0641 gold badge10 silver badges17 bronze badges
Actually I have indicator column to filter the records in Large table
– mohan111
Mar 28 at 16:41
@mohan111 that's not included in the question. Post the actual table schema and query, not a simplified version that may not have any relation to the problem. In any case aBITcolumn is useless. Its selectivity is so bad that including it in an index is meaningless - when half the entries are 1, it's cheaper to just scan the entire table than go through an index.
– Panagiotis Kanavos
Mar 28 at 16:50
add a comment
|
If the goal is to simply identify which records are shared between tables AND you can modify the @Large table, then consider keeping a flag on @Large updated with matches:
DECLARE @Large TABLE (INN BIGINT, OUTT BIGINT, HasMatch BIT DEFAULT(0))
INSERT INTO @Large (INN,OUTT)values (11356686,2101189)
INSERT INTO @Large (INN,OUTT)values (11369336,2101301)
INSERT INTO @Large (INN,OUTT)values (11358687,2101487)
INSERT INTO @Large (INN,OUTT)values (11369337,2101609)
DECLARE @small TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @small (INN,OUTT)values (11356686,2101189)
INSERT INTO @small (INN,OUTT)values (11369337,2101609)
UPDATE @Large SET HasMatch = 0
UPDATE l
SET l.HasMatch = 1
FROM @Large l INNER JOIN @Small s ON l.INN = s.INN AND l.OUTT = s.OUTT
SELECT INN, OUTT, HasMatch FROM @Large
Again, this assumes you have some control over the table structure/data and can execute the UPDATE statements to populate the HasMatch column.
The upside - you get to take advantage of an INNER JOIN and all its inherent benefits (there are inherent benefits, right?).
answered Mar 28 at 16:38
Forty3Forty3
2,0641 gold badge10 silver badges17 bronze badges
Actually I have indicator column to filter the records in Large table
– mohan111
Mar 28 at 16:41
@mohan111 that's not included in the question. Post the actual table schema and query, not a simplified version that may not have any relation to the problem. In any case aBITcolumn is useless. Its selectivity is so bad that including it in an index is meaningless - when half the entries are 1, it's cheaper to just scan the entire table than go through an index.
– Panagiotis Kanavos
Mar 28 at 16:50
add a comment
|
If the goal is to simply identify which records are shared between tables AND you can modify the @Large table, then consider keeping a flag on @Large updated with matches:
DECLARE @Large TABLE (INN BIGINT, OUTT BIGINT, HasMatch BIT DEFAULT(0))
INSERT INTO @Large (INN,OUTT)values (11356686,2101189)
INSERT INTO @Large (INN,OUTT)values (11369336,2101301)
INSERT INTO @Large (INN,OUTT)values (11358687,2101487)
INSERT INTO @Large (INN,OUTT)values (11369337,2101609)
DECLARE @small TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @small (INN,OUTT)values (11356686,2101189)
INSERT INTO @small (INN,OUTT)values (11369337,2101609)
UPDATE @Large SET HasMatch = 0
UPDATE l
SET l.HasMatch = 1
FROM @Large l INNER JOIN @Small s ON l.INN = s.INN AND l.OUTT = s.OUTT
SELECT INN, OUTT, HasMatch FROM @Large
Again, this assumes you have some control over the table structure/data and can execute the UPDATE statements to populate the HasMatch column.
The upside - you get to take advantage of an INNER JOIN and all its inherent benefits (there are inherent benefits, right?).
answered Mar 28 at 16:38
Forty3Forty3
2,0641 gold badge10 silver badges17 bronze badges
If the goal is to simply identify which records are shared between tables AND you can modify the @Large table, then consider keeping a flag on @Large updated with matches:
DECLARE @Large TABLE (INN BIGINT, OUTT BIGINT, HasMatch BIT DEFAULT(0))
INSERT INTO @Large (INN,OUTT)values (11356686,2101189)
INSERT INTO @Large (INN,OUTT)values (11369336,2101301)
INSERT INTO @Large (INN,OUTT)values (11358687,2101487)
INSERT INTO @Large (INN,OUTT)values (11369337,2101609)
DECLARE @small TABLE (INN BIGINT,OUTT BIGINT )
INSERT INTO @small (INN,OUTT)values (11356686,2101189)
INSERT INTO @small (INN,OUTT)values (11369337,2101609)
UPDATE @Large SET HasMatch = 0
UPDATE l
SET l.HasMatch = 1
FROM @Large l INNER JOIN @Small s ON l.INN = s.INN AND l.OUTT = s.OUTT
SELECT INN, OUTT, HasMatch FROM @Large
Again, this assumes you have some control over the table structure/data and can execute the UPDATE statements to populate the HasMatch column.
The upside - you get to take advantage of an INNER JOIN and all its inherent benefits (there are inherent benefits, right?).
answered Mar 28 at 16:38
Forty3Forty3
2,0641 gold badge10 silver badges17 bronze badges
answered Mar 28 at 16:38
Forty3Forty3
2,0641 gold badge10 silver badges17 bronze badges
answered Mar 28 at 16:38
Forty3Forty3
2,0641 gold badge10 silver badges17 bronze badges
answered Mar 28 at 16:38
Forty3Forty3
2,0641 gold badge10 silver badges17 bronze badges
2,0641 gold badge10 silver badges17 bronze badges
Actually I have indicator column to filter the records in Large table
– mohan111
Mar 28 at 16:41
@mohan111 that's not included in the question. Post the actual table schema and query, not a simplified version that may not have any relation to the problem. In any case aBITcolumn is useless. Its selectivity is so bad that including it in an index is meaningless - when half the entries are 1, it's cheaper to just scan the entire table than go through an index.
– Panagiotis Kanavos
Mar 28 at 16:50
add a comment
|
Actually I have indicator column to filter the records in Large table
– mohan111
Mar 28 at 16:41
@mohan111 that's not included in the question. Post the actual table schema and query, not a simplified version that may not have any relation to the problem. In any case aBITcolumn is useless. Its selectivity is so bad that including it in an index is meaningless - when half the entries are 1, it's cheaper to just scan the entire table than go through an index.
– Panagiotis Kanavos
Mar 28 at 16:50
Actually I have indicator column to filter the records in Large table
– mohan111
Mar 28 at 16:41
Actually I have indicator column to filter the records in Large table
– mohan111
Mar 28 at 16:41
@mohan111 that's not included in the question. Post the actual table schema and query, not a simplified version that may not have any relation to the problem. In any case a
BIT column is useless. Its selectivity is so bad that including it in an index is meaningless - when half the entries are 1, it's cheaper to just scan the entire table than go through an index.– Panagiotis Kanavos
Mar 28 at 16:50
@mohan111 that's not included in the question. Post the actual table schema and query, not a simplified version that may not have any relation to the problem. In any case a
BIT column is useless. Its selectivity is so bad that including it in an index is meaningless - when half the entries are 1, it's cheaper to just scan the entire table than go through an index.– Panagiotis Kanavos
Mar 28 at 16:50
add a comment
|
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55402259%2fhow-to-do-a-effective-join-when-querying-huge-table-with-small-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If you could edit your question so that the tables will be called
@smalland@largeand add a link to paste the plan it could probably help.– Zohar Peled
Mar 28 at 16:18
Is there a problem here? The SQL Query Optimizer should figure out the best performance option on its own, providing there are adequate indexes and statistics are up to date. You never state that you're actually seeing a performance problem, or what the magnitude of any such problem is, or what your desired performance is.
– pmbAustin
Mar 28 at 16:21
@ZoharPeled I have edited the question as per your suggestion I have given sample records like this we have multiple tables and coming plan it is secured environment I can't able paste the plan in SO
– mohan111
Mar 28 at 16:23
@pmbAustin I have done full scan on the server and stats are upto date ..we are querying on indexed columns only
– mohan111
Mar 28 at 16:24
1
You're still not stating a problem. I don't see any problem. What PROBLEM are you having? Is it taking 5 hours to run? There's nothing wrong with your data or your update statement. SQL Server will "find the most effective way". That's what the query optimizer is for. If you're having an actual ISSUE, we'll need more information... like index definitions and a SQL Plan, to help you diagnose it. But I don'ts see any issues or problems here.
– pmbAustin
Mar 28 at 16:38