April 17, 2007 at 3:30 am
Good morning,
In situations were data retrieval is from many related tables what do people consider the “best” approach i.e. serially run several stored procedures that read a single table or a single stored procedures that joins the many tables?
Kind regards
Laurence
April 17, 2007 at 3:53 am
It depends.
What is your goal? What are you trying to do?
N 56°04'39.16"
E 12°55'05.25"
April 17, 2007 at 4:30 am
Hi Peter,
It was more of a general question and that fact you said it ‘depends’ probably answers it.
A specific situation is a parts table that can be joined to many tables, but always required is data from a material table and a colour table. I’ve created a stored procedure that retrieves this data where the parts table has an outer join to the material and colour table. However, when an update is required, each of these tables is read separately to assess a timestamp and then is updated to the specific table if no one else is editing the relative record. In this situation, there are 7 stored procedures i.e.
ProcPartsMaterialColourView
ProcPartsView
ProcMaterialView
ProcColourView
ProcPartsUpdate
ProcMaterialUpdate
ProcColourUpdate
With a little more coding in the BLL I could probably do away with the ProcPartsMaterialColourView and just read the ProcPartsView, ProcMaterialView and ProcColourView.
There are probably many situations similar to this and I was just wondering in which direction I should go?
Regards
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply