[RESOLVED] Stored Procedure, how would you approach this?

[motil]

Hi,

in my projects I keep all my queries inside my database as stored procedures. one of the biggest problems with SP (in my opinion) is the lack of dynamically when using them, anyway so far i manage to handle this problem but now i came to a dead end, in one of my project pages i have to create a unknown number of INSERT statements, i know i can create one INSERT statement and call it x number of times, but for performance reasons I need to write the INSERT statement as follow (this is MySql Syntax but i think MSSQL is almost the same):

INSERT(column1,column2)VALUES(value1,value2),(value1,value2) ...

where there can be any number of values ... is there a good approach for this kind of operation using SPs ?

Thanks!

[jmcilhinney]

I would write a procedure to insert one record and then specify that sproc as the InsertCommand of a DataAdapter used to save a DataTable containing all the data.

[motil]

Hi John thanks for the reply.

Wouldn't it be just as calling the the same stored procedure x number of times? is this efficient just as (or more) using multiple values insert statement ?

btw, I'm not using DataAdapters / DataTables in my project what i usually do is saving the records i want to save in LIST<CustomClass> then send it to the DA Layer for INSERT.

[jmcilhinney]

Who says that the data layer can't use a DataAdapter? In fact, if the data layer is given multiple objects of the same type to save, that's pretty much exactly what it should do: populate a DataTable and save using a DataAdapter.

There's really no way to do what you're asking for other than to build your SQL code on the fly, however you might choose to do that.

[motil]

Thanks John,

It's important to me to do things right and not to get used to bad habits. I did ended up creating that
query on the fly since i couldn't find any other option, what i did was to create its string inside the BLL and send it to the DAL layer which at the end sent it to the SP and in there i executed the string, is this a bad thing ? i will have to deal with more situations like this one along this project.

and for the other question i asked in the previous post, setting a single insert statement to a dataadapter and execute it number of times has the same efficiency of using multiple insert statement ? or it just as you execute it one by one (with FOR or WHILE statement) ?

Thanks again for your time.

[jmcilhinney]

Like I said, if you don't know what the SQL will look like until it's time to execute it then you have no choice but to construct it dynamically at some point. There are various ways it could be done but it's going to involve string concatenation at some point.

There would be various optimisations along the way so using a DataAdapter would be quite like executing a command multiple times yourself, but it would be along those lines.

[motil]

Ok thanks a lot John.

[jmcilhinney]

You should also note that you can use the UpdateBatchSize to control how much data gets pushed to the server each trip. A larger value means fewer trips with more data each batch, so you need to strike a balance between batch size and number of batches. I think the default is 1, which means no batching, 0 means a single batch containing all the data.

[motil]

Hi
Thanks again for the information, in the next few days when i'll have some free time I'm going to create test application and test both methods performance.

Best regards.

[mendhak]

Hmm, I'm a bit late. Two ways to do it. You can do it in a single statement like so:

INSERT INTO table1(col1,col2)
SELECT (blah,blah)
UNION
SELECT(blah,blah)
UNION
SELECT(blah,blah)


Depending on how many rows you're inserting, this will perform better for larger numbers of inserts (check the execution plan to see what I mean). Another more common approach is to construct some XML and pass the XML into the stored proc (as the XML can represent each bit of data you want to insert) and then read it in SQL, performing your INSERTs.