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/ [quoted text, click to view] "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.
[quoted text, click to view] "Narayana Vyas Kondreddi" <answer_me@hotmail.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
>
>
>
>
