2

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.

joce
  • 9,624
  • 19
  • 56
  • 74
ps_rs4
  • 21
  • 2
  • what are the indicies on the tables , what does showplan show and what is the schema of these fields? – mmmmmm May 14 '10 at 08:38
  • It's a little involved. First off BIGTABLE is actually a view, I'm new to Sybase so I don't know the exact terminology but it works effectively as a partitioned table spread across several physical tables (the implementation might be more implicit than that). The underlying table(s) has a primary index of KFIELD, DATEVALUE which I am actually passing into the query (query was oversimplified). The showplan shows that its doing a full table scan rather than using the primary key which is the problem. – ps_rs4 May 14 '10 at 17:47
  • There is a second index that is based on just DATEVALUE which is actually what the optimizer is picking. This works for several partitions as none or only a small number of records are retrieved for that interval (although it's still not as efficient as using the primary key and hence I'm still confused). But once it gets to the table that has the majority of the records for the selected time interval it is forced to do a full table scan. It seems that the optimizer is jumping the gun a little trying to pre-fetch the data table but I really don't have enough experience to say for sure. – ps_rs4 May 14 '10 at 17:55
  • Unfortunately I only know ASE but I would start at http://www.sybase.com/detail?id=1023801 – mmmmmm May 17 '10 at 16:49

1 Answers1

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.

JonnyWizz
  • 170
  • 1
  • 2
  • 8