Is it possible to return a list of numbers from a Sybase function?
I'm trying to overcome a very serious performance issue in which Sybase refuses to use a 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
works in ms but
SELECT ... FROM BIGTABLE, LTLTBL WHERE KFIELD = LTLTBL.LOOKUP
AND LTLTBL.UNIQUEID = 'STRINGREPOF123'
takes 30-40 seconds.
I managed to get around this first problem by using a function that basically allows me to do this;
SELECT ... FROM BIGTABLE WHERE KFIELD = MYFUNC('STRINGREPOF123')
which also works in ms.
The problem, however, is that this approach only works when there is one value returned MYFUNCT
, but I have some cases where it might return 2 or 3 values.
I know SQL
SELECT ... FROM BIGTABLE WHERE KFIELD IN (123,456,789)
also returns in milliseconds, so I would like to have a function that returns a list of possible values, not just one - is that possible?
Unfortunately the application runs on Sybase ASA 9. Yes, I know that it is old and needs to be updated, but now I cannot do anything, so I need logic that will work with this version of the database.
a source to share
How do I use a temporary table to store your numbers? So your sql will look like:
select kfield into #tmpKfield
from littleTable
where UNIQUEID = 'STRINGREPOF123'
select * from bigTable
where kfield in (select kfield from #tmpKfield)
go
drop table #tmpKfield
go
This is how I am trying to solve your problem.
a source to share