Monday, March 12, 2012

FASTFIRSTROW or OPTION (FAST 1) doesnt work correctly

I want to optimize a query for fast retrieval of the first row
and after the rest of them automatically.
I used the hint OPTION (FAST 1) but I don't get the hoped results, any idea?
Thanks.The FASTFIRSTROW optimizer hint caused the optimizer to use the nonclustered index if one matches the ORDER BYclause.
Try seeing the execution plan to know what is happening ...|||Can you post the query you wrote?|||I have an index with three columns but the query use only the first two of them.

Table name = TRIAL_TB

Index columns= FIELD1, FIELD2, FIELD3

Query = SELECT * FROM TRIAL_TB WHERE FIELD1='VAL1'
AND FIELD2>'VAL2' ORDER BY FIELD1, FIELD2, FIELD3

If I execute the query, it doesn't match with the index and uses the clustered index.

Then, with the FASTFIRSTROW optimizer hint, it use the index created.

But, when I try to retrieve the data by cursor, It doesn't return the first row so fast as I want.

Thanks.|||What is the size of the table ?|||The table has 214.000 rows approximately and 5 columns.

No comments:

Post a Comment