Use `LIMIT 0` instead of `LIMIT 1` to get result schema.

[mcrumiller]

This would probably save a lot of query execution time for queries which take a very long time to return limit 1 (e.g. with a subquery involving a SORT).

[wangxiaoying]

This is an interesting point. We probably could try it on one database first. Which one are you using?

[mcrumiller]

My company uses postgres (Greenplum parallelism fork), along with MSSQL Server, and some Oracle DB as well which I try to avoid using. I have a local postgres at home as well that I can play with.

I run a lot of DISTINCT ON (...) queries involving joins where even a LIMIT 1 query can take quite a long time.

[wangxiaoying]

That's weird, we are not using limit 1 query for metadata fetching for postgres or mssql. The information is actually fetched before getting any real query results. You can find the implementation here for postgres and here for mssql.

I think currently the limit 1 queries are used for mysql, oracle, sqlite, trino and bigquery.

[mcrumiller]

I think currently the limit 1 queries are used for mysql, oracle, sqlite, trino and bigquery.

I was not aware of this, and was hoping for a magical speedup. Thanks!

[mcrumiller]

By the way, the reason I thought this is because of the text on the main page:

How does ConnectorX download the data?

Upon receiving the query, e.g. SELECT * FROM lineitem, ConnectorX will first issue a LIMIT 1 query SELECT * FROM lineitem LIMIT 1 to get the schema of the result set.

...which makes it seem as though this is always the case.

[wangxiaoying]

By the way, the reason I thought this is because of the text on the main page:

How does ConnectorX download the data?

Upon receiving the query, e.g. SELECT * FROM lineitem, ConnectorX will first issue a LIMIT 1 query SELECT * FROM lineitem LIMIT 1 to get the schema of the result set.

...which makes it seem as though this is always the case.

Yeah, it is the by default implementation for every data source in the beginning. But letter we optimized for some of them if the corresponding client driver supports workarounds like procedure. I will update the readme.