Optimizing a select query

Hi everyone, Here is a question for you guys. I am facing a problem with a simple select query running toooo slow. Before I proceed, let me explain that I am facing this problem in SQL Server not in Informix. I know the die-hard Informix fans will say "Well... there is your answer ", but I do need to get this resolved, more so since Informix seems to perform the same kind of query quite well. ( of course !! ) Without more ado, here is the query "select a.* from tab1 a, tab2 b where a.col1 = b.col1 and b.col2 = constant" INDEX info: unique index on tab1(col1) unique index on tab2(col1) unique index on tab2(col2) YES, SQL Server does let you have multiple unique indexes on a table. My problem here is that it always perform a table scan on tab1, even though it does have an index on col1. It does use the index on tab2(col2) which is great. But tab1 has a few hundred thousand rows which makes life difficult for me. In Informix, the same kind of query runs great. It uses the tab2(col2) index first, then gets b.col1 out of that result, and applies it to tab1 using index tab1(col1). Life couldn't be better, except this software is in SQL Server, and I have tried everything. If you reconstruct the whole query to use a sub-select on tab2, its Nirvana city. But since this is a third party software, I need to try other external changes before I can recommend any changes to the vendor. I know that there are some people out there who have had SQL Server experience ( going by the number of messages comparing Informix, SQL Server and Oracle !! ). I have posted a similar question to the SQL Server newsgroup, but I might as well have posted it in a fashion magazine for all the response I ever get !! I hope some of you can help me. Thanks as usual Sujata ***************************************** Sujata Soman E-Mail: ssoman@omm.com O'Melveny & Myers, Los Angeles ******************************************