• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 723
  • Last Modified:

Inserting key/value pairs into a database

I have a hashtable (list of key/value pairs) that I want to insert into a database.  However, I'd rather not do this by inserting each key/value pair with a separate command since I have a few thousand that need to be inserted and it seems to take too long.  Instead, I want to just call one command that will insert the whole list.  I found this site: http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm that explains a few ways to do this.  The method that uses a temporary table variable seems like it would be best for me but I'm not sure.  Basically, I have three questions:

1) Will I actually get a sizable performance benefit from just making one call to a stored procedure instead of thousands (will the decreased command overhead outweigh the string processing and stuff)?  I think the answer is a definite yes but I'm not an SQL expert.

2) Can you please provide a stored procedure that will let me pass it a comma-separated list of keys, another comma-separated list of values, and a foreign key and have them inserted in the database.  Like this:
Pass the stored proc this:
Key1,Key2,Key3
Value1,Value2,Value3
And inserts this:
Table'sPrimaryKey    ForeignKey    Key1    Value1
Table'sPrimaryKey    ForeignKey    Key2    Value2
Table'sPrimaryKey    ForeignKey    Key3    Value3

3) Can you please provide a stored procedure that will return a comma-separated list of keys and values for a given ForeignKey.

Thanks.
0
thedude112286
Asked:
thedude112286
  • 5
  • 4
1 Solution
 
Anthony PerkinsCommented:
>>1) Will I actually get a sizable performance benefit from just making one call to a stored procedure instead of thousands (will the decreased command overhead outweigh the string processing and stuff)? <<
Yes.  No roundtrips.

>>2) Can you please provide a stored procedure that will let me pass it a comma-separated list of keys<<
You have already posted a very good link.  But I would not use a comma delimitted list, instead I would use an Xml Document and use OpenXml as suggested in that same link.
0
 
thedude112286Author Commented:
It would be A LOT easier to use Xml but I was afraid of the performance hit it might give me.  Do you think that its performance would be a big issue?
0
 
thedude112286Author Commented:
Also, could you maybe just give me an example of how to 1) read the nodes of an XML document passed in as an ntext (I guess) field and 2) create an XML document.  Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
>>Do you think that its performance would be a big issue?<<
I cannot say for sure.  We use it extensively here (95% of parameters are Xml documents), on the other hand we are not passing "a few thousand" at most a few hundred, so I cannot say for sure.  The best way would be to do a series of tests with a) delimitted string and b) Xml document and see if you see any differences.  For us maintenance is more important than a few nanoseconds in performance.

>>could you maybe just give me an example <<
Take a look at the link you posted.  It has an excellent example.  If after that, you have specific questions, ask.
0
 
thedude112286Author Commented:
Here is the layout of my table:
Table Name: DocumentVectors
DocumentVectorKey uniqueidentifier --Primary key, different for each row, only present b/c I need a Primary Key
DocumentVectorID uniqueidentifier --Foreign key, many rows may have the same value
DocumentVectorDimension nvarchar(500) --Hashtable key, many rows may have the same value
DocumentVectorValue float(53) --Hashtable value, many rows may have the same value

This is the stored proc I've made:

CREATE PROC InsertDocumentVector
(
      @VectorXml ntext
)
AS
BEGIN
      SET NOCOUNT ON

      DECLARE @DocHandle int

      EXEC sp_xml_preparedocument @DocHandle OUTPUT, @VectorXml

      INSERT INTO DocumentVectors
            SELECT * FROM OPENXML(@DocHandle, '/Item', 2)
                  WITH (DocumentVectorKey uniqueidentifier,
                        DocumentVectorID uniqueidentifier '/DocID',
                        DocumentVectorDimension nvarchar(500),
                        DocumentVectorValue real(53))

      EXEC sp_xml_removedocument @DocHandle
END
GO

I'd like to pass in XML like this:
<Root>
     <DocID>All items below are for this document id--this is a guid</DocID>
     <Item>
          <DocumentVectorDimension>Dim</DocumentVectorDimension>
          <DocumentVectorValue> Value</DocumentVectorValue>
     </Item>
     ...
</Root>

I think that my stored procedure would work with this, but I don't want to have to pass in a primary key with my xml.  Is there any way to have the stored procedure insert a new guid for me?

Also, to retrieve the data as xml, would a simple select statement with FOR XML on the end work out?

Thank you very much.
0
 
Anthony PerkinsCommented:
Try it this way in SQL Query Analyzer first:
DECLARE @DocHandle int

EXEC sp_xml_preparedocument @DocHandle OUTPUT, '<Root>
                                         <DocID>FBFC8553-26F5-4228-90B0-587CE8A7BEC0</DocID>
                                         <Item>
                                              <DocumentVectorDimension>Dim</DocumentVectorDimension>
                                              <DocumentVectorValue>123456.789</DocumentVectorValue>
                                         </Item>
                                    </Root>'
--INSERT      DocumentVectors
SELECT      NEWID() DocumentVectorKey,      
      DocumentVectorID,
      DocumentVectorDimension,
      DocumentVectorValue
FROM OPENXML(@DocHandle, 'Root/Item', 2) WITH (
            DocumentVectorID uniqueidentifier '../DocID',
                 DocumentVectorDimension nvarchar(500) 'DocumentVectorDimension',
            DocumentVectorValue real 'DocumentVectorValue')

EXEC sp_xml_removedocument @DocHandle

If the output is correct than your stored procedure would look like this:

CREATE PROC InsertDocumentVector
(
     @VectorXml ntext
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @DocHandle int

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @VectorXml

INSERT      DocumentVectors
SELECT      NEWID() DocumentVectorKey,      
      DocumentVectorID,
      DocumentVectorDimension,
      DocumentVectorValue
FROM OPENXML(@DocHandle, 'Root/Item', 2) WITH (
            DocumentVectorID uniqueidentifier '../DocID',
                 DocumentVectorDimension nvarchar(500) 'DocumentVectorDimension',
            DocumentVectorValue real 'DocumentVectorValue')

EXEC sp_xml_removedocument @DocHandle
END
GO
0
 
Anthony PerkinsCommented:
Unrelated, but for performance reasons you should seriously consider changing
1. float (or real) to fixed decimal number data types such as numeric (decimal).
2. nvarchar to varchar, unless you need support for double-byte character languages.
3. uniqueidentifiers to integer columns with the identity attribute.
0
 
thedude112286Author Commented:
I cannot thank you enough.  You have been EXTREMELY helpful--I really appreciate it.  I also appreciate your suggestions but I really do need the extra percision of float and need to support multiple languages.  But I think that I will change over from uniqueidentifiers to bigints.  Thanks!
0
 
Anthony PerkinsCommented:
>>but I really do need the extra percision of float <<
The only reason you should use float is if fixed decimal numeric is not large enough.  So if the range  -10^38 +1 through 10^38 –1 is not enough, than by all means use float.  The problem with the float (real) is that it is aproximate, so you will see 1.5 represented as 1.49999999999999.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now