Developer Forum

A simple select statement is taking 5 minutes to run in SQL Server. There
are no sorts, groupings or where blocks, simply a Select Field1,Field2, ....
From Table. The table contains 606,775 records. When a query is run from
Access that uses a linked table to that table in SQL Server, it takes 35
minutes to run. When the same query is run from Access linked to a table in
another Access application also with 606,775 records, it runs in 35 seconds.
Any ideas ?

Can you actually post DDL or something?

In any event, when Access returns the data is the database local?

What is the actual select statement and what is the schema for the table(s).


--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.yqcomputer.com/

Hi
Don't you have a WHERE condition? What would be a purpose to retrieve half
millinon rows?

Listed below is the Select statement and the DDL to create the table. The
Access database connects via a linked table to this table in SQL Server and
runs the query listed below. When it connects via a linked table in another
Access database with the same number of records, it runs much faster. Is
there any way to increase the speed when it uses SQL Server. Isn't SQL
Server expected to run faster ?

SELECT ACCOUNT, CUST, DESCR, AMOUNT, PERIOD, REFNUM, INVNUM, CDATE
FROM [Local Exchange G/L]


CREATE TABLE [dbo].[Local Exchange G/L] (
[ACCOUNT] [nvarchar] (16),
[CUST] [nvarchar] (8),
[DESCR] [nvarchar] (30),
[AMOUNT] [float] NULL ,
[PERIOD] [nvarchar] (4),
[REFNUM] [nvarchar] (10),
[INVNUM] [nvarchar] (10),
[CDATE] [datetime] NULL ,
[ENTDATE] [datetime] NULL
) ON [PRIMARY]
GO

For now the user has asked why the SQL Server linked table takes so much
longer than the Access linked table. The ultimate solution may be to
re-write the code and reduce the number of records being brought down. Do
you know why the SQL Server linked table would be so much slower. When the
query is run in Access, it is the same query.

Well, I never saw an answer to Uri's question, but I'll assume you don't
have the WHERE clause.

If you're returning 606K rows... that's a HUGE problem.

My guess is either you're running Access locally which will be far faster,
or behind the scenes Access is paging the records for viewing.

In other words, it's not Access vs. SQL it's probably you have a poorly
written app.

I did not write this application and would agree that it is poorly written.
I was told to change all of the linked tables to link to the SQL Server
tables instead of the Access tables, since the Access tables are created from
an application which is being replaced by SQL Server. We are running into
the same problem throughout the application. When the application is run
linked to the Access tables, it runs much much faster than when it is run
linked to the SQL Server linked tables. At this point, we do not have the
resourses to re-write this application, which would be the ultimate solution.
Is there anthing which can be done on the SQL Server or Access side which
will at least let it run at the speed of the Access linked tables ?