Access -> SQL

[Scott Burke]

We have an SQL Server.

I created and filled a table in SQL. Table -> HISTORY_OLD
When I use SQL server mangament software I can create a query and get the
results in a second.

Then I created an Access 2003 program. Linked the SQL table HISTORY_OLD.

Even the simples query takes 5 - 10 min to run.

NOW: HISTORY_OLD has two indexes. 1) name 2) clrdate

It looks like any query that Access sends to the SQL server,
SQL does not use the indexes.?????

Is there a setting in Access or SQL that I have to set to make the SQL
server use it's indexes???

Scott Burke

 

[Al]

I don't know the answer to your questions but I've found it faster to access
a sql server table directly via ADO, rather than linking it.

 

[Scott Burke]

Hi AL.
Can you give me an example of what you are talking about?

Scott Burke

 

[BeWyched]

As you may know, Access isn't very good at manipulating large chunks of data
through SQL as the data is not processed at the back-end database, but bought
through to the front-end which takes time (as someone in this forum once
wrote - Access is all about moving great gobs of data needlessly !). This is
not normally a problem if both front and back-ends are on the same PC, or
within a high speed LAN, but is a nightmare otherwise.

Simply linking the front-end to the SQL server tables doesn't change this as
Access still performs the same way. A solution is to use pass-through queries
- here the SQL command is sent to the SQL server for processing. Accordingly
the full power of SQL Server is utilised and very little data other than the
results pass through the network.

To give you an example, I recently tested a somewhat complex SQL command
acting on a linked table compared to an equivalent pass-through query. The
back-end was situated on our server and accessed by 'remote access' through
'phone lines. The linked version took over 3 minutes whilst the pass-through
version less than half a second!

Access Help files have lots on pass-through queries.

Good luck.

BW

 

[Rick Brandt]

As you may know, Access isn't very good at manipulating large chunks
of data through SQL as the data is not processed at the back-end
database, but bought through to the front-end which takes time (as
someone in this forum once wrote - Access is all about moving great
gobs of data needlessly !). This is not normally a problem if both
front and back-ends are on the same PC, or within a high speed LAN,
but is a nightmare otherwise.

Simply linking the front-end to the SQL server tables doesn't change
this as Access still performs the same way

You are mistaken. When creating local queries against ODBC links the VAST
majority of the processing is done on the server. How much is done on the
server and how much is done locally can vary from one query to another, but it
would actually be very difficult to create a query where all of the processing
was done by Access/Jet.

A passthrough is only required to absolutely guarantee that 100% of the
processing is done on the server. That does not mean that regular queries have
0% of their processing done on the server. In most cases as long as the WHERE
clause is executed by the server then you are still getting a minimum of traffic
over the LAN.

 

[BeWyched]

Hi Rick

Whilst you may be correct, my experience has been otherwise. I have a number
of applications that feed remotely off SQL servers . Withour exception, the
linked route results in a frustratingly slow processing time, compared to a
near instant response for pass-throughs (comparing like-with-like).

This may be due to other factors which I haven't appreciated but it does
seem rather a coincidence.

Regards.

BW

 

[BeWyched]

Thanks Rick for the depth of your response.

As you say... "The main thing is that each query needs to be assesed on this
individually. ". With my DB's the assesment has fallen in favour of
pass-throughs but they are all similar involving, often multiple joins and
complex WHERE statements - perhaps this is why my view is tainted.

There again, diffent viewpoints are what forums are all about!

Cheers.

BW

 

[Scott Burke]

Hi Rick,
I think you missed an import point somewhere.

Here is the sitituation:
Server:
has a table HISTORY_0LD and it has 13.5 millinion records.
It has an index on Name.

My computer:
I linked HISTORY_OLD thru a ODBC driver.
I ran a simple query : Select * from HISTORY_OLD where name = 'Scott Burke'
This query could talk as long as 3 mins to return an answear!


If I use the SQL SERVER MANAGER software I get and instant responce.
<close to instant>

Does this not sound like the index on the SQL server is not being used when
an Access program is using it?

To me it seems that SQL is deliberly degrading Access proformanc!

 

[Rick Brandt]

Hi Rick,
I think you missed an import point somewhere.

Here is the sitituation:
Server:
has a table HISTORY_0LD and it has 13.5 millinion records.
It has an index on Name.

My computer:
I linked HISTORY_OLD thru a ODBC driver.
I ran a simple query : Select * from HISTORY_OLD where name = 'Scott
Burke' This query could talk as long as 3 mins to return an answear!


If I use the SQL SERVER MANAGER software I get and instant responce.
<close to instant>

Does this not sound like the index on the SQL server is not being
used when an Access program is using it?

To me it seems that SQL is deliberly degrading Access proformanc!

I run queries exactly like that all the time and get the same instant
response as I do in a passthrough. A query as simple as that will
undoubtedly send the sql to the server and the server will do the work. If
you are not seeing that then I suggest checking your driver version.

 

[Scott Burke]

HI Rick.
Here is my driver. What version to you use?

SQL Native Client
2005.90.2047.80
Mircosoft Coorporation
SQLNCLI.DLL
04/14/2006

This driver was installed by SQL Server Managemnet Studio 2005

Scott Burke

 

[Rick Brandt]

HI Rick.
Here is my driver. What version to you use?

SQL Native Client
2005.90.2047.80
Mircosoft Coorporation
SQLNCLI.DLL
04/14/2006

This driver was installed by SQL Server Managemnet Studio 2005

I can't explain that behavior. Whenever I have used SQL tracing tools to see
what is being passed to the server a simple SELECT against one table like your
example was always passed to the server intact.