June 5, 2007 at 1:06 pm
We are displaying data for orders per customer in a new application we are developing.
We have two ways we can call the data.
1- A view/proc that joins the list of orders and the order detail per customer. This would utilize only one call to the DB from the application but would return all the data regardless if the user needs it.
2- A view/proc that would list all the orders by customer and another view/proc that would display order details when a specific order was choosen. This would require two calls to the DB.
The first option would return all the order data for that customer. 90% of it probably isn't needed.
So... my question is:
What kind of overhead is typically generated calling a proc/view and would this be performance degrading in comparison to the return of unneccessary data?
Any suggestions? thoughts? TIA!
June 5, 2007 at 4:46 pm
Just populate your table with 500k dummy orders having 5 lines per order (quite typical number for 3-4 years old systems) and try to perform 1st option.
Decide for yourself if it's good or bad.
_____________
Code for TallyGenerator
June 6, 2007 at 7:12 am
Furthermore, suppose you were really clever, and going with option #1, you loaded up the info only once, when the user first starts the app, rather than each time they need to call up an order.
a) Then imagine 25 users logging in at the same time.
b) Would they need to refresh their data each time an order was taken?
Minimize. Minimize. Minimize.
June 6, 2007 at 8:50 am
IMNSHO, option 2 will be the best performer over all. Refinements include only sending enough Order Header info to the initial grid to allow the user to select the order of concern. Then return whatever relevant information for that order header and it's associated details to the order view/edit screen.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 6, 2007 at 8:58 am
Thank you all for your suggestions.
I ran a few traces with profiler with both options. The second one did outperform the first one. We'll be focusing on that option for our development. Thanks again!
June 6, 2007 at 9:08 am
Sometimes it's not the SQL server you need to worry about -- it would have been fine in the scenario you presented.
But think of the network traffic #1 would have occasioned: might be alright with a few users, but not very scalable.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply