Inconsistent Performance of Insert

I am experiencing inconsistent performance when running
insert statements.
Our application automatically generates and sequentially
executes SQL statements that build a results table. After
loading data and running this process, all statements
(about 125 in total) will execute in about 10 minutes. If
it is necessary to rerun the statements I truncate the
results table and rerun the job. In some cases this
second job runs in 10 minutes, and in other cases it runs
6 hours. In particular, one statement takes an inordinate
amount of time.
I've used PROFILER (although I'm not too experienced in
this) and can see that when statements are running
normally the log file updates in the 1000+ per second
range, but with the problematic statement updates are in
the 10 per second range. These updates seem to appear
after the commit statement is executed.
In various combinations I've tried restarting SQLServer,
updating statistics, defragging the hard drive, using
query optimizer, all with no luck.
Any ideas?
Here's one of the statements that seems to get hung up:
Table AV has 4 columns including a Record ID (RID), a
record name (FID) and a record value (Value). This table
will ultimately have approximately 4 million rows at the
completion of the job.
Table A contains a Record ID (RID) and a Process ID
(PID). This table has approx. 100,000 records.
INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
(ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
(ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
(ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
= ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
= 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID = ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
276, 277, 278, 279, 280, 281))
I recognize that the same table AV is being joined
multiple times, but it's my understanding that this should
not be a problem.
Thanks for any guidance on this.Not much to go on but have you looked to see if you are being blocked when
this is happening? How about disk and cpu queues?
--
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:16c501c4a62e$f11bbd90$a601280a@.phx.gbl...
> I am experiencing inconsistent performance when running
> insert statements.
> Our application automatically generates and sequentially
> executes SQL statements that build a results table. After
> loading data and running this process, all statements
> (about 125 in total) will execute in about 10 minutes. If
> it is necessary to rerun the statements I truncate the
> results table and rerun the job. In some cases this
> second job runs in 10 minutes, and in other cases it runs
> 6 hours. In particular, one statement takes an inordinate
> amount of time.
> I've used PROFILER (although I'm not too experienced in
> this) and can see that when statements are running
> normally the log file updates in the 1000+ per second
> range, but with the problematic statement updates are in
> the 10 per second range. These updates seem to appear
> after the commit statement is executed.
> In various combinations I've tried restarting SQLServer,
> updating statistics, defragging the hard drive, using
> query optimizer, all with no luck.
> Any ideas?
> Here's one of the statements that seems to get hung up:
> Table AV has 4 columns including a Record ID (RID), a
> record name (FID) and a record value (Value). This table
> will ultimately have approximately 4 million rows at the
> completion of the job.
> Table A contains a Record ID (RID) and a Process ID
> (PID). This table has approx. 100,000 records.
> INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
> (ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
> (ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
> (ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
> = ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
> ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
> JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
> = 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
> ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID => ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
> 276, 277, 278, 279, 280, 281))
> I recognize that the same table AV is being joined
> multiple times, but it's my understanding that this should
> not be a problem.
> Thanks for any guidance on this.
>