Select takes 5 minutes, With linked table takes 35 minutes.

Select takes 5 minutes, With linked table takes 35 minutes.

Post by cm1jb21wdX » Thu, 12 Apr 2007 23:22:03


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 ?
 
 
 

Select takes 5 minutes, With linked table takes 35 minutes.

Post by Strid » Fri, 13 Apr 2007 04:14:37


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/

 
 
 

Select takes 5 minutes, With linked table takes 35 minutes.

Post by Uri Diman » Fri, 13 Apr 2007 16:10:28

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

Select takes 5 minutes, With linked table takes 35 minutes.

Post by cm1jb21wdX » Fri, 13 Apr 2007 21:22:02

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
 
 
 

Select takes 5 minutes, With linked table takes 35 minutes.

Post by cm1jb21wdX » Fri, 13 Apr 2007 21:26:02

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.
 
 
 

Select takes 5 minutes, With linked table takes 35 minutes.

Post by Strid » Fri, 13 Apr 2007 22:54:00


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.
 
 
 

Select takes 5 minutes, With linked table takes 35 minutes.

Post by cm1jb21wdX » Sat, 21 Apr 2007 22:30:04

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 ?