update to database takes longer after db size has increased 10 times?

[sobeit]

I have retested the application, with the same data getting updated but with database size increased (using alter database cmd, dbreindexes, and update statistics) so no db growing takes place during the transactions. The result is still the same. I even made the db 170% bigger with dummy data, and the conclusion is as the database grow, the longer it takes to process the data.

Can anyone suggest other items that i need to examine (other than the querries submitted by the app)?

Thanks.

 

[tlbroadbent]

I don't exactly follow what you've said. I feel like I've come in during the middle of the conversation and something transpired previously. Is there another thread where you've been discussing a problem?

You increased the database size. Did you increase the size of both the MDF (Data) and LDF (Log) files? Can you explain precisely what the update does and what happens that you consider a problem? For example, are you doing mass updates in the query? Are you updating, deleting or inserting rows?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[sobeit]

Thanks for replying.

This particular system (still in development)has an upload data utility, which takes the data from disk file and populated data into the transactional tables (i.e., Person, address, and so on).

What I am testing is to see how long it will take this app to load the data after the database has grown to some expected size. Yes, once we go live, we will have to load the old data into this new db so it's related to conversion.

My test really involves loading the same file using the upload app's utility, and compare the upload times between a smaller database (75 MB for both log/data) and the bigger db (~450 MB). My original speculation has been that the extents for these tables are increasing prior to the action queries, so I expand the db size prior to loading the file hoping that no db growing occurred. the results is the same upload time.

any suggestion or question?
thanks.
fyi....
The transactional tables do have corresponding history or audit tables, which are populated via triggers. and what I am seeing int he execution plan is the "inserted" virtual table is getting scan because one of the SQL statement select a value from the "inserted" table and evaluate it before executing the insert/update statements.

 

[tlbroadbent]

Are you using a custom program to upload the data? Does that custom program insert one row at a time into the tables? My guess would be that this is the case. You can hardly create a more inefficient way to load data into SQL Server. Of course, using TRIGGERs when bulk loading thousands of records only adds to the inefficency.

Have you considered using DTS, BCP or BULK INSERT to load the data? If it is not possible to bulk load directly into the production tables, you could bulk load into temporary tables and then insert into production tables using SQL.

If I've totally misinterpreted what you are doing, please let me know. It won't be the first time.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SemperFiDownUnda]

Ok I'm like Terry, I'm not completely following you but your 2nd post sheds some more light

If you are planning to do a large import then increasing the Database and log file size before hand can make sense (if the import would normally cause the database to increase size multiple times based on the default growth settings)
Why you would reindex and update statistics BEFORE you add the data or even at all is puzzling to me.

Why you have a 75meg database with 75meg log file is a bit puzzling too.

with your FYI I'm unsure what you are trying to get at.
Are you saying your triggers are taking to long? You aren't using cursors in your triggers are You?

Adding data to your database shouldn't be that big of a deal especially at the level you are talking about.
Increasing your database size is ok. INSERTing the new data can cause page splits but there is no efficient
way to fix that *just* before uploading. If you get alot of page splits then you should rebuild your tables with PADINDEX and FILLFACTORs so that they occur less often. But doing this just before you INSERT won't save you time in your upload.

Perhaps you can show us your triggers and the tables involved so we can get a better idea of what is going on.

 

[sobeit]

Terry,
to answer your question, the "file uploader" process in this system is set to run every 5 minutes. Basically, all it does is read in a flat file, apply business rule, and insert/update the result data into the transactional tables in the database.

The goal of my test is to reduce the amount of time it takes to load one record from the flat file into the database (from 2.4 seconds to 1 seconds, since our previous system took only .63 second per record).

dear instructur,
thanks for replying. The reason that i have chosen to reindex and update statistics after i added dummy data (roughly around 100K more records) into the db is because I thought the tables involved may have had many fragmentation and statistics may not be up to date. The data and log files sizes expansion being applied prior to the "upload file" job is basically my way of preventing SQL Server from expanding these files in the middle of the loading.

No cursor in the triggers.

At the end, the reason why it's taking longer to process the same file through this new system could be due to poor application design. But I would imagine that the amount of time to process the same file in a 80 MB database versus the 400 MB database would be very close (instead of .53 versus 2.5 seconds)

Thanks guys.

 

[tlbroadbent]

I appreciate the additional information. It is extermemly difficult to optimize performance remotely when one does not know the schema, inputs, outputs, indexing, amount of data, etc. Sounds like you are on the right track.

You should use SQL Server Profiler to trace the load activity. Set it up to capture all SQL Statements. There is a template for tracing duration but I prefer to modify it to capture the following events.

SP: Completed
SP: StmtCompleted
SQL: StmtCompleted

Capture the following columns to help identify long running statements and procedures.

TextData, Duration, CPU, Reads, Writes

Just increasing the size of the database shouldn't degrade performance. Sizing the database and log for growth should improve performance because SQL Server doesn't need to grow the files.

However, as more data is addded some queries will take longer especially if the tables are not properly indexed. Analysis of the Estimated Query plan will tell you if indexes are being used. As data is added, it is important to reindex and update statistics. It is also good to revisit index optimization because a query that was fast with 100K rows may perform very poorly when the table contains 500K rows.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm