Solved

Display real elapsed time taking to execute stored procedure call in C#

Posted on 2016-10-10
13
41 Views
Last Modified: 2016-11-02
Hello, I want to execute bunch of stored procedures in a loop in a windows C# application. Each SP executes for over a minute. User will be interested in how long each process takes and I wanted to display real elapsed time while executing SP. I tried below code and it doesn't show real elapsed time instead final time took to execute. Please help.

private void Process()
{
          Dictionary<string, int> spData = new Dictionary<string, int>;
          foreach(KeyValuePair<string, string>k in formData)
         {
               Stopwatch stopWatch = new Stopwatch();
               stopWatch.Start();

               ExeccuteSP(k.Key);  //Key has SP Name and gets executed in ExecuteSP function

               stopWatch.Stop();
               long duration = stopWatch.ElapsedMilliseconds;

               lblTime = duration;
         }

}
0
Comment
Question by:vidhyashan
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41837764
perhaps you can try StartNew instead?

I used this in my project and it works fine:

Stopwatch watch = Stopwatch.StartNew();

//run your stuff here

 watch.Stop();

Open in new window

0
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 350 total points (awarded by participants)
ID: 41837910
The line
lblTime = duration;
overwrites the previous value.  Did you want something like:
lblTime = lblTime + ", " + duration;
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41837951
I guess you'll need a label for each SP. Something like:
lblTime1 = duration;
lblTime2= duration;
lblTimeN = duration;
0
 
LVL 1

Author Comment

by:vidhyashan
ID: 41838740
None works. Please find the sample program I wrote below. When Ok button is pushed the label don't display any value until the for loop completes (please find the 2 screen shots attached). I want the label to display the time while executing the for loop too. Timer_Tick event don't trigger until for loop completes. This is the same scenario when executing a SP from my original post:


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ElapsedTime
{
    public partial class Form1 : Form
    {
        private Stopwatch stopWatch;
        public Form1()
        {
            InitializeComponent();
        }

        private void mytimer_Tick(object sender, EventArgs e)
        {
            mylabel.Text = string.Format("{0}:{1}:{2}", stopWatch.Elapsed.Hours.ToString("00"), stopWatch.Elapsed.Minutes.ToString("00"), stopWatch.Elapsed.Seconds.ToString("00"));
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            stopWatch.Stop();
            mytimer.Enabled = false;
        }

        private void btnOk_Click(object sender, EventArgs e)
        {
            stopWatch = new Stopwatch();
            mytimer.Enabled = true;
            stopWatch.Start();

            for (int i = 1; i < 10000; i++)
            {
                Console.WriteLine(i);
            }

        }

    }
}
Screen.PNG
Screen1.PNG
0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 350 total points (awarded by participants)
ID: 41838778
Application.DoEvents will allow the UI to update, put it where you want in your loop.

ps.  Keep my earlier comment in mind if you want to display all the times taken.
0
 
LVL 1

Author Comment

by:vidhyashan
ID: 41838936
Andy, it doesn't work. I tried it and see same results
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 49

Assisted Solution

by:Ryan Chong
Ryan Chong earned 150 total points (awarded by participants)
ID: 41839376
@vidhyashan,

Not too sure why you need a Timer control and then you still need a StopWatch there. And not too sure why you doing a looping in your button's clicked event there...

But what Andy mentioned should be something similar to below, which should work:

private void btnOk_Click(object sender, EventArgs e)
        {
            stopWatch = new Stopwatch();
            mytimer.Enabled = true;
            stopWatch.Start();

            for (int i = 1; i < 10000; i++)
            {
                Console.WriteLine(i);
                Application.DoEvents();
            }

        }
0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 350 total points (awarded by participants)
ID: 41840824
You need to have something like

SP1
displayElapsedTime
Application.DoEvents
SP2
displayElapsedTime
Application.DoEvents
SP3
displayElapsedTime
Application.DoEvents
....


It will work.
0
 
LVL 1

Author Comment

by:vidhyashan
ID: 41842334
Ryan and Andy, thanks for your response. Lets focus on original item below. When Sp is called cursor wont come back until finished. I already have Application.DoEvents() like below. If this is the case how can it elapsed time gets displayed ? Please advice.


private void Process()
{
          Application.DoEvents() ;
          Dictionary<string, int> spData = new Dictionary<string, int>;
          foreach(KeyValuePair<string, string>k in formData)
         {
               Stopwatch stopWatch = new Stopwatch();
               stopWatch.Start();

               Application.DoEvents() ;
               ExeccuteSP(k.Key);  //Key has SP Name and gets executed in ExecuteSP function
               Application.DoEvents() ;
               stopWatch.Stop();
               long duration = stopWatch.ElapsedMilliseconds;

               lblTime.text = duration;
         }

}
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41842446
A partial repeat of myself:
You need to have something like

SP1
displayElapsedTime
Application.DoEvents


so modifying your example to

private void Process()
{
//          Application.DoEvents() ;
          Dictionary<string, int> spData = new Dictionary<string, int>;
          foreach(KeyValuePair<string, string>k in formData)
         {
               Stopwatch stopWatch = new Stopwatch();
               stopWatch.Start();

//               Application.DoEvents() ;
               ExeccuteSP(k.Key);  //Key has SP Name and gets executed in ExecuteSP function
//               Application.DoEvents() ;
               stopWatch.Stop();
               long duration = stopWatch.ElapsedMilliseconds;

               lblTime.text = duration;
              Application.DoEvents() ;
         }

}

should be what you desire.

Application.DoEvents:
https://msdn.microsoft.com/en-us/library/system.windows.forms.application.doevents(v=vs.110).aspx
Basically it lets the GUI handle waiting events by pausing the current code and then continues with the code after anything waiting is processed.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41842466
ps.  Don't forget what I pointed out in my first comment (plus a potential solution) that
lblTime.text = duration;
will overwrite the previous value the next time through the loop.
0
 
LVL 1

Author Comment

by:vidhyashan
ID: 41843689
Andy, I understand that lblTime.Text = duration will overwrite. Here its to check how long its taking to execute SP. But so far no solutions worked. I will have to find alternate solution. I expected some experts to alter the way I have done so far meaning different solution to achieve result.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41869843
I don't know why it is reported as not working because it does work and do what was asked for
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now