First Attempt and Lookup is Too Slow

[DaveRolph]

This is my first attempt at a VBScript within a DTS package but it is far too slow.

What I am trying to do is insert around 3Million records and Lookup a couple of values at the same time.

My thinking was that this would be faster but it does not seem to be the case.

I have two lookups the first is called GetNewPatNo and looks like this...

SELECT     Patient_Number
FROM         PATIENT_NUMBERS
WHERE     (Old_Code = ?) AND (Conversion_Database = ?)

The second is called GetCentreCode and looks like this ...

SELECT     Centre_Code
FROM         PATIENT_NUMBERS
WHERE     (Old_Code = ?) AND (Conversion_Database = ?)

The following is my VBScript within the same DTS Package

Dim tCounter
Dim tUniqueID
Dim tNewPatNo
Dim tCentreCode
Dim tDatabaseNo


Function Main()
	tDatabaseNo=3
	tCounter = tCounter + 1 
	tUniqueID = Right("0000000" & tCounter, 8) 
	tNewPatNo = DTSLookups("GetNewPatNo").Execute(DTSSource("User.Ref"),tDatabaseNo)
	tCentreCode = DTSLookups("GetCentreCode").Execute(DTSSource("User.Ref"),tDatabaseNo)

	DTSDestination("Patient_Number") =tNewPatNo 
	DTSDestination("Patient_No_Full") =tCentreCode&tNewPatNo
	DTSDestination("Old_Pat_Number") = DTSSource("User.Ref")
	DTSDestination("Details") = DTSSource("NOTE-DESC")
	DTSDestination("Entered_By") = DTSSource("GN-AUTHOR")
	DTSDestination("Entered_Date") = DTSSource("GN-DATE")
	DTSDestination("Gen_Notes_ID") = tUniqueID
	DTSDestination("From_DB") = tDatabaseNo
	Main = DTSTransformStat_OK
End Function

The question is, should this be so slow ?, the reason I ask is that I run some tests without the lookup and it was much quicker, it even seemed much quicker doing an update of the two columns AFTER the insert.

Thanks for any advice

Dave

 

[JBaileys]

Do you indexes defined on Old_Code AND Conversion_Database ?

If not, you will be excuting a full table scan each time the query is run. The larger the table - the larger the "pain". Secondly, I would run the index query wizard or execute plan to recognize the performance hits.

-JB

 

[DaveRolph]

Thanks for the reply JB

In fact I added a few more records and this actually crashes - I have no other problems but.

After 1000 rows (and about 3-4 minutes) I get
The number of failing rows exceeds the maximum specified
Error Code:0
Error Source=Microsoft OLD DB Provider for SQL Server
Error Description[DBNETLIB]pConnectionOpen(Connect()).[SQL Server does not exist of access denied.

Error on line 17
[DBNETLIB][COnnectionOpen(Connect()).]SQL Server does not exist of access denied.

I am sure I am doing something wrong with the connection for my lookup

BTW OLD_Code is indexed but DB is not