Large insert help

[Kerberos]

I hope this is the right group, please point me in the right direction
if it is not.

I have the following table, DTS'd into SQL 2000 from a legacy ISAM
file based DB

(STORE_ID, UPC, MONTH_YEAR, COUNT#1, COUNT#2...COUNT#31)
there is COUNT# for all 31 days.

terrible design in my opinion as it make's it a real problem to report
on sales.

I'm re organizing the data into a table like this for web based
reporting. A process will run once a week to perform the update.

(STOREID, UPC, DATE, SALES)

The original table has 1.1 million records in it, the resulting table
could have as much as 31 million, assuming every item has sales for
every day.

Here is how I'm doing it now. For testing, I only have 10,000 records
in the original table, the resulting table has 98,000 after my process
runs. This takes 10 - 15 mins, I can only imagine how long it will
take with 31 million. What can I do to speed this up as much as
possible?

Private Sub btnCreateTable_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnCreateTable.Click
Dim strSQL As String = "EXEC GetJSMovement"

Dim objConn2 As New SqlConnection(strConn)
Dim objDR As SqlDataReader
Dim objSQL As New SqlCommand(strSQL, objConn)
Dim objSQL2 As New SqlCommand("InsertMovement", objConn2)
Dim i As Integer = 1
Dim x As Integer
Dim strDBDate As Date
Dim strDate As String
Dim prmStore As New SqlParameter("@Store", SqlDbType.SmallInt)
Dim prmUPC As New SqlParameter("@UPC", SqlDbType.VarChar)
Dim prmDate As New SqlParameter("@Date", SqlDbType.DateTime)
Dim prmSales As New SqlParameter("@Sales", SqlDbType.VarChar)

objSQL2.CommandType = CommandType.StoredProcedure
prmStore.Direction = ParameterDirection.Input
prmUPC.Direction = ParameterDirection.Input
prmDate.Direction = ParameterDirection.Input
prmSales.Direction = ParameterDirection.Input

'retreive records to be modified
objConn.Open()
objDR = objSQL.ExecuteReader

Do While objDR.Read
'Loop through all 31 fields in original table
'If the daily sales field is not empty,
'make up the date and enter into the new table
Do Until i = 31
If objDR("COUNT#" & i) <> 0 Then
strDBDate = objDR.GetDateTime(2)
strDate = strDBDate.Month & "/" & i & "/" &
strDBDate.Year
objConn2.Open()

prmStore.Value = objDR("STORE_NUMBER")
prmUPC.Value = objDR("UPC_NUMBER")
prmDate.Value = strDate
prmSales.Value = objDR("COUNT#" & i) / 1000

objSQL2.Parameters.Add(prmStore)
objSQL2.Parameters.Add(prmUPC)
objSQL2.Parameters.Add(prmDate)
objSQL2.Parameters.Add(prmSales)

objSQL2.ExecuteNonQuery()
objSQL2.Parameters.Clear()
objConn2.Close()
x = x + 1
End If
i = i + 1
Loop
i = 1
Loop

Response.Write("Movement table written - " & x & " rows" &
"<br>")
objConn.Close()
End Sub

 

[Miha Markic]

Hi Kerberos,

The fastest way would be to do everything it in a storeprocedure.
If this is not an option here are few tips:
Put these after objSQL2 constructor.

objSQL2.Parameters.Add(prmStore)
objSQL2.Parameters.Add(prmUPC)
objSQL2.Parameters.Add(prmDate)
objSQL2.Parameters.Add(prmSales)

objConn2.Open() and close leave outside the loop
No need for objSQL2.Parameters.Clear() if you set all of them every time.
Try invoking executeReader with CommandBehaviour.SequentialAccess.

But still, since the procedure is fairly simple I strongly suggest you to
put it into stored procedure.
You might also delete indexes on target table before the process and
re-create them after you've finished the job.

 

[Kerberos]

Hi Miha,

Thank you for your suggestions, I'll give them a try. I know doing it
in a sproc would be better, however my sproc abilities are limiited to
basic selects and inserts. I have a T-SQL book, I'll look up more
advanced T-SQL stuff. I have no idea how I would loop through the
fields such as I am in VB, and also how to validate the date and field
data before inserting it into the new table

Thanks again

Peter

 

[Miha Markic]

Hi Kerberos,

I don't think that you can iterate through columns, thus you'll have to do
some copy paste for each of the 31 columns.
However, if you put the insert part into another stored procedure you'll
have just to invoke it 31 times - no big deal.
Converting to datetime: cast('2003'+'12' + '13' as datetime) replace the
string concatenation where first string is year then month and then day
What else do you need to validate?