Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Musician
Starting Member
6 Posts |
 Posted - 2002-02-11 : 07:58:12
|
| I'm a visual basic developer, in particular with databases. I've read the logic that dictates you use Stored Procedures etc. but I'm wondering if Sql Server can report it's progress to the client application. It's good policy to give a user a progress bar for example on a form to indicate the progress of a procedure. You might increment the progress bar every time you loop through a recordset for example. Now say you moved the work you are doing on the database into a stored procedure. How do you then increment your progress bar? Seems unlikely that you can as you'll call the procedure in a one-liner and that will be that. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-11 : 08:23:53
|
| That's true. However, in dealing with stored procedures you are (or definitely SHOULD BE) using set based operations, not cursors or loops. This effectively treats a 10,000 row UPDATE as a single operation. The need for a progress bar in this instance would be nice, but unnecessary. A great deal of the delay in database operations occurs because of row-by-row processing and the looping structures used, not actual database performance.(in other words, if your code looks like Do While not rs.EOF ... rs.MoveNext ... Loop, it's slow)If such an operation takes longer than an end user should reasonably expect, it would be better to focus attention on improving it than providing feedback to the user on how (slow) long it's taking.Do you have any code for the operations you're performing now? Can you post it? It's a pretty safe bet that moving to stored procedures and set-based operations will provide such a performance increase that a progress bar would simply blink on and off! |
 |
|
|
Musician
Starting Member
6 Posts |
Posted - 2002-02-11 : 11:11:46
|
| Yes I take your point. The system I'm doing at the moment was originally on an Ingres database. I was given the task of converting it to Access with a VB frontend. To begin with becuase of time constraints I did alot of straight code conversion but now I'm in a position to redesign it for Sql Server and improve performance. It's a metered water billing system. One of the big operations is creating the demands table. This involves going throught the meter readings table, calculating the charge, rent and raising them on the transaction table to reflect the raised charge and also updating the accounts table to reflect the new current balance etc. It takes about 30-45 mminutes in vb code and I use for x = 1 to recordset.recordcount, not loops, which is supposed to be marginally better performance. If this was all run on SQL Server I'm sure it could be done much faster but I'm not 100% certain it'll be done in an instant. However I do think that perhaps if I thought out the process it could possibly be done more efficiently. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-11 : 11:52:03
|
| I have a sneaking suspicion that if you only need to update a table or two (meaning there are no other external, non-database parts of the transaction), that this can be done in 1 or 2 UPDATE statements.Can you post your table structures and a short description of what needs to be updated, formulas, etc.? Also, about how many rows are affected by this? |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-02-11 : 15:55:38
|
| Just to add my two cents as a VB programmer- I do similar things to what you've mentioned, and I use GUI elements to trick the user into thinking s/t is really progressing the way they see it. It is very easy to fake a progressbar by progressing in pieces and then filling up as your transaction ends, or you could simply use a little animation by moving images around (like the magnifying glass thingy,used for searching) to keep users' eyes busy while you do your logic in the background. The benefits of doing your heavy processing on the backend are well worth it.Good Luck!Sarah Berger MCSD |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-02-11 : 16:52:39
|
| As long as you can keep up the illusion.Copying file... time... 18 minutes... 15 minutes... 10 minutes... BUY WINDOWS XP... 14 minutes... 6 minutes... 20 seconds... almost there... 300 hours... no, I mean finished last week... 18 minutes... time is an illusion... 10458235 hours... just joking... |
|
|
|
|
|
|
|
|
Reporting Progress to Client?