By
Jason Kolb, On 8/10/09 4:17 PM
cuic tips database performance
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.field3That'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 
In some very specific instances, you may want CUIC Web servers to share a database but query different AW/HDS servers, to split load. At first glance this doesn't seem possible as the AW/HDS that is queried is defined by a data source, which is stored in the CUIC database. However, there is a workaround which I'll show you in this post.
Let's say you have two Web servers, Server A and Server B. You want Server A to query against HDS 1 (IP address 192.168.0.1) and you want Server B to query against HDS 2 (IP address 192.168.0.2). However, you want to use the same data source for all reports so that you don't have to keep a duplicate copy of each report pointing to a separate data source.
The trick is that when you set up the data source, you don't use an actual HDS machine name or IP address in the "Server" field. You will supply a bogus "alias" name, which we can then redirect using the Windows host file.
Let's call our alias "HDSREDIRECT". You would plug "HDSREDIRECT" into your data source configuration as the server name. Obviously that will not work right away, there's one more step. You'll have to edit the host file on each of the Web server, typically located in C:\Windows\System32\drivers\etc, the filename is simply "hosts".
In order to make the "HDSREDIRECT" alias work, and split the traffic between the two AW/HDS servers, you would add this line to Server A (which, remember, you want to query HDS 1):
192.168.0.1 HDSREDIRECTAnd you would add this line to the hosts file on Server B (which we want to query HDS 2):
192.168.0.2 HDSREDIRECTNote that the name of the alias is the same on both servers, but the IP address is different.
And that's it! Because the Web server issues the request to the AW/HDS, it will use the hosts file to determine which IP address to query. Just make sure that your security configuration matches up on the two AW/HDS servers and you should be good to go!
