Let the system tell you where potential performance bottlenecks exist
A common cause of performance issues in database backed systems is poorly defined SQL, either a SQL operation that takes a long time to execute, or a SQL query that returns a large number of rows.
Operations that take a long to execute can typically be corrected by database tuning, such as adding indexing, or changing the application logic so that the database can better optimize the operation.
Queries that return a large amonunt of data can stress the application server & network. All that data needs to be transferred from the database to the application server, and in web applications, that data needs to then get transferred from the web server to the web client (browser).
Typically, it’s not that difficult to tune the database or tweak code once these situations are identified. However, it’s not always easy to figure out where the slow operation occurs. There are a couple configuration options in the fcSDK that can help with this.
fchoice.sqlbigquerytimethreshold
Specifies the minimum time (in seconds and fractions of seconds) a query must take before being considered a "Large Query".
fchoice.sqlbigquerycountthreshold
Specifies the minimum number of records a query must return before being considered a "Large Query".
For example, in my configuration file (app config for .NET apps or fc.env file for COM apps), I would add:
fchoice.sqlbigquerytimethreshold=5
fchoice.sqlbigquerycountthreshold=500
Now, if any queries take longer than 5 seconds, or returns more than 500 records, their log entries will be marked with "Large Query: True".
Look at the following example; notice that the number of rows (810) is greater than the count threshold (500), so the log entry is marked with "Large Query: True".
2007-01-15 10:29:12,421 [user: sa] [fcsessionid: 1ad31c93-af0c-4804-ad56-5d1f7cf05ea9] [INFO ] [FChoice.Common.Data.SqlHelper]
Fill DataSet complete.
Query ID: 83a9606b-5bcb-4f8d-a45d-32b162e11dd8
Duration: 0.0427 seconds
Total Records Returned: 81
Number of DataTables: 1
Large Query: True
Threw Error: False
Error Message:
Table 0 [0], Number of Rows: 810
Command:
SELECT * FROM table_rol_contct WHERE ( ( status = @param0 ) ) order by site
Parameters:
@param0 = 0 (Int32)
Because the fcSDK uses log4net as its logging infrastructure, we can take advantage of the log4net filtering capabilities, and log just these large query entries to a separate log file, to a database, to the windows event log, or send an email. This allows us to seperate these large query logs from other log entries.
The following example log4net configuration will notify a sys admin via email when a large query happens.
<log4net>
<appender name="SMTPAppender" type="log4net.Appender.SMTPAppender">
<param name="To" value="admin@company.com" />
<param name="From" value="dovetail_sdk@company.com" />
<param name="Subject" value="Message from Dovetail Log Manager" />
<param name="SMTPHost" value="my_smtp_server_name" />
<param name="LocationInfo" value="false" />
<param name="BufferSize" value="1" />
<param name="Lossy" value="true" /><layout type="log4net.Layout.PatternLayout">
<param name="ConversionPattern" value="%d [user: %X{user}] [fcsessionid: %X{session}] [%-5p] [%c]%n %m%n%n" />
</layout><filter type="log4net.Filter.StringMatchFilter">
<param name="StringToMatch" value="Large Query: True"/>
</filter>
<filter type="log4net.Filter.DenyAllFilter" />
</appender><root>
<level value="INFO" />
<appender-ref ref="SMTPAppender" />
</root>
</log4net>
The important section in the above configuration is the filter. This filters for only the large queries by matching to the "Large Query: True" string, while denying any other logs to get through.
As a sys admin, I can have the system let me know when large queries happen, which is a good indication there the system has a potential performance bottleneck and should be investigated. Resolving performance issues is a topic for another post.
This type of configuration can be used in any application that uses the fcSDK, including fcClient, fcAdmin, fcSelfService, RuleManager, SEC.NET, as well as your own custom applications.
