Without DDL it is difficult to tell. Maybe something like:

UPDATE S

SET qty_in_stock = qty_in_stock - D.orders

FROM sku S

    JOIN (

            SELECT S1.sku, S1.labsupplied ,COUNT(*) AS orders

            FROM orders O1

                JOIN sku S1

                    ON O1.framestylecode =...

This sounds like parameter sniffing. The first time the stored procedure is compiled it uses the parameters which were passed to it to generate the most efficient query plan for those...

I was thinking of a left semi join operator which should require less reads than a join; especially if there are a lot of FKs in TableB for each PK in...

My experience in SQL2000 is that joins in subqueries always produce nested loops. I therefore suspect that Sergiy's LEFT JOIN solution, or something like the following, will be more efficient.

SELECT...

Try replacing * with %.

In the absence of more information, here is an outline of something that may work.

If it does work, getting rid of the functions should speed it up.

CREATE TABLE #Value

(

    Value int...

Is Value, Perf_YTD unique?

What is the PRIMARY KEY of the table?

The following non-ANSI t-sql should do it:

DECLARE @a char(6)

    ,@b varchar(6)

SET @a = '123456'

SET @b-2 = ''

SELECT @b-2 = @b-2 + D.Number

FROM (

        SELECT CAST (

                    CASE D1.Number

                    WHEN 9 THEN 0

                    ELSE D1.Number...

My example was poor.

The potential problem is when the derived table references the table to be updated. If this is the case then an UPDLOCK needs to be applied in...

Using NOLOCK on an UPDATE statement does not make sense as an XLOCK will be obtained on the table to be updated. To avoid deadlocks UPLOCKs should be applied to all...

What does the full UPDATE statement look like?

Does it contain a derived table?

Your Enterprise Manager Designer fiddle obviously does not cope with derived tables. If you want to do this you will have to do the derived tables separately.

I am not convinced...

Scott's approach looks as thow it could be more efficient.

Assuming program_name, Order_number, Revenue_milestone is unique for each table, maybe something like:

SELECT D.program_name, D.Order_number, D.Revenue_milestone

    ,COALESCE(T1.[Labor Hours], 0) AS [Labor Hours]

    ,COALESCE(T1.[Labor Amount], 0) AS [Labor Amount]

    ,COALESCE(T2.Materials, 0) AS Materials

FROM (

        SELECT T11.program_name,...

It is a matter of the order of evaluation and ease of understanding.

Table1 LEFT JOIN Table2 can easily be re-written as Table2 RIGHT JOIN Table1 but when more than two...