Input Data Tool: Record Limit control

Question

Hi all,

In going through all the available options on the input data tool, wanted to truly understand how they work and the options around them, so there are a few questions that I've posted along these lines (this is the first of a short series)

For the record limit: I connected to the Northwind database on SQL, and turned on SQL profiling to understand how the query is ACTUALLY being executed - and interestingly it depends very much on the connection type (Native SQL; OleDB; and ODBC).

However, for all 3 of these, it seems that the record limit is being enacted on the client side, not on the server.   What I mean by this is that when I take the exact queries that are being run by Alteryx on the server, and run these in a query window, you can see that the row-limit is not occurring in SQL, but in Alteryx.

(to test this, I ran several queries with and without the record limit; profiled them using SQL profiler; and the profile trace was identical either way)

Aside from putting "Select top(100) from..." in all the queries that we do - is there any switch or capability that we can use in the Alteryx Designer to force this down to the server, so that we can take advantage of the server's ability to optimize?

Thank you

Sean

I'll post the EXACT SQL executed in each of the three scenarios on a following thread.

JohnJPS · Accepted Answer

I'm not sure, but you might get directed to the In-DB tools for this. Along those lines, possibly my single biggest "want" is for automatic push-down optimization, along with which would come obsolescence of the In-DB tools. Despite my desire for this, my "idea" logged a while back gained zero traction, LOL: Automatic Pushdown Optimization. I perhaps could have worded it better.  (Actually - looks like mine is basically a duplicate of this one. Oops.)

SeanAdams · Accepted Answer

Thanks John - you and I may be pointing in the same direction.

Does anyone else in the community know how to push the row limit down to the SQL server, instead of just truncating at the client?   I have profiled this using SQL profiler, so I can see that the row limitation is not being sent to the SQL server currently (unless you explicitly write your query as "Select top(xx) * from Table", but given that there is a row limiter, this is probably not something that most users would think to do.

If this doesn't exist in any way, then I'll log this as an idea.

cheers

Sean