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.