What part of this short SQL script run on a production is not optimal?

[mr0277]

I am running a script on our production database reffering two tables : our table of users (3700 of them) and the table of quotes that they have made (280000 of them). Quote is the main object in our application, a very large object, for whom many data tables are created and filled. My goal is to clean database from all quotes but those made of a small group of users.

I first create a temp table containing ids of those users (it is used else in the script also) and then a cursor that runs through the main table for the quotes, where they are listed, and for those quotes created from the user group does the necessary cleansing.

I see that this script is going to be executed for 26 hours approximately, which I consider peculiar since I need about 15 minutes for the database restoring in general, and I guess the heaviest sql is executed there. The db, though, weighs more than 100GB.

Is there some part of the script that I am making terribly non-optimal, or you have some suggestion how this could be done with much shorter execution.

We are running SQL Server 2008 R2.

Here's the sketch of the script:

`CREATE table #UsersIdsToStay(user_id int)

INSERT INTO #UsersIdsToStay

select user_id

from users

where user_name like '%SOMESTRING '

-----

declare @QuoteId int

declare @UserId int

declare QuoteCursor cursor for

select DISTINCT QuoteId, UserId

from QuotesTable

where UserId not in

(
    select * from #UsersIdsToStay
)

open QuoteCursor

while 1=1

begin

fetch QuoteCursor into @QuoteId, @UserId

if @@fetch_status != 0 break


-- all the deletions from related tables are executed here using @QuoteId and @UserId

end

close QuoteCursor;

deallocate QuoteCursor`

[AleMonteiro]

Why do you have to use a cursor? I suppose the quotes table have the user id, right? So you can make just one delete, like this:

DELETE FROM Table_Quotes
WHERE
    user_id NOT IN ( 
        SELECT user_id FROM users
        WHERE user_name LIKE '%SOMESTRING '
    )

You don't even need the temporary table.

If this does not work on your table schema, please post the diagram of the related tables so we can understand it better.

[mr0277]

Hi Ale.
I am using a temp table only since I am going to add some code to the script later deleting other things not related to quotes but coming from those users. Not to repeat the code only.
All the deletion lines that I ommitted inside of the cursor body are exactly like this
exec('delete from tablename where QuoteId = ' + (at)QuoteId + 'and UserId = ' + (at)UserId )
and the table name takes 15 different names

[AleMonteiro]

Oh, that's not very nice... If you use exec with a string the database engine will not be able to precompile your stored procedure and optmize.

Try changing to DELETE FROM TableName WHERE QuoteID = @QuoteID AND UserID = @UserID. The performance will be significantly better.

Another thing, if you are using @QuoteID it seems like you are using another cursor inside the one that you posted, is that right?

Nested cursors are terrible for performance.

[mr0277]

Thanks for advising, I will try that definitely.
No, there's no a nested cursor here, I am filling both @QuoteID and @UserID from the same cursor (line above)
But I think you lead me to a push forward here that I wasn't aware of before.

[AleMonteiro]

Oh, if I understood correcly now, there's the Quotes table wich references the Users and the Quotes. Then there's a bunch of other tables with related data from the Quotes.

If so, you can build a single statement if the relationships have Cascade Delete ON:

DELETE FROM Quotes
WHERE
    UserID IN (
        SELECT UserID FROM Users WHERE Something = @Something
    )
    AND QuoteID NOT IN ( Some Other Select )
    AND QuoteText LIKE @Something
    AND ....

If your relationships doesn't have CASCADE DELETE you can create an SELECT statement similar to the delete above, and add thoses quotes in a temporary table.
Like this:

CREATE TABLE #tmpQuotes (QuoteID int)

INSERT INTO #tmpQuotes
SELECT QuoteID FROM Quotes
    WHERE
        UserID IN (
            SELECT UserID FROM Users WHERE Something = @Something
        )
        AND QuoteID NOT IN ( Some Other Select )
        AND QuoteText LIKE @Something
        AND ....


DELETE FROM QuotesTable1 WHERE QuoteID IN (SELECT QuoteID FROM #tmpQuotes)
DELETE FROM QuotesTable2 WHERE QuoteID IN (SELECT QuoteID FROM #tmpQuotes)
DELETE FROM QuotesTable3 WHERE QuoteID IN (SELECT QuoteID FROM #tmpQuotes)
...

I'd say maybe there's even a better way to do it, looking at performance. But this will certanly be faster than what you're currently doing.

Good luck.

[lee.m.timms]

I think this might do what you want, it should delete all quotes that are not applied to a registered user. This is what you want to do as I see it.

DELETE FROM QuotesTable
INNER JOIN(SELECT DISTINCT QuoteId, UserId FROM QuotesTable
WHERE UserId NOT IN (SELECT DISTINCT user_Id 
                        FROM Users WHERE user_name LIKE '%SOMESTRING')) as  res 
ON QuoteId = res.QuoteId