CTEs in MS SQL

[bloukanov]

Hi there,

Loving the package so far. I'd like to now see if we can use it for larger queries with intermediary tables. But when I try to do a single select with any CTE's (https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15), it fails with the error:

RuntimeError: Token error: 'Incorrect syntax near the keyword 'WITH'.' on server X executing on line 1 (code: 156, state: 1, class: 15)

The same select works in pandas read_sql. Would it be possible to make this work or no?

[wangxiaoying]

Hi @bloukanov , thanks for the suggestion! For now we only support single select query. Adding support for CTE and others like view creation will bring challenge to the query partition part since we need to figure out which query should be partitioned, and we also need to support non-selection queries. We will look into the need and possible solutions of this.

[bloukanov]

Hi @wangxiaoying, been busy with other stuff but here are my testing results with 0.2.2-alpha.5. There is definitely improvement! But still takes a bit longer than pandas:

connectorx to polars, no partition: 2m 26s
connectorx to polars, partition_num 4, partition_range approximate: 3m 15s

pd.read_sql(): 1m 20s

[wangxiaoying]

Thanks @bloukanov for the test!

I'm suspecting that the improvement is only from the omitted count query. But getting the metadata is still costing the same time with running the entire query. Also I'm not sure why specifying the partition_range still cannot make partition faster than no partition. It that's ok, could you please print the log for both no partition and partition with partition_range again? Thanks in advance!

[bloukanov]

Yup no problem I have them here:

no partition:

[2021-11-17T23:16:36Z DEBUG connectorx_python::arrow] Running dispatcher
[2021-11-17T23:16:36Z DEBUG connectorx::dispatcher] Fetching metadata
[2021-11-17T23:17:49Z DEBUG connectorx::dispatcher] Do not need counts in advance
[2021-11-17T23:17:49Z DEBUG connectorx::dispatcher] Allocate destination memory: 0x3
[2021-11-17T23:17:49Z DEBUG connectorx::dispatcher] Create destination partition
[2021-11-17T23:17:49Z DEBUG connectorx::dispatcher] Start writing
[2021-11-17T23:19:02Z DEBUG connectorx::dispatcher] Finalize partition 0
[2021-11-17T23:19:02Z DEBUG connectorx::dispatcher] Partition 0 finished
[2021-11-17T23:19:02Z DEBUG connectorx::dispatcher] Writing finished

partition (it seems the whole query is executing for each partition prior to writing?):
[2021-11-17T23:26:51Z DEBUG connectorx::sql] Transformed single column partition query: - now whole query with CTEs, then:
AS CXTMPTAB_PART WHERE 1 <= CXTMPTAB_PART.pid AND CXTMPTAB_PART.pid < 250001 and so on for each of the 4 partitions. Then:

[2021-11-17T23:26:51Z DEBUG connectorx_python::arrow] Running dispatcher
[2021-11-17T23:26:51Z DEBUG connectorx::dispatcher] Fetching metadata
[2021-11-17T23:27:59Z DEBUG connectorx::dispatcher] Do not need counts in advance
[2021-11-18T15:02:21Z DEBUG connectorx::dispatcher] Allocate destination memory: 0x4
[2021-11-18T15:02:21Z DEBUG connectorx::dispatcher] Create destination partition
[2021-11-18T15:02:22Z DEBUG connectorx::dispatcher] Start writing
[2021-11-18T15:03:52Z DEBUG connectorx::dispatcher] Finalize partition 3
[2021-11-18T15:03:52Z DEBUG connectorx::dispatcher] Partition 3 finished
[2021-11-18T15:04:08Z DEBUG connectorx::dispatcher] Finalize partition 0
[2021-11-18T15:04:08Z DEBUG connectorx::dispatcher] Partition 0 finished
[2021-11-18T15:04:26Z DEBUG connectorx::dispatcher] Finalize partition 1
[2021-11-18T15:04:26Z DEBUG connectorx::dispatcher] Partition 1 finished
[2021-11-18T15:04:26Z DEBUG connectorx::dispatcher] Finalize partition 2
[2021-11-18T15:04:26Z DEBUG connectorx::dispatcher] Partition 2 finished
[2021-11-18T15:04:26Z DEBUG connectorx::dispatcher] Writing finished

[wangxiaoying]

Thanks for the log! Seems like fetching metadata still needs to execute the query, also the partitioned query takes longer than no partitioning. I suspect it might because all partitioned queries need to execute the same costly subplan first which takes longer time than just execute it once. We will take a look at how to improve it.

[bloukanov]

sounds good!