-
1. Re: Hint Explaine Plan - Query federation with rest service
rareddy May 30, 2016 10:03 PM (in response to parrotola)Since for the REST service we can not push the LIMIT the LIMIT is applied on the whole query. You can take look at the query plan to validate that.
-
2. Re: Hint Explaine Plan - Query federation with rest service
parrotola May 31, 2016 3:56 AM (in response to rareddy) -
3. Re: Hint Explaine Plan - Query federation with rest service
rareddy May 31, 2016 10:50 AM (in response to parrotola)The query plan is showing that the LIMIT is applied on the rest service results after they are fetched then they are cross joined with the "geo_table". There is no reason to apply LIMIT after the join as it will produce the same result.
-
4. Re: Hint Explaine Plan - Query federation with rest service
shawkins May 31, 2016 10:57 AM (in response to rareddy)To clarify this is showing a lateral / row-by-row join and the limit is applied both before the join for the getStreeMap results (as there is no point in producing more than limit for a single invocation) and after the join once results have been obtained so that the overall limit is respected.
The visualization ideally would be updated to show this as not just a cross join.
-
5. Re: Hint Explaine Plan - Query federation with rest service
parrotola Jun 1, 2016 6:09 AM (in response to shawkins)Hi,
there aren't a way in order to perform row by row join because of timeout problem with rest service and limit call.
Thank you.
Best regards
Francesco
-
6. Re: Hint Explaine Plan - Query federation with rest service
rareddy Jun 1, 2016 8:49 AM (in response to parrotola)Please explain what you mean by row by row join? Retrieve single row from web service each time?
-
7. Re: Hint Explaine Plan - Query federation with rest service
parrotola Jun 1, 2016 11:08 AM (in response to rareddy)Hi Ramesh,
sorry for my English. I can run only ten REST call at a time in this way when I run my query, the cross join performs all rest call and for this reason I put limit.
For example, if I had the geo_table with 100000 rows, my query would perform 100000 rest Call but it's not possible. I would perform it for only ten rows without make a virtual procedure.
It's useful to use a Clause With?
Francesco
Ty.
-
8. Re: Hint Explaine Plan - Query federation with rest service
rareddy Jun 1, 2016 12:23 PM (in response to parrotola)The way you wrote your query is a CROSS JOIN, that means the query will retrieve all the rows from left and all the rows from right then do the join and return the LIMIT will return the first 10 rows. So, the LIMIT is over the final result, not the intermediate geo_table. I am thinking, you need to be doing something like
SELECT t.* FROM (SELECT gt.dindirizzo, gt.dcomune from geo_table LIMIT 10) gt, TABLE( EXEC "modelOpenStreetMap"."getStreeMap"(gt.dindirizzo || ',' || gt.dcomune,'1','json','1')) t;
Ramesh..
-
9. Re: Hint Explaine Plan - Query federation with rest service
shawkins Jun 2, 2016 3:47 PM (in response to rareddy)Ramesh's suggestion will work, but isn't quite the same as applying the limit after the join as the total number or result rows is not guaranteed.
Even with the current version of your query you would not expect it to process the entire join - as batches are returned and the limit is satisfied, the remainder of the join is not processed. Are you seeing significantly more rest calls being made that you expect?
-
10. Re: Hint Explaine Plan - Query federation with rest service
parrotola Jun 3, 2016 5:31 AM (in response to shawkins)It's worked.
I'm seeing the rest calls that I expect.
Thank you Ramesh and thank you Steven.
Thank a lot for your help.
Francesco