Passing a Table to A Stored Procedure

  • Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2977.asp

    .

  • Thank you for your article Jacob. It is very useful. I read your other articles and I am impressed by your ability to explain things concisely and clearly.

    Kind regards,

    Vincent

  • Nice Article with clear explanation

  • Thank you vincent.

    .

  • Hi Jacob

    Nice and well written. You can also pass the data via temporary tables i.e. create the temp table in the caller proc (or prior) and refer to it in the callee proc. I prefer your method, however.

    Kind regards

    Alun

  • Bad effort because of lack of knowledge.

    From BOL:

    A trigger is a special type of stored procedure that is not called directly by a user. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.

    So, create a view. It may be real view or a dummy one:

    CREATE VIEW MyView

    AS

    SELECT

    CAST(NULL as int) as Col1,

    CAST(NULL AS nvarchar(50) as Col2,

    etc.

    Create your SP as a INSTEAD OF INSERT trigger on this view.

    Perform insert into the view.

    Here it is: table passed to stored procedure as system table "inserted".

    Works quickly, effectively, in both versions of SQL Server.

    _____________
    Code for TallyGenerator

  • Has anyone tested to see if doing the XML thing is more or less costly than using a temporary table?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Question for Sergiy,

    What if my View is a compund View. Can we update a compund view?

  • A view is an interesting solution that I haven't seen before.  I suspect it probably has as much (if not more) overhead than passing XML, plus an additional effort in creating the table / view / trigger (but obviously no XML code to write).  Does anyone have information on how the two approaches scale up?

     

  • I don't think Jacob is the one suffering from a lack of knowledge, Sergiy. What happens when two different users simultaneously call the same stored procedure? Kaboom, that's what happens. And if you think that scenario is unlikely, you haven't worked on OLTP systems.


    Scott Gammans
    scottgammans.com

  • Looks like a valid technique to me. Good article.

  • Good reminder of work-arounds to us NOT HAVING A RELATIONAL database product. Passing a table into an operation should have been solved in System R originally and part of every so-called relational DBMS since. Sigh!

    In my testing, the VIEW technique has worked out better for cases where the caller is within other DB code, such as another stored procedure. You simply INSERT INTO the view and the INSTEAD OF INSERT trigger does the work.

    However, for passing in from another application, I found the XML technique usually the best. It's also very friendly to app-developers.

    One CAUTION: carefully test performance. For one case where we passed in around 10k-50k records for insert, I found using the 2000-style "sp_xml_preparedocument" technique was actually FASTER. Odd, and disappointing. The 2005 XML syntax is nicer, but appears isn't always the optimal choice.

  • I have spent the last three years developing a multi-server, multi-database plat formed system, using similar techniques.  We have had various performance penalties, most of which were unacceptable and have been re-engineered.  The worse penalties from the use of XML are network bandwidth, SQL IO and all of the conversion time to and from XML.  There is no real support for XML, (in SQL 2000 and not much more in 2005), and it is not as efficient as any other well defined proprietary/in-house data structure for transferring data to and from SQL or message queues.

     

    The performance penalties were not apparent during the first year, because we were using test data, not real production size recordsets or user counts.  The second year was painful as larger customers came online with growing recordset and user counts, (>2GB databases and >20 users).  We suffered response times, blew out message queues and reached .Net Grid limits using too much XML.  For the larger records we eventually replaced the XML with our own data structure, increasing our performance 4 fold while allowing for easier troubleshooting.  We have some records that were larger than 10MB of XML, (some as large as 50MB). 

     

    What really happens when you update a record that contains XML and you are only updating the XML?  You write back the entire XML.  I have not found anything that actually updates the virtual ‘column’ in the XML.  This may not be a problem when you update 50 bytes and the total XML is small/ 10K.  But try it when the XML is 100K+ and a 50 byte update performance hit is felt.

     

    Conclusion: 

    ·         Use a temp table to share data between stored procedures on the same connection/process.

    ·         XML does not scale up,   it  s l o w s    d   o     w       n  .

    ·         Use the SQL database as it was intended, for storage, selecting out to XML at the end if you application needs it.  A database inside a database makes sense?  NOT!

    ·         Storing large XML records or recordsets in a table, it is not efficient; break it out to columns or multiple related tables.

    ·         XML in SQL 2000 or 2005 is not fully supported and lacking utilities/tools

    ·         Definitely no large file/record support (i.e. try loading 100-500MB of XML and working with it, or load corrupt XML for repair)

    ·         If you use XML, keep is small and for commands or configuration/attributes to enhance a process, not EDI like transfers of data.

     

    [font="Arial"]Clifton G. Collins III[/font]

  • Clifton, you hit heavily on my concerns (and experience) with XML in general and XML-SQLServer in particular.  I advise ALL of my clients to REALLY, REALLY try to avoid XML in their databases and database applications.  I have yet to come across an implementation that was uncodeable using another method and that wasn't significantly faster taking XML out of the system. 

    Before the XML-Zealot-Flamers hit, let me don my asbestos suit and say YMMV!! :-))

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Its a cool script. It makes nice use of xml data type.

Viewing 15 posts - 1 through 15 (of 100 total)

You must be logged in to reply to this topic. Login to reply