gregory.anderson (4/14/2010)

---

PaulB-TheOneAndOnly (4/14/2010)

---

gregory.anderson (4/14/2010)

---

Ok, I modified the stored proc to add the @Today = GetDate() right before it does each insert into PWDPermit so I could gather some real timings,...

lmu92 (4/14/2010)

---

At a first glance it looks like it's not required to do it row by row...

Beside the sample data I already asked for please provide the beginning of the...

PaulB-TheOneAndOnly (4/14/2010)

---

gregory.anderson (4/14/2010)

---

Ok, I modified the stored proc to add the @Today = GetDate() right before it does each insert into PWDPermit so I could gather some real timings, right...

Ok, I modified the stored proc to add the @Today = GetDate() right before it does each insert into PWDPermit so I could gather some real timings, right now, (still...

I just added the unique index on the primary key, and unless I was way off on my previous tests, this appears to be around 5 times as fast...

Edit: Ok,...

Initial test, without gathering actual statistics, it's about half as fast. The actual execution plan shows the load on...

Thanks, I'll try that.

PaulB-TheOneAndOnly (4/14/2010)

---

gregory.anderson (4/14/2010)

---

After trying Elliot's suggesting about displaying the actual execution plan, it appears as though the most time consuming/resource intensive portions of the proc are in "Clustered Index Update"...

Ok, I'll try and post the sql stuff...this is going to be long

Staging Table

CREATE TABLE [dbo].[PWDPermitConversionData](

[PWDPermitConversionDataID] [int] IDENTITY(1,1) NOT NULL,

[PermitPrefix] [varchar](1) NOT NULL,

[PermitNumber] [varchar](6) NOT NULL,

[CustomerNumber] [int] NULL,

[SSN] [varchar](9) NOT...

lmu92 (4/14/2010)

---

Is it possible...

By...

The conversion from the mainframe data (in a VSAM file) inserted into the SQL staging table only takes a few minutes. Then I add some indexes to my staging table,...

I got it. If @ErrorMessage was null, it would never build the dynamic sql...it would just set it to null also.

Also, when I output the @ErrorMessage variable, it comes out as a space also.

GilaMonster (1/19/2010)

---

Scans of the clustered index are table scans. They either indicate that a nonclustered index was not selective enough to be used, or there are nonclustered indexes missing.

How did...