Issue when manipulating tables in Microsoft SQL 2005

jana
jana used Ask the Experts™
on
We have a VB Invoice application where the detail lines are unlimited.  Up to now the max of detail lines are a bit over 700 lines.  

Users can create an invoice, save it and later on another user modifies it.  One specific invoice can be worked by at least 4 user ate different stages.  But no 2 users can work with the same invoice.  However, at least 7 users have each an invoice working with it.

The problem we have is when user are saving the invoice.

We had issues of the process stops or takes too long, but most of the times, it just get uninterrupted and the user looses their entry that can be at least 20 to 50 lines.

So we are looking for EE insight on proper process of saving so the apps stops being interrupted or takes too long.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Wait, we are going to include the process of savings and the errors that is occurring.
Most Valuable Expert 2012
Top Expert 2014

Commented:
What database?
By looking at your question, I am assuming that you have a Header Invoice Table and a Detailed Invoice Table while one row of record is on the Header and unlimited on the Detailed Invoice Table, am I right?

Possibly you are using a button to hit save, and only you would attempt to update the database when this button is hit and that's why if something happens, all data lost.

So, what you have to do is to update database, whenever there's a new "Detailed" transaction going on instead of updating in bulk.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Sorry for the delay we want to give you guys the logic flow of the SELECT, UPDATES, DELETE.... will post it....

As for the tables, yes we have a header and details, but the problem occurs when multiple users updates the detail...

We almost there with info...

Author

Commented:
Here is the process the "save" button does.  Since it's a super long code, we tried to place a description of the logic flow and the areas where the tables are being worked with:

1. Veriffy status:
   - does a for/next thru lines >> exceute SQL statement(s): SELECT
2. Update screen secuence number:
   - does a for/next thru lines >> updates datagrid
3. Update Invoices Tables:
   - does a for/next thru lines >> exceute SQL statement(s): UPDATE/INSERT
4. Delete Invoice Lines process:
   - compares if to delete running for/next thru lines >> exceute SQL statement(s): SELECT
   - If delete, >> does a for/next thru lines >> exceute SQL statement(s): DELETE
5. Update screen secuence number:
   - does a for/next thru lines >> exceute SQL statement(s): UPDATE
6. Add Invoice Lines Process:
   - compare masters with invoice >> does a for/next thru lines >>  exceute SQL statement(s): SELECT
   - if add, >> does a for/next thru lines >> exceute SQL statement(s): INSERT
7. Update screen secuence number:
   - does a for/next thru lines >> exceute SQL statement(s): UPDATE
8. Update sub-lines of each lines >> does a for/next thru lines >> exceute SQL statement(s): SELECT/UPDATE/INSERT
9. Update Invoice on Screen >> does a for/next thru lines >> updates datagrid

Hope this helps to better advice us.
Senior Database Administrator
Commented:
Hi,

I'm no expert on front end apps such as yours, but I suggest the following:

Looping through line at a time is the slow way of doing things. You need to find something like a grid control that can take all lines at once, and then save in one hit, rather than looping through the lines in your app - that just increases the number of SQL round trips.

Does the Header table have a clustered primary key?

Does the Detail table have a clustered primary key?

Does the Detail table have a foreign key constraint to the Header table?

Does the Detail table have an index on this column?

Are the above indexes maintained?

Adding an appropriate indexing strategy and maintaining those will make the performance increase seem like magic.

HTH
  David

Author

Commented:
We are using a grid.  The grid represents the actual invoice details in the screen that the users add, modify or delete the lines they are working on.  The purpose of the grid is because when clicking the save button the routine has to go through each line to see if there are any modifications done in order to update the data within the grid  and also within the database.

Is interesting your comment "...grid control that can take all lines at once, and then save in one hit".  we are using the tool DevExpress for our grids. By what you're saying is there a way we "can take all lines at once, and then save in one hit"?

This is very interesting.  Can you give more info in this type of technique. Thank you

Regarding your other questions, we are using foreign key. I don't think we are using cluster or indexing.

Please advise.
David ToddSenior Database Administrator
Commented:
Hi,

If the grid is populated by a dataset, then my understanding is that those changes to the dataset made via the grid can be inserted/updated to the database with one or two lines or code, and not looping through 700 detail lines and 700+ round trips to the server. As much as anything else, that is what is killing performance imho.

Sure you need to check for changes, but getting SQL to do that at the set level will be a whole lot faster/smarter than doing it line-by-line.

Regards
  David

PS I'm no expert on front-end apps.

Author

Commented:
Hi sorry for the delay.

Can you elaborate more on "...but getting SQL to do that at the set level will be a whole lot faster/smarter than doing it line-by-line."

We really would like to make it faster.
David ToddSenior Database Administrator
Commented:
Hi,

If you have a grid/data adapter pair, that the gird can allow users to edit, and those edits can be preserved with a single insert/update/delete command at the grid or data adapter level, rather than at the individual row level. And more easily wrapped in a transaction.

HTH
  David

Author

Commented:
don't follow.
David ToddSenior Database Administrator
Commented:
Hi,

Take your 700 line invoice.

Edit it.

No you have 700 round trips to the server.

If you can wrap a transaction around everything and do 1 insert, 1 update, 1 delete then there are 3 statements.

You will be using some kind of data-adapter or grid to hold the data/display the data. Look for a method on that to update everything in one statement - rather than you coding it the hard way.

Overall we need more detail to comment meaningfully.

Would need to see some tables with example data.

It could be as simple as needing a better indexing strategey (very easily done) or a redesign of the application (not as easily done).

Regards
  David

Author

Commented:
I think that is what the apps do, process the 3 statements at the "save" button (we'll check tomorrow).

Also we going try setup Table & some data sample.

Finally, you mention "better indexing strategy", can you elaborate a bit (seems this could be an option to go)
David ToddSenior Database Administrator
Commented:
Hi,

If you have a typical order table

orderID
CustomerID
date
etc

Hopefully you have a clustered primary key on OrderID

And a foreign key on CustomerID.

But is CustomerID indexed?

Is the Date indexed.

For the order detail, hopefully there is a foreign key relationship to order - but is the OrderDetail.OrderID column indexed?

Now that you have your indexes, are you maintaining them? The suggestion is to check for index fragmentation and run the appropriate reindex/rebuild weekly.

HTH
  David

Author

Commented:
I don't think we have indexes.  

But how indexes can help when insert/update/delete?  I understand that index are great for searching and reports, but for updating table what is the benefit?
David ToddSenior Database Administrator
Commented:
Hi,

When you update a detail record, then presumably you are linking to and updating the header record. When you look at what is happening for any one insert/update, there are a bunch of reads surrounding that single write.

So, the minimal indexes I suggested should help overall with your application.

I haven't suggested large covering indexes for reporting. Just the indexes to assist with the relational database relating.

Of course because I'm not there I can't be more specific ...

Regards
  David

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial