2

I am working on a C# .net application

I am executing a stored procedure from my c# code and it takes almost 2 minutes to complete its execution. I am using SQL Server 2008 R2 on back end. So I want to show a progress bar which shows the progress of execution of stored procedure . For this I need to calculate the execution time the execution of stored procedure. So is there any way from which I can calculate the time of execution + this application runs on number of my clients so the execution time of stored procedure varies from client to client. So please give me suggestion or a way on which I search how to do this?

Thanks For your valuable feedback in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amit Bisht
  • 4,870
  • 14
  • 54
  • 83

2 Answers2

3

Good problem. Here are some options you might consider.

A. Here's a good explanation of the query plan. How do I obtain a Query Execution Plan?

B. Add feedback (ie report back to a table) within your stored procedure. I assume there is a group of many tasks. Try to report back between each task in the stored procedure to give you a more granular measure. You may even break this up into different stored procedures if it's neater without the feedback to a table.

C. Once the procedure has been run once you may have your best estimated time.

D. Optimize your stored procedure. I can imagine it'll be better off for all if this runs quickly. It means your estimate will be off by less! Things to consider:

Hope there's some points that help.

Community
  • 1
  • 1
Bnyboy
  • 96
  • 1
  • 3
2

From the info you gave in, I can only guess, but still, I only see two approaches:

  • If the the execution length of the procedure depends on parameters passed to it (wild guess: length of byte array), you can measure average execution length for certain levels of parameters, which can be hard-coded (or better you can parse it from file at startup) in your application and then used for expected-runtime estimation, => and with time passing, user sees, how long it should take to finish.
  • If it is not based on passed parameters, but on database-server-load, you could either call the server for its cpu/harddrives load levels or even estimate these based on amount of procedure calls in recent time => then again, this can lead to your execution time estimation.

I can not figure out anything more specific, unless you for example share the procedure call and the sql it generates, with ideally specifics of the tables and scales.

jmodrak
  • 229
  • 4
  • 17