I ported an access database to SQL server 7 (desktop edition) through
the upsizing wizard and now, it seems, the performance (on the select
and insert types of stored procedures) has been slowed by at least
100%.  I am new to SQL Server, so could anyone maybe give me a pointer
or two on what I am missing.

--
Robert Gelb
Data Express

How are you connecting to SQL server?

through OLEDB & ADO.  However, I just tried connecting through DAO & ODBC
Direct and didn't get better results either

--
Robert Gelb
Data Express

I would take a look at your indexs. They make a big difference in the
performance of your server.  Also once a table is propperly indexed,
make sure that you selects and stored procedures use those indexes.

Marten

I am going through a similar process.  We are moving data from an
Access 97 database to SQL Server 6.5.  The VB code that was written
to access the database is in version 4, and I am changing it all
to version 5.  I haven't noticed any slow down in the database yet,
but I will share a few things that I have been correcting and finding
out about.
Lots of things that work in Access do not work in SQL Server.  Some of
these are iif, certain inner joins, [brackets], and I have had to
change the way querydefs are done in the code.  Since the VB4 program
used the DAO model, it appeared to be easier to change the code to use
ODBC Direct to talk to the database.  After initial testing, performance
was so poor that the program was unusable.  That was when I upgraded
to the Enterprise Edition and began recoding the entire program to use
RDO.  So far I have been satisfied with the performance.  I have not
tried to use OLEDB and ADO yet, but will run some tests with it when
I get the extra time.  
Another thing we did to ensure that the SQL Server would perform better
was to put 128MB of RAM in it, and configure SQL Server to use more RAM.
Indexing is always a good thing to do to speed up searches.  It may slow
adding items, but the speed increase when searching is worth it.
I have also ran some recent tests with the Remote Data Control
properties.
I found that having the RDCs work in read only mode makes them much
faster,
and I can use the Execute method to do Add/Edit/Deletes when they are
required.  In a test when retrieving 8,800 records over the network, it
took
8 seconds to load and fill the control, and then 64 seconds to search
from
top to bottom, with the locktype set to rdConcurRowVer and the
resultsettype
set to rdOpenKeyset.  I changed the locktype to rdConcurReadOnly and the
resultsettype to rdOpenStatic and it took 4.4 secs to load and 13
seconds
to search from top to bottom.  I am also testing the cursordriver
property.
You might want to see what similar settings you are using, and if the
resultset does not need to be updated, ensure it is set up for read
only.

Something I am wondering about: I have a program with about 80 forms,
and
at any one time there can be a maximum of about 12 RDCs being used.
Everytime I use an RDC (which is usually in bound mode to an APEX
DBGrid), the RDC opens a new connection to the SQL Server.  I have the
DataSourceName and Connect properties set to the same values in all
controls, and to the same value as when I opened an initial connection
to
the server.  Is there a way to have these RDCs work through a SINGLE
connection to the server?  What is the "normal" number of connections
that should be open at one time on the SQL Server for about 20 users?
Some people tell me 50 is plenty, and others think that since SQL
Server is made to handle more, then 2000 is a better number.

Tim Bratcher
BAH