SQL Question re: performance when insert/update/delete

[guest2013-1]

I've recently modified the original XML reader APP I wrote to write into mssql 2008 instead of mysql. mssql performance on the machine is brilliant compared to mysql, and I figured insert/update/delete would also be.

db is about 14gb big now (have about 7 million records with accompanied data so well over 80 million rows, legacy **** I'm working to get rid of)

In any case, on my slow machine the insert/update and delete happens quite fast. Given this is a blank database I'm working from with real-world xml file data so sqlserver doesn't have that much to do... but why are inserts so slow on the live server? there's no data-check like the updates have.

Would it speed things up if I were to write all the data into a temp table (or different database) and use the stored proc's I wrote for the insert/update/delete to run it in a batch, from table to table?

I've tried everything. truncated the log/simple recovery plan/write cache disabled to speed up the disk/removed unnecessary indexes. Even temporarily removed the full-text index thinking it might slow down. The app is running on the machine it connects to, so no network issues to worry about. I do realize the disk is most likely the culprit since I don't have things setup on separate partitions or whatever, but surely it can't make THAT much of a difference if it ran fine previously?

 

[xrapidx]

Indexes on the required key columns

Try "Analyze Query in Database Engine Tuning Advisor"

 

[guest2013-1]

Will try, and yes I had indexes on the required key column (in the where statements etc) but it only gave a slight boost in performance

I think I'm going to try the batch update idea where I insert all the records into a table and then run a "commit" but first the tuning advisor (I always forget that ****)

 

[dequadin]

Indexes on the required key columns

Try "Analyze Query in Database Engine Tuning Advisor"

I always thought indexing could slow down your inserts, not speed them up? Because SQL has to go and update all the indexes on each insert?

 

[Fuma]

I always thought indexing could slow down your inserts, not speed them up? Because SQL has to go and update all the indexes on each insert?

Yep. Indexing speeds up your data retrieval (selects), and slows down your updates (inserts, updates, and deletes). At least that's what I have read.

 

[guest2013-1]

Yea it does, but even without the indexes on there it was running slow. Running the tuning advisor didn't really help other than to suggest I index some field that isn't really relevant to anything I do to lookup or update/insert a record.

I'm going to try a straight-up insert into a temp table and then run the stored proc that does the inserts

 

[guest2013-1]

It might be a disc queuing issue, that's why I'm looking at separating the update/insert of the database from the actual processing of the XML file. Let them run in separate batches

 

[xrapidx]

Look into table partitioning... might help depending on how you're inserting, updating and inserting.

(excuse short responses...in and out of the office the entire day).

 

[dequadin]

Look into table partitioning... might help depending on how you're inserting, updating and inserting.

I think you need the Enterprise (read $$$$) version for that. What version are you working with AcidRaZor?

 

[Vixremento]

Have you tried updating stats on your tables?

Try something like this:

UPDATE STATISTICS dbo.TheTable WITH SAMPLE 30 PERCENT

You can play with the sample size but we've found that 30% works for most of our larger tables.

 

[guest2013-1]

Have you tried updating stats on your tables?

Try something like this:

UPDATE STATISTICS dbo.TheTable WITH SAMPLE 30 PERCENT

You can play with the sample size but we've found that 30% works for most of our larger tables.

What exactly would that help?

Look into table partitioning... might help depending on how you're inserting, updating and inserting.

(excuse short responses...in and out of the office the entire day).

I call a stored procedure, it inserts or updates 1 table, and then 4-6 other tables with different info based on one call.

I think you need the Enterprise (read $$$$) version for that. What version are you working with AcidRaZor?

SQL Server 2008 R2, I assume it's enterprise or whatever. Not express.


ugh

I tried just inserting all the data into temp tables but the thing stops at like 56000 records processed. Highly annoying. Just did a run on how fast it will process without any calls to the database and it did it in less than 2 hours for 8.4 million records. So I know its not the XML files I'm processing thats taking long.

Wondering now if I should write out one huge file with all the data neatly packed like I need them to and use sqlbulkcopy to push it into the database instead... but if I make that **** work I'm almost going to bet you the same "stall" happens again making me waste several hours of my life without beer...

 

[xrapidx]

I've your doing a bulk insert, try disabled or the index, and recreating them after you have the data in.

 

[guest2013-1]

The bulk insert will go into the temp tables I've done. not the live ones.

once the data is in I'll just run the stored proc and process the tables. it should be faster running in sql server (my guess) than having a 3rd party program do 8.4mil calls to it externally

 

[dequadin]

SQL Server 2008 R2, I assume it's enterprise or whatever. Not express.

Could be standard or enterprise. Maybe check here: SQL 2008 Editions Also Enterprise only runs on Windows Server Enterprise (AFAIK).

Partitioning will definitely help if you're having IO problems. You can check in Performance Monitor to see in you're having excessive disk IO during updates/whatever.

 

[xrapidx]

Depending on how you do your updates/inserts.... lookup MERGE statements as an alternative.

 

[Vixremento]

What exactly would that help?

Well it improved performance for us - not sure how it would help for you (was just a suggestion).

 

[guest2013-1]

Well it improved performance for us - not sure how it would help for you (was just a suggestion).

No I meant technically speaking. What does statistics do and how does it work and WHY would it improve performance. I should have phrased my question better

 

[Vixremento]

No I meant technically speaking. What does statistics do and how does it work and WHY would it improve performance. I should have phrased my question better

Hehe - no worries...I should avoid forums when I've had a few drinks (or maybe I should stop drinking)!
From the SQL documentation:

The Database Engine keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index or indexes to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.

If lots of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated by using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

To see when the statistics were last updated, use the STATS_DATE function.

Basically the stats keep track of indexes - these stats are used by the query optimiser and are intended to optimise performance however things go hairy if a large number of records have been inserted/deleted since the creation of the index(es).

Updating stats has become a maintenance task for us since SQL 2000...not sure how much of a difference it'll have if we stopped using it on SQL 2008 but if it ain't broke we don't fix it!

You can read up some more on it here.