-
1. Re: Feature request - depjoin-like functionality for stored procedures
shawkins May 28, 2015 12:22 PM (in response to markaddleman)I'm not sure this makes sense generally as you are asking for the execution of a batch of stored procedures that can return result sets. That's not something JDBC would allow. Would it be possible just to use a table abstraction here?
Alternatively you could accept array arguments, but that may require something other than the simple join query above.
-
2. Re: Feature request - depjoin-like functionality for stored procedures
markaddleman May 28, 2015 12:44 PM (in response to shawkins)> Would it be possible just to use a table abstraction here? - See more at: https://developer.jboss.org/thread/259328#sthash.e7XWrwCo.dpuf
Yes. It's just more convenient to use a stored procedure when modeling an API. Modeled as a table, I can use access patterns to ensure that the "parameters" are sent to the client. I guess the closest construct is a parameterized view like described in Create parameterized VIEW in SQL Server 2008 - Stack Overflow
-
3. Re: Feature request - depjoin-like functionality for stored procedures
markaddleman May 29, 2015 1:00 AM (in response to shawkins)> Alternatively you could accept array arguments, but that may require something other than the simple join query above.- See more at: https://developer.jboss.org/thread/259328#sthash.ZtspSPZq.dpuf
What would this look like? The only solution I can think of would be to aggregate the results from api_universe as an XML document and pass it into api. I'm concerned that the number of rows from api_universe can be very large (in the hundreds of millions), the resulting XML document could be massive. Is the XML document buffered off-heap? If I understand the dependent join behavior, the dependent predicates can be stored off-heap.
I suppose I could write my own aggregator rather than use an XML document...
-
4. Re: Feature request - depjoin-like functionality for stored procedures
shawkins May 29, 2015 11:31 AM (in response to markaddleman)A stored procedure with a result set is effectively the same as the link you provide, but we don't currently look to inline when there only literal parameters.
No, hundreds of millions of rows would not be amenable to array values, but those scenarios seem problematic in general.
> I suppose I could write my own aggregator rather than use an XML document...
The more I think about this it all generally suffers from determining how to correlate the result back row that they should be assigned to. The only way that can be done is if the procedure projects back out the inputs, and if it's batched then it must also only have distinct inputs.
-
5. Re: Feature request - depjoin-like functionality for stored procedures
markaddleman Jun 14, 2015 3:42 PM (in response to shawkins)(getting back to this thread after several interruptions)
The more I think about this it all generally suffers from determining how to correlate the result back row that they should be assigned to. The only way that can be done is if the procedure projects back out the inputs, and if it's batched then it must also only have distinct inputs.
- See more at: https://developer.jboss.org/thread/259328#sthash.u40os7cu.dpuf
Agreed. The API and/or the translator must honor this. As I've played more around this, modeling as a table isn't such a big problem. Rather the problems I ran into could be addressed with more control over planner. I'll bring this up in another thread.