Link to home
Create AccountLog in
Avatar of megel6805
megel6805

asked on

Updating Large Number of Rows

Hello...

I am trying to figure out hte best way to update a large number of rows in a table from a flat file. Each night I get a new flat file, some values have changes, some rows are new, some rows are no longer there. They are identified by a unique number in the file so that helps.

My current system deletes all the rows in the table with one function, then reloads them using a function vb function that creates and executes in insert statement for each row in the text file. This has been ok but the main problem is that all of the rows are deleted for a short period of time which is not the best way to handle it i'm sure.

So I have a new plan but need help to know if it is the best way to go about it. Your opinions are appreciated. One new plan is to add an "lastupdated" date/time column to the table. Then as I go through my text file each night, I will run a stored proceedure for each line in the text file, this stored proceedure accepts the values for my row in the table, then attempts an update statement, if no rows were affected, it does an insert statement like this:

UPDATE table SET field1 = @parameter1....@parameter20
            WHERE idnumber = @idnumber
      IF @@ROWCOUNT=0
            INSERT INTO table (field1...field20)
                  VALUES (@parameter1...@parameter20)

It also sets the lastupdated to the current date/time and then at the end I was going to delete anyrows that were not affected today (becuase they were obviously not in today's data text file).

I'm not so sure this is the best way to go about this. It is taking much longer than the old way (but is nice that the table is not empty for 10 minutes while loading all these rows). I have not even added indexes to this new table that I'm trying out yet and I think that is supposed to slow down updates/inserts correct?  Please let me know what you think. Thanks in advance.
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi

>It is taking much longer than the old way (but is nice that the table is not empty for 10 minutes while loading all >these rows).

You can insert the new records in a new table. When done, replace the old table with the new table. This way, you will have the faster update time and the table will not be empty while you are doing the update.
hmmm , run a code to update every line in atable... looks lke the slowest way to do it. i give you it might be faster than what you used to do. but i think


SQL is much faster than code. so avoid looping code as much as you can. avoid looping SQL statements . as they can be done as a one shot.


i would go for iserting the whole new text file into a new (temperory) table. then ceck the temptable against the original one resulting in inserting the new values in the temptable in the old one.

maybe in like 3 SQL statements.

one to create the new temptable.
one to check new items and isnert them into the old table
one to delete the temptable

i guess that would be the fastest way, since working within the db is much faster than the code.

you probably wont notice or care unless you having larger data sizes.

hope that helped.

waiting for your reply
Avatar of megel6805
megel6805

ASKER

after doing more research, i agree that using a temporary table and as much sql as possible would be much faster, the problem is that we actually doing a bunch of calculations and if/case statements on many of the values in each row of the text file as we are inputting it into the table. So, some calculations would have to be done as we update the main table with the temporary table - some values even go into a third table... so the structure of the main table is not even close to the structure of the original data text file.

Should I be calculating these somehow/altering the text file, then bulk insert into a temp table, then update the main table?
OR
Should I bulk insert the text file into the temporary table as is, then use a stored proceedure to loop through the temporary table and perform the calculations on each row in the temporary table and run my inserts, and updates on the main table at that time?

thanks for your help
It will be faster to update the records in the database than updating it from the text file. You could create a stored procedure to perform the calculations, but if it is not very complicated, you could perform the calculations while updating/inserting the records.
again you are going to code.

looool.

well , either load the txt file in memory and use your stored (code) procedure to process the calculation first then use sql to get into the db.

or again , even better , just push the txt file as one shot to the db , manipulate it using sql as much as you can then also get it into the table using sql again.

waiting for your reply
I have decided to make a new text file with the values that i need using the VB code, it creates the new text files very very fast. Then do a bulk import into a temporary table, then perform the updates on my existing tables and then truncate the temp table.  My database updates are done like this:

https://www.experts-exchange.com/questions/23225829/Update-Table-from-a-temporary-table.html

As for generating new text files at the beginning, I do not know if that is the correct way but it seems to do a great job so far. The reasoning is that there are a bunch (almost evey value) of fields that must go through a custom function we wrote as a "lookup" for what the values should be. For example our original data file may send "A, B, C, D OR E" for a value but we want to insert into the database the actual words associated with those codes... so our first step to create the new text file uses our vb.net code to just generate the text files the way we want them, then we import/update the db. What do you think?  thanks
ASKER CERTIFIED SOLUTION
Avatar of Smart_Man
Smart_Man
Flag of Egypt image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
yes using the vb.net program to parse through and build my tables to import seems to be the easiest plus we are most familiar with the vb.net programming so that helps. Thanks for your input everyone.