Nested Batch

Hi,

I have looked through other nested batch queries and some of the responses have helped but I am still having issues. My scenario is, I have 6 items (1000’s in reality), I want to first split the 6 into a batch of 3 and then process those 3 items in a batch size of one, then get the next 3 and again process them in a batch size of one.

This is the flow:

Only the first batch of 3 gets processed. I would like the outer batch to run, get the first 3 items, pass it to the inner batch, which would then process each one of those 3 items. When the inner batch has processed the first 3 items, the loop should return to the outer batch, fetch the next 3 items and the inner batch would again process these 3 items individually.

Here is the workflow code:

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n\t{\n\t\t\"json\": {\n\t\t\t\"Products\": [\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP1\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10001\",\n\t\t\t\t\t\"Name\": \"Product Name A\",\n\t\t\t\t\t\"Category\": \"GATEGORY1\",\n\t\t\t\t\t\"Colour\": \"White\",\n\t\t\t\t\t\"Size\": \"Small\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP2\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10002\",\n\t\t\t\t\t\"Name\": \"Product Name B\",\n\t\t\t\t\t\"Category\": \"GATEGORY2\",\n\t\t\t\t\t\"Colour\": \"Black\",\n\t\t\t\t\t\"Size\": \"Medium\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP1\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10003\",\n\t\t\t\t\t\"Name\": \"Product Name C\",\n\t\t\t\t\t\"Category\": \"GATEGORY3\",\n\t\t\t\t\t\"Colour\": \"Blue\",\n\t\t\t\t\t\"Size\": \"Large\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP2\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10004\",\n\t\t\t\t\t\"Name\": \"Product Name D\",\n\t\t\t\t\t\"Category\": \"GATEGORY1\",\n\t\t\t\t\t\"Colour\": \"Green\",\n\t\t\t\t\t\"Size\": \"Small\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP1\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10005\",\n\t\t\t\t\t\"Name\": \"Product Name E\",\n\t\t\t\t\t\"Category\": \"GATEGORY3\",\n\t\t\t\t\t\"Colour\": \"Yellow\",\n\t\t\t\t\t\"Size\": \"Medium\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP2\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10006\",\n\t\t\t\t\t\"Name\": \"Product Name F\",\n\t\t\t\t\t\"Category\": \"GATEGORY3\",\n\t\t\t\t\t\"Colour\": \"Orange\",\n\t\t\t\t\t\"Size\": \"Large\"\n\t\t\t\t}\n\t\t\t]\n\t\t}\n\t}\n]\n"
      },
      "name": "01.Mock Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        400,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return Object.entries(items[0].json.Products).map(([key, value]) => {\n  return {\n    json: value\n  }\n})\n"
      },
      "name": "02.Split Products",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        550,
        300
      ]
    },
    {
      "parameters": {
        "batchSize": 3,
        "options": {}
      },
      "name": "03.Outer Batch",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        700,
        300
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "04.Inner Batch",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        850,
        300
      ]
    },
    {
      "parameters": {
        "options": {}
      },
      "name": "05.Set Value",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1000,
        300
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$node[\"04.Inner Batch\"].context[\"noItemsLeft\"]}}",
              "operation": "notEqual",
              "value2": "false"
            }
          ]
        }
      },
      "name": "06.Inner Items Left",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1000,
        490
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$node[\"03.Outer Batch\"].context[\"noItemsLeft\"]}}",
              "operation": "notEqual",
              "value2": "false"
            }
          ]
        }
      },
      "name": "07.Outer Items Left",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        770,
        630
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "01.Mock Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "01.Mock Data": {
      "main": [
        [
          {
            "node": "02.Split Products",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "02.Split Products": {
      "main": [
        [
          {
            "node": "03.Outer Batch",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "03.Outer Batch": {
      "main": [
        [
          {
            "node": "04.Inner Batch",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "04.Inner Batch": {
      "main": [
        [
          {
            "node": "05.Set Value",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "05.Set Value": {
      "main": [
        [
          {
            "node": "06.Inner Items Left",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "06.Inner Items Left": {
      "main": [
        [
          {
            "node": "04.Inner Batch",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "07.Outer Items Left",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "07.Outer Items Left": {
      "main": [
        [
          {
            "node": "03.Outer Batch",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Any help or tips on making the flow more efficient would be greatly appreciated.

what are you trying to achieve here, is this posting to a HTTP node, as you can do some batch sizes in there are wait times between requests, maybe that might be of use?

Hi @RedPacketSec,

I am getting product data (1000’s) from an API and trying to create them in the product table in my SQL DB. If I process all at once, it does work but it gets quite slow so I wanted to do it in smaller batches.

Hey @Suratwala,

What bit gets slow the SQL Process or n8n? Rather than making a batch then another batch it may be better to just create one batch of say 100 and see how that goes and tweak and as needed.

How do you have n8n deployed as well some of it could come down to hardware limitations.

Hi @jon,

From what I could see, the SQL insert process gets slower. The machine n8n is running on is very well spec’ed so I don’t think it’s a resource issue. If you see my workflow above, it has just 6 items but not sure how else to do it so that all items get processed but in smaller batches and not in one go.

Hey @Suratwala,

Is it slow with just the 6 items? Which SQL implementation are you using as well is it MS SQL, MySQL, Postgres or something else?

Hey @Suratwala,

this should work

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n\t{\n\t\t\"json\": {\n\t\t\t\"Products\": [\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP1\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10001\",\n\t\t\t\t\t\"Name\": \"Product Name A\",\n\t\t\t\t\t\"Category\": \"GATEGORY1\",\n\t\t\t\t\t\"Colour\": \"White\",\n\t\t\t\t\t\"Size\": \"Small\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP2\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10002\",\n\t\t\t\t\t\"Name\": \"Product Name B\",\n\t\t\t\t\t\"Category\": \"GATEGORY2\",\n\t\t\t\t\t\"Colour\": \"Black\",\n\t\t\t\t\t\"Size\": \"Medium\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP1\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10003\",\n\t\t\t\t\t\"Name\": \"Product Name C\",\n\t\t\t\t\t\"Category\": \"GATEGORY3\",\n\t\t\t\t\t\"Colour\": \"Blue\",\n\t\t\t\t\t\"Size\": \"Large\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP2\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10004\",\n\t\t\t\t\t\"Name\": \"Product Name D\",\n\t\t\t\t\t\"Category\": \"GATEGORY1\",\n\t\t\t\t\t\"Colour\": \"Green\",\n\t\t\t\t\t\"Size\": \"Small\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP1\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10005\",\n\t\t\t\t\t\"Name\": \"Product Name E\",\n\t\t\t\t\t\"Category\": \"GATEGORY3\",\n\t\t\t\t\t\"Colour\": \"Yellow\",\n\t\t\t\t\t\"Size\": \"Medium\"\n\t\t\t\t},\n\t\t\t\t{\n\t\t\t\t\t\"Group\": [\n\t\t\t\t\t\t\"GROUP2\"\n\t\t\t\t\t],\n\t\t\t\t\t\"ID\": \"10006\",\n\t\t\t\t\t\"Name\": \"Product Name F\",\n\t\t\t\t\t\"Category\": \"GATEGORY3\",\n\t\t\t\t\t\"Colour\": \"Orange\",\n\t\t\t\t\t\"Size\": \"Large\"\n\t\t\t\t}\n\t\t\t]\n\t\t}\n\t}\n]\n"
      },
      "name": "01.Mock Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        400,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return Object.entries(items[0].json.Products).map(([key, value]) => {\n  return {\n    json: value\n  }\n})\n"
      },
      "name": "02.Split Products",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        550,
        300
      ]
    },
    {
      "parameters": {
        "batchSize": 3,
        "options": {}
      },
      "name": "03.Outer Batch",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        700,
        300
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {
          "reset": "={{$node[\"04.Inner Batch\"].context[\"noItemsLeft\"]}}"
        }
      },
      "name": "04.Inner Batch",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        850,
        300
      ]
    },
    {
      "parameters": {
        "options": {}
      },
      "name": "05.Set Value",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1000,
        300
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [],
          "boolean": [
            {
              "value1": "={{$node[\"04.Inner Batch\"].context[\"noItemsLeft\"]}}"
            }
          ]
        }
      },
      "name": "06.Inner Items Left",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1000,
        490
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "01.Mock Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "01.Mock Data": {
      "main": [
        [
          {
            "node": "02.Split Products",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "02.Split Products": {
      "main": [
        [
          {
            "node": "03.Outer Batch",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "03.Outer Batch": {
      "main": [
        [
          {
            "node": "04.Inner Batch",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "04.Inner Batch": {
      "main": [
        [
          {
            "node": "05.Set Value",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "05.Set Value": {
      "main": [
        [
          {
            "node": "06.Inner Items Left",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "06.Inner Items Left": {
      "main": [
        [
          {
            "node": "04.Inner Batch",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "03.Outer Batch",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Hi @jon,

I am using MS SQL. Sorry, we can ignore the speed issue. It was the save execution progress option that was making it slow. Thank you very much for your help.

1 Like

Hi @Rugia,

Thank you so much. That is exactly what I was trying to do. Much appreciated.

1 Like