Jeff explains how to reduce the time it takes to reduce the retrieve a recordset from a large database using an asynchronous call. Read this article to find out how.
Technologies Referenced
- Microsoft Active Server Pages
- Microsoft SQL Server 7.0
- Distributed Management Objects (SQL DMO)
- Data Transformation Services (DTS)
- Visual Basic
The Company
MatureWell Inc. is an organization based in Tucson, AZ, and specializes in web based solutions for the HMO industry. It recently acquired Premier Healthcare of Arizona, which is based in Phoenix. MatureWell is currently in the process of migrating Premier from their existing IT infrastructure, to MatureWell’s Web based solution, which will eventually be marketed to other organizations also.
The Situation
The users wanted to have an html form that allowed them to pass in some parameters, call a stored procedure, and then return a recordset to be displayed in the browser, using Excel. This was all fine and dandy, except we are talking about searching through over a million rows of data, and bringing back about 15 fields for each returned record, which can be time consuming. Our in-house custom system uses Microsoft Web technologies, n-Tiered architecture, and Windows DNA.
Although still being developed and "productized", it is currently being used by Premier Health Care of Arizona, a subsidiary of MatureWell Inc., which has over 80,000 participating members. Because this number of members is actually considered to be quite small for a well-established HMO, our system has to be very scalable to accommodate for growth and meet the needs of larger HMOs. This project was just one of the many issues we had to overcome during the migration process for Premier.
Back to the Development Aspects
After building the user interface, it was time to move on to the stored procedure. Just building the sProc was not enough. It took several minutes to finish executing, and this was not acceptable. Thus, I found myself spending several hours trying to optimize it.
After modifying and adding additional INDEXES, restructuring the SQL and the WHERE clause, I found that it still took about five minutes to run and generate the recordset. When this was moved into our production site, which uses a four processor I386 architecture and two gigabytes of RAM, the user had to wait for what we still consider to be way too long. Imagine staring at a "status bar" for minutes waiting for the results to be returned; not a good thing.
A Better Way
First, in order to fit with the rest of our system architecture, we decided to move the business logic into a server-side COM object. The logic included into this COM object allowed us to make an asynchronous call to an already created DTS package in MS SQL 7. In order to reference the DTS object model, we used SQL Distributed Management Objects or SQL DMO. This is an object model found inside of MS SQL 7.
Here is a snippet of the method that does this.
Public Function AsyncExecute(ByVal PackageName As String, ByVal Server As String, Optional ByVal UserName As String, _
Optional ByVal UserPass As String, Optional ByVal PackagePassword As String, Optional ByVal GlobalVariables As Variant) As Boolean
Dim oPackage As DTS.Package
Dim oJob As SQLDMOUtil.Job
Dim sJobName As String
Dim i As Integer
On Error GoTo ErrorHandler
Set oPackage = CreateObject("DTS.Package")
If Trim(UserName) <> "" Then
oPackage.LoadFromSQLServer Server, UserName, UserPass, DTSSQLStgFlag_Default, PackagePassword, , , PackageName
Else
oPackage.LoadFromSQLServer Server, , , DTSSQLStgFlag_UseTrustedConnection, PackagePassword, , , PackageName
End If
If Not IsMissing(GlobalVariables) Then
For i = 0 To UBound(GlobalVariables)
oPackage.GlobalVariables(GlobalVariables(i, 0)).Value = GlobalVariables(i, 1)
Next
End If
' Save the package with the new global variable values
If Trim(UserName) <> "" Then
oPackage.SaveToSQLServer Server, UserName, UserPass, DTSSQLStgFlag_Default
Else
oPackage.SaveToSQLServer Server, , , DTSSQLStgFlag_UseTrustedConnection
End If
' Create a job object to schedule this DTS packae to run
Set oJob = CreateObject("SQLDMOUtil. Job")
' Generate a semi unique job name based on the package name and the hour minute and seconds
sJobName = PackageName & "_" & CStr(Hour(Time())) & CStr(Minute(Time())) & CStr(Second(Time()))
' Schedule the package to run immediately and delete itself after running whether the job was successfull or not
oJob.ScheduleDTSPackage sJobName, PackageName, Server, Date, Time(), , , , , , SQLDMOComp_Always, True
' Clean up
Set oPackage = Nothing
Set oJob = Nothing
AsyncExecute = True
#If bUseMTS Then
oCtx.SetComplete
#End If
Exit Function
The purpose of the Asynchronous call was to allow the user to regain control of the browser quickly, instead of having to wait until the stored procedure was finished executing, and the recordset was generated. Instead, using our asynchronous method, the process would work like this:
Change the values of the DTS global variables we created to reflect the values of the parameters that were passed into our asynchronous method as a two dimensional array, which we got from the user via the html form. Then we needed to build a dynamic SQL string using these values, and assign it to the "Data Pump Task" property of DTS. This allows us to call the sProc and pass it the dynamic parameters on the "fly"
Create a new SQL "JOB" and attach our dynamically modified DTS package
Schedule the JOB to run immediately.
After completing the process, delete the JOB. We still have the DTS package to use next time, just not the JOB that scheduled it to run for this instance.
The Logic Inside of the ASP
<%
'dim the variants
dim objDTSJOB
dim sPackageName
dim sServerForPackage
'dim the array for storing the parameters
dim arrayGblVars
redim arrayGblVars(13,1)
'name of the DTS Package
sPackageName = "MyDTSPackage"
'name of the database server
sServerForPackage = "MyServer"
'populate the array. The name values in the array must be the same
'name as the global variable in the DTS.
'Use the values from the html form
arrayGblVars(0,0) = "Name0"
arrayGblVars(0,1) = Request.Form("Value")
arrayGblVars(1,0) = "Name1"
arrayGblVars(1,1) = Request.Form("Value")
arrayGblVars(2,0) = "Name2"
arrayGblVars(2,1) = Request.Form("Value")
arrayGblVars(3,0) = "Name3"
arrayGblVars(3,1) = Request.Form("Value")
arrayGblVars(4,0) = "Name4"
arrayGblVars(4,1) = Request.Form("Value")
arrayGblVars(5,0) = "Name5"
arrayGblVars(5,1) = Request.Form("Value")
arrayGblVars(6,0) = "Name6"
arrayGblVars(6,1) = Request.Form("Value")
arrayGblVars(7,0) = "Name7"
arrayGblVars(7,1) = Request.Form("Value")
arrayGblVars(8,0) = "Name8"
arrayGblVars(8,1) = Request.Form("Value")
arrayGblVars(9,0) = "Name9"
arrayGblVars(9,1) = Request.Form("Value")
arrayGblVars(10,0) = "Name10"
arrayGblVars(10,1) = Request.Form("Value")
arrayGblVars(11,0) = "Name11"
arrayGblVars(11,1) = Request.Form("Value")
arrayGblVars(12,0) = "Name12"
arrayGblVars(12,1) = Request.Form("Value")
arrayGblVars(13,0) = "Name13Email"
arrayGblVars(13,1) = AUOEmailValue
'Create an instance of the object that will build a DTS JOB, execute it, then delete the JOB.
'control will be returned back to the browser as soon as the method is executed.
on error resume next
set objDTSJOB = Server.CreateObject("SQLDMOUtil.DTSPackage")
objDTSJOB.AsyncExecute sPackageName, sServerForPackage,,,,arrayGblVars
if err.number <> 0 then
Response.Write "There was an error, please run the report again. "
Response.Write "The following error occured: " & err.description
else
Response.Write "Your request has been submitted and the data will be emailed to you in the next few minutes."
end if
set objDTSJOB = nothing
%>
Main Advantage
The user will gain back control of the browser as soon as the JOB gets scheduled (almost immediately). The browser’s response will be a message saying "the data will be emailed to you in a few minutes". As far as the email goes, our site is configured using Site Server 3.0 and it's Personalization and Membership Directory. Thus, every user has a Site Server account, which stores personal information, including each users email address.
So, we query this LDAP server, retrieve the user’s email address from the AUO object, and this value is also one of those global variables that is passed inside of the array. Now, while they are waiting for the data to be emailed to them, then can be running other reports, or doing whatever else they want since they will no longer have to wait for the asp page to load the response. The generated recordset will be attached to the email as an Excel spreadsheet.
Summary
I was given the task of creating a report that would allow the user to pass in specific parameters, call a stored procedure, generate a recordset, and then display it in Excel via the user’s browser. The problem that I encountered was the amount of time required for this process to complete.
So, realizing this was not an optimal solution we decided to transfer control of the process to the server as soon as possible so the user could go about their other activities, instead of waiting and being tied down to the browser until the result it brought back to display. To do this, we used SQL DMO, ASP, AND DTS.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP Articles
More By Jeff Mangan
 developerWorks - FREE Tools!
