Fastest way to copy redis data to sql server

Question

i have a redis with more than 10M rows, i want to copy all data to sql server db, currently i wrote a nodejs script to transfer data as below


redis.scan(cursor, "MATCH", "*_*", "COUNT", "100", function (err, res) {
    var keys = res[1];
    cursor = res[0];
    keys.forEach(function (id) {
        redis.hgetall(id, function (err, items) {
            let splited = Object.keys(items)[0].split("#");

            let data = {
                customerId: splited[0],
                orderId: splited[1],
            };

            sql.connect(config).then((pool) => {

                pool.request().query(
                    `INSERT INTO [dbo].[Bi]
                          ([CustomerId]
                          ,[OrderId]
                          ,[AddressId])
                    SELECT
                          ${data.customerId}
                          ,${data.orderId}
                          ,[dbo].[Address].Id
                    FROM [dbo].[Address] where CustomerId = ${data.customerId}`
                )
            })
        })
    })
})

but it takes 1 day to complete, so how should i optimize the script or is there any alternative way to transfer data?

Does this answer your question? [Bulk inserting with Node mssql package](https://stackoverflow.com/questions/43663017/bulk-inserting-with-node-mssql-package) — Charlieface, Mar 10 '21 at 12:36
Is it a one time or do you want to sync it on a regular base to MySQL? — Guy Korland, Mar 10 '21 at 13:47
@GuyKorland i want to run it regulary, probably every 3 days — sajjad kalantari, Mar 11 '21 at 16:58
@Charlieface tnx but no, because i get "Address.Id" in the query from another table — sajjad kalantari, Mar 11 '21 at 16:59
But you could load up a temp table, put an index on it and join on that. `10M data` means 10 megabyte? 10 million rows? What are your indexes? — Charlieface, Mar 11 '21 at 17:02
@Charlieface 10M rows, i dont get what do you mean by index, but i want to join with address table on customerId — sajjad kalantari, Mar 11 '21 at 17:28
@Charlieface cool but if i create a temp table like [this](https://www.npmjs.com/package/mssql#bulk-table-options-callback), how can i join to AddressTable in the bulk — sajjad kalantari, Mar 11 '21 at 17:35
Create a local temp table (single `#`), bulk insert into it, add a good index (looks like `CustomerId, OrderId` would work well), then execute a [standard joined insert](https://stackoverflow.com/questions/44469503/sql-insert-into-with-inner-join). Your temp table will be dropped when the connection is closed — Charlieface, Mar 11 '21 at 17:44
@Charlieface i got your point, that should work, i will try it, tnxx — sajjad kalantari, Mar 11 '21 at 19:06

score 2 · Answer 1 · answered Mar 11 '21 at 22:43

The Redis Module RedisGears has a recipe called rgsyncenter link description here using this module you can make sure each update to Redis is being synchronized back to your SQLServer.

See an example here: https://github.com/RedisGears/rgsync/tree/master/examples/mssql

Fastest way to copy redis data to sql server

1 Answers1