Query performance?!

[Averno]

Running this query:

INSERT INTO FACT_AGGR_IMP_1
SELECT TOP 110000 * FROM FACT_AGGR_IMP

on my server (compaq 8000 4 cpu 1 GB RAM) takes 29 seconds, running THE SAME query with 120.000 instead of 110.000 takes 3mins14secs, almost 7 times more!! Who is the guilty? Is there a way to understand the why of this sudden performance crackdown?
Thanks a lot, bye.
Averno, Italy.

 

[JNC73]

Hi Averno,

When you insert a large amount of records into a database and it can't be logged anymore whithout swapping the data the SQL*Server performance decreases. This means that the LOG device of your database is full.

A work around for this problem is commiting the new data after an X amount of records. This way SQL*Server can delete the commited records from the LOG device and keep enough space over for the remaining records.

An other way of solving the problem is to increase the LOG device SPACE of the database. This isn't always the way to solve the problem.

The first work-around works the best and will work on any SQL*SERVER database.

Hope this helps,


JNC73

 

[Cagliostro]

I don't know, will it help, but you can try select distinct. Usualy it is more slow, but when there are too many records it can be faster. John Fill

 

[foxdev]

It could be a log write problem like JNC73 mentioned, or 110,000 may simply be the boundry of the size of your SQLS's data buffer at that time (it can change size dynamically). Or, as I've often found, it is affected by the SQLS Recovery Interval setting; if this is too low or set to the default, SQLS will dramatically slowdown at a given point while it writes out its dirty buffers, then resumes. Try changing this to, say, 60 to see if this makes a difference. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[vanoordt]

The problem may be dynamic file growth of either database- or log file. This can take quite some time. Looking at file sizes before and after your operation it is easy to determine if it is. (Also the performance monitor is very instructive! What is the system doing all this time?)
If file growth is the problem, increase the size of your files beforehand and don't shrink them later on.
My tip: Never shrink database files unless there are very good reasons to do so.

NB. Do not use the distinct construct as suggested above. This only requires an extra sort.