Re: so slow DDQ update statement...

From: Narayana Vyas Kondreddi (answer_me_at_hotmail.com)
Date: 03/01/05 

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" <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
>
>
>
>

Relevant Pages

  • Re: Stored procedure question
    ... Vyas, MVP (SQL Server) ... > where UserID = @UserID ... > Vyas, MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: delete a system sp
    ... Vyas, MVP (SQL Server) ... >> how to rename or delete a stored procedure .. ...
    (microsoft.public.sqlserver.security)
  • Re: Question about check contraints
    ... that CHECK constraint is looking for. ... Vyas, MVP (SQL Server) ... > Vyas, MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Lost Permissions after Detach/Attach
    ... Vyas, MVP ... > Did you move those databases to a different machine/different domain? ... > Vyas, MVP (SQL Server) ...
    (microsoft.public.sqlserver.server)
  • Re: Query Analyzer Server Selection
    ... Vyas, MVP (SQL Server) ... SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ ... > Vyas, MVP ...
    (microsoft.public.sqlserver.server)
Loading