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:
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)