from a stored procedure: how to return datasets and their tabl
in [Programming SQL]
|
From: Paul Anthony Paul on 22 Jul 2008 06:57 Omid, Unfortunately I do not have an answer to your question. I found your post when searching for the same thing. What you want to do is not as outlandish as these other guys sugest. It is not unreasonable to want a collection of objects to be identifiable by name as well as by their ordinal. This is the case for many collections but it seems not the result sets that we are interested in. It's easy for Eric Isaacs to say that you should use seperate procedures but there are many reason why you would want to use only one, Performance and integrity being the ones that come to mind. Basically, this is a shorcoming of the software provided by Microsoft. Why can't we just say that?
From: William Vaughn (MVP) on 22 Jul 2008 11:40 Ah, when you choose to break up a stored procedure so it gets its own tuned query plan its performance is usually faster, not slower. -- __________________________________________________________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) ____________________________________________________________________________________________ "Paul Anthony" <Paul Anthony(a)discussions.microsoft.com> wrote in message news:38C39FE0-1E51-4032-9EEB-D3AF3A3BE3E7(a)microsoft.com... > Omid, > > Unfortunately I do not have an answer to your question. I found your post > when searching for the same thing. > > What you want to do is not as outlandish as these other guys sugest. It is > not unreasonable to want a collection of objects to be identifiable by > name > as well as by their ordinal. This is the case for many collections but it > seems not the result sets that we are interested in. > > It's easy for Eric Isaacs to say that you should use seperate procedures > but > there are many reason why you would want to use only one, Performance and > integrity being the ones that come to mind. > > Basically, this is a shorcoming of the software provided by Microsoft. Why > can't we just say that?
From: Paul Anthony on 22 Jul 2008 11:56 And when you perform a multitude of statistical functions against the same huge set of data, it is usually faster to do them together. By hours sometimes. "William Vaughn (MVP)" wrote: > Ah, when you choose to break up a stored procedure so it gets its own tuned > query plan its performance is usually faster, not slower. > > -- > __________________________________________________________________________ > William R. Vaughn > President and Founder Beta V Corporation > Author, Mentor, Dad, Grandpa > Microsoft MVP > (425) 556-9205 (Pacific time) > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) > ____________________________________________________________________________________________ > > > > "Paul Anthony" <Paul Anthony(a)discussions.microsoft.com> wrote in message > news:38C39FE0-1E51-4032-9EEB-D3AF3A3BE3E7(a)microsoft.com... > > Omid, > > > > Unfortunately I do not have an answer to your question. I found your post > > when searching for the same thing. > > > > What you want to do is not as outlandish as these other guys sugest. It is > > not unreasonable to want a collection of objects to be identifiable by > > name > > as well as by their ordinal. This is the case for many collections but it > > seems not the result sets that we are interested in. > > > > It's easy for Eric Isaacs to say that you should use seperate procedures > > but > > there are many reason why you would want to use only one, Performance and > > integrity being the ones that come to mind. > > > > Basically, this is a shorcoming of the software provided by Microsoft. Why > > can't we just say that? > >
From: Eric Isaacs on 22 Jul 2008 15:25 On Jul 22, 8:56 am, Paul Anthony <PaulAnth...(a)discussions.microsoft.com> wrote: > And when you perform a multitude of statistical functions against the same > huge set of data, it is usually faster to do them together. By hours > sometimes. But if you're doing a multitude of them in a procedure and producing multiple result sets, then why would you do them together and have a huge delay between the completion of the results sets? If you really are working in a batch environment, where data is moved from one process to another (as opposed to a normalized database) I would suggest that your sprocs shouldn't have two purposes (returning datasets and processing data.) I would instead suggest having one sproc that does the processing and another sproc that returns a result set from that processing. Alternatively, the processing can be done in code, but then you wouldn't need the multiple results sets again. Perhaps you can provide a more concrete example (with DDL) of what you're wanting to do? -Eric Isaacs
From: Paul Anthony on 23 Jul 2008 09:10
I think that there is a misunderstanding, My system is doing what I want it do. The only bugbear is the fact that rowsets are not identified by names. Which is a shortcoming of the software. For example – it would be unacceptable if the columns in a rowset were only addressable by their ordinal. Names are used because it makes life easier. As for my own minor problems with this, they are solved by programmers reading the comments in the procedure. My work does not neatly fit into the categories of online or batch. Some long running procedures use a large amount of data and do complex analysis. It can be advantageous to do all the processing while the data is in memory otherwise the data has to be brought back in for another sweep. The same procedures can be run for hundred, or hundreds of thousands of rows. Going back to my original post to Omid, My point is that his expectation of being able to refer to his rowsets by name is not unreasonable. He may have decided on an architecture (or inherited one) with the expectation that the rowsets were identified by name and I think that his assumption is reasonable. "Eric Isaacs" wrote: > On Jul 22, 8:56 am, Paul Anthony > <PaulAnth...(a)discussions.microsoft.com> wrote: > > And when you perform a multitude of statistical functions against the same > > huge set of data, it is usually faster to do them together. By hours > > sometimes. > > But if you're doing a multitude of them in a procedure and producing > multiple result sets, then why would you do them together and have a > huge delay between the completion of the results sets? If you really > are working in a batch environment, where data is moved from one > process to another (as opposed to a normalized database) I would > suggest that your sprocs shouldn't have two purposes (returning > datasets and processing data.) I would instead suggest having one > sproc that does the processing and another sproc that returns a result > set from that processing. > > Alternatively, the processing can be done in code, but then you > wouldn't need the multiple results sets again. > > Perhaps you can provide a more concrete example (with DDL) of what > you're wanting to do? > > -Eric Isaacs > |