Re: Inconsistent Performance of Insert
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 09/29/04
- Next message: Andrew J. Kelly: "Re: unable to allocate 'min server memory'"
- Previous message: Andrew J. Kelly: "Re: Shrink problem"
- In reply to: Chuck Hardy: "Inconsistent Performance of Insert"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 29 Sep 2004 18:21:54 -0400
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. >
- Next message: Andrew J. Kelly: "Re: unable to allocate 'min server memory'"
- Previous message: Andrew J. Kelly: "Re: Shrink problem"
- In reply to: Chuck Hardy: "Inconsistent Performance of Insert"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
