On Thursday, March 6, 2014 2:11:54 AM UTC-8, Jurgens du Toit wrote:
>
> Hey
>
> I currently use the following to add WITH (NOLOCK) to tables in a query:
>
> transactions = db[:transactions]
> .nolock
> .join(:accounts, :id => :account_id)
> .select(:transaction_date)
>
> It adds the WITH (NOLOCK), but only on the transactions table. I need it
> to be added to the accounts table as well. I tried
>
> transactions = db[:transactions]
> .nolock
> .join(:accounts, :id => :account_id)
> .nolock
> .select(:transaction_date)
>
> and
>
> transactions = db[:transactions]
> .join(:accounts, :id => :account_id)
> .nolock
> .select(:transaction_date)
>
> Neither worked. Is this possible?
>
You don't mention the SQL you are attempting to produce. I'm guessing the
following may work, though it uses a subquery:
transactions = db[:transactions].
nolock.
join(db[:accounts].nolock, :id => :account_id).
select(:transaction_date)
SQL Server is hopefully smart enough to optimize that correctly.
The real problem is that the developer that added support for NOLOCK added
it as a query-level flag, when SQL Server appears to consider it as a
per-table flag (http://technet.microsoft.com/en-us/library/ms177634.aspx).
I'm guessing if there are multiple FROM tables, Sequel currently will make
only the last FROM table NOLOCK if Dataset#nolock is used. Since NOLOCK
should be a per-table flag, the API should be something like:
transactions = db[Sequel.expr(:transactions).nolock].
join(Sequel.expr(:accounts).nolock, :id => :account_id).
select(:transaction_date)
The problem is that adds a global method for something only one adapter can
use, which I don't want to do by default. It's possible to add an
extension that adds the method, so it is only loaded by the people who need
it. Before adding such an extension, I'd like to get some feedback from
SQL Server users as to whether they like that API or can think of a better
one.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/groups/opt_out.
