Performance Issues Performing a List of Stored Procedures

Question

I am having performance issues when starting a Windows service, the first round my lstSps is long (about 130 stored procedures). Is there a way to speed this up (with the exception of speeding up stored procedures)?

When the foreach is finished and goes into the second round, it goes faster, because there will not be much return on TimeToRun (). But, my concern is the first time there are many more stored procedures to run.

I have a question about creating an array and a for loop, since I read that it works faster, but I believe the problem is that the procedures take a lot of time. Can I build it better? Maybe use multiple threads (one for each execution) or something like that?

I would like some tips :)

EDIT: just to clarify, this HasResult () method executes SP: s and does a time search.

lock (lstSpsToSend)
{
    lock (lstSps)
    {
        foreach (var sp in lstSps.Where(sp => sp .TimeToRun()).Where(sp => sp.HasResult()))
        {
            lstSpsToSend.Add(sp);
        }
    }
}

while (lstSpsToSend.Count > 0)
{
    //Take the first watchdog in list and then remove it
    Sp sp;
    lock (lstSpsToSend)
    {
        sp = lstSpsToSend[0];
        lstSpsToSend.RemoveAt(0);
    }

    try
    {
        //Send the results
    }
    catch (Exception e)
    {
        Thread.Sleep(30000);
    }
}

Answer 1

What I would do is something like this:

int openThread = 0;
ConcurrentQueue<Type> queue = new ConcurrentQueue<Type>();
foreach (var sp in lstSps)
{
    Thread worker = new Thread(() =>
        {
            Interlocked.Increment(ref openThread);
            if(sp.TimeToRun() && sp.HasResult)
            {
                queue.add(sp);
            }
            Interlocked.Decrement(ref openThread);
        }) {Priority = ThreadPriority.AboveNormal, IsBackground = false};
        worker.Start();
}
// Wait for all thread to be finnished
while(openThread > 0)
{
    Thread.Sleep(500);
}

// And here move sp from queue to lstSpsToSend

while (lstSpsToSend.Count > 0)
{
    //Take the first watchdog in list and then remove it
    Sp sp;
    lock (lstSpsToSend)
    {
        sp = lstSpsToSend[0];
        lstSpsToSend.RemoveAt(0);
    }

    try
    {
        //Send the results
    }
    catch (Exception e)
    {
        Thread.Sleep(30000);
    }
}

Answer 2

The best approach will largely depend on what these stored procedures do if they return the same set of results, or if there is no result in this regard, it would be useful to send them to the SQL server at the same time instead of one at a time.

, SQL- -, WAN, 200 . , 130 , "" 200 X 130. 26 , , proc.

, 200 .

, , , , .

.

, , !