Speed of reading recordsets

[dalyjason]

Am I missing a faster way to read a sql and write all code from an access db?
Or is this just my server or ISP slowing things down?

This is an example of my code:
<%
sql2 = "SELECT * FROM children WHERE vid = "&request.querystring("id")&""
set RS2 = Server.CreateObject("ADODB.Recordset")
RS2.Open s1l2, conn1, 1, 3
Do Until RS2.EOF
%>
<%=RS2("vid")%><br>
<%=RS2("vtest1")%><br>
<%=RS2("vid2")%><br>
<%=RS2("vtest2")%><br>
<%=RS2("vid3")%><br>
<%=RS2("vtest3")%>
<%
RS2.MoveNext
Loop

RS2.Close
set RS2=nothing
%>

[Bob Barrows]

dalyjason wrote:

> Am I missing a faster way to read a sql and write all code from an
> access db? Or is this just my server or ISP slowing things down?
>
> This is an example of my code:
> <%
> sql2 = "SELECT * FROM children WHERE vid =

Don't use "Select *" in production code. You are forcing ADO to make two
trips to the database: one to get the field names, and the next to get the
data.

> "&request.querystring("id")&"" set RS2 =
> Server.CreateObject("ADODB.Recordset")
> RS2.Open s1l2, conn1, 1, 3
> Do Until RS2.EOF
> %>
> <%=RS2("vid")%><br>
> <%=RS2("vtest1")%><br>
> <%=RS2("vid2")%><br>
> <%=RS2("vtest2")%><br>
> <%=RS2("vid3")%><br>
> <%=RS2("vtest3")%>
> <%
> RS2.MoveNext
> Loop
>

Recordset loops are slow. You should avoid them.

> RS2.Close
> set RS2=nothing

Good. But, you also need to close and destroy your connection.

> %>

Try this:

Create a saved query in Access with the following sql:

SELECT vid, vtest1, vid2, vtest2, vid3, vtest3
FROM children WHERE vid = [pvid]

This is a "saved parameter query". when you run it in Access, Access will
prompt you for a value for [pvid]. when you run it via ADO, you will supply
that value.

Save it as qGetChildren. Then, in ASP, do this:

dim lVid, arData, iRow, iCol
lVid=request.querystring("id")

'debugging - comment this out when debugging is complete:
response.write lVid & "<BR>"

set RS2 = Server.CreateObject("ADODB.Recordset")
conn1.qGetChildren lVid,RS2

if not RS2.EOF then arData = RS2.GetRows
RS2.Close: Set RS2=Nothing
conn1.Close: Set conn1=Nothing

If IsArray(arData) then
for iRow = 0 to Ubound(arData,2)
for iCol = 0 to Ubound(arData,1)
response.write arData(iCol,iRow) & "<BR>"
next
next
else
response.write "No records were returned"
end if

This will be the most efficient way of returning your record(s).

I notice that your table needs to be normalized. You have repeating data
elements (vid2, vid3) which violates one of the rules of normalization. I
don't understan your application, so I cannot get more specific. I suggest
you do some reading on normalization. Start here:
[url]http://databases.about.com/library/weekly/aa080501a.htm[/url]

A google search should turn up more articles for you.

HTH,
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"

[Jason Daly]

do you have an email i can contact you one a one-on-one basis? My email
is [email]daly_jason@yahoo.com[/email].

I'm 18, and have been using ACCESS for my websites for about a year now.
I am only self taught from online sources, and therefore could use any
help you could offer.

I am not perfectly clear as to what fixes should be made to my db in
terms of normalization as well as other things...I can send you the link
to that if you wouldn't mind looking it over. I would more than
appreciate that.

I know i have another problem with the efficiency of my database though,
and this is that i use a separate ID column from the autonumber default
column to id my recordsets...i completely ignore the autonumber, and
don't know how to use it, or reference it.

Any help you can offer would be greatly appreciated.

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Jason Daly wrote:

> do you have an email i can contact you one a one-on-one basis? My
> email is [email]daly_jason@yahoo.com[/email].
>

I see no need to take this outside of the newsgroup. I offer my assistance
for free in the newgroups. For a one-to-one arrangement, we would need to
start talking about some sort of remuneration. Besides, if we keep it in the
newgroups, others can benefit from the assistance you receive, and you get
the benefit of receiving assistance from the other experts in this group
(I'm not the only one).

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"

[Bullschmidt]

Speed Tips by Charles Carroll
[url]http://www.learnasp.com/learn/speedtips.asp[/url]
GetRows is fast and GetString is faster but doesn't allow formatting of
a field.

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Jason Daly]

