>>The problem certainly has to do with poor optimization. In the first case SQL server cannot optimize the query because it has no clue on what is going to be in @Altern_ID. Therefore it is scanning indexes. The reason the TOP 1 is faster is because it stops scanning when it finds a match.
>
>If that is the case, how can we explain that with the other field, I never had this problem?

?? I'm not sure what you mean.. Are you talking about the speed improves when excluding the streetname ?? That can be explained because that field could not be extracted from the indexes directly and a table lookup or even table scan is neccesary to retrieve that information.

All information should be in the execution plan. It will tell you exactly what it is doing and how much time it takes. When you're able to read what it is doing you can draw the proper plan to optimize in a better way (Different indexes, enabling parameter sniffing, recompiling)

Can you tell me whether you are executing a stored procedure or passing parameterized SPT ?

Walter,