August 5, 2003 at 7:25 pm
I need to find a method for inserting records into a table which uses the identity value of a record inserted into another table, with the 2nd table insertion dependent on the existence of a record in a 33rd table.
I start with a DTS package which inserts records into the main table which has an identity ID colum. The insert can be from 1 to (max seen so far on one import) 20,000 rows. This table is named Prospect, and has a an int identity field ProspectID. After all rows are inserted, I need to determine if any of the ProspectIDs inserted should be placed in a hold table.
The table used to determine which records should be on hold is ExlusionList,
[HoldType] [varchar](5) ,
[IdField] [varchar] (15),
[FieldValue] [varchar] (25),
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL
HoldType is the type of hold and includes CUSTID, PHONE, CustAcctID;
IdField is the field in the Prospect table which will be used to match to this table (CustomerID, CrAttgPhone, CustCredAct ..).
FieldValue is the value of this field which should be on hold.
For example, CustomerID = 100 should not be contacted betweeen StartDate and EndDate, so, if a new ProspectId is added to Prospect where CustomerID on Prospect = 100, a record should also be written to ProspectHold with this ProspectId value.
The ProspectHold table includes:
[hold_id] [int] IDENTITY,
[prospectid] [int] NOT NULL ,
[hold_type] [varchar] (5),
[start_dt] [datetime] NULL ,
[end_dt] [datetime]
I have tried a DTS package, extracting all rows just inserted, joining to the ExclusionList table, then inserting into ProspectHold, but the performance is too slow.
I've tried various flavors of selecting (Hold_Type in select to get desc):
SELECT prospectid,
holdtype,
holdtype_desc,
fieldvalue,
startdate,
enddate
FROM Prospect,
ExclusionList,
Hold_Type
WHERE Prospect.CustCredAct = ExclusionList.FieldValue
AND ExclusionList.holdtype = Hold_Type.hold_type
AND ExclusionList.IdField = 'CustCredAct'
AND Prospect.prospectid NOT IN (SELECT prospectid FROM Prospect_Hold)
....
with UNIONS to handle the four types of IdFields, but this seems overly complex to do what needs to be done and also takes a while to process (the early morning process for the entire insert includes numerous host extract files and may result in over 100,000 rows being inserted into the Prospect table).
I'm over my head on this one and would appreciate any assistance in constructing a process which works - with speed taking a back seat to works - but performance is also a major issue.
Thanks in advance for any suggestions you may have on resolving this.
August 6, 2003 at 7:08 am
Sounds like a index problem, as in lack of useful indices. You don't mention any in your post.
Also, I'm not sure, but it appears that ExclusionList might not be normalized. Are rows in this table related to one another? If so, you may have a design problem.
Also, if there is a dependancy between tables ('2nd table insertion dependent on the existence of a record in a 33rd table'), there should be a foreign key declared on the 2nd table.
How slow is too slow? Inserting 100 000 rows can be done quickly, but selecting them from 4 tables can take time, depending how many rows are in those tables....
P
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply