Update in - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update in

Author

Topic

slickster79
Starting Member

3 Posts

Posted - 2006-10-06 : 11:17:10

Hi, I hope someone can help me with this please

If i run the following select it runs about 1 second

select * from apps where AppID in (SELECT TheAppID FROM @AppStats WHERE IdentityAppID >= @lowerLimit AND IdentityAppID <= @upperLimit)

however if i use this where clause to run an update it takes well over 2 minutes as it seems to scan the entire table for each row in the sub-query. Is there a better way of making this update?

UPDATE Apps SET CommissionEarned = @Commission WHERE AppID in (SELECT TheAppID FROM @AppStats WHERE IdentityAppID >= @lowerLimit AND IdentityAppID <= @upperLimit)

@AppStats consists of around 700 rows
Apps consists of 900,000 rows and growing

Thanks for any help.

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 11:21:04

Are you creating a PRIMARY KEY when you create your @AppStats table? (its going to need to be on IdentityAppID to be any good for this query though!)

Is a JOIN (even if that is perhaps to a SubSelect) any faster? (I expect the query plan will be the same, but it would be worth checking)

If all that lot fails you could make another @TableVar with just the rows that you need, and then use that in your UPDATE statement.

I'm a bit surprised that the SELECT and UPDATE have any different in query plan to be honest. The UPDATE will of course be slower than the SELECT - 'coz there is more work to be done - so I suppose I should check that you have checked that the query plan is different have you?

Kristen

slickster79
Starting Member

3 Posts

Posted - 2006-10-06 : 11:32:19

yes IdentityAppID is the primary key on @AppStats. I'm shocked by the difference between the select and the update. In the execution plan 71% is used on the index scan.

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 12:19:30

I never get very excited about the percentages - the thing has to add up to 100% somehow! ... however I do get worked up about the SCANs and LOGICAL I/O. Here's what I use to see what's-what (ignore the Physical stats, the Scans and Logical I/O need to be as low as you can get them):


-- Comment in the SHOWPLAN for Query Plan, OR the STATISTICS for logical stats
-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

-- ... put query here - e.g.:

SELECT * FROM Northwind.dbo.Products

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SET SHOWPLAN_TEXT OFF
GO

If you put a:
BEGIN TRANSACTION
before your UPDATE statement and a
ROLLBACK
after it you could try a few scenarios without mucking up the data at all.


-- 1 --
UPDATE U 
SET CommissionEarned = @Commission 
FROM Apps AS U
WHERE AppID in 
      (
           SELECT TheAppID
           FROM @AppStats
           WHERE     IdentityAppID >= @lowerLimit 
                 AND IdentityAppID <= @upperLimit
      )



-- 2 --
UPDATE U 
SET CommissionEarned = @Commission 
FROM Apps AS U
     JOIN @AppStats
          on TheAppID = AppID
         AND IdentityAppID >= @lowerLimit
         AND IdentityAppID <= @upperLimit


-- 3 --
DECLARE @TempAppStat TABLE
(
    TheAppID int NOT NULL,
    PRIMARY KEY
    (
        TheAppID
    )
)
--
INSERT INTO @TempAppStat(TheAppID)
SELECT TheAppID
FROM @AppStats
WHERE     IdentityAppID >= @lowerLimit 
      AND IdentityAppID <= @upperLimit
--
UPDATE U 
SET CommissionEarned = @Commission 
FROM Apps AS U
     JOIN @TempAppStats
          on TheAppID = AppID

Sorry if you know all this already.

Kristen

slickster79
Starting Member

3 Posts

Posted - 2006-10-09 : 04:23:30

Thanks for the replies. I've used a join which removes the need for multiple table scans. Runs in 2 seconds now.

UPDATE Apps SET CommissionEarned = @Commission from Apps a inner join @AppStats aps on a.AppID = aps.TheAppID WHERE aps.IdentityAppID BETWEEN @lowerLimit AND @upperLimit

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 04:25:33

aka -- 2 --

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources