CODEWITHSUNDEEP
sqldatabasequery-optimizationsap-ase
Javier Rivera
Javier Rivera

Reputation: 89

Slow SQL query on Sybase

I'm trying to extract information from a database. Sybase ASE 16 (not sure of the SP thouogh). I don't have direct access to the database myself so I have to ask a collegue to run the code to extract the information that I then use.

First, the following code is executed:

create table AAA (
    Operacion varchar(10) null,
    Actual smallint null,
    Castigado smallint null,
    RentasVar smallint null)
go

Table Indice is populated from a text file using a bcp. Actual, Castigado and RentasVar are either 0 or 1.

Then this code:

create view XXX as
    select Operacion, cast(convert(char(8), FecAplica, 112) as int) as FecAplica,
            IdPolizaSeg, SaldoInsSeg, CapitalSeg, InteresSeg,
            IvaSeg, MontoSeg, SegVenc
        from SEG_Vencim
        where Operacion in (
            select Operacion from AAA 
                where Actual=1)
go

create view YYY as
    select Operacion, IdTipoMov, DescMov, IdMoneda, StatuMov,
            cast(convert(char(8), FecMov, 112) as int) as FecMov,
            cast(convert(char(8), FecVencMov, 112) as int) as FecVencMov,
            cast(convert(char(8), FecPago, 112) as int) as FecPago,
            MontoMov, IvaMov, TotalMov, MontoDelPago
        from Movimientos
        where (Operacion in (
            select Operacion from AAA where Castigado=1))
                or (Operacion in (
                    select Operacion from AAA where Actual=1)
                        and (FecPago=null or FecPago>dateadd(dd, -30, current_date())))
go

Field Operacion in Movimientos is varchar(10). I cannot modify this table.

A bcp instruction is issued to copy XXX's content into a text file. The task is completed a couple seconds after, having written a file of aprox. 140 MB. A bcp instruction is issued to copy YYY's content into a text file. The task is completed after almost 7 hours, having written a file of aprox. 70 MB.

So my problem is that the second bcp instruction takes too long. I read that functions on where clause can be inefficient so I asked my colleague to try again using

and (FecPago=null or FecPago>='2020-09-30'))

instead of

and (FecPago=null or FecPago>dateadd(dd, -30, current_date())))

but it didn't help.

AAA has between 80,000 and 90,000 records. Would putting an index on it help? I read that unnesting queries might help, could that help in my case? if so, why does the nested query in the first query work?

I cannot run tests on my own and don't have access to the execution plans. I realize it probably won't be possible to give me advice on this without me providing additional information.

Upvotes: 1

Views: 1046

Answers (1)

deroby
deroby

Reputation: 6002

I don't have much experience with Sybase but in MSSQL (which shares it's ancestry with Sybase) ORs tend to be performance killers. Changing those to UNION constructions as markp-fuso suggests often is a good idea.

That said, I wonder what moving the OR entirely into the sub-select would do:

create view YYY as
    select <your fields>
     from Movimientos
     where Operacion in (select Operacion 
                           from AAA 
                          where ( Castigado = 1)
                             or ( (Actual = 1 and (FecPago=null or FecPago > dateadd(dd, -30, current_date()))))
                        )

Not sure what the optimizer might do to the above. Therefore I wonder what happens if we force the operations on AAA to 'materialize' first... Could you give below a try?

create view YYY as
    select <your fields>
     from Movimientos m
     JOIN (SELECT DISTINCT Operacion 
             from AAA 
            where ( Castigado = 1)
               or ( (Actual = 1 and (FecPago=null or FecPago > dateadd(dd, -30, current_date()))))
          ) d
      ON d.Operacion = m.Operacion

Good luck.

PS: apart from using UNION to get rid of the ORs you can also try to use WHERE EXISTS() instead of the IN() construction.

Upvotes: 1

Related Questions