SQL Server updates slower than MS Access?

From: Fzy (trnospam_at_earthlink.net)
Date: 04/07/04 

Date: Wed, 7 Apr 2004 00:29:06 -0500

(sorry for the cross post - not sure where most people are on these sites)

We're having trouble with a new SQL Server installation upgraded from MS
Access.

A very large update query affecting 750,000 records with an inner join takes
more than an hour to run on SQL Server and was actually faster in MS Access!
Basically the query looks like:

UPDATE tbl1 set tbl1.code = lookup.code
FROM tbl1 INNER JOIN lookup ON (lookup.id1=tb1.id1 AND lookup.id2=tbl.id2)

tb1 has 750k records, lookup has a few hundred. Both tables have clustered
indexes defined on the id1,id2 combined fields.

Adding WHERE clauses to limit this query to less than 100k records takes a
few seconds, but as soon as it hits about 400k records, SQL Server hits the
wall. I'm guessing it has to do with logging all the changes for rollback
in case the query fails. So, other than trying to break the query into
small chunks with more complicated stored procedures (I really don't want to
do this because we have MANY data massaging queries of this nature, some
with joins some without), does anyone know how to improve this performance?
Does the temptbl come into play here? If so, what should I do to it to
improve performance?

Thanks for any help.

Relevant Pages

  • Re: "EXEC" in SQL Server 2000 Views
    ... Kalen Delaney ... > INNER JOIN b ... > Could not execute query against OLE DB provider 'MSDASQL'. ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Ac
    ... John and Baz, ... Now that I understand the underlying differences in query syntax, ... inner join cmsopen.dbo.apm_vendor v ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ...
    (microsoft.public.access.queries)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)