[jira] [Commented] (DERBY-6036) If you wrap a SELECT * view around a table, all of the columns are read from the base row even when you SELECT only a subset of the view columns.

["Richard Huddleston (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6036?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13846261#comment-13846261 ] 

Richard Huddleston commented on DERBY-6036:
-------------------------------------------

i'm working on a project that uses 1 derby function and n views of that 1 function to access data in a foreign database. for each foreign database table, we have a corresponding view in derby with the EXACT name of the table in the foreign database.

we were able to port our code over to derby by simply changing the jdbc connection params to derby instead of our foreign database, and all the code magically worked.  if we had to change to functions instead of views, we'd lose the flexibility of our code running against the foreign database or derby. i have 30+ developers who have existing sql in tons of different places and development languages that reference the table names / matching derby view name, it's not practical for us to change all the table.

why performance matters.  i created a derby facade to our foreign database out there that supports bulk extractions .  that database shards data into 16 or more different slave machines. i use derby to issue a query to the "master" machine instructing all the slaves to send data back to derby. those slave machines in aggregate pump data to our derby facade machine at greater than 1 gigabit/sec, where the data flow lasts more than 30 seconds and we are network bandwidth constrained.  so, it's LOTS of data.  we have more than 8 fields, and this is a common thing in our facade:
select some_id from my_derby_function_view where theDate = '12/11/2013'
select count(some_id) from my_derby_function_view where theDate = '12/11/2013'
because derby doesn't pass down the field names from the view into our function via initscan, our data flow is more than 8x larger than needed. if this bug were fixed, instead of say 32 seconds, our query would in 4 seconds if we knew which columns derby really needed.

is there any way to motivate a fix for this bug ?






> If you wrap a SELECT * view around a table, all of the columns are read from the base row even when you SELECT only a subset of the view columns.
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6036
>                 URL: https://issues.apache.org/jira/browse/DERBY-6036
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.10.1.1
>            Reporter: Rick Hillegas
>              Labels: derby_triage10_11
>         Attachments: derby-6036-01-aa-testForRestrictionPushing.diff, derby-6036.sql, derbyAST.xml
>
>
> This also affects SELECTs from views wrapping RestrictedVTIs. Restrictions are pushed into a restricted VTI if you wrap it in a view. However, projections are not. I will attach a script showing this problem.



--
This message was sent by Atlassian JIRA
(v6.1.4#6159)