Background Threading in ASP.NET and Impact on Performance

Question

My current site is set up to leave messages for certain people the first time anyone logs into the app. The number of messages left could be anywhere from 1000 to 10000, with each being a query, and all queries in a transaction statement.

Yes, I know this seems like poor design, that is not my question.

Currently, when a user logs in, the server has to complete all of the queries before the user can actually do anything. What I mean is, the user views the login screen until the timeout kicks in and resets the page. Recently we've had waits up to 30 minutes (!)

I want to put this process on a thread such that the user can log into the website, do any activity s/he needs to, and view/delete their messages while not being impeded by the message deliverer.

My question is, does simply spinning off a new thread to handle this process solve the problem (i.e. allow the user to work while the deliverer leaves messages)? Also, does spinning off the thread impact the other web applications on the same server?

EDIT Based on KM's comments

Simplified schemas:

Tasks table:

TaskID        StartDate         EndDate       PercentComplete
TSK1          4/17/09           5/17/09       60
TSK2          3/19/09           3/29/09       80
TSK3          1/1/08            2/9/09        100

Assignments table:

TaskID        ResourceID
TSK1          111
TSK1          222
TSK2          222
TSK2          333
TSK2          444
TSK3          111
TSK3          333

Messages table schema:

Subject       Receiver       Sender        Content
Overdue Task  111            000           Task TSK1 is overdue.
Overdue Task  222            000           Task TSK1 is overdue.
Overdue Task  222            000           Task TSK2 is overdue.
Overdue Task  333            000           Task TSK2 is overdue.
Overdue Task  444            000           Task TSK2 is overdue.

So, the code will get all people assigned to an overdue task (i.e. EndDate earlier than today and != 100% complete) and leave a message for them telling them such

I think the real problem is that i am using a select query to get the people assigned to overdue tasks from the database and then using C# code to compose the messages and transactions before calling the database to insert the messages.

Answer 1

replace your loop with this:

BEGIN TRANSACTION

INSERT INTO Messages 
        (Subject, Receiver, Sender, Content)
    SELECT
        'Overdue Task', t.TaskID, '000', 'Task '+CONVERT(varchar,t.TaskID)+' is overdue.'
        FROM Tasks                    t
            INNER JOIN Assignments    a ON t.TaskID=a.TaskID
            LEFT OUTER JOIN Messages  m ON t.TaskID=m.Receiver
        WHERE t.EndDate<GETDATE() AND PercentComplete<100
            AND m.Receiver IS NULL

DELETE Messages
    FROM Messages
        INNER JOIN Tasks  ON Messages.Receiver=Tasks.TaskID AND PercentComplete=100

COMMIT

this will only insert for over due tasks that are not done and do not have already have a message. It will remove messages from completed tasks. you have to add error handling of your choice.

EDIT
since you can't join a message to a task, you can can't do a delete like I have above. If you don't have time on the EndDate column you can run this as a SQL Server Job right after midnight, as part of that job truncate the table first and then insert what is needed. I'd make sure that you delete messages when a task is marked 100 complete

Answer 2

You can delegate the work to a background thread. Create a function to do the work, and start it like:

new Thread(
    () => SendALotOfMessagesByBadDesign(par1)
    ).Start();

Restarting IIS or recycling the application pool would interrupt the background thread, but that happens in your current solution too.

Answer 3

Also, does spinning off the thread impact the other web applications on the same server?

Assuming that the web server has multiple CPUs, I suppose that you could assign this web app to its own application pool and set the affinity to a particular processor to limit the impact on the other web applications. http://www.iis.net/ConfigReference/system.applicationHost/applicationPools/add/cpu

I've never done this, I'm just throwing it out there as "within the realm of possibilities". I don't know if it's advisable or not.