Re: sql server slower than MS Access

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

Date: 7 Apr 2004 13:30:21 -0700

Responses inline...

> Verify that any poorly written update triggers are not causing problems

There are no update triggers.

>
> Add "tbl1.code <> lookup.code" to the WHERE clause to avoid un-necessary
> updates (note that you need to adjust this logic to handle NULL values
> correctly if NULLs are allowed) .

This would help in theory, but this project is a huge data
manipulation project, where source text files (snapshots from
mainframe processing systems) are read in every few days and
manipulated. When this query is run, tbl1.code is NULL for all
records.

>
> The statement "Does the temptbl come into play here?" means nothing to
> anyone without knowledge of your system internals. Perhaps you can
> elaborate on what prompted the question and provide some background on why
> you think it might "come into play".

I'm sorry - I should have typed 'tempdb'! My question arises from my
lack of knowledge of how SQL Server stores changed records during an
update query before actually committing the results. If it uses the
tempdb(question #1), should I look at enlarging the initial size of
tempdb? move it to another physical drive? etc.

>
> The biggest problem is that you have clustered on the updated columns. By
> changing these columns, you are effectively forcing the DB to physically
> re-arrange the table (and [i believe] to update any other non-clustered
> indexes as well). If you want to keep this clustering arrangement, then it
> would be faster to first drop all non-clustered indexes and then recreate
> them after the update.

The clustered index is not on the columns being updated. I'm only
updating column 'code', clustered index is defined on columns 'id1'and
'id2'.

The problem is definately related to number of rows being updated. If
I SET ROWCOUNT in the stored procedure to 100000, the query executes
in about 1 minute. Where ROWCOUNT=400000, it executes in just less
than 3 minutes. If I remove ROWCOUNT limitation, I killed the query
after 30 minutes of execution with no completion.

I can rewrite the procedure to run in batches since I can see which
rows have already been updated. I just don't want to do this since I
have about 20 queries to convert.

Relevant Pages

  • Re: Speed question
    ... > Your suggestion to change the clustered index to the packet_time works ... I execute the query from VB6 using ADO. ... When I run the stored procedure, it takes about 1 minutes to ... >>> getdate()) ...
    (microsoft.public.sqlserver.programming)
  • Re: Processing queries simultaneously
    ... Thanks for the suggestion on the stored procedure. ... The clustered index is in Packet_contract, ... If I force the query to use one processor, will it slow down this query? ... > In order for SQL Server to use more than 1 processor to process a request ...
    (microsoft.public.sqlserver.programming)
  • VB-> ADO -> ORACLE
    ... I have a oracle procedure which executes a query to access external ... have permissions to run the external query in the stored procedure) i am ... when run from sqlplus both user1 and owner can execute the stored procedure ...
    (microsoft.public.data.ado)
  • Re: Datatable Update
    ... using the value of a column in the row to query the db, ... and Rows to each respective valeu of the datareader or the output ... > the customer_id from the datatable and then executes a stored ... > stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: IS NULL on field is not using index placed on that field
    ... > criteria in your WHERE clause the query engine needs to look through 2 ... > NULL" you get a nice quick plan using your nonclustered index too. ... > efficiently by just scanning the whole clustered index until it's got ... > even took out the null values and made the DeliverDate column NOT NULL ...
    (microsoft.public.sqlserver.programming)