net mvc 2 application with an sql server express database.
My application is used to estimate fence installation projects. User draws all fence items in a flash drawing which outputs an xml file.
With this data the user can add items to each element that is in the image they made. Basically they draw whatever type of configuration using Posts, Fence and Gate items.
after the drawing elements are added. Each element has an "item price"
the types of items that can be added to each post fence or gate are:
- Labor
- Material
- Equipment
- Subcontracts
Each has a formula to calculate its price. The final price for each comes from pricing values added in the application budget. This is an example of how each would be calculated
Labour1 = qty(user enters this) * unit cost * profit (comes from budget)
unit cost = hour rate(from budget) - discount % (user enters it) * labor burden %(user enters it) * Shipping %(User enters it) * Tax (comes from main application tax setup)
* overhead markup % ( from budget) * profit (from budget) = item price
this is sort of the formula to calculate just 1 labor. The formulas for the other elements such as materials, equipment and subcontract are very similar.
So estimate would look something like this:
In this example I drew a piece of fence with a gate in the middle. The data that is created from this diagram is like this:
Fence = F1
Gate= G1
Post= P1
Post = P2
Post = P3
Post = P4
to find the final price of this job (subtotal) I add all fence,gates and post final price.
**Fence F1**
Material = $120
Material 2= 115
Labor 1= $134
Labor 2= $100
Equipment 1= $100
Equipment 2 = $150
Subcontract 1 = $120
Subcontract 2 = $100
**TOTAL FENCE ITEMS= $939**
Gate G1
Mat 1= $100
Labor 1= $200
Equip 1= $400
Subcontract 1= $250
**TOTAL GATES= $950**
then the next post EACH has this elements
**P1, P2 P3 P4**
Material 1 = 250 (x4)
equipment 1= $250 (x4)
labor 1 = $100 (x4)
subcontract 1= $200 (x4)
**Total POSTS = $3200**
Total Estimate Price = 939 + 950 +3200 = $5,089
Any estimate can have more than one fence gate or posts. But basically for any fence gate or post you can add as many materials, equipment, labor and subcontracts
The previous example is just a typical example but some other estimates can include up to 30 or 40 items in total.
I have a jqgrid that loads with the customer information and other general columns and the last column is Price:
Right now if I had about 50 estimates that looked like the example above my grid would take about 45 seconds to load.
There will be more than 500 estimates that are going to be shown in that grid per year.
So at that number loading this grid at once with the 500 would take more than 5 mins.
Based on this info could you tell me if this is a normal time to process this data?
Let me know if you need any other details to give me a suggestion.
Thank you
this is impossible to anwer without any more details of your code, your environemnt, the size of your database etc.
However, to me it seems like a lot of time.
You need to start profiling to find out what part is causing the problem. Is it the database, is it the processing of the data, is it the creation of the grid or is it perhaps all of it.
So break up the entire process and check what is causing the delay.
Now if it is the database: Profile the sql sent to the database. Perhaps you can create more efficient queries (load all data in one step instead of one-by-one, perhaps you need to look at indexes? Perhaps your database is too large for sql server express (it has memory limitations and will use only one core, no matter how large the physical server).
Since you mention linq-to-sql, pay close attention to the n+1 problem caused by lazy loaded data. Read up on
DbLoadOptionsLoadWithin that case.Similar kind of steps you need to do with your code untill you have found what is causing the problem