Regarding performance issues

[KeShengchuan]

After my test, it takes about 1 second to parse a sql with sqllineage.
I need to process a lot of files (more than 10k), so the total time is relatively long,
Is there any way to speed it up?
Thanks in advance

[reata]

Update:

Rule of thumb:

1. Currently specifying dialect is much slower due to sqlfluff performance issue. If you care about performance more than dialect, for example you're exposing an ad-hoc end user facing lineage analyzing feature, then use non-validating dialect, which is sqlparse underneath.
2. Consider using parallelism (process pool) to run sqllineage if you have tons for sql files to analyze in batch mode.

Original Question: are you using master branch with dialect-awareness feature?

[KeShengchuan]

The dialect used by databricks, I think it is the same as spark-sql ?

[reata]

yes, dialect databricks and sparksql are the same. The overall performance issue is due to sqlfluff parser performance being slower than sqlparse. We plan to contribute to sqlfluff to enhance on this part in future releases.

From your perspective, if performance is a concern. I'd suggest you either: 1) stay with dialect non-validating (it's sqlparse underneath). 2) leveraging multi-process to speed up the end-to-end time.

For me I also maintain an internal version of sqllineage to collect lineage info from all the SQLs in DW. It's ~500,000 scripts I'm analyzing every day. So even if each sql takes 0.1s, it's too slow to analyze them one by one. If you leverage multi-process, then 0.1s or 1s doesn't make much difference. (BTW, multi-thread won't work, because parsing is CPU-intensive task and GIL will stand in the way)

[KeShengchuan]

OK, thanks for your guidance,
After my test, in fact the time-consuming difference between multi-threading and multi-processing is not big, probably because I wrote each processing result into a file separately (so this is also an IO-intensive task, even though the GIL stans in the way, python's multi-threading is still optimized for IO-intensive tasks).
So I would like to ask how do you save the processing results? Are they all written in a same file?

[reata]

For my case, I separate the workload through producer/consumer pattern: 1) a process pool to parallel read the sql file, use sqllineage to analyze the result, generate info to a memory queue. 2) another single thread to read from the queue, accumulate some records, e.g. 500 message per batch, and then bulk write it to database in one single transaction.

For workload #1, replacing it to a thread pool causes significant performance downgrade. For workload #2, single thread is enough, because producer side is much slower even with 10~20 producers running at the same time.

Hope this helps.

[KeShengchuan]

I think this is a great idea, I'll give it a try, thank you so much for your guidance, it helped me a lot !!!