问题描述:

I have a query with sql like this:

SELECT * FROM table WHERE field IN (a,b,c,d,e,f,g,h....)

here the list size in the in() clause might be as many as 2000, and the table is a view created in SybaseIQ. as I know, I cannot create index in the view.

So are there any other possible method to optimize this query?

What I have find through google by now:

  1. Use union all instead of in, which would need a very long list of union all(at most 2000)
  2. Insert the list in the in clause into a table and try join, I cannot do this yet since the sybase IQ database is not owned by me, I can only do query.

Thanks in advance.

网友答案:

If you have enough permission, try creating Materialized Views on top of the view you are using or modifying the existing view to be a materialized view itself. Materialized views, in simple words, are hybrid between tables and views. With materialized view you can create indexes on columns, cache the results and not compute everytime. You might want to check limitations on the Materialized View over normal views/tables.

Sybase Documentation for Materialized View

网友答案:

Assuming it is Sybase and you can create temp tables then something along these lines should help performance:

CREATE TABLE #In_List
    ([Code] varchar(1))
;

INSERT INTO #In_List
    ([Code])
VALUES
    ('d'),
    ('e'),
    ('k'),
    ('p'),
    ('r')
;

CREATE CLUSTERED INDEX IDX_In_List_Code ON  #In_List([Code])
;

select
*
from My_Table
inner join #In_List on my_table.code = #in_list.code
;

NB: This has only been trialled on SQL Server 2008 (using this SQLFiddle)

相关阅读:
Top