a very very strange sql bug (?)

I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId =
View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
Felix
I often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov
|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause.
> Bojidar Alexandrov