(NOLOCK)

rbuser

Any new approach to this, posted in 2003?

Or i will have to modify the SQL Text when printing?


"Using D6, RB Enterprise 7 against MSSQL Server 2000.
I have a lot of reports, some of them access a heavy load of data, and
therefore causing locks on the tables. More worse, lock escalation can
occur. In my app, I have quietened my Sql statements by adding "with
(nolock)" to the table names. I want to do this with the reports too, but I
am unsure how to achieve this. I am sure, this is not possible using DADE
only.

My preferred solution therefore would be a RAP function I can call in some
event:

Function AddWithNoLock(Report: TppReport; sTableName: String):Boolean;

How can I isolate the sql-statement before execution but after the auto
search fields are set and how can I write back the modified sql-statement?

Thank you for every opinion.
Bernd"

nardmoseley

I recommend adding some custom code to the ADO DADE plug-in, which is
defined in daADO.pas. Modify the TdaADOQueryDataView.SQLChanged method. This
method assigns the SQLText generated by the TdaSQL object to the TADOQuery
object. Just after this statement, add your extra sql text.

example:


FQuery.SQL.Assign(SQL.MagicSQLText);

// add custom code here





--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

nardmoseley

The QueryDataView has a TdaSQL object. The TdaSQL object generates a SQL
text statement. For the ADO plug-in, the SQL text is assigned to the
ADOQuery.

QueryDataView
TdaSQL ---> SQL text ---> ADOQuery

The SQL statement is generated upon demand. In other words when the
TdaADOQueryDataView.SQLChanged method references the SQL.MagicSQLText
property, the 'get' function for that property generates the SQL statement.

FQuery.SQL.Assign(SQL.MagicSQLText);

// add custom code here


You can trace the RBuilder\Source code in the Delphi debugger to see what is
happening. The SQL statement assigned to the ADOQuery (i.e. FQuery in the
above) will be submitted to the SQL server database engine. I do not know
why it would be 10 times slower. Try pasting that same SQL statement into
TADOQuery that resides on a form and test that for performance.



--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Modifying the SQLText make my report become 10 times slow then before.

I imagine that somewhere the RB creates the SQL Statement. At this
place, i request the RB put the NOLOCK keyword for every table used at the
query.

And that is a very serious issue, because using SQL Server, if u don?t
use the NOLOCK, every record the report shows will be locked.