Re: so slow DDQ update statement...
From: Narayana Vyas Kondreddi (answer_me_at_hotmail.com)
Date: Tue, 1 Mar 2005 13:44:24 -0000
Check the execution plan of the update. See if there's a table/index scan?
-- HTH, Vyas, MVP (SQL Server) SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ "Jéjé" <willgart_A_@hotmail_A_.com> wrote in message news:%23MdIZQmHFHA.560@TK2MSFTNGP12.phx.gbl... I know this is not allways the best solution. But this give me a lot of flexibility in my code. In my case I have a simple where clause based on the primary key of my target table: Create procedure sp_UpdMyTable(@newCol1 vrachar(50), @newCol2 varchar(50)...) as update MyTable set col 1 = @newCol1, col 2 = @newCol2, ... Where MyTableID = @UpdateID and from what I have tested the procedure is slow outside DTS too! But I have no idea of the reason. "Narayana Vyas Kondreddi" <email@example.com> wrote in message news:uZ3T0hlHFHA.3500@TK2MSFTNGP14.phx.gbl... > DDQs are going to be slow, as they work on one row at a time. Further, the > speed of your update depends on the WHERE clause also. > > I'd actually load the input data into a temp table, and run set based > INSERT/UPDATE/DELETE commands to work out the changes. I have examples at: > http://vyaskn.tripod.com/sql_server_dts_best_practices.htm > -- > HTH, > Vyas, MVP (SQL Server) > SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > > > "Jéjé" <willgart_A_@hotmail_A_.com> wrote in message > news:%232aiB3bHFHA.3332@TK2MSFTNGP15.phx.gbl... > Hi, > > I have a dts package which is very slow to update a table. > I'm using a DDQ where in input I send some rows to update, and I call a > storedprocedure to do my update. > > 1 update takes 2 to 3 seconds!!!! (i can have more then 1000 updates) > > * I have destroyed my indexes > * I have disabled the checks of all foreign keys to my target table > > (before these actions, the same update take 10-12 seconds!!!) > > what can I do??? > why its so slow? > > thanks > > Jerome > > > >