Re: 100% CPU Util on Simple Update Query

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 17:07:23 +0100

On Tue, 15 Feb 2005 06:55:08 -0800, Joel H wrote:

>ITS BAACK. Yesterday, I had the same problem.
>The query in full looks like this:
(snip)

Hi Joel,

If I read your query correctly, you are updating each column with the
value from a different column if switch is -1 for that row, and setting
each column to the value of that column itself if switch is unequal to -1.
That means you are wasting a lot of time, updating rows with the original
values. It's better to exclude the rows with a value for switch other than
-1 from the update process.

UPDATE IMPT_ZACKS_01
SET DATEOFFISCALQUARTER1END = DATEOFFISCALQUARTER0END,
       Q1MEDIAN = Q0MEDIANCURRENT,
       Q1NBROFESTIMATES = Q0NBROFESTIMATES,
      (.....)
WHERE SWITCH = -1

Depending on the amount of rows with switch unequal to -1, this will speed
up execution either a bit or a whole lot.

Another thing: judging by the column names, your table is not properly
normalized. Consider changing your design to follow at least third normal
form. If the task you're trying to solve is what I think it is, you'll
probably find it much easier to do in a properly normalized database.

>Each row only works on that row.

Yes, but your update statement works on the whole table. That means that
all rows need to be locked until the transaction finishes.

>I took the table, and tried to remove the key, then replace the key, then
>save - hoping to rebuild the key.

If you don't have a WHERE clause, each row needs to be processed. That
means that SQL Server will never use an index; it will always perform a
table scan (if the table has a clustered index, this is called a clustered
index scan - but the name is the only thing that changes).

>I took the table, and tried to remove the key, then replace the key, then
>save - hoping to rebuild the key. I got the following error message:
>'IMPT_ZACKS_01' table
>- Unable to create index 'PK_IMPT_ZACKS_01'.
>ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
>[Microsoft][ODBC SQL Server Driver][SQL Server]Location: statisti.cpp:3222
>Expression: cbStmtTxtLen/sizeof(WCHAR) <= cwchStmtTxtLen
>SPID: 53
>Process ID: 5576
>Maybe thats a clue?

I have no idea. What tool were you using when you dropped and recreated
the key? Could you try it from QA (Query Analyzer) - that's the tool that
will give the most "vanilla" version of any error messages.

Also, what output do you get when you run the following command in QA:
        sp_help IMPT_ZACKS_01

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Change an update query
    ... Are you in query design view? ... you should see the table that you are updating and be able to see the fields. ... If you are basing the update query on another query, check the base query to see if it has the other fields in it. ... switch to SQL view, ...
    (microsoft.public.access.queries)
  • Re: Delete query unable to delete records from table. Fix how?
    ... Or are you executing an Append query to add the new records? ... Paste your subquery (I did fix several brackets. ... Switch to design view. ... Switch to datasheet view. ...
    (microsoft.public.access.queries)
  • RE: Loops
    ... table with multiple columns 5 for each pay period, ... I run my query for the pay period ending on 01/05/07 ... you are updating multiple tables. ...
    (microsoft.public.access.modulesdaovba)
  • RE: How to update this...
    ... I'm excited that I was close thinking it might be a switch thing. ... > Hi, Bonnie. ... your reporting hours grid doesn't accommodate the numbers between 500 ... > wrong with the query. ...
    (microsoft.public.access.queries)
  • Re: Display data from ODBC table
    ... Have you tested updating the data in the DatasheetView of the Query? ... If you can in the above, you might have set the Recordset Type of the Form ... If you can't update in the DatasheetView then the problem is in the Query. ... Check your permissions on the SQL Server. ...
    (microsoft.public.access.forms)
Loading