| Programmer to ProgrammerTM | |||||
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
| |||||||||||||||||||
| The ASPToday
Article April 9, 2001 |
Previous
article - April 6, 2001 |
||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
| ABSTRACT |
| ||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||||
| Article Discussion | Rate this article | Related Links | Index Entries | ||||||||
| ARTICLE | |||||||||||
Recently, our consulting firm was called in to look at the performance of a web site deployed using ASP, VBScript 5.5, ADO, Windows 2000 Advanced Server and SQL Server 2000. We adopted a variety of optimizations in order to decrease user latency and increase user capacity. The technologies associated with these performance enhancements are presented in this article. These technologies include:
In order to isolate performance bottlenecks, a series of typical use scenarios were explored. These scenarios generated ninety percent of the web site's traffic. When the web site's behavior was analyzed, the following observations were made:
The head of IS at the company felt an array of new servers should be purchased. Given the current power shortage in California, we felt a better solution would be to improve the software. Our specific improvement was going to be the caching of the queries in XML. Rather than continually executing against the database, the results of queries would be stored in files (a.k.a. cached). If the database was updated a SQL trigger would mark the cache as stale (in layman's terms, "delete the cache file").
Before embarking on our performance improvement, we decided to verify that it actually improved performance. Our prototype test was simple:
The results of this prototyping are as follows:
This approach yielded an order-of-magnitude performance gain and was hence adopted. The performance increase is two fold: our application's performance increased and the database was freed up to service other applications. For those interested in how performance was measured, please see "Appendix A" of this article.
Further enhancements were made to improve the performance of the web site in question. These enhancements are not discussed in this article but included the use of a RAM disk. Those interested using memory (RAM) as a virtual disk (memory is cheap and disks are slow) should see the following URLs:
Within this articles source code, the letters "SWP" prefix all functions, classes, table names, trigger names and index. These letters are an abbreviation for our corporation (Software Pronto, Inc., www.softwarepronto.com) and are used to distinguish this article's code from standard Microsoft API's and SQL Server structures.
For the purpose of this article a table was created that contains jobs listings. This is not the actual table used in the web site that was optimized. The jobs listing table reflects the type of table frequently accessed by a great many former "dot-Com startup developers". The table is created as follows:
CREATE TABLE SWPContractingJobs (
JobID INTEGER PRIMARY KEY IDENTITY(1, 1),
JobTitle VARCHAR(50) NOT NULL DEFAULT 'no title',
JobDescription VARCHAR(255) NOT NULL DEFAULT 'no description',
timestamp)
The columns of table, SWPContractingJobs, are self-documenting. The caching scheme adopted is based on the first letter of the job title. All jobs beginning with the letter, 'A', are cached in a file called, SWPContractingJobsA.XML. Jobs beginning with the letter, 'B', are cached in SWPContractingJobsB.XML and so on. This form caching serves as an example of how to implement a cached solution and should not be considered a "real world" solution. Each deployment has its own caching needs. This solution was designed to be understandable and easily made practical once all caching considerations were accounted for.
The column of interest inside table, SWPContractingJobs, is "timestamp ". A "timestamp " column is an automatically generated binary number (VARBINARY(8)) that is updated whenever the row is updated. This number only increases in size so an inserted row yields a new (highest value) timestamp. SQL Server 2000 provides a synonym for timestamp, namely, "rowversion ". This synonym is provided to support SQL-92 compatibility.
On any insertion or update to the table, a SQL trigger, SWPTrigger01, is fired. This trigger will be examined in detail in a later section. The trigger, SWPTrigger01, contains a SELECT statement that uses the timestamp field. This SELECT statement determines which rows were added or modified to table, SWPContractingJobs.
In order for this scheme to work, the value of the last timestamp must be persisted. The table, SWPTriggerStamps, is used to persist the last timestamp associated with the first letter of each job title. The trigger, SWPTriggerStamps, maintains the caches associated with SWPContractingJobs. The table used to store the timestamps, SWPTriggerStamps, is defined as follows:
CREATE TABLE SWPTriggerStamps (
TriggerName VARCHAR(128) PRIMARY KEY,
LastTimestamp binary(8) NOT NULL DEFAULT 0x00000000)
This SELECT statement used by trigger, SWPTrigger01, that monitors table, SWPContractingJobs, is defined as follows:
SELECT LEFT(A.JobTitle, 1) JobTitleLetter,
MAX(A.timestamp) LetterTimeStamp
FROM SWPContractingJobs A, SWPTriggerStamps B
WHERE (A.timestamp>B.LastTimeStamp) AND
(B.TriggerName='SWPTrigger01')
GROUP BY LEFT(A.JobTitle, 1)
A typically selection by this SQL statement is as follows (query generated with SQL Server's Query Analyzer):
A cache is maintained on a per-starting-letter basis for the column, JobTitle. If a new JobTitle is added starting with the letter 'A' then the cache associated with all jobs beginning with the letter 'A' will be updated. In the previous result set, the column JobTitleLetter represents maps to the first letter of a JobTitle. The SQL function, LEFT, ensures that job titles are selected based on first-letter only. The GROUP BY clause ensures that the data selected is grouped by first-letter. The cache for each of the per-letter selections will be set to stale (file deleted). The function, MAX, ensures that only one row per-letter is selected. The row selected by MAX is the row with the maximum timestamp.
The trigger will determine the maximum of these per-letter timestamps and record this in the table, SWPTriggerStamps. This value is used to ensure that only the caches associated with modified data are marked as stale. Entries in SWPContractingJobs are not updated if the following is true: SWPContractingJobs.timestamp < SWPTriggerStamps.LastTimeStamp
A set of SQL scripts can be downloaded to create the database, triggers, tables, indexes and initial data data associated with the project. These scripts are defined as follows:
A C++ project, SWPSetup01, is also provided that sets up all the appropriate SQL Server constructs used in this article
The trigger, SWPTrigger01, implements the following steps:
The trigger is clearly quite sophisticated in nature and must actually access cached files. For this reason, the trigger was implemented in a high-level language. Actually two implementations of the trigger are provided:
SQL Server provides a stored procedure call, xp_cmdshell, that allows executables to be launched from within a SQL statement. This stored procedure call is actually what is referred to as an "extended stored procedure call." The stored procedure call, xp_cmdshell, is not found in every database instance of a given SQL Server installation, but is found in the Master database. When the stored procedure call, xp_cmdshell, is executed, the database name must be fully qualified as master..xp_cmdshell.
The trigger, SWPTrigger01, was created using the stored procedure call, xp_cmdshell, to launch C++ executable, SWPTrigger01.exe, as follows:
CREATE TRIGGER SWPTrigger01 ON SWPContractingJobs
AFTER INSERT, UPDATE AS
EXEC master..xp_cmdshell 'c:\\Bin\\SWPTrigger01.exe', NO_OUTPUT
The trigger, SWPTrigger01, was created using the stored procedure call, xp_cmdshell, to launch the VB Script, Windows Scripting Host application as follows:
CREATE TRIGGER SWPTrigger01 ON SWPContractingJobs
AFTER INSERT, UPDATE AS
EXEC master..xp_cmdshell
'cscript c:\\Bin\\SWPTrigger01.wsf', NO_OUTPUT
Each flavor of the trigger was created using SQL Server 2000. For those running SQL Server 7.0, beware that there may be issues.
The C++ source file, SWPTrigger01.cpp, and the VB Script file, SWPTrigger01.wsf, contain equivalent implementations of the trigger. Those interested in the C++ version can download that source code. This article only presents the VB Script implementation. All comments made with respect the VB Script implementation apply to the C++ implementation.
An excerpt from the Windows Scripting Host stored procedure call, SWPTrigger01.wsf, is as follows:
Dim args
Dim newScript
Set args = WScript.Arguments
If args.Count = 0 Then
Dim shell
newScript = "wscript " & WScript.ScriptFullName & " dummyarg"
Set shell = WScript.CreateObject("WScript.Shell")
WScript.Quit shell.Run(newScript, 0, FALSE)
Else
SWPTriggerBody
End If
The previous code snippet shows the trigger's source code performing the following tasks:
Basically, the script launches a copy of itself with an additional command-line argument. This is because when the table, SWPContractingJobs, is updated, the trigger (SWPTrigger01) is executed. SQL Server does not complete processing the update until after the trigger is executed. When the table, SWPContractingJobs, is updated, the row being changed is locked. If the trigger were to perform a SELECT operation on table, SWPContractingJobs, this SELECT operation would hang because of the locked row. SQL Server is waiting on the triggers completion and the client application is waiting on SQL Server to complete the update operation. This is a classic deadlock scenario as demonstrated below (circular arrows indicate a deadlock):
The trigger's executable, SWPTrigger01.wsf, spawns a copy of itself to break the deadlock. The newly spawned version performs a SELECT operation on table, SWPContractingJobs. The original instance of the trigger exits once it has spawned a copy of itself. When the original trigger exits, then SQL Server allows the update to be complete and the row of the table, SWPContractingJobs, is unlocked. The newly created instance of SWPTrigger01.wsf is not deadlocked and can therefore perform the select without blocking.
The second instance of the trigger performs the real work, namely to:
The excerpt from SWPTrigger01.wsf that performs these operations is as follows:
Sub SWPTriggerBody()
Dim sConStr, sQuery
Dim dbConn, dbRSet, dbCommand
Dim lRecordsAffected
On Error Resume Next
Set dbConn = WScript.CreateObject("ADODB.Connection")
dbConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=localhost;" & _
"DATABASE=SWPASPToday;USER ID=sa;PASSWORD=sa;"
sQuery = "SELECT LEFT(A.JobTitle, 1) JobTitleLetter, " & _
"MAX(A.timestamp) LetterTimeStamp " & _
"FROM SWPContractingJobs A, SWPTriggerStamps B " & _
"WHERE (A.timestamp>B.LastTimeStamp) AND " & _
"(B.TriggerName='SWPTrigger01') " & _
"GROUP BY LEFT(A.JobTitle, 1)"
' adCmdText=1
Set dbRSet = dbConn.Execute(sQuery, lRecordsAffected, 1)
Dim jobTitleLetter, letterTimeStamp
Dim fileName
Dim fso
'////////////////////////////////////////////////////////////
'// Mark caches as stale with delete file
'////////////////////////////////////////////////////////////
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
Do Until dbRSet.EOF
jobTitleLetter = dbRSet.Fields("JobTitleLetter").Value
letterTimeStamp = dbRSet.Fields("LetterTimeStamp").Value
fileName = "c:\Bin\SWPContractingJobs" & _
jobTitleLetter & _
".xml"
' If the cache is in-use then it won't be deleted
' Loop until it is cleared
While fso.FileExists(fileName)
fso.DeleteFile fileName
Wend
If IsEmpty(maxTimeStamp) Then
maxTimeStamp = letterTimeStamp
ElseIf StrComp(letterTimeStamp, maxTimeStamp) = 1Then
maxTimeStamp = letterTimeStamp
End If
dbRSet.MoveNext
Loop
dbRSet.Close
set dbRSet = nothing
'////////////////////////////////////////////////////////////
'// Set new maximum timestamp in table, SWPTriggerStamps
'////////////////////////////////////////////////////////////
If NOT IsEmpty(maxTimeStamp) Then
Set dbCommand = WScript.CreateObject("ADODB.Command")
dbCommand.ActiveConnection = dbConn
dbCommand.CommandText = "SWPUpdateTriggerTimeStamp"
' adCmdStoredProc = 4
dbCommand.CommandType = 4
dbCommand.Parameters("@TriggerName").Value = "SWPTrigger01"
dbCommand.Parameters("@LastTimeStamp").Value = maxTimeStamp
dbCommand.Execute
set dbCommand = nothing
End If
dbConn.Close
set dbConn = nothing
End Sub
There are potential race conditions where a client could create a new copy of the cache while the trigger is marking the cache as stale (deleting the file). For this reason the file deletion is performed in a loop as follows:
While fso.FileExists(fileName)
fso.DeleteFile fileName
Wend
The maximum timestamp for the trigger could face a similar race condition. One instance of trigger SWPTrigger01 could be updating this table with timestamp A. Meanwhile another instance of trigger SWPTrigger01 could have updated this table with timestamp B. If timestamp A were less than B then additional caches would be marked as stale. The stored procedure, SWPUpdateTriggerTimeStamp, updates the value of a trigger's timestamp. This stored procedure is written to ensure that only a true maximum value of a timestamp is actually updated (the WHERE clause's @LastTimeStamp > LastTimeStamp). The stored procedure, SWPUpdateTriggerTimeStamp, is defined as follows:
CREATE PROCEDURE SWPUpdateTriggerTimeStamp
@TriggerName VARCHAR(128), @LastTimeStamp VARBINARY(8) AS
UPDATE SWPTriggerStamps
SET LastTimeStamp = @LastTimeStamp
WHERE TriggerName = @TriggerName AND
@LastTimeStamp > LastTimeStamp
Two clients are provided that make use of the cached version of table, SWPContractingJobs. These client are:
For the purposes of this article, only the Active Server Page, XMLResults.asp, will be reviewed and critiqued. The C++ project, SWPSetup01, was similarly implemented to its ASP counterpart. The source file for the Active Server Page is XMLCache.asp. This source file contains a function, strCacheFile, that creates the cache file name. Source file, XMLCache.asp, contains a function, XMLGetCache, that sets up the cache file using an ADO Recordset's Save method. These functions are as follows:
function strCacheFile(strHash)
strCacheFile="SWPContractingJobs"&strHash&".XML"
end function
function XMLGetCache(strHash)
'Try to recreate the xml file designated by the hash
dim strSQL,strConn
const adPersistXML=1
strSQL="select JobTitle, JobDescription from SWPContractingJobs " & _
"where JobTitle like '"&strHash&"%'"
strConn="Provider=SQLOLEDB;Database=SWPAspToday;uid=sa;pwd=;"
'setup connection through ADO
dim objConn
set objConn=CreateObject("ADODB.Connection")
objConn.ConnectionString=strConn
objConn.Open
'create the record set
dim objRS
set objRS = objConn.Execute(strSQL)
'persist it to cache file...
objRS.Save Server.MapPath(strCacheFile(strHash)),adPersistXML
set objRS=nothing
set objConn=nothing
end function
The XSL file associated with each XML cache is MyResults.xsl. The source file, XMLCache.asp, contains a function, bXMLResults, that determines if the XML cache file exists or not. XMLCache.asp also contains a function, XMLDisplay, that loops ten times attempting to create the cache file. This loop is necessary because there is a small chance that after a Recordset uses Save to create the XML file, the trigger, SWPTrigger01, could fire and "refresh the cache" (delete the file). Looping ten times was initially selected as an arbitrary number. This value was chosen and then tested. Under worst-case conditions the loop iterated twice. For the case of the database running on an extremely fast computer and the web server running on an extremely slow computer ten might be too small a number. Spending a hundred bucks to upgrade a machine's CPU might just ensure that ten is reasonable number of iterations. The functions, bXMLResults and XMLDisplay, are as follows:
function bXMLResults(strHash)
'create page using the hashing file
'this will fail if the hashing file has been deleted
dim XmlResult,XmlStyle,HTMLResult
set XmlResult= Server.CreateObject("Microsoft.XMLDOM")
XmlResult.async=false
XmlResult.load(Server.MapPath(strCacheFile(strHash)))
if (XmlResult.parseError =0)then
set XmlStyle=Server.CreateObject("Microsoft.XMLDOM")
XmlStyle.async=false
XmlStyle.load(Server.MapPath("myresults.xsl"))
if (XmlStyle.parseError <>0) then
Response.Write(XmlStyle.parseerror.reason)
end if
HTMLResult=XmlResult.transformNode(XmlStyle)
Response.Write(HTMLResult)
bXMLResults=true
else
'xml file could not be loaded
bXMLResults=false
end if
end function
function XMLDisplay(strHash)
dim LoopCnt
Response.Write("<h3>Titles starting with the letter:" & _
strHash& "</h3>")
LoopCnt=10
'Try to display the XML file designated by the hash
while (LoopCnt>0) and not bXMLResults(strHash)
'if the file does not exist try to recreate it
XMLGetCache(strHash)
LoopCnt=LoopCnt-1
wend
end function
The HTML associated with displaying the cached information is a bit anti-climactic given the amount of setup that went into developing the cache. Readers can see the source file, XMLCache.asp, for more detail.
The XML caching scheme presented in this article is a common sense approach to caching and should not be considered, "The Complete Guide To Caching Under Every Situation." Two specific criticisms could be levied against this approach:
Actually, both of these "issues" can be solved in a reasonable manner. The intent was to write a novella and not an entire textbook. Readers who want a "Part II" to this article should specify this in their reader comments.
With respect to deleting entries, the current approach could be enhanced. The table SWPTriggerStamps could hold a row for each cached file. At present table SWPTriggerStamps contains one row per-table from which cached data is extracted. The number of entries in the cache could be stored in table SWPTriggerStamps. The entry count value would be in addition to the timestamp presently stored in table, SWPTriggerStamps. When the number of entries decreased or the timestamp changed a cache would be marked as stale. The SQL used to create this table would no longer treat column TriggerName as the primary key. The SQL used to create the enhanced version of table SWPTriggerStamps is as follows:
CREATE TABLE SWPTriggerStamps
(TriggerName VARCHAR(128),
LastTimestamp binary(8) NOT NULL DEFAULT 0x00000000,
CacheLetter CHAR(1),
LastCount int DEFAULT 0)
The SELECT statement that detects inserts, updates and deletes would be extremely complicated. It would actually be simpler to have one SELECT statement that detects inserts and updates and a second SELECT statement that detects deletions.
As mentioned previously, the triggering mechanism would be costly if a large amount of data is updated all at the same time. Under these circumstances, a variety of remedies exist. The trigger could be disabled until the majority of the data has been updated. Before the final data element is updated, the trigger could be re-added to the database. Clearly, this approach would only work if it was known when a specific client or clients would performs updates.
Another approach to both the delete and bulk update issue would be not to use a trigger. Each insert, update or delete could be made responsible for marking the specific cache as stale. The trigger represented a convenience and is not specifically necessary in order to support caching.
The section of the article demonstrates a reasonably simple approach to measuring performance on a Windows NT or Windows 2000 computer. This approach to performance measurement was used to determine the cost of cached queries versus queries made directly against the database.
Every language and operating system provides mechanisms for evaluating time. Under windows, one of the most precise mechanisms is the high-resolution performance counter. This counter is accessed using the Win32 API. The frequency of this counter (counter ticks-per-second) varies depending on a combination of the underlying hardware and version of the operating system. In theory, faster hardware means a higher frequency. The following functions are used to determine the frequency of the high-resolution performance counter and to query its value at a given moment in time:
Using C++, the time to perform a specific task is determined using the high frequency performance counters as follows:
LARGE_INTEGER liStart, liEnd, liFrequency;
FLOAT fTime; // Will contain time in seconds including
// decimal, subsecond portion
::QueryPerformanceFrequency(&liFrequency);
::QueryPerformanceCounter(&liStart);
// *******************************************************
// Perform task whose performance is to be measured here
// *******************************************************
::QueryPerformanceCounter(&liEnd);
fTime = (FLOAT)(liEnd.QuadPart - liStart.QuadPart)/
(FLOAT)(liFrequency.QuadPart);
The high-frequency performance counter for a given machine is closely tied to it hardware. For this reason it is important to insure that the task being executed runs on a single CPU even if the machine supports multiple CPU's. Running the task on a single CPU avoids certain bugs found in a machine's BIOS and/or the Hardware Abstraction Layer (HAL). Remember, that a hardware vendor or a third-party may have developed the HAL. For this reason the function, SetThreadAffinityMask is used to ensure the task executes on a single CPU.
To more accurately measure a task's performance, measurement should begin when the thread starts a fresh time quantum. This can be achieved by using the Sleep function in order to surrender the remaining time quantum - a.k.a. Sleep(0). When the thread wakes, performance measurement commences at the start of a new time quantum.
The code used to determine cached versus non-cached performance is as follows:
static HRESULT SWPShowTiming(CComPtr<ADODB::_Connection>& spConnection)
{
_bstr_t bstrCachedData;
long lTimingCount = 0;
LARGE_INTEGER liStart;
LARGE_INTEGER liEnd;
LARGE_INTEGER liFrequency;
static const long lMaxCount = 1000;
FLOAT fTime;
HRESULT hr = S_OK;
DWORD dwOrgAffinityMask = 0;
if (!(dwOrgAffinityMask =
::SetThreadAffinityMask(::GetCurrentThread(), 1)))
{
return HRESULT_FROM_WIN32(::GetLastError());
}
::QueryPerformanceFrequency(&liFrequency);
::Sleep(0);
::QueryPerformanceCounter(&liStart);
for (lTimingCount = 0; lTimingCount < lMaxCount; lTimingCount++)
{
if (FAILED(SWPGetCachedData(spConnection, L'A', bstrCachedData)))
{
return hr;
}
}
::QueryPerformanceCounter(&liEnd);
fTime = (FLOAT)(liEnd.QuadPart - liStart.QuadPart)/
(FLOAT)(liFrequency.QuadPart);
cout << "Time for (" << lMaxCount << ") cached iterations: "
<< fTime
<< endl;
::Sleep(0);
::QueryPerformanceCounter(&liStart);
for (lTimingCount = 0; lTimingCount < lMaxCount; lTimingCount++)
{
if (FAILED(SWPGetNeverCachedData(spConnection,
L'A',
bstrCachedData)))
{
return hr;
}
}
::QueryPerformanceCounter(&liEnd);
fTime = (FLOAT)(liEnd.QuadPart - liStart.QuadPart)/
(FLOAT)(liFrequency.QuadPart);
cout << "Time for (" << lMaxCount << ") non-cached iterations: "
<< fTime
<< endl;
if (!::SetThreadAffinityMask(::GetCurrentThread(), dwOrgAffinityMask))
{
return HRESULT_FROM_WIN32(::GetLastError());
}
return hr;
}
In the previous code snippet the function, SWPGetNeverCachedData, performs a SQL query each time against the SQL Server database. The function, SWPGetCachedData, retrieves the data from cache rather than the SQL Server database. These function generate XML as follows:
The functions, SWPGetNeverCachedData and SWPGetCachedData, are implemented as follows:
static HRESULT
SWPGetNeverCachedData(CComPtr<ADODB::_Connection>& spConnection,
WCHAR whichLetter,
_bstr_t& bstrCachedData)
{
HRESULT hr = S_OK;
WCHAR letterArray[2] = {whichLetter, 0};
CComBSTR bstrCommand = (WCHAR *)nSWPSQL::bstrMasterSelect;
CComPtr<ADODB::_Recordset> spRecordset;
CComPtr<ADODB::_Stream> spStream;
long lSize;
BSTR bstrTemp = NULL;
if (FAILED(hr =
spRecordset.CoCreateInstance(__uuidof(ADODB::Recordset))))
{
return hr;
}
bstrCommand += letterArray;
bstrCommand += L"%'";
if (FAILED(hr = spRecordset->Open(
CComVariant(bstrCommand),
CComVariant(spConnection),
ADODB::adOpenForwardOnly,
ADODB::adLockReadOnly,
ADODB::adCmdText)))
{
return hr;
}
if (FAILED(hr = spStream.CoCreateInstance(__uuidof(ADODB::Stream))))
{
return hr;
}
if (FAILED(hr = spRecordset->Save(CComVariant(spStream),
ADODB::adPersistXML)))
{
return hr;
}
if (FAILED(hr = spStream->get_Size(&lSize)))
{
return hr;
}
if (FAILED(hr = spStream->ReadText(lSize, &bstrTemp)))
{
return hr;
}
bstrCachedData.Assign(bstrTemp);
return hr;
} // SWPGetNeverCachedData()
static HRESULT
SWPGetCachedData(CComPtr<ADODB::_Connection>& spConnection,
WCHAR whichLetter,
_bstr_t& bstrCachedData)
{
HRESULT hr = S_OK;
HANDLE hFile = NULL;
DWORD dwSize = 0;
DWORD dwNumRead = 0;
string strCachedData; // data stored ini ANSI
// Use _bstr_t because it converts WCHAR/CHAR automatically
// The file name is the Table followed by the Letter queried
_bstr_t bstrFileName = L"c:\\Bin\\SWPContractingJobs";
WCHAR letterArray[2] = {whichLetter, 0};
CComBSTR bstrCommand = (WCHAR *)nSWPSQL::bstrMasterSelect;
bstrFileName += letterArray;
bstrFileName += L".xml";
bstrCommand += letterArray;
bstrCommand += L"%'";
// Loop here to avoid a race condition that takes place:
// 1) if the table is updated
// 2) the trigger files
// 3) The trigger deletes the file just saved (spRecordset->Save)
while (true)
{
if (!::PathFileExists((TCHAR *)bstrFileName))
{
CComPtr<ADODB::_Recordset> spRecordset;
VARIANT vFileName;
::VariantInit(&vFileName);
if (FAILED(hr = spRecordset.CoCreateInstance(
__uuidof(ADODB::Recordset))))
{
return hr;
}
if (FAILED(hr = spRecordset->Open(
CComVariant(bstrCommand),
CComVariant(spConnection),
ADODB::adOpenForwardOnly,
ADODB::adLockReadOnly,
ADODB::adCmdText)))
{
return hr;
}
vFileName.vt = VT_BSTR;
V_BSTR(&vFileName) = bstrFileName;
if (FAILED(hr = spRecordset->Save(vFileName,
ADODB::adPersistXML)))
{
return hr;
}
}
hFile = ::CreateFile(bstrFileName,
GENERIC_READ,
FILE_SHARE_READ,
NULL,
OPEN_EXISTING,
0,
NULL);
if (INVALID_HANDLE_VALUE == hFile)
{
DWORD dwError = ::GetLastError();
if (ERROR_FILE_NOT_FOUND == dwError)
{
continue; // This handles the race condition
}
return HRESULT_FROM_WIN32(dwError);
}
if (0xFFFFFFFF == (dwSize = ::GetFileSize(hFile, NULL)))
{
hr = HRESULT_FROM_WIN32(::GetLastError());
break;
}
strCachedData.resize(dwSize);
if (0 == strCachedData.length())
{
hr = E_OUTOFMEMORY;
break;
}
if (!::ReadFile(hFile,
const_cast<CHAR *>
(strCachedData.c_str()),
dwSize,
&dwNumRead,
NULL))
{
hr = HRESULT_FROM_WIN32(::GetLastError());
break;
}
break;
}
if (!::CloseHandle(hFile))
{
// hr may have been set to error in the while loop
if (SUCCEEDED(hr))
{
hr = HRESULT_FROM_WIN32(::GetLastError());
}
}
bstrCachedData = strCachedData.c_str();
return hr;
} // SWPGetCachedData()
|
| |||||||
| |||||||||||||||
|
| ASPToday is brought to you by
Wrox Press (http://www.wrox.com/). Please see our terms
and conditions and privacy
policy. ASPToday is optimised for Microsoft Internet Explorer 5 browsers. Please report any website problems to [email protected]. Copyright © 2001 Wrox Press. All Rights Reserved. |