On Oct 12, 8:59 am, Nate Wiger <[email protected]> wrote:
> First off, sorry for the tone of my emails yesterday, my work stress
> has gotten retarded and I kinda went off the deep end there in a few
> of my replies.
That's OK, it happens to all of us occassionally.
> By "totally jacked" I didn't necessarily mean Sequel. It could be the
> Ruby DB driver or even C-level DB lib. What version of Postgres is
> that on? 8.4? 8.3?
8.3. I could try with 8.4 if you think there's likely to be a
significant difference.
> I do have benchmarking data just not in front of me. I've done a ton
> of benchmarking with Oracle and the results were the exact opposite of
> what you saw. Roughly 30% faster on Oracle across the board. I also
> benchmarked Postgres, although not as extensively as Oracle. It is
> definitely possible Oracle's bind var implementation is more efficient
> than PostgreSQL's.
It wouldn't suprise me if Oracle is always faster with bound
variables. I bet PostgreSQL is faster with them too, but the extra
overhead in ruby layer overshadows any benefit in the database layer.
It certainly could be that with Oracle, the benefits in the database
layer exceed the overhead in the ruby layer
Considering your numbers, I don't think it's worth changing Sequel's
API for a modest 30% improvement on Oracle (which fewer than 1% of
Sequel users use).
> Based on your numbers, I do agree that simple queries with integers
> appear to perform better in Postgres. I also did more complex
> queries; strings, blobs, joins, etc, and the more complex the query,
> the more bind vars have an advantage. Also there's a big advantage
> when reusing :named bind vars, I have a great example I thought of
> last night that I actually wanted to post here.
Integers are obviously a best case scenario for literalization, and
large blobs are probably a best case scenario for binding.
Literalizing variables has two costs. The cost in Sequel to
literalize, and the cost in the database to parse, and the costs are
probably proportionate (the more costly to literalize, the more costly
to parse). For that reason, binding makes the most sense for large
objects (blobs/strings) that are expensive to literalize.
I wouldn't think that query complexity would be affected by whether or
not variables are bound. Complex queries that return small results
sets have the most benefit of being prepared (and preparing the query
implies binding the variables), but if you aren't preparing the query,
I wouldn't think the complexity of the query was related to any
potential performance increases causing by binding variables.
> Re: Implementation and your suggestions about overriding literal()
> instead of hooking into placeholder_literal_string_sql, the problem I
> saw with overriding literal() is that it's used a ton of places in
> Sequel where bind variables are illegal, such as escaping columns for
> CREATE TABLE, SELECT AS, etc. Also, for bind variables, you need to
> know the context you're being called in, eg, are you being asked to
> bind ?,? or :name, because what you'll want to do is different (hence
> why I created two callbacks). Oracle OCI supports either positional
> or named binding, for example, with different low-level calls.
You can override lower level methods such as literal_blob and
literal_string to just override behavior for specific types.
In terms of integrating things with Sequel, using the named binding
always is probably best. That's what is done for the SQLite adapter,
and it should be fairly easy to handle with Sequel's current API.
You'd still have to do:
dataset.filter('id = ?', :$id).call(:select, :id=>1)
If you really want to have Sequel use variable binding implicitly,
e.g.:
dataset.filter('id = ?', 1).all
I'm going to recommend creating a Sequel extension that extends the
Oracle adapter to do that.
> So I'm not opposed to going about things differently, but we do need
> to hook in at a level where you can tell the context where you're
> being called.
Well, placeholder_literal_string_sql doesn't give you any context.
While it is used implicitly in some filters, it can be used pretty
much anywhere in Sequel:
dataset.select('? AS a'.lit(1))
And as I mentioned earlier, Sequel's placeholder literal strings can
handle plenty of things that the database cannot:
dataset.filter('? = 1', :id)
So you can't really just override Dataset#filter or a related method
and use bind variables for all substitutions. I suppose you could
check types and only use binding for the types you know you can
handle, but I don't recommend that approach.
The only reasonable way I can think of to handle this is to use a
separate method/syntax for variable bindings than for general
literalization, which is why Sequel's bound variable support is
implemented the way it is.
If you really want a nicer API, I recommend creating a new method that
does various things behind the scenes:
dataset.bindfilt('id = :id', :id=>1)
Which could be expanded behind the scenes to:
dataset.filter('id = ?', :$id).bind(:id=>1)
As I mentioned earlier, I'm OK with adding a Dataset#bind method that
allows variable binding to be handled before the #call method is
called. You could also have the method override Dataset#all/insert/
update/delete to call #call instead, so you could do:
dataset.bindfilt('id = :id', :id=>1).all
Which would be expanded to:
dataset.filter('id = ?', :$id).bind(:id=>1).call(:select)
So this is my complete recommendation:
1) Add support to Sequel's Oracle adapter to support bound variables
and prepared statements, using Sequel's existing API.
2) Create a Sequel extension that adds a method allowing nicer syntax
for bound variables/prepared statements. If this extension meets the
5 points I mentioned earlier, I wouldn't have a problem shipping it
with Sequel.
> As for this:
>
> Dataset.filter{id > 1}.all
>
> I'm having a hard time tracing thru the code for that, is that
> Sequel.virtual_row and SQL::VirtualRow?
Yes, it uses a virtual row. It's not necessary on ruby 1.8 (where you
can do :id > 1), but on 1.9 Symbol#> is already defined, so Sequel
doesn't override it. The safe method for inequality comparisons (if
you don't want to use string interpolation), is to use a virtual row,
which will work on both 1.8 and 1.9.
Jeremy
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
