Resuscitating the subject
Following the Jurgens idea, I created a plugin to apply directly in models
and datasets. As follow
module Sequel
module Plugins
module NoLockTable
def self.configure(model)
model.instance_exec do
self.dataset = dataset if @dataset
end
end
module ClassMethods
private
def convert_input_dataset(ds)
super.nolock
end
end
end
end
end
*But that don't apply in associations and joins. Are there any kind of
instruction to apply in these others (associations and joins)?*
*Or some place common for all queries to identify tables and apply WITH
(NOLOCK) instruction?*
The follow queries that I use, try to examplify the problem
# Using plugin, apply successful
job = Job.first
# SELECT TOP (1) * FROM [JOBS] WITH (NOLOCK)
# Object call create a dataset, but doesn't apply.
job.company
# SELECT * FROM [COMPANIES] WHERE [ID] = 38866
# Calling dataset method, adjust the query with correct statement
job.company_dataset.first
# SELECT TOP (1) * FROM [COMPANIES] WITH (NOLOCK) WHERE ([COMPANIES].[ID] =
38866)
# Creating a dataset, create a subselect, not desired
Job.join(DB[:companies].nolock, id: :company_id).first
# or
Job.join(Company.dataset, id: :company_id).first
# SELECT TOP (1) * FROM [JOBS] WITH (NOLOCK)
# INNER JOIN (SELECT * FROM [COMPANIES] WITH (NOLOCK)) AS [T1] ON
([T1].[ID] = [JOBS].[COMPANY_ID])
# Create the expect join, but without WITH(NOLOCK) instruction, not desired
too
Job.join(:companies, id: :company_id).first
# SELECT TOP (1) * FROM [JOBS] WITH (NOLOCK)
# INNER JOIN [COMPANIES] ON ([COMPANIES].[ID] = [JOBS].[COMPANY_ID])
Models for reference:
class Company < Sequel::Model(:companies)
set_primary_key :id
end
and
class Job < Sequel::Model(:jobs)
many_to_one :company, key: :company_id
end
Em quinta-feira, 6 de março de 2014 13:09:57 UTC-3, Jeremy Evans escreveu:
>
> 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 https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
