Raimonds-
Thanks for the reply as always. Always appreciate the help.
Cursor sharing similar is a good solution when there are no other
options. The problem is our app needs a solution that makes better
native use of Oracle bind variables, as the parsing overhead is
currently causing performance problems. Do you think you and I could
brainstorm some ideas? I can devote some time to an ActiveRecord
patch if we need to make changes to AR::Base.
The problem we're hitting is that association sub-selects in
particular are currently generating variable length queries, depending
on the number of records present in the association. So, using your
example as a starting point:
select * from posts where posts.user_id IN (1,5)
select * from posts where posts.user_id IN (2,4,6)
select * from posts where posts.user_id IN (3,5,8,18)
select * from posts where posts.user_id IN (1,2,5,6,10)
select * from posts where posts.user_id IN (1,4,6,9,14,23)
As I'm sure you know, the problem is, each time the literal SQL string
is different, Oracle has to parse and create an explain plan for each
one:
select * from posts where posts.user_id IN (:"SYS_B_0",:"SYS_B_1")
select * from posts where posts.user_id IN
(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2")
select * from posts where posts.user_id IN
(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3")
select * from posts where posts.user_id IN
(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4")
select * from posts where posts.user_id IN
(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")
select * from posts where posts.user_id IN
(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5","SYS_B_6")
Since we have a large schema and many associations, this is currently
generating an overhead upwards of 20%.
Do you have any ideas? Maybe we could monkey-patch out some of the
SQL-generation methods in AR::Base as a proof-of-concept.
Thanks,
Nate
On Mar 12, 4:21 pm, Raimonds Simanovskis