When you write a custom report, it's always a good idea to use the SQL NOLOCK hint to improve performance and reduce the chance of deadlocks.

By default, when a query is sent to SQL Server (i.e. the AW/HDS), a read lock must be acquired before the data can be read. This means that the data cannot be changed while the record(s) is being read for the query. Sounds like a good thing, but this can create conflicts when multiple queries are trying to access the same data at the same time, which results in performance degradation.

The fix is easy, however, and it is to simply add a NOLOCK hint to your query, simply by adding the text "(NOLOCK)" after any table names in your query, like this:

SELECT field1, field2 FROM table1 (NOLOCK) left join table2 (NOLOCK) on table1.field3 = table2.field3

That's it! If you use this your reports will be more reliable, and they will run faster! You should also use these hints in your stored procedures and anonymous block reports.

Note that this doesn't apply to any reports you're writing against an Informix data source, CUIC automatically sets this hint at the connection level for Informix.

For more tips and tricks you should follow me on Twitter here