Solved

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

Posted on 2016-10-10
13
71 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:Shanmugam Rajagopal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 52

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 50

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 1

Author Comment

by:Shanmugam Rajagopal
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:Shanmugam Rajagopal
ID: 41838936
Andy, it doesn't work. I tried it and see same results
0
 
LVL 52

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:Shanmugam Rajagopal
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:Shanmugam Rajagopal
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

690 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