SQL Server Performance Forum – Threads Archive
Poor performance with ADO stored proc. callI’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.
True using Stored Procs is more effecient then dynamic SQL, have you recompiled the stored procedure? The IF NOT EXIST could be the cause but Im not sure can you post your code (stored proc)?
It is due to the NOT EXISTS. I removed it and it ran as quickly as before. I can’t afford to double the execution time of this SP, so I’ll have to find a way of trapping duplicates and logging them without having the SP fall over. I added a unique key for the SELECT clause columns and now the SP spits out errors on the duplicates. My DTS ActiveX Script (VBScript) has logging using the FSO object, so I can just write out the errors there.
What I did is created one sp for my whole loading process into staging and fact table. It includes one @action paramter that I call using seperate SQL Task steps i.e Missing Dimension keys, duplicate id’s and so forth. I use both logging and keep complete records of exceptions during the load process into seperate tables.