slow sql performance finding records towards end of table

[Chad]

We have an MS Access 2003 front-end (MDE) linked to MS SQL 2000 Server
database back end. One of the tables in the SQL server contains
(16700) records. The performance is slow when executing the following
line of VB code to go to a specific record on a form:

CustomerCode.SetFocus

DoCmd.FindRecord yCustomer, , True, , True, acCurrent

This process takes a long time (up to 10 seconds) for records towards
the end of the table. It is rather quick when opening a record that
falls at the beginning of the table. We did not have this problem
when we were using Access to store data. We tried optimizing the
performance by creating indexes on the table and that improved the
performance but the performance is still much slower than it was when
the data was in Access.

I should also point out that this is happening for all tables with a
large amount of records. Old records (towards the top of the table)
come up fast, and new records (towards the bottom) come up very slow.

Thanks in advance for any suggestions.

 

[Albert D. Kallal]

You really do want to avoid attaching a form to a table with a large number
of records.

(by the way, 16,000 records is VERY small). Even for non sql server type
files, 100,000 records is a less then 1 second deal in JET.

With sql server, or just a plan mdb file share, you really want to control
the number of records that a form has to deal with. This in effect is the
secret to high performance software. In fact, if you use sql server with
c++, vb.net, VB, ms-access, they ALL PERFORM the same!!! Again, VB, or c++
is NOT faster then ms-access when using sql server (so, ms-access is just as
fast as those other guys).

And, as you note, ms-access and JET is ACTUALLY FASTER then sql server!!! We
see weekly posts in the sql newsgroup about how some ms-access application
that runs fine now runs slower with sql server. So, ms-access is very fast,
but does have the scalability and ability to support the number of users
that sql server has..

So, why is some super duper heavy duty industrial data base system like sql
server not running fast? Well, it turns out it usually the developers fault!

Imagine for a minute you walk up to a instant teller (bank) machine, and
the form comes up to ask you for your account number. Do you actually think
the teller machine downloads every single account number then THEN asks you
what account number to show? I think you can rapidly see how this is a very
waste full approach.

The same applies to your form. Don't just open up a form with a huge file.
Ask the user what customer number, or what account number, and THEN LOAD the
form to that ONE RECORD. This approach means that 1 record is ONLY loaded to
the form. If you have 50,000, or even a 1 million records, the load time of
the form will be instant (even without sql server, if you only load one
record...the speed is instant...).

So, you need to ask, or get the search BEFORE you load the form...

Don't just go and throw up a form with 16,000 records at the user...as that
is just a bad design, and as you can see, even with super duper sql server,
it don't run very well. In fact, the ONLY reason why you could get away
doing this before is because JET is so fast (in fact, faster then sql
server!). However, this idea of forms being loaded up with huge recordsets
is just not a good way to develop software. Ms-access just lets you easily
do this, and is very fast. As a downfall, many people think ms-access is not
very good and is slow (in fact, as mentioned, JET is usually faster then sql
server). So, the performance slowness is really the fault of us developers
(myself included until I learned that good software don't show forms with
HUGE rocordsets).


So, you need to build up some search screens BEFORE the form. Here is some
screen shots of the kind of thing you can do:

http://www.attcanada.net/~kallal.msn/Search/index.html

 

[Albert D. Kallal]

So, ms-access is very fast,
but does have the scalability and ability to support the number of users
that sql server has..


The above should read:

bus does NOT have the scalablity and ability.....

 

[Fred Boer]

Or would that be:

but does NOT have the scalability and ability.... <g,d,r>

Cheers!
Fred Boer

 

[Sylvain Lafontaine]

It's probably because Access must transfert the whole table over the network
to make this kind of search. This doesn't occur with JET as the backend
because Access has a lot more of insider knowledge about the statistics of
the table in comparaison of a linked table; whose internal is completely
hidden by the SQL-Server.

When using Access as a FE to SQL-Server, you must rethink a good part of
your logic. For example, you shouldn't open the whole table but only a
subset of it. Make plenty use of views, too, especially in case of joined
tables. (If you want to make update through a view, then take a look at
http://support.microsoft.com/?kbid=209123 ). If you don't use views, there
is a good chance that some of your queries over joined tables will slow down
to a crawl, so slow that even your current waiting time of 10 seconds will
look like blazing speed.

S. L.

 

[Pieter Wijnen]

DoCmd is very slow on linked tables
try using a RecordsetClone instead

something like (in DAO):
Sub btnfindName_Click()
Dim RsC As DAO.Recordset
Set RsC = Me.RecordsetClone
RsC.Find "Name = '" & Me!SearchName.Value & "'"
Me.Bookmark = RsC.Bookmark
Set RsC = Nothing
End Sub
' Make sure the field is properly indexed....

HTH

Pieter