Is it possible that any of your tables or indexes are heavily fragmented ?

How large are the tables you are inserting to ?

lbdw (8/6/2010)

---

Well, let me list some more stats:

average IO write ops 4~47 / sec, avg 188434 bytes/ sec (running raid thus using averages) , half way through it starts to...

lbdw (8/6/2010)

---

Thanks for the feedback guys, but unfortunately I only upload 400K entries at a time never more using 1 Transaction and commit at end.

Also Isolation level are set to...

Leo.Miller (8/5/2010)

---

I recommend you build in some sort of batch control to try load about 100000 rows at a time and then do a commit. You would probably need to...

Chad Crawford (3/6/2009)

---

Yes, that is what I would like to do - have it open in the SSMS that is already running. Right now it opens a new...

lbdw (8/4/2010)

---

I checked the script durations and went like this:

The first 10min duration 0, at 10min it drops to 35, at 20min to 110 and after 30min it goes to...

Glad I could help! 😎

Just of curiosity, could you please post the actual query plans of Query1 and Query2 after the statistics update ?

This sounds a lot like another thread some time ago. In that case the problem was caused by having ALLOW_ROW_LOCKS=OFF in one of the indexes in the affected table.

See http://www.sqlservercentral.com/Forums/FindPost944539.aspx

I made some tests and they confirm my suspicions:

* It is very important to formulate the CHECK condition on the individual tables appropriately.

* If you have correct statistics it...

DB_Newbie2007 (8/3/2010)

---

I understand that having the DB objects, query plans, etc. help in diagnosis, but I was interested AT A HIGH LEVEL in knowing why there was such a dramatic...

lb_henning (8/3/2010)

---

Well let’s elaborate the situation:

Technologies used:

Server is a Quad Xeon 2.45Ghz (or something like that) 16Gb Ram, 1.5Tb total storage divided into 4 drives 1 for Windows, 1 for...

DB_Newbie2007 (8/3/2010)

---

Stefan,

1) We do have constraints on the tables... here is an example:

ALTER TABLE [dbo].[Jan10] WITH NOCHECK ADD CONSTRAINT [CK_Jan10] CHECK ((datepart(month,[InvoiceDate])=(1) AND datepart(year,[InvoiceDate])=(2010)))

GO

ALTER TABLE...

What is the actual execution times of the two query plans you posted ?

The estimated cost for Query1 is 109.4 while the estimated cost for Query2 is 46.3

With these numbers...

DB_Newbie2007 (8/3/2010)

---

Grant,

Unfortunately, "ChargeType" is actually my name for a bunch of columns added together in this particular table to list a charge. The tables (dbo.Jan10, dbo.Feb10, etc) are wide...

----- Query 1: This query takes 24 minutes

SELECT ID.InvoiceNumber, ID.InvoiceDate, ID.CustomerNumber, SUM(ID.ItemCharge) as SumCharges

FROM dbo.VIEW_InvoiceData ID

INNER JOIN dbo.InvSummary IS

ON ID.InvoiceNumber = RIGHT(RTRIM(InvoiceNumber),12)

AND ID.InvoiceDate = IS.InvoiceDate

WHERE IS.CustomerNumber = '12345678'

AND ID.InvoiceChargeItem =...