Our client has a MS SQL database that has 1 million customer invoice records in it. Using the database, our client wants its customers to be able to log into a frontend web site and then be able to view, modify and download their company’s invoices. Given the size of the database and the large number of customers who may log into the web site at any time, we are concerned about data base engine performance and web page invoice rendering performance.

The 1 million invoice database is for just 90 days sales, so we will remove invoices over 90 days old from the database. Most of the invoices have multiple line items. We can easily convert our invoices into various data formats so for example it is easy for us to convert to and from SQL to XML with related schema and XSLT. Any data conversion would be done on another server so as not to burden the web interface server.

We have tentatively decided to run the web site on a .NET Framework IIS web server using MS SQL on MS Azure.

  1. How would you suggest we structure our database for best performance? For example, should we put all the invoices of all customers located within the same 5 digit or 6 digit zip codes into the same table? Or could we set up a separate home directory for each customer on IIS and place each customer’s invoices in each customer’s home directory in XML format?

  2. And secondly what would you suggest would be the best method to render customer invoices on a web page and allow customers to modify for best performance? The ADO.net XML Data Set looks intriguing to us as a method, but we have never used it.

  • One million records is not a lot for a properly indexed SQL database, assuming you're not using exotic searches. What will your search term be? The invoice number? Customer last name? – Robert Harvey May 26 '14 at 22:31
  • As to your second question, you would use the same methods you would use for any other invoicing system. Your particular application is just not that unusual. Consider that the invoice table will take up 300 megabytes in your database (give or take an index), if the invoice record is 300 bytes in size. I would imagine you have single video files on your laptop that are larger than that. – Robert Harvey May 26 '14 at 22:37
  • Our search term will first be by customer number so that we can list each customers' various open invoices on a web page "open invoice summary." Then when a customer clicks on one of the listed invoices on summary page, we will render the selected invoice so that customer can modify, save, and download. – user132624 May 27 '14 at 1:17
  • Sounds good to me. Just make sure you have the proper indexes in place. – Robert Harvey May 27 '14 at 4:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.