On Fri, Jan 23, 2009 at 1:30 PM, Ricardo Aráoz <[email protected]> wrote: > Stephen Russell wrote:
>> I would still say that if you put this into a SP your going to get >> better performance, say 10 sec could be cut in half or a third? >> > How come? My line of thought is that a select is optimized by the > server, which will probably do a much better job than that which I'll be > able to do accessing the table a record at a time (unless there was a > special case that justifies it, don't think this is it). --------------------- You could preset 1-N prepared or precrunced tables that are defined in the SP and then dropped later when not needed. You could then index them and join them probably faster then the rowscan that is happening currently. Have you done an execution plan on that yet? I trust the output that SQL Servers plans give. SPs have a lot of potential power. I just published a 700+ line SP to our production server today that takes in 36 or so params that will be used as inserts or updates to 12 tables. For all the uses of diff tables it is faster to do the super crunch instead of multiple connections from the in this case winform client. I had to track many ids as the data was getting defined. This was reading a spreadsheet and taking all the cols as a different piece of data in a lookup table and put the 1:M:1 data in place. >> Was this 2000 or 2005? Some of what your crunching could be done >> differently in 2005 if available with cross tab functionality using >> the PIVOT command. >> > I don't have that, this is 2000. Anyway as I understand it "PIVOT" is to > transpose totals from rows into columns, this is not the requirement I > had in this case. Funny when I read your description after you described what T1 T2 were I said, wow a cross tab would probably be a good fit. -- Stephen Russell Sr. Production Systems Programmer First Horizon Bank Memphis TN 901.246-0159 _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