Using the code Bob gave in the beginning of this post, how can I
paginate the results of the array??

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

Jason Daly wrote:

> Using the code Bob gave in the beginning of this post, how can I
> paginate the results of the array??
>

Start reading here:
[url]http://www.aspfaq.com/show.asp?id=2120[/url]

and here:
[url]http://www.adopenstatic.com/experiments/recordsetpaging.asp[/url]

HTH,
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.

[Jason Daly]

Ok, I used one of the articles on one of the sites you recommended...but
now i'm getting this problem:


Microsoft JET Database Engine error '80040e24'

Rowset does not support fetching backward.

/D4Ly/child.asp, line 296

'================================================= =======
<!--#include file="connect.asp"--><%


check = "SELECT * FROM products WHERE vchild =" &
request.querystring("chd") & " AND vturnoff=" & 0 & " ORDER BY vid DESC"
ende=0


PerPage=12
PageNum= Request.QueryString("pg")
PerPage=clng(PerPage)
PageNum=clng(PageNum)


PageCnt = RowCnt \ PerPage

if RowCnt mod PerPage <> 0 then PageCnt = PageCnt + 1
if PageNum < 1 Then PageNum = 1
if PageNum > PageCnt Then PageNum = PageCnt


set RS = conn1.execute(check)



if not RS.eof then

Dim gr

rstop = PerPage * PageNum
rstart = rstop - (PerPage - 1)
RS.move(rstart-1) 'TROUBLE LINE HERE!!

if not RS.eof then
RowCnt = clng(RS(0))
end if
'================================================= =======

I don't know what change to make, but in the article it has a WITH
(NOLOCK) in the sql query. when i place this in my query, i get another
error on the query line.

suggestions? I already notice improvements in speed...amazing

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Jason Daly wrote:

> Ok, I used one of the articles on one of the sites you
> recommended...but now i'm getting this problem:
>
>
> Microsoft JET Database Engine error '80040e24'
>
> Rowset does not support fetching backward.
>

You need to use a different cursor type (which was probably shown in the
article)

>
> set RS = conn1.execute(check)
>

This is the problem right here. When you use Execute to open a recordset,
you get the default server-side, forward-only cursor. "forward-only" means
just that: you can't move backwards in the cursor

> RS.move(rstart-1) 'TROUBLE LINE HERE!!
>

You need to instantiate your own recordset object and set its properties
instead of using the default one returned from Execute. Like this:

set rs=server.createobject("adodb.recordset")
rs.open check,conn1,3,1,1
'3 = adOpenStatic - static cursor
'the first 1 stands for adLockReadOnly
'the second 1 stands for adCmdText - it tells ADO the type of command you
are
'using - in this case it 's a sql statement contained in a string of text

The ADO documentation can be found here.
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp[/url]
I suggest you look up all the methods and properties you see in these
articles and suggestions you see in the newsgroups.

> '================================================= =======
>
> I don't know what change to make, but in the article it has a WITH
> (NOLOCK) in the sql query. when i place this in my query, i get
> another error on the query line.
>

NOLOCK is a query hint that is only usable in SQL Server. You are using a
Jet database.

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"

[Dhanraj K.S]

Whats Your SQl Server Version,, if it is 7 or 2000 u can try using
OpenRecordset function

Thanks



"dalyjason" <daly_jason@yahoo.com> wrote in message
news:2ef39e59.0402160621.17874dda@posting.google.c om...

> Am I missing a faster way to read a sql and write all code from an access

db?

> Or is this just my server or ISP slowing things down?
>
> This is an example of my code:
> <%
> sql2 = "SELECT * FROM children WHERE vid = "&request.querystring("id")&""
> set RS2 = Server.CreateObject("ADODB.Recordset")
> RS2.Open s1l2, conn1, 1, 3
> Do Until RS2.EOF
> %>
> <%=RS2("vid")%><br>
> <%=RS2("vtest1")%><br>
> <%=RS2("vid2")%><br>
> <%=RS2("vtest2")%><br>
> <%=RS2("vid3")%><br>
> <%=RS2("vtest3")%>
> <%
> RS2.MoveNext
> Loop
>
> RS2.Close
> set RS2=nothing
> %>