Query Optimization

[sujitkar]

Hi,

I have just started to develop one website for my company. The database is in Sql Server 2005. There I have two tables-

Manual_Data Table
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.

Auto_Data Table
1. Mat_ID
2. Date
3. Qty (Quantity Sold)
4. Qty_sold_till_date

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.

[mo3s]

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 :-)