Re: SQL Server extremely slow

It's a common misconception that upsizing to SQL Server will give an
immediate performance boost, and it's very common for some queries to
perform worse (sometimes massively worse) when you do upsize.

This isn't because there is anything fundamentally wrong with architecture.
An Access application using a SQL Server back-end via ODBC linked tables is
capable of thoroughly outperforming an application with an mdb back-end, but
only if:

(i) the Access application with the mdb back-end was performing poorly (if
it was performing well anyway, then there isn't much scope to improve it!)
(ii) the Access application was performing poorly for the kind of reasons
which will make it worthwhile to upsize (which basically boils down to it
having "outgrown" Access in terms of user numbers and the load it imposes on
the network)
(iii) the Access application is reasonably well designed (a poor design is a
poor design regardless of the database engine)
(iv) you are prepared to selectively tune and re-design the application to
get the best out of the Access/SQL Server architecture.

Point (iv) means understanding this: when you use SQL Server as the back-end
with ODBC linked tables, running any query involves TWO query engines:
Access/Jet, and SQL Server. Access/Jet queries are "translated" into SQL
Server queries, for the server to execute and return a result set. Often
this works very well. Almost as often it doesn't, either because you have
done something in the Access/Jet query which is fundamentally incompatible
with SQL Server, or because it's simply too complex to be "translated". In
these circumstances, large quantities of data will be hauled out of the
database and shunted across the network so that Access/Jet can execute the
query locally. Performance nose-dives horribly.

The good news is that, for any one query, this is usually easily rectified,
using techniques such as:

- alter the Access/Jet query so that it *can* be executed at the server
- rewrite it as a "passthrough" query i.e. a native SQL Server query
- create a view on the server and link to it as though it were a table.

The bad news is that, in a large application, you might find yourself doing
this a lot...


"Niranjan" <nhiras@xxxxxxxxx> wrote in message
news:6f900e82-66cf-4407-9d6e-bfed0566dea2@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi everyone.
I upsided my Access 2000 backend database to SQL Server. Everything
works, however, we have a remote site where a couple of people access
the database. That site is extremely slow. It takes about 5 minutes to
run a report that normally took less than 30 seconds on the Access
backend connected to the remote site.

The backend is connected via ODBC. The front end is still Access. The
backend SQL Server database does not have any stored procedures or
triggers.

The connection to this remote site is thru trust created between the
domains. They also use Opt-e-man service.

Any ideas are appreciated.


.

Relevant Pages

  • RE: full-text search failed with one million of key words (Sqlserv
    ... Research with "toto" was just a test. ... In real life, my database contains ... for only one book) And the table i want to query looks like this: ... I have to make big tests with sql server 2005 express (i'm waiting credits ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Error 25081: Incorrectly generating identities after a merge
    ... database similarly to SQL Server CE Replication. ... On the SQL Server 2000 database with replicated tables from the original ... The I synchronized that database and ran the same query: ... >> synchronization. ...
    (microsoft.public.sqlserver.ce)
  • Re: Looking for a professional SQL programmer for a small job
    ... Pro SQL Server 2000 Database Design - ... I have two queries to build and while I know my way around SQL, ... >> One is a seach query that pull rental properties from a database based on ...
    (microsoft.public.sqlserver.programming)
  • Re: Using a worksheet as a table in an SQL query
    ... I'll probably just dump the SQL Server data to a spreadsheet and join the two spreadsheets. ... You could use Access to link to both sources and create a crosstab query. ... There are ways to enter the data into their database via the application, but it would require them to go to many different windows instead of being able to enter the data all on one screen. ... There is not a great deal of data they would enter on the spreadsheet, but I'm not sure how slow that would make the query. ...
    (microsoft.public.excel.programming)
  • Re: Issue with a table that is 75%-80% of the database size
    ... SQL Server 2000 does not support table partitioning. ... Have you tried database differential backup? ... your query response time and backup time. ...
    (microsoft.public.sqlserver.server)