performance - Having a lot of SQL query conditions

Question

I am updating remote MySQL database by comparing row by row from local MSSQL one. Idea was to do update in 3 steps:

1: select all ID's from local and execute following query on remote:

delete from REMOTE_DATABASE.TABLE where ID not in 
( list of LOCAL_DATABASE.TABLE ID's linked by OR condition )

2: select ID's from LOCAL_DATABASE.TABLE that are not exist on remote and insert

3: update rows that have both tables.

My question is regarding first step: I have 50000+ products. I am working in C# and I could join all ID's in string but I was wondering can query with 50000 ID's linked by OR condition be executed? Is it a wrong approach?

Answer 1

So I have this done this before. A long time ago though so I might miss a step but I hope you will get the idea.

1. Setup a linked server on the MSSQL database to the MySQL database. See this Linked MySQL Server article

2. SELECT all the table you want from the linked server into a temp table on the MSSQL SQL. Something like SELECT * INTO #temp FROM linkedserver.tablename however it would be better to create a proper temp table and index the columns you will be joining on i.e.

   CREATE TABLE #Test
   (
   ID INT PRIMARY KEY NOT NULL
   )
   INSERT INTO #Test
   SELECT * FROM linkedserver.tablename
   

3. Do a LEFT/RIGHT JOIN to find new ID on the local machine and insert them into the remote server via linked server. See this link for more information on how to use LEFT/RIGHT joins to get the new records when comparing two tables Using Left join to find new rows

4. Update the remote server with a UPDATE statement and JOIN in it. So basically using a INNER JOIN do a update to the remote server with the values in the temp table.

Now there might be some errors you run into with the syntax post them here and I can try and resolve them for you. However I have used this technique to synchronize between MySQL and MSSQL servers and it works pretty well. As it is SETS based and not RBAR based it is very fast as well.