Re: Recordsets vs. Arrays?
![Bob Barrows [MVP]'s profile photo](https://lh3.googleusercontent.com/a/default-user=s40-c)
Bob Barrows [MVP]
> Is there a performance advantage to parsing thru a recordset verus
> using an array?
>
> I'm currently trying to populate a listbox by returning data from my
> database, then either parsing thru the recordset until I reach the
> EOF, or putting the data into an array.
>
> Thanks for your assistance.
The definitive answer is here:
http://www.aspfaq.com/show.asp?id=2467
Bob Barrows
PS. My preference for listboxes is to use GetString
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Dave Anderson
> http://www.aspfaq.com/show.asp?id=2467
>
> PS. My preference for listboxes is to use GetString
Is there a way to insert the SELECTED attribute when you do this?
--
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
Bob Barrows [MVP]
> Bob Barrows [MVP] wrote:
>> http://www.aspfaq.com/show.asp?id=2467
>>
>> PS. My preference for listboxes is to use GetString
>
> Is there a way to insert the SELECTED attribute when you do this?
>
>
Bob Barrows [MVP]
> Bob Barrows [MVP] wrote:
>> http://www.aspfaq.com/show.asp?id=2467
>>
>> PS. My preference for listboxes is to use GetString
>
> Is there a way to insert the SELECTED attribute when you do this?
>
>
>
AS400 for use in various pages:
Function FacilityDropdown(pName, pDefault,pAllowAll)
dim cn,rs,strsql,sOptions, sHTML, i
sHTML=Session("Fac_HTML")
if len(sHTML) = 0 then
'|| is the concatenation operator in DB2 SQL
strsql = "SELECT '<OPTION value=""' || Facility || '"">' ||" & _
" Facility || ' - ' || Description || '</option>' " & _
"FROM Facilities where Record_ID='MF' and Facility <> '61' " & _
"and Facility between 'AA' and '99' ORDER BY Facility"
ODBCDatasource = "censored"
set cn = server.createobject("ADODB.CONNECTION")
set rs=Server.CreateObject("adodb.recordset")
cn.open ODBCDatasource
set rs = cn.Execute(strsql,,1)
if not rs.eof then sOptions= rs.GetString(,,"",vbCrLf)
rs.Close:set rs=nothing
cn.Close:set cn=nothing
sHTML="<SELECT style=""font-family:Lucida Console"">" & _
vbCrLf & sOptions & vbCrLf & "</SELECT>"
Session("Fac_HTML")=sHTML
end if
sHTML=replace(sHTML,"<SELECT","<SELECT name=""" & _
pName & """")
if pAllowAll then
if InStr(sHTML,"value=""ALL""") = 0 then
i=instr(sHTML,"<OPT")
if i>0 then
sHTML=left(sHTML,i-1) & _
"<OPTION value=""ALL"">ALL</OPTION>" & vbCrLf & _
mid(sHTML,i)
end if
end if
elseif InStr(sHTML,"""ALL""") > 0 then
sHTML=replace(sHTML, _
"<OPTION value=""ALL"">ALL</OPTION>" & vbCrLf, "")
end if
if len(pDefault) > 0 then
if InStr(sHTML,pDefault & """ SELECTED") = 0 then
sHTML=replace(sHTML," SELECTED","")
sHTML=replace(sHTML, _
"value=""" & pDefault & """>", _
"value=""" & pDefault & """ SELECTED>")
end if
else
if InStr(sHTML," SELECTED") > 0 then
sHTML=replace(sHTML," SELECTED","")
end if
end if
FacilityDropdown=sHTML
end function
Dave Anderson
>>> PS. My preference for listboxes is to use GetString
>>
>> Is there a way to insert the SELECTED attribute when you do this?
>>
> Yes, with a Replace statement.
I guess this also requires that you know your whether your data might
require Server.HTMLEncode().
My personal preference is to save user data as entered, so I always use
Server.HTMLEncode on attribute values populated from user-submitted values.
This would render GetString() somewhat less uniformly useful.
Which, combined with your technique, leads to a question -- does SQL Server
2005 offer HTMLEncode functionality?
Bob Barrows [MVP]
> Bob Barrows [MVP] wrote:
>>>> PS. My preference for listboxes is to use GetString
>>>
>>> Is there a way to insert the SELECTED attribute when you do this?
>>>
>> Yes, with a Replace statement.
>
> I guess this also requires that you know your whether your data might
> require Server.HTMLEncode().
>
> My personal preference is to save user data as entered, so I always
> use Server.HTMLEncode on attribute values populated from
> user-submitted values. This would render GetString() somewhat less
> uniformly useful.
I'm not sure I follow. Rather than guess, I think I'll wait until you look
at the example I posted. Maybe if you relate your reservation to the
example, I'll understand your point better.
>
> Which, combined with your technique, leads to a question -- does SQL
> Server 2005 offer HTMLEncode functionality?
>
I don't know for sure, but given the ability to use the CLR, I would be
surprised if it didn't.
Bob Barrows
Bob Barrows [MVP]
> Bob Barrows [MVP] wrote:
>>>> PS. My preference for listboxes is to use GetString
>>>
>>> Is there a way to insert the SELECTED attribute when you do this?
>>>
>> Yes, with a Replace statement.
>
> I guess this also requires that you know your whether your data might
> require Server.HTMLEncode().
>
> My personal preference is to save user data as entered, so I always
> use Server.HTMLEncode on attribute values populated from
> user-submitted values. This would render GetString() somewhat less
> uniformly useful.
Oh wait, I see what you're getting at. If the Description data in my example
required encoding, I would not be able to use GetString. Unless ..
I need to think about this.
Bob
Chris Hohmann
news:%23K%23HUQ1S...@TK2MSFTNGP10.phx.gbl...
> Bob Barrows [MVP] wrote:
>> http://www.aspfaq.com/show.asp?id=2467
>>
>> PS. My preference for listboxes is to use GetString
>
> Is there a way to insert the SELECTED attribute when you do this?
Here's what I used. DBCFS is for single-select controls and DBLSS is for
multi-select controls:
<%
Function DBCFS(strConn,strSQL,varSelectedKey)
'I/O:
'--> strConn : Connection String
'--> strSQL : SQL Statement OR "Table" Name
'--> varSelectedKey : Variant that identifies which option should be
selected
'Notes:
'The function expects strSQL to return at least two(2) columns.
'Column 1 will be used to populate the value attribute of the option tag
'Column 2 will be used to populate the content of the option tag, ie. what
gets displayed
'Determine command type
Dim re, lngOptions
Set re = New RegExp
re.Pattern = "^\s*(SELECT|EXEC)"
re.IgnoreCase = True
If re.Test(strSQL) Then
lngOptions = &H1 'Command Text
Else
lngOptions = &H2 'Table
End If
'Get the data
Dim conn, rs, arr
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn
set rs = conn.Execute(strSQL,,lngOptions)
If Not rs.EOF Then arr = rs.GetRows()
rs.Close : Set rs = Nothing
conn.Close : Set conn = Nothing
'Build the option tags
Dim j,o
o=""
If IsArray(arr) Then
For j = 0 to UBound(arr,2)
o=o & "<option value=""" & Server.HTMLEncode(arr(0,j)) & """"
If arr(0,j) = varSelectedKey Then
o=o & " selected"
End If
o=o & ">" & Server.HTMLEncode(arr(1,j)) & "</option>" & vbCRLF
Next
Else
o=o & "<option>[No Option Data]</option>"
End If
DBCFS = o
End Function
Function DBLSS(strConn,strSQL)
'I/O:
'--> strConn : Connection String
'--> strSQL : SQL Statement OR "Table" Name
'Notes:
'The function expects strSQL to return at least three(3) columns.
'Column 1 will be used to populate the value attribute of the option tag
'Column 2 will be used to populate the content of the option tag, ie. what
gets displayed
'Column 3 determines if the select attribute should be set for the option
tag
'Determine command type
Dim re, lngOptions
Set re = New RegExp
re.Pattern = "^\s*(SELECT|EXEC)"
re.IgnoreCase = True
If re.Test(strSQL) Then
lngOptions = &H1 'Command Text
Else
lngOptions = &H2 'Table
End If
'Get data
Dim conn, rs, arr
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConn
Set rs = conn.Execute(strSQL,,lngOptions)
If Not rs.EOF Then arr = rs.GetRows()
rs.Close : Set rs = Nothing
conn.Close : Set conn = Nothing
'Build option tags
Dim j,o
o=""
If IsArray(arr) Then
For j = 0 to UBound(arr,2)
o=o & "<option value=""" & Server.HTMLEncode(arr(0,j)) & """"
If arr(2,j) Then
o=o & " selected"
End If
o=o & ">" & Server.HTMLEncode(arr(1,j)) & "</option>"
Next
Else
o=o & "<option>" & strSQL & "</option>"
End If
DBLSS = o
End Function
%>
Dave Anderson
> Oh wait, I see what you're getting at. If the Description data in my
> example required encoding, I would not be able to use GetString.
That was precisely my point. In your example, the db was generating HTML,
which more or less requires that you know in advance that your values are
safe to insert into the VALUE attribute of the OPTION elements.
Mark J. McGinty
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:uDDL3CzS...@tk2msftngp13.phx.gbl...
> SABmore wrote:
>> Is there a performance advantage to parsing thru a recordset verus
>> using an array?
>>
>> I'm currently trying to populate a listbox by returning data from my
>> database, then either parsing thru the recordset until I reach the
>> EOF, or putting the data into an array.
>>
>> Thanks for your assistance.
>
> The definitive answer is here:
> http://www.aspfaq.com/show.asp?id=2467
I think use of the word "definitive" here is something of an overstatement.
For one thing, there is an optimization technique available to the recordset
test that was not presented, that being explicit creation of field objects,
which will improve field value access times that rival the array method.
And even if field access is minutely slower, the overhead of allocating
space for a complete, sometimes redundant copy of the data as an array can
make the recordset method faster overall, depending on number of records,
cursor, etc.
Yet another optimization technique overlooked is to access the fields using
their ordinal positions rather than their names. This isn't quite as fast
as creating field objects, but is considerably faster than using names. The
article approached this concept in the GetRows section, except that it
talked about using a dictionary object as a lookup for field positions. It
didn't bother to delve into the performance hit *that* would incur -- for
sure there would be COM object access overhead... simple constants would be
much faster.
For another thing, the GetString test doesn't properly belong in the set,
it's too dissimilar from the others. Yes it can be faster [depending on
cursor] because it makes far fewer allocations, and avoids the overhead of
iteration in ASP code. But if you need to process something in each row
[and said processing cannot be performed in the SQL statement] it's not an
option.
Further, depending on the provider and cursor used to source it, GetString
can actually be quite a bit slower than iterating. (In principle I agree
that calling GetString on a recordset opened with a cursor other than a
firehose is usually nearly insane, but there are plausible scenarios.)
Almost lastly, this is in essence a comparison of field data access
techniques, not a test of connection pooling or query plan cache... point
being that including object creation/opening time in the results tends to
cloud the issue, as well as subject the test to potential skew from
transient server-side factors.
And lastly, the difference between the winner and the loser is 31.25 ms.
The resolution of Timer is going to be 15 ms by default on an SMP system, 10
ms on a single CPU system, so relative conclusions such as method A is x%
faster than method B aren't going to tend to be highly accurate. The test
should instead have an inner loop that redundantly accesses the fields of
each row [n] times, because that's what's really at the core of this
benchmark.
Of course this would incur the loop overhead that the article was so
concerned with, but that, I believe, is infantissimal: a quick test on my
system indicates that it is less than 0.0002 ms per iteration (using a for
loop.) Hmm, that doesn't consider calling MoveNext and testing EOF, which
could be consequential, but since we're talking about an inner loop here,
not relevant. Here's the code I used:
dim t, i, t2 : i = 0 : t = Timer
for i = 0 to 1000000 : next : t2 = Timer
Response.Write Left(CStr(((Timer - t) * 1000000.0) / CDbl(i)), 5) & _
" microseconds per iteration"
Maybe I'll construct an enhanced version of the drag-race... not today
though. :-)
-Mark
Bob Barrows [MVP]
> (using a for loop.) Hmm, that doesn't consider calling MoveNext and
> testing EOF, which could be consequential, but since we're talking
My understanding has always been that the entire process of moving through a
recordset is very processor-intensive given all the "stuff" that has to go
on under-the-hood. I have never seen a test result showing a recordset loop
to be faster than using GetRows. I'll be interested to see what you come up
with.
Bob Barrows [MVP]
> "Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
> news:uDDL3CzS...@tk2msftngp13.phx.gbl...
>> SABmore wrote:
>>> Is there a performance advantage to parsing thru a recordset verus
>>> using an array?
>>>
>>> I'm currently trying to populate a listbox by returning data from my
>>> database, then either parsing thru the recordset until I reach the
>>> EOF, or putting the data into an array.
>>>
>>> Thanks for your assistance.
>>
>> The definitive answer is here:
>> http://www.aspfaq.com/show.asp?id=2467
>
> I think use of the word "definitive" here is something of an
> overstatement.
>
> For one thing, there is an optimization technique available to the
> recordset test that was not presented, that being explicit creation
> of field objects, which will improve field value access times that
> rival the array method. And even if field access is minutely slower,
> the overhead of allocating space for a complete, sometimes redundant
> copy of the data as an array can make the recordset method faster
> overall, depending on number of records, cursor, etc.
>
Of course, this does not address the benefit of being able to discard the
cursor and close the connection immediately after doing the GetRows, but
some of that benefit can be achieved by using a client-side cursor and
disconnecting it. I may need to do some rethinking here. Thanks for jumping
in. I've never seen any data to refute the "arrays are faster" contention.
Bob Barrows
Chris Hohmann
news:uAqFY%23ATFH...@TK2MSFTNGP15.phx.gbl...
>
> "Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
> news:uDDL3CzS...@tk2msftngp13.phx.gbl...
>> SABmore wrote:
>>> Is there a performance advantage to parsing thru a recordset verus
>>> using an array?
>>>
>>> I'm currently trying to populate a listbox by returning data from my
>>> database, then either parsing thru the recordset until I reach the
>>> EOF, or putting the data into an array.
>>>
>>> Thanks for your assistance.
>>
>> The definitive answer is here:
>> http://www.aspfaq.com/show.asp?id=2467
>
> I think use of the word "definitive" here is something of an
> overstatement.
>
That's stated both at the beginning and end of the article. I do however
appreciate Bob's sentiment.
> For one thing, there is an optimization technique available to the
> recordset test that was not presented, that being explicit creation of
> field objects, which will improve field value access times that rival the
> array method.
This is very interesting. I use the WITH construct to create a pointer to
the Recordset.Fields collection to minimize object model traversal, but it
makes sense that pointers to the Field objects themselves would be even
better. I reworked my example to use explicit field object references and it
did improve recordset iteration performance. However...
> And even if field access is minutely slower, the overhead of allocating
> space for a complete, sometimes redundant copy of the data as an array can
> make the recordset method faster overall, depending on number of records,
> cursor, etc.
Unfortunately, I could not verify this assertion. I tested 1, 10, 100, 1000,
10000 rows and as the number of rows increased, the measure by which
GetString/GetRows outperformed recordset iteration increased as well. Do you
have an example of where recordset iteration outperforms GetString/GetRows?
If so, could you post it here?
> Yet another optimization technique overlooked is to access the fields
> using their ordinal positions rather than their names. This isn't quite
> as fast as creating field objects, but is considerably faster than using
> names.
I do use ordinal positions in the article.
> The article approached this concept in the GetRows section, except that it
> talked about using a dictionary object as a lookup for field positions.
> It didn't bother to delve into the performance hit *that* would incur --
> for sure there would be COM object access overhead... simple constants
> would be much faster.
Agreed, constants would be better than a dictionary object. Better still
would be to use ordinal positions and include a field name map in a comment
section.
> For another thing, the GetString test doesn't properly belong in the set,
> it's too dissimilar from the others. Yes it can be faster [depending on
> cursor] because it makes far fewer allocations, and avoids the overhead of
> iteration in ASP code. But if you need to process something in each row
> [and said processing cannot be performed in the SQL statement] it's not an
> option.
I disagree. The basis of the article was to compare various ways of
retrieving and display recordset data. And in that context, GetString
qualifies. I do address shortcomings of the GetString approach including
those you cited.
> Further, depending on the provider and cursor used to source it, GetString
> can actually be quite a bit slower than iterating. (In principle I agree
> that calling GetString on a recordset opened with a cursor other than a
> firehose is usually nearly insane, but there are plausible scenarios.)
Again, I am unable to verify this assertion. Could you provide an example of
recordset iteration outperforming GetString?
> Almost lastly, this is in essence a comparison of field data access
> techniques, not a test of connection pooling or query plan cache... point
> being that including object creation/opening time in the results tends to
> cloud the issue, as well as subject the test to potential skew from
> transient server-side factors.
I included object creation/opening time because none of the methods exists
in isolation. They all require the retrieval of a recordset. In fact,
presenting the performance data without taking into account the cost of
retrieving the recordset would actually unfairly skew the result in favor of
GetString/GetRows. Perhaps a good middle ground would be to include timing
data for recordset retrieval separately and then allow the reader to decide
if it should factor into their decision making process.
> And lastly, the difference between the winner and the loser is 31.25 ms.
> The resolution of Timer is going to be 15 ms by default on an SMP system,
> 10 ms on a single CPU system, so relative conclusions such as method A is
> x% faster than method B aren't going to tend to be highly accurate.
I think that reporting timing data for recordset retrieval separately would
address these concerns.
> The test should instead have an inner loop that redundantly accesses the
> fields of each row [n] times, because that's what's really at the core of
> this benchmark.
>
> Of course this would incur the loop overhead that the article was so
> concerned with, but that, I believe, is infantissimal: a quick test on my
> system indicates that it is less than 0.0002 ms per iteration (using a for
> loop.) Hmm, that doesn't consider calling MoveNext and testing EOF, which
> could be consequential, but since we're talking about an inner loop here,
> not relevant. Here's the code I used:
>
> dim t, i, t2 : i = 0 : t = Timer
> for i = 0 to 1000000 : next : t2 = Timer
> Response.Write Left(CStr(((Timer - t) * 1000000.0) / CDbl(i)), 5) & _
> " microseconds per iteration"
I don't feel I was "so concerned" with loop overhead. It was one of many
considerations, but it was not overriding. You're correct, the inner loop
does not consider the MoveNext or EOF calls. However, I do believe it's
relevant because the inner loop is not representative of how data from a
recordset is accessed. Normally, there is a single pass on the recordset and
occasionally there are multiple passes, but I am hard pressed to imagine a
scenario where a field is redundantly accessed for each row. I such cases,
the field value would be stored to a local variable.
>
> Maybe I'll construct an enhanced version of the drag-race... not today
> though. :-)
>
I look forward to it.
Mark J. McGinty
I'll respond in more detail over the weekend, but a couple of quick items:
Do you think that testing VBS script in a scripting control (hosted in a VB
app) would be an "apples to apples" test, compared to VBS in ASP? Reason
being it would be possible to obtain more precise timing data by calling
QueryPerformanceCounters. There would be some call overhead involved
(comparable to calling COM, to make the call out to its host from within the
script control), but I'm thinking it can be isolated and subtracted from the
results.
Not sure whether they use the same core script processing engine -- surely
there are some threadedness differences in ASP's implementation. Maybe I
should just create a quickie COM object and call it from ASP... that would
probably be just a viable.
The other thing is that some of my observations/tests were made/performed on
server boxes under some load, and/or having been up for months and thus
subject to a fair degree of memory fragmentation. To me memory allocation
performance is one of ASP's weakest aspects. Depending on circumstances it
can fluctuate wildly. (In the same vein, though not specific to ASP, if the
system is starved for RAM, and has to do frequent paging ops, anything
involving a large allocation will perform badly and scale even worse. )
Also (as you mentioned in your article) the way a given technique scales is
(as pool players would say) the "money ball" in real life ASP. What runs
fast as a single instance when the machine is otherwise idle may degrade
rapidly under load. I'm fairly certain it's exactly that allocation
performance that can cause the array approach to scale negatively. Long
story short, I'll do my best to make it reproducible.
Lastly, as for the real life occurrence of an inner loop to re-read the same
data multiple times, I agree completely that such constructs won't be found
in sane production code. The purpose was strictly an attempt to get more
accurate timing data given the resolution of available timers. I realize it
doesn't always work out right -- some old C/C++ compilers might even
optimize the redundancy away as "loop invariant"... Nobody said profiling
was easy! :-)
The point of it all is accuracy when making comparative judgments. If one
op is timed at 15 ms, and another is timed at 30 ms using 15 ms timer
resolution, it can't be said conclusively that the first took half as long
as the second, it can only be said that the first took less than 15 ms, and
the second took more than 15 ms but less than 30 ms
-Mark
"Chris Hohmann" <nos...@thankyou.com> wrote in message
news:%23lz21mD...@TK2MSFTNGP12.phx.gbl...
Mark J. McGinty
and QueryPerformanceCounters, making available a timer mechanism that has an
effective resolution equal to the system CPU frequency. It returns results
as a double in your choice of seconds, microseconds or nanoseconds.
The call to the timer itself seems to have an overhead of about 3-6
microseconds (on my system, in it's current state, your mileage may vary.)
(Somehow I was thinking COM overhead was more than that...)
To verify its accuracy I executed the following script:
set hrt = CreateObject("HighResTimer.HRTimer")
hrt.StartTimer
t = Timer
for i = 0 to 10000000 : Next
t2 = Timer
WScript.Echo hrt.Seconds
WScript.Echo t2 - t
The results were within a few milliseconds of each other -- expectable as
the outer timer construct results include overhead incurred by the inner
one. Results were also consistent at 100M and 1M iterations (much below 1M
is beyind the resolution of Timer.)
I've made this component available with C++ source at the following address:
http://www.databoundzone.com/HighResTimer.zip. (DLL is included, must be
registered in the standard way, sources require VS6 or better to build.)
Not counting the part that was generated by the IDE, it's all of 71 lines
(not including 13 lines of white space and 31 lines of file description and
disclaimer.)
Hmm, it occurs to me I've needed this component for a very long time...
thanks for inspiring it! :-)
In case you're wondering, databoundzone.com is a domain I registered, that
hopefully will soon be the home of a website dedicated to databound UI
elements in VB and HTML, the magic and mystery of persistance provider XML,
and ADO in general.
Lastly I have done some ADO profiling as part of testing this component:
for test data I used master.information_schema.Tables. I found that the
bare call to GetRows takes between 25% to 50% less time than GetString
(without taking iteration into account.)
However, when all physical memory has been allocated, and the memory manager
must virtualize new allocations, I observed GetString taking as much as 88
times longer than GetRows! I believe it's because GetRows allocates a
series of smaller buffers, while GetString must allocate a contiguous
buffer.
For a table with all fields of a character type, GetRows must surely consume
more total memory (space for pointers to array elements as well as the data
itself) though it would be possible for GetRows to consume less total
memory, if most of it's fields were numeric (as binary representations of
most numbers take less space than text representations of the same numbers.)
But I don't think the total is of much significance, I think the size of the
largest contiguous piece required is the issue.
I will do some more testing tomorrow.
-Mark
Bob Barrows [MVP]
All I can say is: wow!
I wish I had the time to spend on something like this (I'm barely keeping my
head above water). Thanks for taking interest.
Regarding your results, I've always been surprised that, given the poor
string-handling inherent in vb/vba/vbscript, GetString has always
outperformed GetRows in every test I've ever run or read about. I think your
results have helped resolve this apparent contradiction: the initial call to
the function vs. the handling of the data after the call. The array is
loaded by GetRows very quickly, but looping through the array to build an
output string takes longer than simply writing the result of GetString. That
definitely makes sense now.
As far as the issue of disconnecting the recordset goes, my initial reaction
was that I would have to give up using a firehose cursor (only client-side
static cursors can be disconnected): but now that I think about it, I
realize that this concern is illusory. By transferring the data into another
structure (the array), one is, in essence, creating a quasi client-side,
static cursor. When ADO builds a client-side static cursor, it uses a
firehose cursor to populate the client-side cursor structure.
So, all that remains is to see if looping through a disconnected recordset
(efficiently, using the Field objects to grab the data, or the undocumented
Collect property) can perform as well as an array loop. I look forward to
your results.
Bob Barrows
Chris Hohmann
> Chris,
>
> I'll respond in more detail over the weekend, but a couple of quick items:
>
> Do you think that testing VBS script in a scripting control (hosted in a
> VB app) would be an "apples to apples" test, compared to VBS in ASP?
> Reason being it would be possible to obtain more precise timing data by
> calling QueryPerformanceCounters. There would be some call overhead
> involved (comparable to calling COM, to make the call out to its host from
> within the script control), but I'm thinking it can be isolated and
> subtracted from the results.
>
> Not sure whether they use the same core script processing engine -- surely
> there are some threadedness differences in ASP's implementation. Maybe I
> should just create a quickie COM object and call it from ASP... that would
> probably be just a viable.
Yes, I think creating a COM and calling it from ASP would be better.
> The other thing is that some of my observations/tests were made/performed
> on server boxes under some load, and/or having been up for months and thus
> subject to a fair degree of memory fragmentation. To me memory allocation
> performance is one of ASP's weakest aspects. Depending on circumstances
> it can fluctuate wildly. (In the same vein, though not specific to ASP,
> if the system is starved for RAM, and has to do frequent paging ops,
> anything involving a large allocation will perform badly and scale even
> worse. )
>
> Also (as you mentioned in your article) the way a given technique scales
> is (as pool players would say) the "money ball" in real life ASP. What
> runs fast as a single instance when the machine is otherwise idle may
> degrade rapidly under load. I'm fairly certain it's exactly that
> allocation performance that can cause the array approach to scale
> negatively. Long story short, I'll do my best to make it reproducible.
I've been giving this a lot of thought since this thread began. So much so
that I looked up some of your past posts on the topic of GetRows.
Specifically, there's this thread:
http://groups-beta.google.com/group/microsoft.public.vb.database.ado/browse_frm/thread/aea6333dc48e0ff5/f9a7c0bfa3fa2c5a
You make a very interesting point about the meory allocation requirements
for GetRows. I had never considered the fact that the values themsleves are
variant and as such take up much more memory than would be expected.
However, the field values in a Recordset are also stored as variants. I
believe the reason a Recordset requires less memory is because only a "page"
worth of data is loaded at any one time. To that end, one could simulate
this in GetString/GetRows by pulling data in chunk which are equivalent to
the pagesize of the recordset. In this way you could reduce the memory
footprint of the GetString/GetRows apprach, yet still be able to sidestep
the reentrant object model traversal required for recordset iteration.
> Lastly, as for the real life occurrence of an inner loop to re-read the
> same data multiple times, I agree completely that such constructs won't be
> found in sane production code. The purpose was strictly an attempt to get
> more accurate timing data given the resolution of available timers. I
> realize it doesn't always work out right -- some old C/C++ compilers might
> even optimize the redundancy away as "loop invariant"... Nobody said
> profiling was easy! :-)
>
> The point of it all is accuracy when making comparative judgments. If one
> op is timed at 15 ms, and another is timed at 30 ms using 15 ms timer
> resolution, it can't be said conclusively that the first took half as long
> as the second, it can only be said that the first took less than 15 ms,
> and the second took more than 15 ms but less than 30 ms
Agreed. The COM timer sounds like a great idea. Having said that, I believe
that instead introducing an inner loop, the timer calls should be moved
outside the loop. Something like:
Start Timer
Call GetStrings function 10,000 times
End Timer
Start Timer
Call GetRows function 10,000 times
End Timer
Start Timer
Call Recorset Iteration 10,000 times
End Timer
Chris Hohmann
> Lastly I have done some ADO profiling as part of testing this component:
> for test data I used master.information_schema.Tables. I found that the
> bare call to GetRows takes between 25% to 50% less time than GetString
> (without taking iteration into account.)
>
> However, when all physical memory has been allocated, and the memory
> manager must virtualize new allocations, I observed GetString taking as
> much as 88 times longer than GetRows! I believe it's because GetRows
> allocates a series of smaller buffers, while GetString must allocate a
> contiguous buffer.
Actually, I think the reason GetString, and any string concatenation for
that matter, falls down is that it uses so many redundant buffers. For
example
string1 & string2 & string3 & string4 & string5 ...
translates into the following:
Create copy of string1
Append copy of string2 to copy of string1
Create copy of string1+string2
Append copy of string3 to copy of string1+string2
.
.
.
All the intermediate buffers don't get cleaned up until after the
concatenation operation is completed. This translates to an O(n^2) -"order n
squared"- performance based on the number of concatenations. Here's an
article that goes into some more detail about that:
http://www.sql-server-performance.com/string_concat_article.asp
> For a table with all fields of a character type, GetRows must surely
> consume more total memory (space for pointers to array elements as well as
> the data itself) though it would be possible for GetRows to consume less
> total memory, if most of it's fields were numeric (as binary
> representations of most numbers take less space than text representations
> of the same numbers.) But I don't think the total is of much significance,
> I think the size of the largest contiguous piece required is the issue.
I don't think it could every take less space. As you yourself pointed out in
the past, data values stored in VB Safe Arrays are stored as variants so the
binary representation do not come into play.
> I will do some more testing tomorrow.
I'm looking forward to it.
Bob Barrows [MVP]
> Agreed. The COM timer sounds like a great idea. Having said that, I
> believe that instead introducing an inner loop, the timer calls
> should be moved outside the loop. Something like:
>
> Start Timer
> Call GetStrings function 10,000 times
> End Timer
>
> Start Timer
> Call GetRows function 10,000 times
> End Timer
>
> Start Timer
> Call Recorset Iteration 10,000 times
> End Timer
That's exactly what I did last Thurs in my VB tests. I was using the API
GetTickCount function to do the timing. My results were rather surprising to
me. Again, I'm looking forward to Mark's results.
Bob Barrows
Chris Hohmann
> Chris Hohmann wrote:
>> Agreed. The COM timer sounds like a great idea. Having said that, I
>> believe that instead introducing an inner loop, the timer calls
>> should be moved outside the loop. Something like:
>>
>> Start Timer
>> Call GetStrings function 10,000 times
>> End Timer
>>
>> Start Timer
>> Call GetRows function 10,000 times
>> End Timer
>>
>> Start Timer
>> Call Recorset Iteration 10,000 times
>> End Timer
>
> That's exactly what I did last Thurs in my VB tests. I was using the API
> GetTickCount function to do the timing. My results were rather surprising
> to
> me. Again, I'm looking forward to Mark's results.
Can you post your code?
Bob Barrows [MVP]
>>
>> That's exactly what I did last Thurs in my VB tests. I was using the
>> API GetTickCount function to do the timing. My results were rather
>> surprising to
>> me. Again, I'm looking forward to Mark's results.
>
> Can you post your code?
Sure. It's pretty raw, no comments ...
Public Declare Function GetTickCount& Lib "kernel32" ()
Dim cn As New ADODB.Connection
Dim sSQL As String
Private Sub Form_Load()
Dim rs As New Recordset
sSQL = "Select CompanyName,ContactName,City FROM Northwind..Suppliers"
cn.Open "provider=sqloledb;data source=clnsqldev7;" & _
integrated security=SSP1;initial catalog=northwind"
'use disconnected recordset
'rs.CursorLocation = adUseClient
'rs.Open sSQL, cn, adOpenStatic, adLockReadOnly, 1
'Set rs.ActiveConnection = Nothing
TestRSNewRS
TestArNewRS
cn.Close: Set cn = Nothing
'TestArray rs
'rs.MoveFirst
'TestRSloop rs
'Set rs = Nothing
End Sub
Sub TestArray(pRS As Recordset)
Dim arData As Variant
Dim i As Integer
Dim loops As Long
Dim ticks As Long
Dim s As String
ticks = GetTickCount
If Not pRS.EOF Then arData = pRS.GetRows
If IsArray(arData) Then
For loops = 0 To 10000
For i = 0 To UBound(arData, 2)
s = arData(0, i)
Next
Next
End If
txtArray.Text = GetTickCount - ticks
End Sub
Sub TestRSloop(pRS As Recordset)
Dim loops As Long
Dim ticks As Long
Dim s As String
Dim fld As ADODB.Field
Set fld = pRS.Fields(0)
ticks = GetTickCount
For loops = 0 To 1000000
Do Until pRS.EOF
s = fld.Value 'pRS ("CompanyName")
pRS.MoveNext
Loop
Next
txtRsLoop.Text = GetTickCount - ticks
End Sub
Sub TestArNewRS()
Dim rs As New Recordset
Dim arData As Variant
Dim i As Integer
Dim loops As Long
Dim ticks As Long
Dim s As String
ticks = GetTickCount
'rs.CursorLocation = adUseClient
rs.Open sSQL, cn, adOpenForwardOnly, adLockReadOnly, 1
'Set rs.ActiveConnection = Nothing
If Not rs.EOF Then arData = rs.GetRows
rs.Close: Set rs = Nothing
If IsArray(arData) Then
For loops = 0 To 100000
For i = 0 To UBound(arData, 2)
s = arData(0, i)
Next
Next
End If
txtArray.Text = GetTickCount - ticks
End Sub
Sub TestRSNewRS()
Dim rs As New Recordset
Dim loops As Long
Dim ticks As Long
Dim s As String
Dim fld As ADODB.Field
ticks = GetTickCount
rs.Open sSQL, cn, adOpenForwardOnly, adLockReadOnly, 1
Set fld = rs.Fields(0)
For loops = 0 To 100000
Do Until rs.EOF
s = rs("CompanyName")
rs.MoveNext
Loop
Next
txtRsLoop.Text = GetTickCount - ticks
End Sub
Chris Hohmann
Here are some preliminary observations:
1. Your retrieving 3 columns but only using 1.
2. In TestArNewRS you only need to call UBound once, assign it to a local
variable then use that value in the limiting clause of the For Loop.
3. In TestRSNewRS, you went through the trouble of explicitly creating a
reference to the field object, but then you forget to use it in the loop.
4. In TestArNewRS , you could close and deallocate the connection object
immediately after the call to GetRows. That's one of the key benefits of
GetRows.
5. What kind of numbers are you getting from this? Does recordset iteration
beat GetRows? In the tests I've done, GetRows continues to consistently beat
Recordset Iteration. In fact, I've identified a flaw in my original model.
Namely, there's a call to Server.HTMLEncode inside the loops for both
GetRows and Recordset iteration, but only one call in GetString. In my new
model, I've eliminate the Server.HTMLEncode calls and the results are
surprising. It turns out GetRows is just as fast and sometimes faster than
GetString. Both GetString/GetRows perform about three times (3x) as fast as
Recordset Iteration. I do want to tweak it some more. I like the idea of
eliminating the Response.Writes altogether like you did above. I'm going to
modify my code accordingly and then I'll post what I come up with.
Chris Hohmann
<%
Option Explicit
Sub BaseLine(db,sql)
Dim cn, rs
Set cn = CreateObject("ADODB.Connection")
cn.CursorLocation = 3
cn.Open db
Set rs = cn.Execute(sql,,1)
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
End Sub
Sub SaveXML(db,sql)
Dim s, cn, rs
Set cn = CreateObject("ADODB.Connection")
cn.CursorLocation = 3
cn.Open db
Set rs = cn.Execute(sql,,1)
rs.Save Response, 1 '1 = adPersistXML
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
End Sub
Sub GetString(db,sql)
Dim cn, rs, s
Set cn = CreateObject("ADODB.Connection")
cn.CursorLocation = 3
cn.Open db
Set rs = cn.Execute(sql,,1)
Do While NOT rs.EOF
s = rs.GetString(2,2000)
Loop
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
End Sub
Sub GetRows(db,sql)
Dim cn, rs, arr, j, jMax, s
Set cn = CreateObject("ADODB.Connection")
cn.CursorLocation = 3
cn.Open db
Set rs = cn.Execute(sql,,1)
arr = rs.GetRows
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
jMax = UBound(arr,2)
For j = 0 To jMax
s = arr(0,j)
Next
End Sub
Sub Recordset(db,sql)
Dim cn, rs, field0, s
Set cn = CreateObject("ADODB.Connection")
cn.CursorLocation = 3
cn.Open db
Set rs = cn.Execute(sql,,1)
Set field0 = rs.Fields(0)
Do While Not rs.EOF
s = field0.Value
rs.MoveNext
Loop
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
End Sub
Dim db, sql, i, iMax, start, bl, sx, gs, gr, rs
db = <<Your Connection String Here>>
sql = "SELECT TOP 1000000 C1.dt FROM Calendar AS C1, Calendar C2"
iMax = 1
start = Timer : For i = 1 To iMax : Baseline db, sql : Next : bl = Timer -
start
start = Timer : For i = 1 To iMax : SaveXML db, sql : Next : sx = Timer -
start
start = Timer : For i = 1 To iMax : GetString db, sql : Next : gs = Timer -
start
start = Timer : For i = 1 To iMax : GetRows db, sql : Next : gr = Timer -
start
start = Timer : For i = 1 To iMax : Recordset db, sql : Next : rs = Timer -
start
Response.Clear
Response.Write "<br>Baseline: " & bl
Response.Write "<br>SaveXML: " & sx
Response.Write "<br>GetString: " & gs
Response.Write "<br>GetRows: " & gr
Response.Write "<br>Recordset: " & rs
%>
And here are the results:
Baseline: 1.34375
SaveXML: 6.328125
GetString: 4.984375
GetRows: 3.734375
Recordset: 9.75
Notes:
1. I used a Cartesian product of my Calendar table which has 1000+ rows as
the datasource. This allows me to range the result set from 1 to 1 million+
rows. Any table will do.
2. I've added a Baseline procedure to calculate the cost of the data
retrieval common to all methods.
3. I also added a SaveXML procedure that persists the recordset to the
Response stream. I've wanted to incorporate this method into the mix for
sometime. For very large resultsets (1 million+), with all methods
outputting to the Response object, the SaveXML method was actually the best
performer. I think this has to do with the fact that the Recordset.Save
method sends the data directly to the IStream interface implemented by the
Response object.
4. Most surprisingly, the GetRows method is now the best overall performer.
I guess in hindsight, this is not that surprising. The GetString method has
to deal with the performance issued associated with string concatenation. I
implemented the burst output approach in the GetString procedure to mitigate
this performance issue. GetString and GetRows now perform equally well for
small to medium resultsets, but GetRows starts to pull away for larger
resultsets. However the tradeoff is that GetRows requires more memory than
GetString.
Bob Barrows [MVP]
> Here are some preliminary observations:
> 1. Your retrieving 3 columns but only using 1.
True, but I wanted to cause some "overhead" for the field access.
> 2. In TestArNewRS you only need to call UBound once, assign it to a
> local variable then use that value in the limiting clause of the For
> Loop.
I think the compiler does that for you, but I could be wrong ... I'll check
it both ways tomorrow.
> 3. In TestRSNewRS, you went through the trouble of explicitly
> creating a reference to the field object, but then you forget to use
> it in the loop.
Oops - told you the code was raw ...
> 4. In TestArNewRS , you could close and deallocate the connection
> object immediately after the call to GetRows. That's one of the key
> benefits of GetRows.
True, but that's not what I was testing. :-)
> 5. What kind of numbers are you getting from this? Does recordset
> iteration beat GetRows? In the tests I've done, GetRows continues to
> consistently beat Recordset Iteration. In fact, I've identified a
> flaw in my original model. Namely, there's a call to
> Server.HTMLEncode inside the loops for both GetRows and Recordset
> iteration, but only one call in GetString. In my new model, I've
> eliminate the Server.HTMLEncode calls and the results are surprising.
> It turns out GetRows is just as fast and sometimes faster than
> GetString. Both GetString/GetRows perform about three times (3x) as
> fast as Recordset Iteration. I do want to tweak it some more. I like
> the idea of eliminating the Response.Writes altogether like you did
> above. I'm going to modify my code accordingly and then I'll post
> what I come up with.
I was seeing faster results from the recordset loops, which definitely
surprised me. Maybe it's a VB vs vbscript difference ... ?
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Mark J. McGinty
[snip]
> I've been giving this a lot of thought since this thread began. So much so
> that I looked up some of your past posts on the topic of GetRows.
> Specifically, there's this thread:
> http://groups-beta.google.com/group/microsoft.public.vb.database.ado/browse_frm/thread/aea6333dc48e0ff5/f9a7c0bfa3fa2c5a
> You make a very interesting point about the meory allocation requirements
> for GetRows. I had never considered the fact that the values themsleves are
> variant and as such take up much more memory than would be expected.
> However, the field values in a Recordset are also stored as variants. I
> believe the reason a Recordset requires less memory is because only a "page"
> worth of data is loaded at any one time.
[snip]
> that instead introducing an inner loop, the timer calls should be moved
> outside the loop. Something like:
> Call GetStrings function 10,000 times
> End Timer
Mark J. McGinty
timings added in.) Below the results is the ASP script as modified.
Needless to say, you'd need to download (and build, if you wish) and
register the high-res timer component to run it.
These times are in milliseconds (which is all that ResetTimer returns --
thinking about adding a property to set the increment it returns, and then
adding an Elapsed property to take the place of Seconds, Milliseconds,
MicroSeconds and NanoSeconds.)
-Mark
GetString
4.17743301330265
5.79479685138404
2.40294423462406
10.5239937256359
3.04610371280849
0.701303824289221
27.6612958205446
--------------------------------------------------------------------------------
GetRows
0.610937259932325
0.580072819783542
1.86451804236982
5.30469612568738
3.30435596654149
12.9090753173806
--------------------------------------------------------------------------------
Recordset
0.67432548799617
1.19991424559347
22.4348305814506
25.5832393012445
total: 67.1136596050295
''''''''''''''''''''''''''''''''''''''''''''
<%
Dim hrt, hrt2, hrt3, sConn
Set hrt = CreateObject("HighResTimer.HRTimer")
Set hrt3 = CreateObject("HighResTimer.HRTimer")
sConn =
"Provider=SQLOLEDB;Server=(local);Database=misc;Trusted_Connection=Yes;"
Function GetString
Dim cn, rs, res, tdat(5)
hrt.StartTimer
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
tdat(0) = hrt.ResetTimer
With cn
.Open sConn
tdat(1) = hrt.ResetTimer
cn.TheBigList rs
tdat(2) = hrt.ResetTimer
res = rs.GetString(2,," ",vbCRLF,"Category:")
tdat(3) = hrt.ResetTimer
Response.Write Server.HTMLEncode(res)
rs.Close: Set rs = Nothing
tdat(4) = hrt.ResetTimer
End With
cn.Close: Set cn = Nothing
tdat(5) = hrt.ResetTimer
GetString = tdat
End Function
Function GetRows
Dim cn, rs, res, tdat(4)
hrt.StartTimer
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
tdat(0) = hrt.ResetTimer
With cn
.Open sConn
tdat(1) = hrt.ResetTimer
cn.TheBigList rs
tdat(2) = hrt.ResetTimer
Dim arr: arr = rs.GetRows
rs.Close: Set rs = Nothing
tdat(3) = hrt.ResetTimer
End With
cn.Close: Set cn = Nothing
jMax = UBound(arr,2)
For j = 0 To jMax
If vartype(arr(0,j)) <> 8 Then
Response.Write arr(0,j)
Response.Write " "
Response.Write Server.HTMLEncode(arr(1,j))
Else
Response.Write "Category: "
Response.Write arr(1,j)
End If
Response.Write vbCRLF
Next
tdat(4) = hrt.ResetTimer
GetRows = tdat
End Function
Function Recordset
Dim cn, rs, res, tdat(2)
hrt.StartTimer
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
tdat(0) = hrt.ResetTimer
With cn
.Open sConn
cn.TheBigList rs
tdat(1) = hrt.ResetTimer
set fld0 = rs.Fields(0)
set fld1 = rs.Fields(1)
set fld2 = rs.Fields(2)
With rs
Do While Not .EOF
With .Fields
If vartype(fld0.Value) <> 8 Then
Response.Write fld0.Value
Response.Write " "
Res = fld1.Value
Response.Write Server.HTMLEncode(res)
Else
Response.Write "Category: "
Response.Write fld1.Value
End If
End With
Response.Write vbCRLF
.MoveNext
Loop
End With
tdat(2) = hrt.ResetTimer
rs.Close: Set rs = Nothing
End With
cn.Close: Set cn = Nothing
Recordset = tdat
End Function
Set hrt2 = CreateObject("HighResTimer.HRTimer")
hrt2.StartTimer
Response.write "<pre>"
hrt3.StartTimer
gs = GetString()
t1 = hrt3.ResetTimer
gr = GetRows()
t2 = hrt3.ResetTimer
rs = Recordset()
t3 = hrt3.ResetTimer
Response.Clear
Response.Write "<hr>GetString<br>" & join(gs, "<br>") & "<br><b>" & t1 &
"</b><br>"
Response.Write "<hr>GetRows<br>" & join(gr, "<br>") & "<br><b>" & t2 &
"</b><br>"
Response.Write "<hr>Recordset<br>" & join(rs, "<br>") & "<br><b>" & t3 &
"</b><br>"
Response.Write "<br><br>total: " & hrt2.MilliSeconds
Response.write "</pre>"
%>
</body>
</html>
Bob Barrows [MVP]
>> Yes, I think creating a COM and calling it from ASP would be better.
>
> You caught that I created and published this component, yeah?
>
> http://www.databoundzone.com/HighResTimer.zip.
I tried to dl it, but IE never prompted for a destination folder. I'm not
sure why ..
I'll try again tomorrow.
Michael D. Kersey
- The benchmarks don't appear to consider that the database caches SQL
requests. The first SQL request in one benchmark is done by the
user-defined Function GetString (not to be confused with the GetString()
method) and thus that method may be penalized somewhat. The initial
request for a given SQL statement requires more processing time (for SQL
compilation) than subsequent requests. To balance this it is necessary
to either wait until the database de-caches (if that's a word) the
compiled SQL or, prior to the benchmark timings, perform an initial
set-up SQL query that caches the SQL statement. Once that set-up query
executes, successive calls using the various methods should be timed
without bias. Merely swapping the order of subroutine calls in the
benchmark (or taking the timings from only a second run of the
benchmark) might also show whether this is a problem.
- The result set (a million rows) seems awfully large: perhaps testing
also with a range of smaller values (and some variety of column counts,
also - in my experience most result sets are more than two columns)
would be useful?
- As one poster stated earlier, the size of the result set may push the
server to extremes (out of physical memory). This is a problem that is
easily fixed these days: memory is cheap, programming isn't. [Well, it
wasn't until recently!8-(] How much memory is on the IIS server? The
database server?
- One of the benchmarks had multiple rs.GetString() method calls, which
may bias the test results. While multiple GetString() method calls are
interesting, a second subroutine that performs only a single GetString()
method call (as is done in another of the benchmarks) would be nice for
comparison.
Mark J. McGinty
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
> Mark J. McGinty wrote:
>>> Yes, I think creating a COM and calling it from ASP would be better.
>>
>> You caught that I created and published this component, yeah?
>>
>> http://www.databoundzone.com/HighResTimer.zip.
>
> I tried to dl it, but IE never prompted for a destination folder. I'm not
> sure why ..
> I'll try again tomorrow.
My bad, the virtual dir was set for immediate expiry, it's been corrected.
I've added an alternative interface to it, that's more streamlined and more
functional, details are below. (I just updated the zip, latest DLL version
is 1.0.0.2) An updated copy of the ASP script is included in the zip.
I'm thinking if this new interface works out well, I'll copy the code into
another component with just one interface.
-Mark
---------------------------------------------------------------
Interface: HighResTimer.HiResTimer
Thread Model: Both
-----------------------
Methods:
-----------
HiResTimer.Start()
Internally stores start time, the reference point from which
time elapsed is calculated
Properties:
-----------
HiResTimer.Elapsed
This property returns the time elapsed since start time, in
increments as set in the Units property. (See Units below.)
Return value type is double.
Note that this property does not cause the start time to be reset.
Therefore it can be called more than once consecutively, to
return progressive time-elapsed values if desired.
HiResTimer.Reset
Calculates time elapsed between start time and current time, and
resets the start time, effectively restarting the timer. Results
are returned in increments as defined by the value of the
Units property (default is microseconds.)
HiResTimer.Units
Sets or retrieves the incremental units that are represented
by the results. The default value is 1000000, which causes
the elapsed time to be returned as microseconds. Valid values
must be even multiples of 10, up to and including 1000000000
(1 billion.) Below is a list of typical values (which is a
subset of all valid values:)
1 = Seconds
1,000 = Milliseconds
1,000,000 = Microseconds (default)
1,000,000,000 = Nanoseconds
HiResTimer.FrequencyMHz
Returns the frequency, in megahertz, upon which this timer is
based, thereby indicating its resolution.(Typically this will be
the CPU clock speed.)
HiResTimer.QueryCounter
Returns the raw value obtained by calling QueryPerformanceCounters
and casting the 64 bit int to a double.
---------------------------------------------------------------
Example (VBS):
-------------
set hrt = CreateObject("HighResTimer.HiResTimer")
WScript.Echo "Reality check (in seconds)"
' set to return seconds
hrt.Units = 1
hrt.Start : t = Timer
for i = 0 to 1000000 : Next
t2 = Timer
WScript.Echo hrt.Elapsed
WScript.Echo t2 - t
WScript.Echo "Timer Frequency: " & hrt.FrequencyMHz & " MHz"
---------------------------------------------------------------
---------------------------------------------------------------
Chris Hohmann
> Chris Hohmann wrote:
>> Here are some preliminary observations:
>> 1. Your retrieving 3 columns but only using 1.
>
> True, but I wanted to cause some "overhead" for the field access.
>
fields. You could specify just the "CompanyName" field in the third
parameter of the GetRows method.
>> 4. In TestArNewRS , you could close and deallocate the connection
>> object immediately after the call to GetRows. That's one of the key
>> benefits of GetRows.
>
> True, but that's not what I was testing. :-)
>
Fair enough. :) Although it might be useful for us to come to a consensus on
what is being tested. All three of us are running separate test with varying
results. I'm reminder of the story of three blind men describing an
elephant.
>> 5. What kind of numbers are you getting from this? Does recordset
>> iteration beat GetRows? In the tests I've done, GetRows continues to
>> consistently beat Recordset Iteration. In fact, I've identified a
>> flaw in my original model. Namely, there's a call to
>> Server.HTMLEncode inside the loops for both GetRows and Recordset
>> iteration, but only one call in GetString. In my new model, I've
>> eliminate the Server.HTMLEncode calls and the results are surprising.
>> It turns out GetRows is just as fast and sometimes faster than
>> GetString. Both GetString/GetRows perform about three times (3x) as
>> fast as Recordset Iteration. I do want to tweak it some more. I like
>> the idea of eliminating the Response.Writes altogether like you did
>> above. I'm going to modify my code accordingly and then I'll post
>> what I come up with.
>
> I was seeing faster results from the recordset loops, which definitely
> surprised me. Maybe it's a VB vs vbscript difference ... ?
Perhaps, but it seems unlikely to me. I'm interested to see your findings
when the code is a little less "raw". :)
Chris Hohmann
news:%23wpfzS6...@TK2MSFTNGP09.phx.gbl...
> I'm puzzled about some things:
> - The benchmarks don't appear to consider that the database caches SQL
> requests. The first SQL request in one benchmark is done by the
> user-defined Function GetString (not to be confused with the GetString()
> method) and thus that method may be penalized somewhat. The initial
> request for a given SQL statement requires more processing time (for SQL
> compilation) than subsequent requests. To balance this it is necessary to
> either wait until the database de-caches (if that's a word) the compiled
> SQL or, prior to the benchmark timings, perform an initial set-up SQL
> query that caches the SQL statement. Once that set-up query executes,
> successive calls using the various methods should be timed without bias.
> Merely swapping the order of subroutine calls in the benchmark (or taking
> the timings from only a second run of the benchmark) might also show
> whether this is a problem.
You make a very compelling point. It's one that echoes what Mark said
earlier about not clouding the issue by including object creation and
opening time in our metrics. I suggested that object creation and opening
time be measured separately and we've both adopted this in our respective
testing models. When you discount object creation and opening times, Marks
results are as follows (approximately)
GetString:
10.52
03.05
00.70
------
14.27
GetRows:
5.30
3.30
-----
8.60
Recordset:
22.43
This is roughly in line with what I observed in my new testing model,
although the GetString time seems a little high. It may be that the
GetString procedure is in fact being penalized for being first to the party
and GetRows/Recordset are getting a free ride on the coattails of GetStrings
hard work. :) It may be worthwhile to redesign the testing model to open the
recordset once and perform all three tests on the same recordset.
> - The result set (a million rows) seems awfully large: perhaps testing
> also with a range of smaller values (and some variety of column counts,
> also - in my experience most result sets are more than two columns) would
> be useful?
Whose test are you talking about? I believe Mark's test uses the same data
as the original article which uses approximately 400 rows and two columns.
As far as I can tell, the only changes he made was to replace the Timer
calls with call to his higher resolution timer component. And he used
explicit field object references which he discussed earlier in the thread.
> - As one poster stated earlier, the size of the result set may push the
> server to extremes (out of physical memory). This is a problem that is
> easily fixed these days: memory is cheap, programming isn't. [Well, it
> wasn't until recently!8-(] How much memory is on the IIS server? The
> database server?
Yes, this is a valid point. Memory utilization should be taken into account
when deciding among these various methods. This is especially true when the
selected method will be scale for many concurrent users. I mention it in
passing in the original article but it could certainly do with a more
thorough treatment. However, for the purposes of the original test execution
speed was the only metric.
> - One of the benchmarks had multiple rs.GetString() method calls, which
> may bias the test results. While multiple GetString() method calls are
> interesting, a second subroutine that performs only a single GetString()
> method call (as is done in another of the benchmarks) would be nice for
> comparison.
Oh, I see, you're talking about the revised code I posted in response to
Bob. That's what the million row reference was about as well? The purpose of
that code was to incorporate some of the observations that had been made
thus far in the thread. Specifically:
1. Mitigate the 15ms Timer() resolution issue by using a larger resultset. I
opted for this instead of an inner loop suggested by Mark, since it was more
representative of how one moves through a recordset. Namely, in one pass. It
also eliminates any possible contamination of results by introducing
addition loop variable costs.
2. Create a baseline measure for object creation and opening time.
3. Used explicit field object references in the recordset iteration method.
4. Added the SaveXML procedure to test the performance of persisting data
directly to Response using the Recordset.Save method. I've always been
curious to see how this would perform.
5. Use multiple calls to GetString to mitigate the effects of large string
concatenation. I did start out using a single call, but the script was
timing out. Large string concatenations in VBScript are terrible performers.
Chris Hohmann
> Yes, I think creating a COM and calling it from ASP would be better.
You caught that I created and published this component, yeah?
http://www.databoundzone.com/HighResTimer.zip.
[Chris] Yes, but I had the same problem Bob had. Also, I don't have VB6 at
work and I use Linux at home.
> You make a very interesting point about the memory allocation requirements
> for GetRows. I had never considered the fact that the values themselves
> are
> variant and as such take up much more memory than would be expected.
> However, the field values in a Recordset are also stored as variants. I
> believe the reason a Recordset requires less memory is because only a
> "page"
> worth of data is loaded at any one time.
Server cursors yes, but client cursors read all the data into the recordset
at once, right? Given that, and looking back at my test results, something
doesn't quite add-up:
I reviewed the structure definition for variants, it looks to me that the
actual size of it (not counting string, array, object or other data to which
a variant may point is about 128 bits. (It's a structure with one member
that's a nested union.)
Anyway the point is, ADO must store data for a client cursor in a more
efficient block, and wrap variants around only the fields in the current
row, otherwise it would take 4x as much space.
Just a theory...
[Chris] I think the data is stored in it's native binary format, perhaps a
bytearray, and a PAGE worth of data is cast into variants as you move
through the recordset. I suspect that the difference between server-side and
client-side cursors is that with server-side cursors request multiple binary
chunks as need, whereas client-side cursors request one huge binary chunk.
Chris Hohmann
So if I'm interpreting these numbers correctly, and we discount the object
creation and opening times, the results are as follows:
GetString:
10.52
03.05
00.70
------
14.27
GetRows:
5.30
3.30
-----
8.60
Recordset:
22.43
This is in line with the result of my new testing model as well. To quote
from my response to Michael, It may be that the
GetString procedure is in fact being penalized for being first to the party
and GetRows/Recordset are getting a free ride on the coattails of
GetString's hard work. :) It may be worthwhile to redesign the testing model
to open the recordset once and perform all three tests on the same
recordset. I'd still like to see an example of recordset iteration
outperforming GetRows, but both our results seem to confirm that this is
unlikely.
Bob Barrows [MVP]
> Fair enough. :) Although it might be useful for us to come to a
> consensus on what is being tested.
Yes. Part of the reason the code was so raw (it really wasn't intended to be
distributed) was I kept doing new tests, without saving old ones.
>
> Perhaps, but it seems unlikely to me.
Frankly, I'm somewhat baffled by what I was seeing.
> I'm interested to see your
> findings when the code is a little less "raw". :)
When I get a few minutes free, I'll revisit this. I need to clean things up,
especially regarding the reporting of results. I'll also utilize Mark's dll
instead of GetTickCount so that confounding factor will be eliminated. Then
I'll need to write a similar test for asp ... did I say I need to get a
"few" minutes free?
:-)
Bob
Bob Barrows [MVP]
>
> [Chris] I think the data is stored in it's native binary format,
> perhaps a bytearray, and a PAGE worth of data is cast into variants
> as you move through the recordset. I suspect that the difference
> between server-side and client-side cursors is that with server-side
> cursors request multiple binary chunks as need, whereas client-side
> cursors request one huge binary chunk.
David Sceppa addresses this somewhat in his "Programming ADO" book. I'll
need to look it up and refresh my memory about it when I get home. I DO
remember that when a client-side cursor is constructed, a server-side
firehose cursor is used to populate it. Hiow does that information affect
your theory?
Bob Barrows
Bob Barrows [MVP]
> It may be that the
> GetString procedure is in fact being penalized for being first to the
> party and GetRows/Recordset are getting a free ride on the coattails
> of GetString's hard work. :) It may be worthwhile to redesign the
> testing model to open the recordset once and perform all three tests
> on the same recordset.
Or randomize the order of tests to remove that confounding factor ...
Mark J. McGinty
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
> Chris Hohmann wrote:
>> It may be that the
>> GetString procedure is in fact being penalized for being first to the
>> party and GetRows/Recordset are getting a free ride on the coattails
>> of GetString's hard work. :) It may be worthwhile to redesign the
>> testing model to open the recordset once and perform all three tests
>> on the same recordset.
>
> Or randomize the order of tests to remove that confounding factor ...
Worth trying but I doubt it'll make a lot of difference, the recordset is
created by calling a stored procedure, so it's query plan is already
compiled and cached.
To eliminate conjecture, I'm using the same table schema, and a stored
procedure with the same name, but I had no access to Chris' original data,
so I just grabbed 500 rows I knew would be non-repetitive and roughly the
same size to stuff that table with. So the SP is just a select statement,
and its query plan is very uncomplicated.
(btw, I forget who said it -- Chris I think -- but I like the analogy of 3
blind guys describing an elephant... we've definitely got a huge beast
before us, and we've all got quite a bit invested in feeling around it...
funny.) :-)
-Mark
Chris Hohmann
> To eliminate conjecture, I'm using the same table schema, and a stored
> procedure with the same name, but I had no access to Chris' original data,
> so I just grabbed 500 rows I knew would be non-repetitive and roughly the
> same size to stuff that table with. So the SP is just a select statement,
> and its query plan is very uncomplicated.
http://aspfaq.com/inserts_2467.txt
Michael D. Kersey
> "Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
> news:uON%23hbCUF...@TK2MSFTNGP15.phx.gbl...
> Worth trying but I doubt it'll make a lot of difference, the recordset is
> created by calling a stored procedure, so it's query plan is already
> compiled and cached.
I don't know which database you're using but IIRC with SQL Server 7 and
2000 Microsoft changed to a scheme whereby stored procedures are no
longer precompiled. Instead both dynamic SQL and stored procedures are
dynamically compiled and cached. Here's a URL that references the change:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
[I'm somewhat curious to know the reasons this change was made. All I
can think of is that it simplified compilation (now done in one place
instead of two) and always produces an up-to-date execution plan.]
Mark J. McGinty
> Mark J. McGinty wrote:
>> "Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
>> news:uON%23hbCUF...@TK2MSFTNGP15.phx.gbl...
> <snipped>
>> Worth trying but I doubt it'll make a lot of difference, the recordset is
>> created by calling a stored procedure, so it's query plan is already
>> compiled and cached.
>
> I don't know which database you're using but IIRC with SQL Server 7 and
> 2000 Microsoft changed to a scheme whereby stored procedures are no longer
> precompiled. Instead both dynamic SQL and stored procedures are
> dynamically compiled and cached. Here's a URL that references the change:
> http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
That guy is something of a zealot, yeah? :-) (I'm running 2K.) He raises
some interesting points however, not all cache objects are equal. I've seen
some instances of query plan cache pollution that get pretty severe, to the
extent that the overhead involved in looking for a cached plan exceeds
recompiling. I've also seen many, many quasi-redundant cache entries, that
properly should've reused the same plan, but had discrete lexical
differences that would be ignored by a query plan, but are separately
distinguished in plan cache. The query plan for a SP has got to be a simple
object id lookup
SELECT objtype, cacheobjtype, COUNT(*) AS Expr1
FROM syscacheobjects
GROUP BY objtype, cacheobjtype
ORDER BY objtype
Adhoc Executable Plan 15
Adhoc Compiled Plan 24
Default Parse Tree 1
Prepared Compiled Plan 47
Prepared Executable Plan 45
Proc Executable Plan 41
Proc Compiled Plan 43
Proc Extended Proc 3
SysTab Parse Tree 17
View Parse Tree 7
Also his rant about role security is a little out there, how are you going
to implement impersonation via roles? A context that executes ad hoc sql
(even dynamic sql in a SP) must have access privileges to all db objects
referenced. If that context provides a way for the user to compose and
execute SQL, it allows the user access to anything in those db objects. (For
purposes of this rant let's say that row-level security is impractical,
because really it sort-of is.)
With stored procedures using inheritance, precisely restricted access to
data can be provided without having to grant blanket access to those
underlying db objects. The user with exec permissions can only access data
as provided by the SP, s/he neither needs not has direct explicit access to
the tables.
Plus there are developmental issues, like reuse and consistency... they
surely still have their advantageous places... to me the prospect of "going
stored procedure free" seems a little whacked, but then again that might
just be me. :-)
> [I'm somewhat curious to know the reasons this change was made. All I can
> think of is that it simplified compilation (now done in one place instead
> of two) and always produces an up-to-date execution plan.]
The BOL topics about sp_recompile and WITH RECOMPILE are interesting... I'd
say they did it to blur the lines, but even so, the lines are still there to
some extent.
Anyway, struggling to get back to the topic, what I was trying to say is,
after the first pass through the tests, wouldn't the advantage be gone? And
also, at least on my end, that query plan consists of one step, a clustered
index scan... is that even worth caching?
-Mark
Michael D. Kersey
Yes. Once the query is cached it probably would remain cached for the
duration of the test. A dummy initial call to the SP should cause the
query to be cached.
> And
> also, at least on my end, that query plan consists of one step, a clustered
> index scan... is that even worth caching?
>
> -Mark
Actually I was thinking more of the SQL compilation time than the query
plan build time, but I have no idea how these times compare.
About http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
My saved URL info for the article didn't fully indicate (or remind me)
of how much territory/controversy he covers at that URL. I apologize
because that was somewhat misleading - I had only intended to refer to
the few paragraphs where he describes the changes in SQL caching in SQL
Server 7 and 2000. I later went back and found myself caught up in
reading the full article, as I see you did also! But I apologize for
that because most of it is orthogonal to this thread. I thought (and
still do) that it was a very interesting discussion, but I really had
intended to refer to it only as a reference for the change in caching
behavior.
Mark J. McGinty
"Chris Hohmann" <nos...@thankyou.com> wrote in message
news:%23GS0GOC...@tk2msftngp13.phx.gbl...
>
> [Chris] Yes, but I had the same problem Bob had.
Sorry about that, it's fixed, I tested the dl from my laptop. I also ended
up configuring a new net block for my office on the fly, I was whining to my
ISP about a pesky little drop-off problem, and they noticed I was in a wrong
block, so I was down for an hour or so today. (Thank goodness for short
TTL.)
Give it another try when you get a chance..
http://www.databoundzone.com/highrestimer.zip
> Also, I don't have VB6 at work and I use Linux at home.
Wait, you use WHAT?! :-) I run 'nix in a virtual from time to time -- man
I did not see that coming, I even missed it the first time I read that post.
<LOL> Ok then, ahem... moving right along....
Um, VB6 wouldn't do you a lot of good, except for maybe the object browser,
I wrote the component in VC++ with ATL, mostly because dealing with 64 bit
integers in VB was entirely impractical, plus I wanted to keep overhead to a
minimum.
I included the compiled DLL, so building yourself is unnecessary (though if
the places were switched, I'd probably build it just for GP, if possible --
the Internet has become a hostile place.) You will have to call REGSVR32 to
register it, didn't bother with an installer...
But there's just no getting past the linux thing... guess I didn't think it
was a requirement. :-)
> [Chris] I think the data is stored in it's native binary format, perhaps a
> bytearray, and a PAGE worth of data is cast into variants as you move
> through the recordset. I suspect that the difference between server-side
> and client-side cursors is that with server-side cursors request multiple
> binary chunks as need, whereas client-side cursors request one huge binary
> chunk.
Agreed. One interesting aspect of server cursors is the almost lazy use of
the network. Client cursor is on more of a mission; server cursor is
apparently trying to avoid burdening the network?
-Mark
Michael D. Kersey
> "Michael D. Kersey" <mdke...@hal-pc.org> wrote in message
> news:%23wpfzS6...@TK2MSFTNGP09.phx.gbl...
>
<snipped>
Yes. Sorry about the confusion: I was looking at two benchmarks at the
same time.
The purpose of
> that code was to incorporate some of the observations that had been made
> thus far in the thread. Specifically:
> 1. Mitigate the 15ms Timer() resolution issue by using a larger resultset. I
> opted for this instead of an inner loop suggested by Mark, since it was more
> representative of how one moves through a recordset. Namely, in one pass. It
> also eliminates any possible contamination of results by introducing
> addition loop variable costs.
> 2. Create a baseline measure for object creation and opening time.
> 3. Used explicit field object references in the recordset iteration method.
> 4. Added the SaveXML procedure to test the performance of persisting data
> directly to Response using the Recordset.Save method. I've always been
> curious to see how this would perform.
I'm glad you added this. I've seen it used several times and remember
being quite puzzled the first time I saw it used!
> 5. Use multiple calls to GetString to mitigate the effects of large string
> concatenation. I did start out using a single call, but the script was
> timing out.
BTW do you remember whether that was a script timeout
(Server.ScriptTimeout) or a connection (conn.connectionTimeout) timeout?
Chris Hohmann
> Chris Hohmann wrote:
>> 5. Use multiple calls to GetString to mitigate the effects of large
>> string concatenation. I did start out using a single call, but the script
>> was timing out.
>
> BTW do you remember whether that was a script timeout
> (Server.ScriptTimeout) or a connection (conn.connectionTimeout) timeout?
It was a script timeout.
Chris Hohmann
>
> "Chris Hohmann" <nos...@thankyou.com> wrote in message
> news:%23GS0GOC...@tk2msftngp13.phx.gbl...
>>
>> [Chris] Yes, but I had the same problem Bob had.
>
> Sorry about that, it's fixed, I tested the dl from my laptop. I also
> ended up configuring a new net block for my office on the fly, I was
> whining to my ISP about a pesky little drop-off problem, and they noticed
> I was in a wrong block, so I was down for an hour or so today. (Thank
> goodness for short TTL.)
>
> Give it another try when you get a chance..
> http://www.databoundzone.com/highrestimer.zip
I already did. :)
>> Also, I don't have VB6 at work and I use Linux at home.
>
> Wait, you use WHAT?! :-) I run 'nix in a virtual from time to time --
> man I did not see that coming, I even missed it the first time I read that
> post. <LOL> Ok then, ahem... moving right along....
I should have said, I use Linux WITH Mono at home. :)
> Um, VB6 wouldn't do you a lot of good, except for maybe the object
> browser, I wrote the component in VC++ with ATL, mostly because dealing
> with 64 bit integers in VB was entirely impractical, plus I wanted to keep
> overhead to a minimum.
Sorry, force of habit. I should have said, I don't have Visual Studio 6 at
work.
> I included the compiled DLL, so building yourself is unnecessary (though
> if the places were switched, I'd probably build it just for GP, if
> possible -- the Internet has become a hostile place.) You will have to
> call REGSVR32 to register it, didn't bother with an installer...
Yes, I found it in the ReleaseUMinDependency folder eventually. It takes me
a little longer than most, but I get there eventually. :) On a side note, I
did some looking into converting your component to a Windows Scripting
Component(WSC). One problem I'm encountering is how to get access to
QueryPerformanceCounter et al. I see that I can get to it via the
Win32_PerfRawData_PerfOS_Processor in Windows Management
Instrumentation(WMI), but that seems like a lot of overhead just for a
timer. Do you have any idea how I can call QueryPerformanceCounter directly
from WSC? I don't think it's possible, but I'd love to be wrong. The reason
I'd like to convert it to a WSC is so that it could be included in a rewrite
of the article without requiring the reader to register the component.
Mark J. McGinty
[snip]
> Yes, I found it in the ReleaseUMinDependency folder eventually. It takes
> me a little longer than most, but I get there eventually. :)
A litle confusing I agree. Last night I reorganized the zip a little, I
created folders DLL, Test Scripts and Docs, and moved files appropriately.
I also fancied-up the docs
(http://www.databoundzone.com/highrestimerdocs.htm) for the HiResTimer
interface (which I like a lot more than my first attempts.) I'm happy
enough with its set of properties and method, but am totally open to any
object name suggestions. I'm thinking it will only take about 10 minutes to
create a new component and copy the code from HiResTimer to it -- way easier
than trying to remove the old interfaces from the IDL and all... so that is
my intent.
> On a side note, I did some looking into converting your component to a
> Windows Scripting Component(WSC). One problem I'm encountering is how to
> get access to QueryPerformanceCounter et al. I see that I can get to it
> via the Win32_PerfRawData_PerfOS_Processor in Windows Management
> Instrumentation(WMI), but that seems like a lot of overhead just for a
> timer. Do you have any idea how I can call QueryPerformanceCounter
> directly from WSC? I don't think it's possible, but I'd love to be wrong.
> The reason I'd like to convert it to a WSC is so that it could be included
> in a rewrite of the article without requiring the reader to register the
> component.
I didn't even think about WMI... but you're right that's a lot of o/h,
likely rendering it useless for our purposes here. I think you're right
about it being impossible from WSC, for lack of access to the API for one
thing, and difficulty handling LARGE_INTEGER for another.
I could wrap it in a quick Install Vise setup app, and I may be able to get
permission to sign it, if that'd help. The installer would at least give
them a painless way to register and remove it.
I did some looking-into the threadedness/SMP aspect of it. The counter
value will be read from whatever CPU is executing the thread, but it doesn't
matter because all chips will apparently have the same value. I tested it
out on a 2-processor box and it seems to hold true (I didn't get exact
matches but that's due to the way threads are scheduled, I believe; the
amount of difference seems consistent with theory.) So I'm assuming it's a
non-issue.
What are your thoughts as far as ideal thread model go? Is 'both' the right
choice?
-Mark
Chris Hohmann
cheat the system by using Currency.
> I could wrap it in a quick Install Vise setup app, and I may be able to
> get permission to sign it, if that'd help. The installer would at least
> give them a painless way to register and remove it.
>
> I did some looking-into the threadedness/SMP aspect of it. The counter
> value will be read from whatever CPU is executing the thread, but it
> doesn't matter because all chips will apparently have the same value. I
> tested it out on a 2-processor box and it seems to hold true (I didn't get
> exact matches but that's due to the way threads are scheduled, I believe;
> the amount of difference seems consistent with theory.) So I'm assuming
> it's a non-issue.
>
> What are your thoughts as far as ideal thread model go? Is 'both' the
> right choice?
We're not storing the timer in the Application/Session scope so I think
either model is fine. With regards to the absense of GetThreadAffinity you
mentioned earlier, I don't think it's necessary since on success,
SetThreadAffinity returns the bitmask of the prior thread affinity mask.
Chris Hohmann
> cheat the system by using Currency.
That should read ..."I think you CAN cheat the system by using Currency".
Mark J. McGinty
> We're not storing the timer in the Application/Session scope so I think
> either model is fine. With regards to the absense of GetThreadAffinity you
> mentioned earlier, I don't think it's necessary since on success,
> SetThreadAffinity returns the bitmask of the prior thread affinity mask.
Yeah I saw that, but didn't feel really comfortable with altering such a
thing blindly (setting it to the process' affinity mask) within the process
space of a server app, just to find out what it was before I changed it...
didn't seem like a polite thing to do. :-)
-Mark
Mark J. McGinty
I got some info about the processor affinity thing:
Mark J. McGinty wrote:
> When running on an SMP box, is there any way to determine which CPU is
> executing your code? [...]
The thread's affinity mask as well as the ideal processor could
be retrieved with the NtQueryInformationThread native API function.
However, I do not know a way to get (in user mode) the thread's
soft affinity (which is used internally by the scheduler to "mark"
the last CPU a thread was executed on).
> Actually I should mention what brought this up, I wrote a quickie COM
> object to call QueryPerformanceCounters from within ASP, for
> profiling purposes. [...]
>
> Since the implementation of QueryPerformanceCounters may read a
> counter from the CPU, does the system make sure it always reads that
> counter from the same CPU?
No, it does not. Most implementations (depends on the HAL) simply
use the rdtsc assembly instruction to read the CPU-internal clock
count. However, as each CPUs gets the same clock signal, their
rdtsc values should also be same.
The QueryPerformanceCounter specs gives some more evidence: "On a
multiprocessor machine, it should not matter which processor is
called. However, you can get different results on different
processors due to bugs in the BIOS or the HAL."
So it depends on the machine...
Well, I suppose you can assume that a more or less recent SMP
machine has no such bugs in the BIOS or HAL.
[ Written by Daniel Lohman]
-Mark
"Mark J. McGinty" <mmcg...@spamfromyou.com> wrote in message
news:7ncee.15674$_K.4813@fed1read03...