Table Types & Stored Procedures. Best Friends?

[guest2013-1]

Okay so I've been playing around with XML (as my data transporter) between servers and I'm using a bit of XQuery in my stored procedure to convert this back into a table. I then load the table into its corresponding table-type (predefined) and send it on it's way to it's corresponding controller (stored procedure that will process the data)

So instead of firing 1 stored procedure with x amount of parameters, 100 times, I load that data into the UDTT and only send that as a parameter to that stored procedure.

I do a simple

UPDATE

SET destination.Col1 = source.Col1
FROM

destination
INNER JOIN @UDTT source ON source.ID = destination.ID
WHERE action = 'Updated'

INSERT INTO

(columns)
SELECT (columns) FROM @UDTT WHERE action = 'Inserted'

DELETE FROM

WHERE Col1 IN (SELECT Col1 FROM @UDTT WHERE action = 'Deleted')

For updates/inserts etc. 100 records is less than 1 second, whereas calling the SP 100 times puts it to 10 records per second +-

I'm now in the stage where "What kind of logic am I going to want to apply to this in a stored procedure that will render my method useless and a normal parameter based stored procedure would be superior?"

So that's my question, can anyone poke any holes in this that "normal" T-SQL methods won't work. Push comes to shove you can still do cursors and normal IF logic... just wondering if there's anything special you guys can think of.

 

[zippy]

I always try to avoid cursors where possible. Use methods like bulk collecting into arrays if you need to do row processing, but there may be situations where cursors are unavoidable. Cursors tend be abused, because they make it easy to debug and read code.

Fast SQL tends to confuse most people. It seperates the men from the boys, so to speak

With databases, its always best to adopt strategies which minimise prolonged disk io. I.e. grab all the records as fast as possible, manupilate in memory. That type of thing.

EDIT: I had stored procedure which was to gather the entire policy details for a customer. Its about 30 or 40 tables. It started with single procedures for groups of tables. It ran like a dog. 20 seconds to retrieve the policy. Changed it all to 1 single monster SQL., which came to 1900 lines, and ran in less than a second

 

[guest2013-1]

Yea, I was wondering, since I'm using a table (in memory, made from XML) if I could use that in all scenarios when manipulating data.

I'm going through our current implementations and it would appear to get rid of a lot of "I have to use a cursor here" stuff, but I'm afraid I'm painting myself into a corner if, down the line, we want to code something and can't because of the way we implemented the transport mechanism of data between servers.

My initial assessment on everything is that we'd be fine in all scenarios, can't really think of any we can't be fine with