Question

I m trying to overcome a very serious performance issue in which Sybase refuses to use the primary key index on a large table because one of the required fields is specified indirectly through another table - or, in other words;

SELECT ... FROM BIGTABLE WHERE KFIELD = 123

runs in ms but

SELECT ... FROM BIGTABLE, LTLTBL WHERE KFIELD = LTLTBL.LOOKUP 
   AND LTLTBL.UNIQUEID =  STRINGREPOF123

takes 30 - 40 seconds.

I ve managed to work around this first problem by using a function that basically lets me do this;

SELECT ... FROM BIGTABLE WHERE KFIELD = MYFUNC( STRINGREPOF123 )

which also runs in ms.

The problem, however, is that this approach only works when there is a single value returned by MYFUNCT but I have some cases where it may return 2 or 3 values.

I know that the SQL

SELECT ... FROM BIGTABLE WHERE KFIELD IN (123,456,789)

also returns in milliseconds so I d like to have a function that returns a list of possible values rather than just a single one - is this possible?

Sadly the application is running on Sybase ASA 9. Yes I know it is old and is scheduled to be refreshed but there s nothing I can do about it now so I need logic that will work with this version of the DB.

Answer 1

What about using a temporary table to store your numbers? So your sql would look like this:

    select kfield into #tmpKfield 
    from littleTable 
    where UNIQUEID =  STRINGREPOF123 

    select * from bigTable 
    where kfield in (select kfield from #tmpKfield)
    go

    drop table #tmpKfield
    go

That is how I try to solve your issue.

友情链接