How can we optimize ADF pipelines that uses control tables and lookup activities

Aditya Singh Rathore 195 Reputation points
2026-09-21T11:59:55.3033333+00:00

Hi There ,

I am using ADF pipelines for data migration from on prem to azure , i had been doing it from a long and the thing i face most of the time is when we use control table and look up activities , the pipelines became slow . I just want to know how can we optimize our pipeline and reduce the execution time .

Azure Data Factory
Azure Data Factory

An Azure service for ingesting, preparing, and transforming data at scale.

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
kagiyama yutaka 5,575 Reputation points
2026-09-21T14:33:43.04+00:00

I think ADF Lookup runs sync on the IR and calling the control table many times just adds wait, and the control row is better taken once at the start and passed through a pipeline parameter so later activities do not hit the table again.

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Senthil kumar 2,580 Reputation points
2026-09-21T13:23:22.0566667+00:00

Hi @Aditya Singh Rathore

Root cause :

May be lookup and less foreach batch count.

Solutions :

1 . Replace Lookup with Cached Configuration

Lookup is slow because it:

Spins up compute

Reads metadata

Waits for IR

Serializes JSON

Runs synchronously

Instead, use:

Azure SQL or Azure Table Storage with a pre-cached config

Get Metadata + Set Variable

Pipeline parameters passed from trigger

This removes 80% of Lookup overhead.

2.ForEach → Batch count = 20 (or higher)

3.Instead of:

Lookup → Condition → Copy

Use:

Stored Procedure → Return JSON → Set Variable → Copy

Stored procedures are 10–20× faster than Lookup for control tablesInstead of:

Lookup → Condition → Copy

Use:

Stored Procedure → Return JSON → Set Variable → Copy

Stored procedures are 10–20× faster than Lookup for control tables

4.For large datasets, use:

Azure SQL staging

Synapse staging

ADLS staging

This offloads heavy lifting to the database engine.

Thanks

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest