How the database excute works to have the best performance?Tips?

[walkerline117]

Hi,

I am using Oracle/SQL execute snaps a lot to run some complicated queries.

Can someone explain to me:

1. In my case, my query would return 100000+ documents/rows, what I found is that during the execution, the oracle/sql execution snap will first output first 2000 rows as output and gradually output abt 6 docs/s to next snap. To me it is very slow, why does it act this way? Should it output all 100000+ rows together to next snap as input?

ref pipeline: pipe_runtime_id=e791e2cb-7256-405d-af1b-92f97

2. I tuned the Batch size and fetch size for my database account, but i don’t see any improvement for my query. Any guideline or best practice or example for me to follow to tune those parameters?

Thanks

[cstewart · Accepted solution]

Are the downstream Snaps causing back-pressure? When it is running in that state producing 6 docs/sec, what are the input/output numbers on those downstream snaps?
One quick way to establish if it is the Execute Snap causing the issue is to just run that Snap (disconnect the downstream ones, and see how it preforms.

[walkerline117]

Yes, the downstream has a lot of pressue, you got it.

And we have removed the pressure and now the database query snap goes fast.