Solved

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

Posted on 2016-10-10
13
65 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
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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 62
SQL Query 9 26
VM SQL server license. 1 62
How do you think this website does searches? 5 34
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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

737 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