Calculating the invoice Net Charge, Paid and Discount Amount Totals

Sami
Sami used Ask the Experts™
on
I have an invoice and invoice details Payment tables. I need to calculate the total amount of the Net Charge, Paid and Discount for every invoice.

The Net Charge, Paid and Discount columns are not exists in the invoice table instead I'm planning to calculate their amount on the fly from the invoice details and Payment tables.

The calculation will be for each Individual invoice and also for all of invoice table rows based on a giving date period.

Please I need advice and answer regarding the following two points.
Is my above approach okay regarding the performance?
Should I create the Net Charge, Paid and Discount columns inside the invoice table?

Thank you.

Invoice Table
nvoiceId
InvoiceDate
 
Invoice Details Table
InvoiceDetailId
InvoiceId
ServiceId
Price
Discount (%)
 
Payment Table
PaymentId
InvoiceId
Amount
PaymentDate

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The performance depends of:
How many rows can be (100, 1000 or more than 100.000)
How many concurrent transactions (requests)
Etc ..

Typically, basic mathematical calculations doesn't not consume resources.
Unless you have a lot of records and a lot of concurrent requests to the database, you will not have a problem.
Other thing, if the result goes for an application or a website, you may balance the effort through asign the calculation to the application.
Performance would be OK if the number of invoices is low (less than let say 100000 - depends on hardware used, of course). Once you are planning millions of invoices each having 10 lines and several possible (partial) payments then the performance will be degraded and I would recommend to have totals included in the invoice header even when it is more difficult to handle such redundant values.

Another point to discuss would be the occurence of total calculations. If it is calculated once a week and patient users can wait for results then you probably don't need extra columns in invoice headers.
Senior Database Architect
Commented:
It is really how well your tables are constructed.  I created the tables and 1 extra index for the InvoiceDate below and loaded 100 Services, 1000 Customers, 1,000,000 Invoices (over a 4.5 year span), 3,000,000 Payments records on those invoices, and 5,000,000 Invoice Detail records with 2-10 details per invoice.  All this is just on SQL 2008 running on XP on a laptop.  I can return queries like these in sub second timeing.
-- single invoice, near instantanious
SELECT Invoice.InvoiceId, InvoiceDate, CustomerId, COUNT(InvoiceDetailId) DetailRecs, SUM(InvoiceDetails.Price) Price, SUM(InvoiceDetails.Price*(1-COALESCE(InvoiceDetails.Discount,0))) ExtendedPrice
FROM Invoice
INNER JOIN InvoiceDetails ON Invoice.InvoiceId = InvoiceDetails.InvoiceId
INNER JOIN Payment ON Payment.InvoiceId = Invoice.InvoiceId
WHERE Invoice.InvoiceId = 100
GROUP BY Invoice.InvoiceId, InvoiceDate, CustomerId

-- one months data (15,622 rows, reports 00:00:00 execution time in SSMS)
SELECT Invoice.InvoiceId, InvoiceDate, CustomerId, COUNT(InvoiceDetailId) DetailRecs, SUM(InvoiceDetails.Price) Price, SUM(InvoiceDetails.Price*(1-COALESCE(InvoiceDetails.Discount,0))) ExtendedPrice
FROM Invoice
INNER JOIN InvoiceDetails ON Invoice.InvoiceId = InvoiceDetails.InvoiceId
WHERE Invoice.InvoiceDate BETWEEN '1/1/2009' AND '2/1/2009'
GROUP BY Invoice.InvoiceId, InvoiceDate, CustomerId

