Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Unanswered: Serious Performance Problem

    All,

    I have a major Sybase performance problem. I am writing a Crystal Report and coded some views. Perf sucked on the Sybase so exported data into MySQL on Laptop (700MHz with 139MB) and recreated the views in MySQL.

    Views all run in under 15 minutes on the Laptop. When running in SyBase on the Server (2.8GHz with 4GB) the Views take over 2 hours each to run.

    Optimization is obviously screwed and do not know what to do.

    Please help!!!!

    DBS4M

  2. #2
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Code

    All,

    Code works fine without count as in:
    Code:
    select distinct dat_action_code as Act_CD,
        dat_user_id as UID,
        (select distinct acc_desc from
          cds.acc where
          acc_code = dat_action_code) as Act_Desc,
        (select distinct usr_name from
          cds.usr where
          usr_code = dat_user_id group by usr_name) as UserName from
        cds.dat where
        UserName <> ' ' and
        dat_action_code <> ' ' and
        datediff(day,dat_trx_date_o,NOW(*)) <= 14 and
        dat_dbr_no > '0000000' order by
        UserName asc,
        Act_CD asc
    Runs in under 2 minutes also in a view with field reorder
    Code:
    select distinct UserName,Act_CD,Act_Desc
    from desk_perf_AUS
    same result, under 2 minutes.

    When the count is added as follows:
    Code:
    select distinct dat_action_code as Act_CD,
        dat_user_id as UID,
        (select distinct acc_desc from
          cds.acc where
          acc_code = dat_action_code) as Act_Desc,
        (select count(cds.dat.DAT_ACTION_CODE) from
          cds.dat where
          cds.dat.DAT_USER_ID = UID and
          cds.dat.DAT_ACTION_CODE = Act_CD and
          datediff(day,dat_trx_date_o,NOW(*)) < 14) as Cnt,
        (select distinct usr_name from
          cds.usr where
          usr_code = UID) as UserName from
        cds.dat where
        UserName <> ' ' and
        dat_action_code <> ' ' and
        datediff(day,dat_trx_date_o,NOW(*)) < 14 and
        dat_dbr_no > '0000000' order by
        UserName asc,
        Act_CD asc
    the performance goes south.

    Don't understand why, especially when MySQL on slow machine handles it in under 15 minutes.

    DBS4M

    PS. Had to add the date criteria for subquery. MySQL transfers the criteria of the main query to the subquery, SyBase does not.
    Last edited by dbsupport4me; 02-28-08 at 18:37.

  3. #3
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Abandoned

    Good thing I abandoned this project.

    DBS4M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •