June 16, 2005 at 12:05 pm
The temporary table method works fine from both ado and ado.net.
You create a temporary table by executing a "CREATE TABLE #tableName" statement from the connection object in ADO. On the same connection object, you execute INSERT #tableName statements to populate the temp table. Again, on the same connection you execute the stored procedure. I've used this method for years, and I prefer it over sending individual updates to a permanent table or a group of related tables. It reduces the number of updates because I can commit everything with set-based DML statements, and it provides the query optimizer the best possible options on how best to perform the INSERT or UPDATE operation. In addition, I can move the transaction handling code into the stored procedure which reduces the number of places I need to look to troubleshoot deadlock problems.
Brian
MCDBA, MCSE+I, Master CNE
June 16, 2005 at 12:15 pm
Remi:
Using a permanent table instead of a temporary table to hold the information is a bad idea for a couple of reasons. First, it is much more difficult to support multiple users because you have to add code and columns to track which process stored information in the table, and also add code to clean up the holding table. It wouldn't do to delete user B's records after you're done processing user A's records. In addition, every write to the permanent table gets logged, so using a permanent table can cause your log file to grow, especially when you have a large number of users. The temp table is simply that: a temporary holding area for sending a set of information from an application to a stored procedure for processing. There isn't any reason to save the information once it has been processed by the stored procedure, because that's what the stored procedure is for!
Brian
MCDBA, MCSE+I, Master CNE
June 16, 2005 at 12:22 pm
I see you have way more experience in that area than me. Thanx for sharing.
August 29, 2005 at 7:42 am
remi,
can u elaborate more...i am using oracle database and i have a datatable. this is the first time i am writing a stored proc. so how to pass data...i mean can u help me wiith the syntax??
thank you
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply