Need Advice on SqlTransactions in VB.Net 2008

[Auguy]

I have to Delete/Insert approximately 15,000 records in a table (SQL 2005) that has only a few columns. I may also have to Delete/Insert only a subset of these records. Is Deleting/Inserting this many records in one SqlTransaction too many? Should I break it up into smaller quantities like 1000 or so records? If it makes any difference there will only be one or two users. Any other problems with this I should be aware of?

Auguy
Northwest Ohio

 

[RiverGuy]

There is no general rule of thumb. 15,000 records is not too many, especially if you are deleting them in a set, like DELETE FROM Table WHERE Column IN (...). If you are firing off 15,000 individual delete statements instead of deleting the 15,000 records as a set, then you should try to do it set-based regardless of declaring an explicit transaction or not. The only problem I see with wrapping 15,000 delete statements within a single transaction is the locking on your table. If someone wants to run a report or use an application against that table, you could have a long running lock which would cause other applications hitting that table to become unresponsive. But again, that all depends on the design of your table (indexing, etc.).

 

[Auguy]

Thanks RiverGuy. Bcause of the way these records are being recalculated, I don't think I can use a "Where Column In", but I might be able to use a join to delete all that need to be deleted. Still trying to figure out the best way to do it. As far as the table lock goes, I was thinking of creating a "Calc In Progress" record somewhere to warn users the table is not available.

Auguy
Northwest Ohio

 

[Jasen]

There's no reason to break up even large transactions.
If the table keys and indexes are set correctly, even deleting a few million records at once should take less than a second.
Inserts will take a bit longer, because SQL Server has to allocate space in the memory pages and write the data out, potentially even expanding the physical size of the files. This can be mitigated by purposely making the file size initially larger than necessary, essentially forcing it to have a lot of free space to handle large insert batches without having to resize.

If I knew more about what the data looked like, and on what columns you'd be keying off of when deleting, I could steer you towards the proper index and index type to use.

 

[Auguy]

Thanks Jasen. This is a table with only nine columns. This table holds the results of tax assessment calculations. The most important columns are the ParcelFK and the AssessFK that are the foreign keys of the real estate Parcel table and the tax Assessment rate table respectively. Both of these are indexed as non-clustered. I can probably come up with a stored proc to delete the proper records. The complication comes because I let the user select subsets of the Parcels and Assessments to calculate. Therefore I can't just delete all of the records for a given year. As I said I think I can come up with the stored proc for the deletion with a little work that will use the foreign keys. The inserts will be done after the deletion. Should I break up the deletes and the inserts into separate transactions just to make it cleaner?

Auguy
Northwest Ohio

 

[gmmastros]

Should I break up the deletes and the inserts into separate transactions just to make it cleaner?

Cleaner? No. Just the opposite, in fact.

The primary purpose of a transaction is to prevent data from getting "screwed up".

Think about a banking situation where you are paying your mortgage. During this transaction, you should first make sure there is available funds. Then you should remove the funds from the payer, and finally add the funds to the payee. Now, suppose something unexpected/bad happens. Suppose the funds were removed from your account, but never added to the banks account, so they think you haven't paid, but your account shows that it has been paid.

By wrapping the whole thing in a transaction, you can commit the transaction at the end, or roll back the entire transaction (as though it never happened).

It's not "cleaner" if you are (potentially) causing more problems later.

Also.... despite what you may think, 15,000 rows is nothing. I have a process that downloads data on a daily basis, 190 megabytes of data. Using bcp, I can load all this data in about 2 or 3 seconds.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

There's no reason to break up even large transactions.
If the table keys and indexes are set correctly, even deleting a few million records at once should take less than a second.

jasen, you're missing the point -- he's not "deleting a few million records at once." He's firing off individual delete statements.

 

[Auguy]

As I mentioned before I am working to develop a stored proc to do the deletions to avoid doing them one at a time. Having looked at it a little more, I should be able to get it accomplished. I will have to do the inserts one by one.

Auguy
Northwest Ohio

 

[gmmastros]

Where are you getting these 15,000 inserts from? Is the data in a file?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

No, I'm building a table of the calculated taxes in the vb.net app. I select all of the parcel records and all of the assessment rates the user requested. I then iterate through the parcel records and the assessment records and calculate taxes for any of the asessments that apply to that parcel. Most of the calculations are straight forward, but some of them involve minimums and maximums for a group of assessments that I determined to be too difficult to do within a SQL proc. I've seen some references to passing tables to SQL, but have never done it before.

Auguy
Northwest Ohio

 

[gmmastros]

Lemme' see if I understand the overall process.

1. get 15,000 rows for the database
2. do some complicated stuff which affects a small subset of the data.
3. delete all the data from the db.
4. insert your 15,000 records

Is this right?

Is it possible to keep track of stuff that has changed and do a simple update instead of a delete & insert?

Would you consider a SQL only solution if one was presented to you, and you understood it?




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George. Here's the process as I have it so far.
1. The user selects from a set of combos the filters they want for both the parcel table and the assessment table.
They may select more than one filter or none.
These filters can be such things as subdivision, type of structure (Condo, Single Family, etc.) for the parcel table.
For the assessment table they can be tax category (Lights, Mowing, Tree Removal, etc.), tax type (by foot or value), etc.
2. When they click the Calc button, I select the matching parcel records (Approx 6500) and the matching assessment records (Approx 20) from the database.
3. I then build a table (Approx 15000 records) in the app iterating through all of the selected parcels and assessments and making the calculations.
Not all assessments apply to all parcels due to lot size, parcel type, etc.
These are yearly or twice a year calculations that need to be saved.
4. I need to delete any previously saved entries for the current tax year and matching filter settings.
I actually think I can get this part to work using joins and the filter settings in a "Delete" stored proc.
I don't think I can do an update because there is no guarantee that I will have the same records (parcel and assessment combination) due to possible changes in the parcel or assessment tables.
If I just delete the matching ones, there may be some records left in the file that are no longer valid.
5. I will then insert one record at a time into the Calculated Tax table.

Hope this is a better explanation of what is happening.

Yes, I would consider an SQL only solution.



Auguy
Northwest Ohio