是否可以从 Sybase 函数返回数字列表？

发布于 2024-09-02 00:59:54 字数 773 浏览 9 评论 0原文

我正在尝试克服一个非常严重的性能问题，即 Sybase 拒绝在大型表上使用主键索引，因为其中一个必需字段是通过另一个表间接指定的 - 或者换句话说；

SELECT ... FROM BIGTABLE WHERE KFIELD = 123

以毫秒为单位运行，但

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

需要 30 - 40 秒。

我已经通过使用基本上可以让我做到这一点的函数来解决第一个问题；

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

这也以毫秒为单位运行。

但问题是，这种方法仅在 MYFUNCT 返回单个值时才有效，但在某些情况下它可能会返回 2 或 3 个值。

我知道 SQL

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

也会以毫秒为单位返回，所以我想要一个函数返回可能值的列表，而不仅仅是单个值 - 这可能吗？

遗憾的是，该应用程序正在 Sybase ASA 9 上运行。是的，我知道它很旧并且计划进行刷新，但现在我对此无能为力，因此我需要适用于此版本数据库的逻辑。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时光与爱终年不遇 2024-09-09 00:59:54

使用临时表来存储您的号码怎么样？所以你的 sql 看起来像这样：

    select kfield into #tmpKfield 
    from littleTable 
    where UNIQUEID = 'STRINGREPOF123'

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

    drop table #tmpKfield
    go

这就是我尝试解决你的问题的方式。

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.

回复收藏 0 原文

~没有更多了~