Optimization help
-
I'm trying to optimize some sql code, and I figure there is an easier way to do this, but since I'm still relatively new to SQL, I'd like some help if possible. I have a table that has 4 relevant values, the table's id key (TKey, int), a parent key (PKey, int), a Type (Type, int), and a Locked value(Locked, bool). I'm trying to return the locked value of the maximum local key of a certain set of types. What I've come up with so far: SELECT Locked TKey FROM Table WHERE TKey = (Select Max(TKey) from Table) where (Type = 0 or Type = 1) AND PKey = @Pkey) Is there a better way to do this, or am I doing it correctly? It just seems that I shouldn't be querying a table within a query to that table. Maybe something with 'Top 1' or 'Group By'?
-
I'm trying to optimize some sql code, and I figure there is an easier way to do this, but since I'm still relatively new to SQL, I'd like some help if possible. I have a table that has 4 relevant values, the table's id key (TKey, int), a parent key (PKey, int), a Type (Type, int), and a Locked value(Locked, bool). I'm trying to return the locked value of the maximum local key of a certain set of types. What I've come up with so far: SELECT Locked TKey FROM Table WHERE TKey = (Select Max(TKey) from Table) where (Type = 0 or Type = 1) AND PKey = @Pkey) Is there a better way to do this, or am I doing it correctly? It just seems that I shouldn't be querying a table within a query to that table. Maybe something with 'Top 1' or 'Group By'?
Drew McGhie wrote:
Is there a better way to do this, or am I doing it correctly?
Other than the extra closing bracket it looks okay to me. If you need to optimising it then look at the query plan in Query Analyzer because it never ceases to amaze me what SQL Server thinks is optimal in a given situation - I've also had one change somewhere in the database cause an existing query to start running a lot slower than before. I feel that database optimisation is an on-going process rather than something you do once when you are doing your initial development. Anyway, an alternative to your code:
SELECT TOP 1 Locked, TKey
FROM Table
WHERE Type IN (0,1)
AND PKey = @Pkey
ORDER BY TKey DESC
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog