Monitoring SQL Queries

by Noel Jerke

When you're building client/server applications that interact intensively with your SQL database, you don't want to make the user wait for a long query to return. The user may need to continue with another task—at the very least, you should notify her when the query finishes. In this article, we'll explore two methods for monitoring SQL queries with Remote Data Objects (RDO) working against Microsoft SQL Server.

In the first technique, we'll use the Microsoft Data Bound Grid control and the Microsoft Remote Data Control to attach to a SQL database; a SQL query will then populate the Grid control. In our second example, we'll use RDO to monitor the status of a multirow insert into a SQL table.

Before you begin the Visual Basic programming, you need to build a sample database table. In this case, the table will consist of an identity column (idRow), a column that shows the date the record was inserted (dtInserted), and 12 miscellaneous varchar fields. The SQL script to create the table appears in Listing A. Note that you use the SQL getdate() function to ensure the dtInserted column has a default value.

Listing A: SQL script to create a Microsoft SQL Server table

CREATE TABLE dbo.testtable (
  idRow int IDENTITY (1, 1) NOT NULL ,
  dtInserted datetime NULL ,
  field1 varchar (50) NULL ,
  field2 varchar (50) NULL ,
  field3 varchar (50) NULL ,
  field4 varchar (50) NULL ,
  field5 varchar (50) NULL ,
  field6 varchar (50) NULL ,
  field7 varchar (50) NULL ,
  field8 varchar (50) NULL ,
  field9 varchar (50) NULL ,
  field10 varchar (50) NULL ,
  field11 varchar (50) NULL ,
  field12 varchar (50) NULL 
)
GO

To facilitate testing the table, you'll build the Populate stored procedure shown in Listing B, which will loop continuously until a specified number of rows has been inserted into the table. You pass the number of rows to the stored procedure as a parameter.

Listing B: Stored procedure to insert test data into the table

CREATE PROCEDURE populate @insertcount int as

/*  Delete any current rows */
delete from testtable

/* Loop until we have inserted the number of rows indicated by the @insertcount parameter */
WHILE (SELECT count(*) from testtable) < 
  @insertcount

begin

/*  Insert data. Note the dtInsert column is not identified because the table is set to give 
the field a default value returned from the    getdate() function */
insert into testtable(field1, field2, field3, 
  field4, field5, field6, field7, field8, 
  field9, field10, field11, field12) 
  values('1', '2', '3', '4', '5', '6', '7', 
  '8', '9', '10', '11', '12')

END
GO

In this stored procedure, you first delete any current rows in the table. Then the code loops until the number of rows in the table equals the value of the passed-in parameter, @insertcount. You determine the number of rows in the table using the count(*) SQL syntax. There are probably more efficient ways to do this, but we want the query to run at some length so we can monitor it.

Now that the database is ready to roll, you're ready to begin programming against it. For our first example, you'll want to insert 2,000 or more rows into the database. From SQL Enterprise Manager (or any similar tool), run the Populate stored procedure to insert the rows (that is, execute populate 2000). Finally, you'll need to add an ODBC data source called SQLTEST to your new SQL database.

Our first example will demonstrate how the Remote Data Control can notify users when a specified query has finished. To begin, open a new project named prjRDC. Add to the project a new form called frmRDC.

Choose the Project | Components… menu item to open the  Components dialog box. Add a reference to the Remote Data Control and another to the Data Bound Grid Control. On the form, add a Remote Data control (RDC); keep the default name. Set the control's DataSourceName property to your ODBC DSN, SQLTEST.

Next, add a DBGrid control to the form and set its DataSource to the RDC. Finally, add to the form a command button named cmdRefresh. Your form should now look like Figure A.

Once you've set up the form, add the code from Listing C. This form code is fairly simple. When you click the command button, you set the SQL command for the RDC to select all the records from your table. Next you call the RDC's Refresh method to execute the SQL command.

Listing C: frmRDC code

Private Sub cmdRefresh_Click()

'  Set the SQL statement to retrieve all of
'  the records
MSRDC1.SQL = "select * from testtable"

'  Refresh the result set
MSRDC1.Refresh

End Sub


Private Sub MSRDC1_QueryCompleted()

'  Notify the user when the query is complete
MsgBox "Query complete!"

End Sub

When the query is done, the RDC fires its QueryCompleted event to indicate that the query has finished. When you run the sample code and see that the message box notification is given, you'll also see that the grid has been populated. Figure B shows the results of the query.

When executing a query directly against a dedicated SQL test database, there's almost no pause. But if you were executing a complex SQL query, working against a heavily used database, or perhaps working against a legacy system, you could experience significant delays in the return of nearly any query. Using the RDC's QueryCompleted event, you have the ability to provide the user with feedback when the query is finished, to allow the user to continue working, or to carry out any other option that suits your environment.

