Board index » delphi » SQL Server 7.0 : Update Speed Problem

SQL Server 7.0 : Update Speed Problem

Hi,

I am migrating my DB from Interbase 5.6 to SQL 7 SP1.

Everything seems fine but the following update statement takes quite a long
to execute:

  UPDATE TRADE
  SET Trade_Size = @DNEWSIZE
  WHERE Trade_No = @NTRADENO;

The TRADE table contains more than 200,000 rows and Trade_NO is the Primary
Key (clustered).
The same query under Interbase takes almost nothing.

Any suggestion?

I had SQL Tools to perform a full index analysis and it says everything is
fine.
This is a big problem at the moment cause I need to call this procedure (it
is actually a Stored Procedure) several times with different parameters.

Thanks for any help.

Andrea

 

Re:SQL Server 7.0 : Update Speed Problem


Quote
Andrea Onorati wrote in message <398ac353_2@dnews>...

>I am migrating my DB from Interbase 5.6 to SQL 7 SP1.

>Everything seems fine but the following update statement takes quite a long
>to execute:

>  UPDATE TRADE
>  SET Trade_Size = @DNEWSIZE
>  WHERE Trade_No = @NTRADENO;

>The TRADE table contains more than 200,000 rows and Trade_NO is the Primary
>Key (clustered).
>The same query under Interbase takes almost nothing.

An update statement can't really be any simpler than this, so I don't see
any way to speed it up. Interbase is known to be faster than MSSQL for
updates and inserts.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
Helicopters can't fly; they're just so ugly the earth repels them.

Re:SQL Server 7.0 : Update Speed Problem


Quote
> The TRADE table contains more than 200,000 rows and Trade_NO is the
Primary
> Key (clustered).
> The same query under Interbase takes almost nothing.

How long is long? As Wayne says, SQL server is slower to insert, but faster
to select from. If you let us know the timing then maybe we can say whether
we think there is a problem.

Dean
Pontefract, England

Re:SQL Server 7.0 : Update Speed Problem


I've been making many test...and what I discovered is pretty strange, not
definitely the kind of behaviour I was expeting from my DB , anyway:

If I use the following sintax:

UPDATE TRADE SET TRADE_SIZE = 7.0
 WHERE trade_no = 500579

The duration (from SQL Server Profiler) is something around 1200
milliseconds.

If, instead, I use the following sintax,

UPDATE TRADE SET TRADE_SIZE = 7.0
 WHERE trade_no =
(select t.trade_no from trade t
with ( index (0) )
 where t.trade_no = 500579)

The duration is about 200 milliseconds.

As trade_no is the primary clustered index, I don't understand why I should
specify the index to be used.

Using Index(1) doesn't change much the performances.

Now my point is, do I have to specify the index to be used in each query I
use in my programs ?

After that, I have "embedded" the above SQL Statements into two different
stored procedures...

CREATE PROCEDURE SP_CHANGE_TRADE_SIZE
  @NTRADENO INTEGER,
  @DNEWSIZE NUMERIC(15, 2)
  AS
BEGIN
  UPDATE TRADE
  SET Trade_Size = @DNEWSIZE
  WHERE Trade_No = @NTRADENO;
END

and

CREATE PROCEDURE SP_CHANGE_TRADE_SIZE2
  @NTRADENO INTEGER,
  @DNEWSIZE NUMERIC(15, 2)
  AS
BEGIN
  UPDATE TRADE
  SET Trade_Size = @DNEWSIZE
  WHERE Trade_No =
  (SELECT t.trade_no from TRADE t
   WITH ( INDEX (0) )
   WHERE t.trade_no = @NTRADENO);
END

and then doing
EXEC SP_CHANGE_TRADE_SIZE @NTRADENO = 500579, @DNEWSIZE = 7.0
and
EXEC SP_CHANGE_TRADE_SIZE2 @NTRADENO = 500579, @DNEWSIZE = 7.0

They are both pretty slow..they both take around 1200 ms each.

Why a stored procedure is slower and a "normal" SQL statement, shouldn't be
the opposite???

Thanks again for your help

Other Threads