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)


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


fetch QuoteCursor into @QuoteId, @UserId

if @@fetch_status != 0 break

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


close QuoteCursor;

deallocate QuoteCursor`

Recommended Answers

All 6 Replies

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
    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.

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

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.

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.

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:

    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
        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.

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.

                        FROM Users WHERE user_name LIKE '%SOMESTRING')) as  res 
ON QuoteId = res.QuoteId
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.