[
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)
