SQL Inserting up to 10'000 records from a table to a secondary table

Question

I'm relatively new to ASP.net and working with larger data sets as a fresh graduate, and I am currently maintaining/developing for an ASP webform and T-SQL system at the moment where we have a database table with ~4000 records, but there is potential for multiple key differences leading up to 10,000 records as a rough estimate to be submitted at a time. This is used for printing to a thermal printer, so a separate standalone database table with duplicate data is necessary.

I'll rewrite the code for insertion to a similar enough format so it's a bit more readable. The query is pretty much as efficient as it can possibly be, it's 3 left joins on ID fields and 2 where clauses to sort to the record I want to copy.

INSERT INTO PRINT_TABLE(ID, KEYNAME1, KEYNAME2, KEYNAME3, KEYNAME4,
PRINTFORMATSTRING, PRINTQUANTITY, COLLECTION, COLLECTION_OWNER, MANAGER, LOCATIONID )
(SELECT
--parameters and joins necessary to copy data across
with parameters @quantity, @id, and @label
)

Now I made an efficiency change already in an attempt to speed it up, it currently runs a foreach loop on a set of data in a telerik radgrid table, and I moved it to start using selecteditems instead of all items and picking out items that are selected, and any that are selected are added to the queue to iterate through. My question is, is it best to run this query in the code behind, on a button submit, i.e.?

Protected Sub AddToPrintDatabaseOnButtonClick(sender As Object, e As System.EventArgs)
    Using connection As New SqlConnection(connectionString)
        Dim command = New SqlCommand()
        command.Connection = connection
        command.CommandText = "INSERT INTO PRINT_TABLE(ID, KEYNAME1,
        KEYNAME2, KEYNAME3, KEYNAME4,
        PRINTFORMATSTRING, PRINTQUANTITY, COLLECTION, COLLECTION_OWNER,
        MANAGER, LOCATIONID )
        (SELECT
            --parameters and joins necessary to copy data across
            with parameters @quantity, @id, and @label
        )"
        connection.Open()
        command.Parameters.AddWithValue("quantity", TextBox.Value)
        command.Parameters.AddWithValue("label", Dropdownlist.Value)
        command.Parameters.AddWithValue("id", 0)
        For Each GDI As GridDataItem In RadGrid.SelectedItems
            command.Parameters("id").Value = Integer.Parse(GDI("id").Text)
            command.ExecuteNonQuery()
        Next
    End Using
End Sub

Which is what I just implemented to speed up the page which would create a new connection string on every selected value. Or is there some other method such as using a procedure and parsing in a list of the ID's and doing it database side, or another method I'm not thinking about? I'm more interested in suggestions or ways of thinking and approach to the issue than being given a flat solution, I want to learn. If you have 1 or 2 snippets that will help me out as an alternative I'd be very thankful.

Depends on how many items are selected. If you have a lot of selected items (in the thousands), it will hurt performance. You could then look at maybe using something like SqlBulkCopy or sending array. The ids can be inserted in a temp table, and then copied to print_Table with a proper quantity and label. — the_lotus
– the_lotus, Commented Jan 11, 2016 at 16:31

Alexei · Accepted Answer · 2016-01-07 08:08:41Z

Ideally, you should provide all relevant code (including the whole query) in order to receive more targeted answers (and also obey site's rules), but based on you have provided, one way to optimize the insertion is the following:

1) Define a session table which will hold all the keys that require inserting the data for

ThermalPrinterSession

ThermalPrinterSessionId INT PRIMARY KEY,
SessionId UNIQUEIDENTIFIER,
Quantity INT,
Label VARCHAR(100),
Id INT

2) Create a stored procedure that will be able to process data for a session

CREATE PROCEDURE uspProcessThermalPrinterData
(
   @SessionId UNIQUEIDENTIFIER,
   @Quantity INT,
   @Label VARCHAR(100),
   @Id INT
)
AS
BEGIN
   INSERT INTO PRINT_TABLE(ID, KEYNAME1,
       KEYNAME2, KEYNAME3, KEYNAME4,
       PRINTFORMATSTRING, PRINTQUANTITY, COLLECTION, COLLECTION_OWNER,
       MANAGER, LOCATIONID )
   SELECT 1                       -- all columns for insertion
   FROM ThermalPrinterSession S
      JOIN OtherTable1 T ON T.Id = S.Id        -- other joins and other tables come here
   WHERE T.SessionId = @SessionId
END
GO

3) For each insert session you can define a new unique identifier

Guid.NewGuid();

4) Actual insertion can be done using bulk insert for improved performance. Entity framework has an extension that helps with bulk inserts and an elegant generic solution can also be found here.

5) call the stored procedure for current session

6) Session table maintenance

If this table is not going to be very big (depends on session inserts), it is better not to perform deletes during the working hours and truncate it once a day (e.g.in a nightly job)

Stack Exchange Network

SQL Inserting up to 10'000 records from a table to a secondary table

1 Answer 1

You must log in to answer this question.

Hot Network Questions

SQL Inserting up to 10'000 records from a table to a secondary table

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions