Response Time When retrieving data into an ACCESS form from SQL se

[Joe S]

It seems like the slow response time (about 700 records) from SQL Server
database, is related to retrieving one record at a time instead of
retrieving all records at the same time into recordset for the ACCESS form.
This retrieval generates considerably network traffic. What I would like to
know is how response time can be improved. Is there a setting in ACCESS to
improve the retrieval process or is this something that is related to
programming such as an event?? In this case of 700 records the response time
is about 4 to 5 minutes. The response time is instantanous for just a few
records. The response time is about the same using either ACCESS 2003 or
ACCESS 2007 and differences in local computer memory and speed don't factor
in much.

 

[Jeff Boyce]

Here's another approach ...

Don't retrieve all 700, just retrieve the one you need to work with.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom van Stiphout]

On Fri, 22 Feb 2008 14:49:04 -0800, Joe S

If it takes 4-5 minutes to retrieve 700 records, you either have A LOT
of complicated calculations going on, or you are going about it the
wrong way.
For example some people write too much procedural code in stored
procedures. I have been accused of that myself With SQL Server
2005 you can use .NET assemblies for procedural code.
Indexing *can* make a big difference, but probably not as much as
needed in this case.
You'll need to have a SQL expert look at this in more detail. A few
hours of billable time may make all the difference. "Microsoft
Solution Provider" in your yellow pages may be a good place to start.

I see that question quite often: is there a setting to speed things
up? Like the TURBO button we had on the early PCs. Sorry, no such
thing anymore.

-Tom.

 

[MikeL]

Have you tried calling a stored procedure?

 

[Joe S]

Mike,

I am already calling a stored procedure. I am using a SQL 2000 Server
Database.

What I was wondering if there was something internally in ACCESS, that's
causing the slowdown such as some event being trigger when each record is
retrieved, even though I am calling just one stored procedue with parameters?
If not, then I guess I may not be able to do anything with the 700 records
response issue.

Yes, I have already taken means to select fewer records by provide extra
select options. Seems like 700 records should not create a 4 to 5 minute
response where it took only 70 seconds in the past, but the network response
has apparently slown down.

 

[MikeL]

I see. We have not had that issue here. I call SQL Server SPs using ADO
connect as follows. No problems with this.

cn.Open "Provider=SQLOLEDB.1;Password='" & PvarPSWD & "';" _
& "User ID='" & PvarUSERID & "';Persist Security info
=false;Initial Catalog='" & DATABASE & "';Data Source='" & PvarSERVER & "'"
Set cmd.ActiveConnection = cn