|
This demonstration gives you an overview of IBM® Rational® Build Forge Express Edition, a global offering that provides a framework to automate and execute software processes. Rational Build Forge provides a software assembly line that can support all of your tools, technologies, and platforms so you can achieve a repeatable, reliable, and traceable build and release process.
FREE! Go There Now!
|
|
|
|
Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download IBM DB2 Express-C 9.5, a no-charge version of DB2 Express 9 database server. DB2 Express-C offers the same core data server base features as other DB2 Express editions and provides a solid base to build and deploy applications developed using C/C++, Java, .NET, PHP, and other programming languages.
FREE! Go There Now!
|
|
|
|
Download the IBM WebSphere Portal V6.1 beta code and learn more about the rich features and enhancements in IBM WebSphere Portal V6.1. WebSphere Portal provides a composite application or business mashup framework and the advanced tooling needed to build flexible, SOA-based solutions, and scalability to meet the needs of any size organization.
FREE! Go There Now!
|
|
|
|
Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started.
FREE! Go There Now!
|
|
|
|
Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle.
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components.
FREE! Go There Now!
|
|
|
|
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, where he will overview Rational’s new offerings and programs to help customers accelerate software innovation on System z. He will discuss how these solutions help organizations extend their core business processes toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
FREE! Go There Now!
|
|
|
|
The Eclipse community is constantly working to extend Eclipse's functionality. In this webcast, learn about some of the most important and feature-rich projects under development. From multi-language support to plug-in development, tune in to see what Eclipse is capable of now.
FREE! Go There Now!
|
|
|
|
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
