CODEWITHSUNDEEP
sqlsql-serverinsert
Martian Marcera
Martian Marcera

Reputation: 47

MSSQL - Question about how insert queries run

We have two tables we want to merge. Say, table1 and table2. They have the exact same columns, and the exact same purpose. The difference being table2 having newer data. We used a query that uses LEFT JOIN to find the the rows that are common between them, and skip those rows while merging. The problem is this. both tables have 500M rows.

When we ran the query, it kept going on and on. For an hour it just kept running. We were certain this was because of the large number of rows.

But when we wanted to see how many rows were already inserted to table2, we ran the code select count(*) from table2, it gave us the exact same row count of table2 as when we started.

Our questions is, is that how it's supposed to be? Do the rows get inserted all at the same time after all the matches have been found?

Upvotes: 1

Views: 57

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

No data are inserted or updated one by one.

I have no idea how it is related with "Select count(*) from table2 WITH (NOLOCK) "

Join condition is taking too long to produce Resultset which will be use by insert operator .So actually there is no insert because no resultset is being produce.

Join query is taking too long because Left Join condition produces very very high cardinality estimate.

so one has to fix Join condition first.

for that need other info like Table schema ,Data type and length and existing index,requirement.

Upvotes: 0

SQL_M
SQL_M

Reputation: 2475

If you would like to read uncommited data, than the count should me modified, like this:

select count(*) from table2 WITH (NOLOCK)

NOLOCK is over-used, but in this specific scenario, it might be handy.

Upvotes: 2

Related Questions