Poor performance with ADO stored proc. call

I've got a SQL Server 2000 DTS package that has an ActiveX Script task written in VBScript that inserts rows into a table. I've tested it with
1) an explicit "INSERT INTO " command with objCommand.CommandType = adCmdText
2) a stored proc. call with objCommand.CommandType = adCmdStoredProc and parameter objects
3) a stored proc. call in a text string with objCommand.CommandType = adCmdText (e.g., ins_tblStage_TestResult5 'CAN001',123,etc.)

Methods 2 and 3 above take approx. 5 times longer than method 1. I've programmed a lot of ADO in VB6 and my experience there was that ADO calls to stored procs. with parameter objects is very efficient.

Is there a performance issue with calling a stored proc. with ADO from VBScript? My stored proc. does a IF NOT EXISTS prior to inserting the row. I create the command and connection objects outside my insert loop, and create the objCommand.CommandText inside the insert loop.

 

[jemminger]

well, if your sp has to check IF NOT EXISTS for each insert, this could be time consuming...


=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }