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. 50 11532
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
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.
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?
--
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.
Dave Anderson wrote: 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?
Yes, with a Replace statement.
--
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 wrote: 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 an example of a function I did to get a list of facilities from our
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
--
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.
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.
Which, combined with your technique, leads to a question -- does SQL Server
2005 offer HTMLEncode functionality?
--
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.
Dave Anderson wrote: 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
--
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 wrote: 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
--
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" <GT**********@spammotel.com> wrote in message
news:%2******************@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
%>
Bob Barrows [MVP] wrote: 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.
--
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:uD**************@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 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.
Mark J. McGinty wrote: (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.
--
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.
Mark J. McGinty wrote: "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message news:uD**************@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.
I'm doing some tests now in VB6 that seem to bear out what you are saying.
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
--
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.
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:uA****************@TK2MSFTNGP15.phx.gbl... "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message news:uD**************@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.
Agreed. The article's intent is not to dictate which approach is best.
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.
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.
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" <no****@thankyou.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl... "Mark J. McGinty" <mm******@spamfromyou.com> wrote in message news:uA****************@TK2MSFTNGP15.phx.gbl... "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message news:uD**************@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.
Agreed. The article's intent is not to dictate which approach is best. 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.
I went ahead and created a COM object that calls QueryPerformanceFrequency
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
Mark J. McGinty wrote: I went ahead and created a COM object that calls QueryPerformanceFrequency 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
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
--
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.
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:ep*************@TK2MSFTNGP15.phx.gbl... 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/...a7c0bfa3fa2c5a
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
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:O0**************@tk2msftngp13.phx.gbl... 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.co...at_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.
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.
Bob Barrows
--
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.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2***************@TK2MSFTNGP15.phx.gbl... 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?
Chris Hohmann wrote: 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
--
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.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:O%****************@TK2MSFTNGP14.phx.gbl... Chris Hohmann wrote:
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
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.
Here's my modified test code:
<%
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.
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.
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" 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.
The DLL actually contains 2 objects, .HRTimer and .HRTimerFT, they are identical except that the former is marked single/apartment threaded, and the latter is marked free-threaded. I couldn't imagine any potential thread conflicts, though as I think of it I wonder... apparently the API just reads a counter maintained by the CPU, which might be a problem on an SMP box if one method was executed on one CPU, but another call into the same instance executed on another CPU.
But I see nothing obvious that can be done -- I wonder why there is a SetThreadAffinityMask but no corresponding Get...? There is an API to set the preferred thread, but that seems insane to do it inside an ASP app... Maybe the system always reads the same CPU counter when QueryPerformanceCounter is called? I posted the question to the WinDev list, someone there might know.
[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/...a7c0bfa3fa2c5a
I'd forgotten about that post entirely... cool! :-)
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.
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...
[snip] 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
[...]
Hopefully the extra iterations won't be necessary any more. :-) There should never be a case where my HRTimer object's resolution is insufficient, however, since the time it takes to call it will vary by as much as 4 microsecs (on my system) the ops being timed need to take longer than about 10 microsecs for numbers to be meaningful.
Ok so, I took your timing test script and replaced the references to Timer with calls to my object... I think I'll make that its own post.
-Mark
I modified Chris' ASP script to call my timer object (with a few interim
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;Tr usted_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>
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.
--
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"
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.
- 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.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:e%****************@TK2MSFTNGP09.phx.gbl... 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"
---------------------------------------------------------------
---------------------------------------------------------------
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eV**************@tk2msftngp13.phx.gbl... 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.
That may unfairly penalize the GetRows method since it is loading all three
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". :)
"Michael D. Kersey" <md******@hal-pc.org> wrote in message
news:%2****************@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.
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:udBde.724$eU.315@fed1read07... 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.
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:uK**************@TK2MSFTNGP12.phx.gbl... I modified Chris' ASP script to call my timer object (with a few interim 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
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.
Chris Hohmann wrote: 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
--
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.
Chris Hohmann wrote: [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
--
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.
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 ...
Bob Barrows
--
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.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uO****************@TK2MSFTNGP15.phx.gbl... 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
Bob Barrows
-- 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.
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:Oo**************@TK2MSFTNGP14.phx.gbl... 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
Mark J. McGinty wrote: "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message news:uO****************@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/archiv.../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.]
"Michael D. Kersey" <md******@hal-pc.org> wrote in message
news:e%****************@TK2MSFTNGP15.phx.gbl... Mark J. McGinty wrote: "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message news:uO****************@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/archiv.../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
Mark J. McGinty wrote: "Michael D. Kersey" <md******@hal-pc.org> wrote in message news:e%****************@TK2MSFTNGP15.phx.gbl...
Mark J. McGinty wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message news:uO****************@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/archiv.../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?
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/archiv.../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.
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:%2****************@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
Chris Hohmann wrote: "Michael D. Kersey" <md******@hal-pc.org> wrote in message news:%2****************@TK2MSFTNGP09.phx.gbl...
<snipped>- 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?
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?
"Michael D. Kersey" <md******@hal-pc.org> wrote in message
news:Ou**************@TK2MSFTNGP09.phx.gbl... 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.
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:uA**************@TK2MSFTNGP15.phx.gbl... "Chris Hohmann" <no****@thankyou.com> wrote in message news:%2****************@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.
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:OK**************@TK2MSFTNGP14.phx.gbl...
[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
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:nJ9ee.15435$_K.11619@fed1read03... "Chris Hohmann" <no****@thankyou.com> wrote in message news:OK**************@TK2MSFTNGP14.phx.gbl...
[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.
Yeah, that's what I feared. With regards to LARGE_INTEGERS, I think you
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.
> Yeah, that's what I feared. With regards to LARGE_INTEGERS, I think you cheat the system by using Currency.
That should read ..."I think you CAN cheat the system by using Currency".
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:Od**************@TK2MSFTNGP15.phx.gbl... 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Stuart |
last post by:
Hi there,
I am trying to populate the array "yourImages" in the following code,
http://www.javascriptkit.com/script/script2/preloadimage2.shtml
except i am trying to fill it with the values from...
|
by: Steve Jorgensen |
last post by:
I keep having problems in which ADO disconnected recordset work under some
circumstances, but lose all their data at other times, having no rows or
fields, though the recordset object still exists....
|
by: Pieter Linden |
last post by:
The answer to this one is probably "test it yourself and find out!",
but I'll ask anyway. Pursuant to my previous question - sending
separate recordsets to Word using the CreateTableFromRecordset...
|
by: lakshmi |
last post by:
Hi all,
I recently rewrote a data intensive C++ program in C#.
The C++ program was traversing 3 recordsets that were all
open at the same time. I replaced those 3 recordsets with
3 .NET data...
|
by: Randy Harris |
last post by:
I was inspired by the recent discussion of returning multiple recordsets to
ADO from a stored procedure. (Amazed is probably more accurate). I asked
about how to accomplish same with Oracle and...
|
by: Donald Grove |
last post by:
I want to populate an array with values from an ado recordset
(multiple rows)
I use the absolute position of the cursor in the recordset to define
the row of my array to be populated. I have a...
|
by: mrmagoo |
last post by:
I'm building a vb.net Forms project that is getting data from a SQL Server
database.
One of the main goals of the project is to be really responsive to events,
such as textbox change events. I...
|
by: rdemyan via AccessMonster.com |
last post by:
Can someone help me with creating code that will look for DAO recordsets in
modules and then check to see if the recordset is also closed in the module.
All of my recordsets are of the form rs*...
|
by: BeckR |
last post by:
Hello - Thanks for reading my post.
I am a newbie when it comes to VBA programming, but have managed to do what I need to do, until now.
I have an Access 2000 database (running WinXP Pro...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |