ADP searches vs. MDB

[Charax]

I'm a newbie to ADP using Access 2003, WinXP sp2, SQL Server 2000 sp3a. I
thought about which group to post this message in -- and chose ADP since I
need people who understand both MDB and ADP to consider my question.

I'm an Access user since 1.0 and love the flexibility. But I want to put my
database on the Internet and so have upsized my MDB to ADP with SQL Server
back end. I am concurrently using (1) the MDB with ODBC connection to the
SQLDB for all those neat things that can be done fast and easy; (2) an ADP
to manage the SQLDB and learn how to adapt my MDB tricks to the ADP world;
(3) SEM, QA and other SQL Server tools when needed; and (4) plan to use
Visual Studio 2003 to create the ASP.NET web browser interface.

My first question is about searches (and I know next to nothing about SQL
Server). I have an identical Access form in both the ADP and MDB front ends
based on a full table dataset of 30,000 records. In ADP, a Ctrl-F search on
a non-PK indexed nvarchar (255) text field is lightening-fast. The same
search in the ODBC linked MDB form takes 26 seconds (or 35 seconds if the
entry is not found). Can someone help me understand why the dramatic speed
difference? The search dialog options are Match = Any Part Of Field, Search
= All, Match Case not checked, Search Fields As Formatted not checked. The
underlying SQL Server table column allows nulls but has a restraint to not
allow zero-length entries.

Chris Hopkins

 

[Sylvain Lafontaine]

By default, ADP load into memory the first 10000 records for display and
only these records are searched when using Ctrl-F. You should try searching
a record which is not in the first 10000.

MDB will load 100 records for display and then will load each subsequent
record from the database until a match is found.

 

[Steve Jorgensen]

By default, ADP load into memory the first 10000 records for display and
only these records are searched when using Ctrl-F. You should try searching
a record which is not in the first 10000.

MDB will load 100 records for display and then will load each subsequent
record from the database until a match is found.

By the way, even though the ADP loads 10000 records into a recordset, it does
so 50 records at a time. If you search for a record that hasn't been loaded
yet, it just won't be found. This is frequently a problem when trying to move
to a specific row in the Open or Load event handler of a form.

 

[Charax]

... In ADP, a Ctrl-F search on a non-PK indexed nvarchar (255) text

By default, ADP load into memory the first 10000 records for display and
only these records are searched when using Ctrl-F. You should try
searching a record which is not in the first 10000.

MDB will load 100 records for display and then will load each subsequent
record from the database until a match is found.

Sylvain,

I didn't mention that I navigate to the last record, then back to the first
record before using the Ctrl-F search. The search term is constructed to
find a record within the last 10 records. So why the great disparity in
search time?

I also didn't mention that I know better than to use such a large number of
records behind a form; this is just for testing to help me understand the
differences betwixt MDB and ADP.

Regards,

Chris Hopkins

 

[Charax]

By the way, even though the ADP loads 10000 records into a recordset, it

does
so 50 records at a time. If you search for a record that hasn't been
loaded
yet, it just won't be found. This is frequently a problem when trying to
move
to a specific row in the Open or Load event handler of a form.

Steve,

Thanks for the reply. See my earlier note in this thread to Sylvain where I
mention that I force all the records to be loaded before beginning the
search. So what then could explain the dramatic difference in search speed
between an SQL Server table ODBC attached in MDB and the same table in an
ADP?

Regards,

Chris Hopkins

 

[giorgio rancati]

Thanks for the reply. See my earlier note in this thread to Sylvain where I
mention that I force all the records to be loaded before beginning the
search. So what then could explain the dramatic difference in search speed
between an SQL Server table ODBC attached in MDB and the same table in an
ADP?

Chris Hopkins

Hi, Chris

In ADP project all of the record are transferred to the client side, the
search comes performed in the memory of the client.

In a MDB (with SQL Server table ODBC attached) is necessary to wait for
that all the lines are analyzed from the cursor that questions Sql Server.
It monitors with the SQL Profiler when search a text
Open Cursor
----
declare @P1 int
set @P1=4
exec sp_prepexec @P1 output, N'@P1 int', N'SELECT .....'
----

next cursor until the finds
----
exec sp_execute 4, 1
exec sp_execute 4, 2
exec sp_execute 4, 3
....
....
exec sp_execute 4, 78856
.....
....

 

[Sylvain Lafontaine]

The fact that you did take the precaution of navigating first to the last
record and then back changes absolutely nothing because Access don't keep
these records in memory.

Also, you should learn how to use the profiler on SQL-Server and stop
wasting your time in comparaisons between different technologies; especially
with thing such as Ctrl-F.