Roundtrips!

Suppose an ASP application retrieves 100 records from a SQL Server 7
database table. How many roundtrips (between the front-end & the
back-end) are involved in retrieving the 100 records?

Thanks,

Arpan

Re: Roundtrips!

That depends on how you do it. Are you getting all the records at once? If
so, one. What's the underlying question?

Ray at work

"Arpan" wrote in message
news:1129403757.467362.310150@z14g2000cwz.googlegroups.com.. .
> Suppose an ASP application retrieves 100 records from a SQL Server 7
> database table. How many roundtrips (between the front-end & the
> back-end) are involved in retrieving the 100 records?
>
> Thanks,
>
> Arpan
>

Re: Roundtrips!

Well I am doing it in the simplest way........something like this:

<%
Dim objConn
Set objConn=Server.CreateObject("ADODB.CONNECTION")
'ConnectionString
objConn.Open

Dim objRS
Set objRS=Server.CreateObject("ADODB.RECORDSET")

Dim strSQL
strSQL="SELECT * FROM MyTable"
objRS.Open strSQL,objConn

'all 100 records are populated in this Recordset
%>

That's it!

What I mean to ask is is it something like to get the 1st record, ASP
goes to the database which makes it 1 trip, reads it & comes back thus
making it 2 trips. So for fetching all the 100 records, 100*2=200 trips
are involved in the process. Is it something like that?

Thanks,

Regards,

Arpan

Re: Roundtrips!

On 19 Oct 2005 12:18:58 -0700, "Arpan" wrote:

>Well I am doing it in the simplest way........something like this:
>
><%
>Dim objConn
>Set objConn=Server.CreateObject("ADODB.CONNECTION")
>'ConnectionString
>objConn.Open
>
>Dim objRS
>Set objRS=Server.CreateObject("ADODB.RECORDSET")
>
>Dim strSQL
>strSQL="SELECT * FROM MyTable"
>objRS.Open strSQL,objConn
>
>'all 100 records are populated in this Recordset
>%>
>
>That's it!
>
>What I mean to ask is is it something like to get the 1st record, ASP
>goes to the database which makes it 1 trip, reads it & comes back thus
>making it 2 trips. So for fetching all the 100 records, 100*2=200 trips
>are involved in the process. Is it something like that?
>
>Thanks,
>
>Regards,
>
>Arpan

Avoid, if possible Recordsets,.see:

http://www.aspfaq.com/show.asp?id=2467

Re: Roundtrips!

Arpan wrote:
> Well I am doing it in the simplest way........something like this:
>
> <%
> Dim objConn
> Set objConn=Server.CreateObject("ADODB.CONNECTION")
> 'ConnectionString
> objConn.Open
>
> Dim objRS
> Set objRS=Server.CreateObject("ADODB.RECORDSET")
>
> Dim strSQL
> strSQL="SELECT * FROM MyTable"

Hopefully this was an example only and you really aren't using selstar in
your production code. If you're really interested in reducing round trips,
you will avoid the use of "Select *".

> objRS.Open strSQL,objConn
>
> 'all 100 records are populated in this Recordset
> %>
>
> That's it!
>
> What I mean to ask is is it something like to get the 1st record, ASP
> goes to the database which makes it 1 trip, reads it & comes back thus
> making it 2 trips. So for fetching all the 100 records, 100*2=200
> trips are involved in the process. Is it something like that?
>
Since you are using the default forwardonly cursor, then yes, something like
this will be happening. Fortunately, since a "firehose" cursor is being
used, it will be extremely efficient. You can reduce the mumber of trips to
the database by increasing the recordset's CacheSize property, which
defaults to 1 (record).

Different cursortypes will result in different behavior. For example, a
client-side cursor (which will always be a static cursor) will always
retrieve all its records in a single round trip to the database. You will
need to weigh the benefits of the fewer round trips against the penalties of
increased resource usage on the web server. Factors involved in your
decision about what cursor type to use include:
desired functionality - e.g., do you need MovePrevious functionality?
number of records to be retrieved
size of the records to be retrieved

There is no magic answer here: you need to evaluate this on a case-by-case
basis.

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.