CREATE TABLE Customer (CustomerId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, NAME VARCHAR(100) NOT NULL);
CREATE TABLE Service (ServiceId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, NAME VARCHAR(100) NOT NULL);
CREATE TABLE Invoice (InvoiceId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, InvoiceDate DATETIME NOT NULL, CustomerId INT  NOT NULL REFERENCES Customer (CustomerId));
CREATE TABLE InvoiceDetails (InvoiceDetailId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, InvoiceId INT NOT NULL REFERENCES Invoice (InvoiceId), ServiceId INT  NOT NULL REFERENCES Service (ServiceId), Price MONEY NOT NULL, Discount DECIMAL(10,2));
CREATE TABLE Payment (PaymentId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, InvoiceId INT NOT NULL REFERENCES Invoice (InvoiceId), Amount MONEY NOT NULL, PaymentDate DATETIME NOT NULL);
GO
CREATE NONCLUSTERED INDEX [idxInvoiceDate] ON [dbo].[Invoice] 
(
	[InvoiceDate] ASC
)
INCLUDE ( [InvoiceId], [CustomerId]) 
WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Chris LuttrellSenior Database Architect
Commented:
opps, forgot to include Payments in those.  The second one would be like this now and still returns in 00:00:01.
SELECT Invoice.InvoiceId, InvoiceDate, CustomerId, COUNT(InvoiceDetailId) DetailRecs, 
		SUM(InvoiceDetails.Price) Price, SUM(InvoiceDetails.Price*(1-COALESCE(InvoiceDetails.Discount,0)/100.00)) ExtendedPrice,
		SUM(Payment.Amount) Payments
FROM Invoice 
INNER JOIN InvoiceDetails ON Invoice.InvoiceId = InvoiceDetails.InvoiceId
INNER JOIN Payment ON Payment.InvoiceId = Invoice.InvoiceId
WHERE Invoice.InvoiceDate BETWEEN '1/1/2009' AND '2/1/2009'
GROUP BY Invoice.InvoiceId, InvoiceDate, CustomerId 

Open in new window

Good work Chris!
SamiDeveloper

Author

Commented:
Hi,

I really appreciated all of the responses it's admirable especially what has been written by CGLuttrell.

I did ask my question because I have an application and database running in production for the last 4 years including the remaining of 2009. I have been assigned the task that will do some improvements to the system and the database regarding new requirements and resolve the preformance issue.

The following figures are some of information I gathered regarding the existing database.
The Data base
1. The total number of the invoices in the invoice table is 96,573.  
2. The total number of the invoice details table is 756,116.
3. No payment table exists in the database.
4. Total number of the invoices for 2006 year is 14,422.
5. Total number of the invoices for 2007 year is 21,274 (almost 50% increase).  
6. Total number of the invoices for 2008 year is 31,656 (almost 30% increase).  
7. Total number of the invoices for 2009 year is 29,401 (June).  
8. Total number of the concurrent users accessing the database via the application is 27 they are working five days a week.  
9. Total Number of services is 784.

The Application
1. ASP.NET application (framework 1.1)
2. Web services
3. SQL Server 2000

The hardware:
1. Dell Intel server  2.8 GHz With 2 GB RAM
2. RIAD 5 Storage (3 HD 180 GB)
3. OS Windows 2003

They are experiencing a decrease of the system performance.

Please I need your advice.


Thank you.
If you have some performance issue then it is not rising from SQL side. Total columns are definitely not necessary.
Chris LuttrellSenior Database Architect
Commented:
Your setup seems pretty standard, but getting on the old side.  Have they considered upgrading hardware and a newer SQL Server version?  There are several perfomance improvements just in the newer SQL engines, and newer faster hardware with a memory boost is always a help.
Just on the database side, has any analysis been done on indexes or if any of the tables have grown and maybe data is fragmented?  Do some searches on SQL 2000 Performance tuning to see what you can do yourself.  Make sure you are looking at 2000 because a lot of the more recent/relavant results will be on the newer versions.  Sorry, but we have long moved all our clients off 2000, so I am not familar with them myself right now.  
SamiDeveloper

Author

Commented:
We did purchase SQL server 2008 standard edition and we are going to replace SQL 2000 But we didn't decide yet about the hardware until we make sure it's necessary to do so.

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