As you can see, this feature is fairly straightforward. However, many client/server applications don't utilize the Remote Data control, especially if they're trying to completely abstract away the database interface from the user interface in a multitier application. But don't fear—you can accomplish the same task using RDO without using the RDC.

To set up our next example, start a new project named prjQueries. Add a form called frmQueries to the project. In your project, you'll need to reference Remote Data Objects by using the Project | References menu item. Table A outlines the controls you add to frmQueries. When you finish, the form should resemble Figure C. 

Next, add the code from Listing D to the form. When the code runs, you'll enter in the text box a value that indicates the number of rows you want to insert into the test table. Then, click the Populate button to begin the insertion. As the query executes, with each tick of the timer the program shows the status of the query, as well as the current execution time in seconds. Figure D shows an insert in progress.

Listing D: frmQueries code

Option Explicit

'  Globally declare our rdo environment and 
'  connection objects
Dim en As rdoEnvironment
Dim cn As rdoConnection

'  Globally save the StartTime when the query 
'  is executed
Dim StartTime As Date


Private Sub cmdPopulate_Click()

Dim SQL As String

'  Get our rdo environment to work with.
Set en = rdoEnvironments(0)

'  Open a connection using the
'  sqltest system DSN
Set cn = en.OpenConnection(dsName:="sqltest", _
  Prompt:=rdDriverCompleteRequired)
    
If txtNumRows.Text <> "" Then
    
    '  Build our stored procedure execute 
    '  statement. The number of rows to 
    '  insert into the database is retrieved 
    '  from the text box
    SQL = "execute populate " & _   
      CLng(txtNumRows.Text)

    '  Get the start time
    StartTime = Now

    '  Also set the timer interval to 100 ms
    Timer1.Interval = 100
    
    '  Set query checking interval to 100 ms
    cn.AsyncCheckInterval = 100
    
    '  Execute the SQL Statement. Use the
    '  rdAsyncEnable parameter to indicate we 
    '  want to continue processing in the 
    '  program even if the query is not 
    '  completed
    cn.Execute SQL, rdAsyncEnable
    
    '  Enable the timer to monitor the status
    '  of the query
    Timer1.Enabled = True

Else

    '  No value was entered, notify the user.
    MsgBox "You did not enter the number of 
?    rows to populate the database with."

End If

End Sub

Private Sub Timer1_Timer()

'  Check to see if the query is still executing
If cn.StillExecuting = True Then

    '  Show the status
    lblStatus.Caption = _
      "Status:  Still Executing"
    
    '  Show the current query time
    lblTime.Caption = "Query Time:  " & _
      DateDiff("s", StartTime, Now)

Else

    '  Indicate the query is done
    lblStatus.Caption = "Status:  Done!"
    
    '  Show the query time
    lblTime.Caption = "Query Time:  " & _
      DateDiff("s", StartTime, Now)
    
    '  Disable the timer
    Timer1.Enabled = False
    
End If

End Sub

The two labels show the current status of the query, as well as the number of seconds it has been executing. When the query is finished, the labels will show the final status and number of seconds required to run the query, as shown in Figure E.

The key to understanding how this code works lies in the Populate stored procedure. In the cmdPopulate_Click subroutine, you create the RDO connection. Then you create the SQL statement to execute the Populate stored procedure and pass in the number of rows to be inserted (indicated by the user in the text box). Next, you set the timer interval to 100 milliseconds.

Now you're at the heart of the functionality. Remote Data Objects provides a feature called asynchronous queries, which lets the VB program continue on its merry way while the SQL query continues to execute. The program will then periodically go out and check the status of the query. The periodicity is set by the AsyncCheckInterval property of your connection object. In this case, you set it to 100 milliseconds. The value normally defaults to 1,000 milliseconds (one second).

Now you're ready to execute the query, using the usual execute method of the connection object. But in this case, you're going to provide one additional parameter—rdAsyncEnable—to indicate that this query should be run asynchronously. This parameter tells the program to continue processing even though the query might not be finished.

In order to monitor the query as it runs, you can check the status of the connection object's StillExecuting property. You do so in the Timer1_Timer subroutine. Each time the timer fires, the code checks to see whether the query is still running. If it is, you simply update how long it's been running by subtracting the current time from the time when the StartTime variable was set. When the value of StillExecuting is false and the query is finished, the timer stops and you indicate that the query is finished. With a few-thousand row insert, you'll see a several-second delay.

Both methods we've presented in this article for monitoring queries work equally well. If you're accustomed to using the Remote Data control as your backend database interface, the QueryCompleted event can help you. Or, if you're encapsulating database functionality in multitier business objects, you can dig directly into the Remote Data Objects features. Either way, if you're building complex queries hitting heavily used databases, knowing the status of your queries can be invaluable and make your programs much more user-friendly.

Copyright © 1998, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.