I have just started to develop one website for my company. The database is in Sql Server 2005. There I have two tables-
1. Mat_ID (Containing the ID of the Materials)
2. Date (Containing the Date of Stock Taken, Generally once in a week)
3. Quantity (Quantity of the material)
This table gives us the physical_qty_sold per week, if we deduct the previous week data from the latest week data. Actually it gives the sold qty within any period of time.
3. Qty (Quantity Sold)
Whenever we sell something, the record is being updated in the Auto_Data Table. Thus we get the total qty_sold_till_date. And also, we can calculate the qty_sold within a period of time.
Now I have been asked to put the Material Audit option in my website. It should generate the difference between two sold quantities, i.e. the physical sold qty and the sold_qty as per auto_data table.
To solve this, I had to take 4 Recordsets in my application - one for Quantity (from manual_Data) on the start date, one for Quantity (from manual_Data) on the End date, one for Quantity_sold_till_date (from auto_Data) on the start date, one for Quantity_sold_till_date (from auto_Data) on the End date.
But when I am trying to open my website in the intranet only (including localhost), it is taking around 4-5 mins to load. Looking forward for your help.
I might be mistaken since your question is a bit vague to me (might be my english) but this is what i think might help you a bit.
1) Since you have Mat_ID in both tables you can do a INNER JOIN on the tables and thus you don't need 4 recordsets (only one).
2) Make an asp page that pre-calculates and stores your values and schedule a job to run this page.
3) Make sure that you set indexes on your tables (this is basic but i still see so many databases with no indexes on it)
4) Use triggers to update your values. So when a value changes the database updates your database and not your asp script.
I might be totally off here but hey....I try :-)