Cursor taking longer to open at every open

I am connecting to a server using ODBC. The program I am
running opens, fetches and closes a cursor at regular
intervals. It is a 'greater-than-or-equal-to'
FAST_FORWARD, READONLY cursor. Isolation level is READ
UNCOMMITED. We use a dedicated connection for the
cursor. Using TCP/IP to connect.
The program is moving from the beginning of a million row
table, opening and closing the cursor every time it wants
a new row - it does not want every row. Bad code -
DEFINATELY! But a re-write may not be a usable option.
When I run my program locally (pointing to the database on
the server), it takes the same amount of time every time
the cursor is opened. The ODBC DSN is defined locally.
When I run the program directly on the server (same
database), the amount of time to open the cursor gets
progressivly worse. Almost as though on the server it is
reading from the beginning of the table everytime, and as
the program logically moves through the table. The ODBC
DSN is defined on the server.Just curious why you're using cursors and not the
preferred method of T-SQL, which would make your access
much faster. I know you mention bad code, but if it's
ingrained, and other than, say, making sure you have thr
right index or hints, there's not much you can do. SQL
Server is not best when used with cursors for large
things; using T-SQL is like night and day.
>--Original Message--
>I am connecting to a server using ODBC. The program I am
>running opens, fetches and closes a cursor at regular
>intervals. It is a 'greater-than-or-equal-to'
>FAST_FORWARD, READONLY cursor. Isolation level is READ
>UNCOMMITED. We use a dedicated connection for the
>cursor. Using TCP/IP to connect.
>The program is moving from the beginning of a million row
>table, opening and closing the cursor every time it wants
>a new row - it does not want every row. Bad code -
>DEFINATELY! But a re-write may not be a usable option.
>When I run my program locally (pointing to the database
on
>the server), it takes the same amount of time every time
>the cursor is opened. The ODBC DSN is defined locally.
>When I run the program directly on the server (same
>database), the amount of time to open the cursor gets
>progressivly worse. Almost as though on the server it is
>reading from the beginning of the table everytime, and as
>the program logically moves through the table. The ODBC
>DSN is defined on the server.
>.
>

CurrentProject.IsConnected is reporting on what?

Does CurrentProject.IsConnected tell me that the ADP is connected to the SQL
database, or that it sees the network?
I have an ACCESS ADP connecting to a SQL db via a wireless network. I
thought I would check CurrentProject.IsConnected to see if I had lost
connection due to out of wireless range, and if so, re connect to the DB via
code. But when I come back into range, this CurrentProject.IsConnected is
always true, but I am still not re-connected to the DB.
Any thoughts?
Thanks,
SteveTry posting to one of the Access newsgroups instead - try:
microsoft.public.access.adp.sqlserver
-Sue
On Tue, 11 Apr 2006 07:49:02 -0700, SteveInBeloit
<SteveInBeloit@.discussions.microsoft.com> wrote:

>Does CurrentProject.IsConnected tell me that the ADP is connected to the SQ
L
>database, or that it sees the network?
>I have an ACCESS ADP connecting to a SQL db via a wireless network. I
>thought I would check CurrentProject.IsConnected to see if I had lost
>connection due to out of wireless range, and if so, re connect to the DB vi
a
>code. But when I come back into range, this CurrentProject.IsConnected is
>always true, but I am still not re-connected to the DB.
>Any thoughts?
>Thanks,
>Steve