Thanks Jeremy, That solve the problem.
2017-10-05 17:06 GMT-03:00 Jeremy Evans <[email protected]>: > On Thursday, October 5, 2017 at 12:35:44 PM UTC-7, Adriano Dadario wrote: >> >> 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 >> > > This is due to an optimization Sequel performs, which doesn't do what you > want because it doesn't respect the NOLOCK setting. You can disable it via: > > Company.send(:simple_table=, nil) > > >> >> >> >> # 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]) >> >> >> > Is MSSQL not smart enough to optimize the subselect join? > > In any case, if you really want NOLOCK everywhere: > > def DB.dataset > super.nolock > end > DB.extend_datasets do > def join_clause_sql_append(sql, jc) > super > sql << " WITH (NOLOCK)" unless jc.table.is_a?(Sequel::Dataset) > end > end > DB.send(:reset_default_dataset) > > Thanks, > Jeremy > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sequel-talk" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sequel-talk/hc6KrMlH5Lk/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- Adriano Dadario in: [email protected] twitter: @dadario -- 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.
