×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

thank for your reply, but that's not the point

the problem is on the join on logic table inserted and deleted. (they are actually log) which is extremely expensive.

I got some help from my colleague and resolve the problem, here is the solution --
since the trigger is for "after update" (default in ase), instead of joining on inserted and deleted to compare the column value change, I changed to use joining deleted with the main table tblA (force using clustered index).

!= will not cause full table scan in this case is becaused the sybase optimizer already choose the clustered index on id and the columns to comare are not index columns.

Thanks.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / sybase高手请进
    table tbl A (around 10M rows)
    {
    id (unique clustered index on it)
    col1,
    col2,
    col3,
    col4,
    col5
    )

    table restrictTblA (around 50k rows)
    {
    id (unique clustered index on it)
    }

    To define a trigger (for update) on tblA to prevent col1 and col3 be updated for those ids in restrictTblA.

    I am doing something like
    if exists (
    select 1
    from
    inserted a,
    deleted b
    where
    a.id = b.id and
    exists ( select 1 from restritTblA where id = a.id ) and
    (
    a.col1 != b.col1 or
    a.col3 != b.col3
    )
    )
    then
    raiserror xxxxxx, "xxxxxxxxxxxxxxxxxxxx"
    rollback transaction
    return
    end

    I tested it using a update statment for 1000 rows without change on col1 and col3, the trigger takes 20 seconds!!!! I mimic the same sql above (just replace the logic tables inserted and deleted with two temp tables) and run manully, the if check just takes 1 second, is the join on inserted and deleted so expensive? anyway to tune it?
    • This is fundamental principle of relational database, not just sybase. Try not "table scan"
      You can see how it works by showing the plan, "!=" is very dangerous in SQL identifier as well, try to avoid it.

      You have to tune the command for sure.
      • thank for your reply, but that's not the point
        the problem is on the join on logic table inserted and deleted. (they are actually log) which is extremely expensive.

        I got some help from my colleague and resolve the problem, here is the solution --
        since the trigger is for "after update" (default in ase), instead of joining on inserted and deleted to compare the column value change, I changed to use joining deleted with the main table tblA (force using clustered index).

        != will not cause full table scan in this case is becaused the sybase optimizer already choose the clustered index on id and the columns to comare are not index columns.

        Thanks.