SQL Query Optimisation

I have a query that is selecting the last known Latitude and Longitude along with the Name from a table. At the moment the query is taking over 10 seconds to execute with only 18 Units to sort through, I can see this being a rather large issue when there are 300+ Units.

So I need a way to optimise this Query to execute much faster.

SELECT DISTINCT T1.ID, T1.sVesselName,
                          (SELECT     TOP (1) Latitude
                            FROM          tGPSData AS T3
                            WHERE      (VesselID = T1.ID)
                            ORDER BY ID DESC) AS LastLat,
                          (SELECT     TOP (1) Longitude
                            FROM          tGPSData AS T3
                            WHERE      (VesselID = T1.ID)
                            ORDER BY ID DESC) AS LastLon
FROM         tUser INNER JOIN
                      tUserVesselMap ON tUser.ID = tUserVesselMap.iUserID INNER JOIN
                      tVesselMaster AS T1 ON tUserVesselMap.iVesselID = T1.ID
WHERE     (T1.bEnabled = 1) AND (tUser.sUsername = @Username)
mgordon-spiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
For *this* query -- not taking into account any other workload on the tables -- you need to have these indexes:

tUser (sUsername) --preferably clustered
tUserVesselMap (iUserID)
tVesselMaster (ID)
tGPSData (VesselID, bEnabled ) --if bEnabled is bit, make it a tinyint instead
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mgordon-spiAuthor Commented:
I'm sorry - I dont quite understand your response?

I was more referring to the fact that I was using two nested select statements to retrieve the last record in the GPS table for each vessel. I was wondering if there was a faster way to do that?

The GPSData table holds hundreds of thousands of records for many vessels.

Untimately I am trying to retrieve a list of vessels and their last known location. This query achieves that but takes about 20 seconds to execute.
0
Anthony PerkinsCommented:
I was wondering if there was a faster way to do that?
Yes, as Scott has pointed out it is called indexes.  Until you have confirmed the existence of those indexes, there is not a lot we can add...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mgordon-spiAuthor Commented:
I'm sorry but this might be stretching my SQL abilities. Jsut to confirm we are not talking about primary keys?

I have not created indexes before. Will the creation of them help with query execute speed?
0
Anthony PerkinsCommented:
Jsut to confirm we are not talking about primary keys?
Maybe, but not necessarily.

Will the creation of them help with query execute speed?
Yes.  Although you should be aware that too many indexes can slow down INSERTs and UPDATEs, But I suspect you are a long way from that.
0
mgordon-spiAuthor Commented:
So it is as simple as creating indexes (the ones mentioned above) and I should see a difference?
0
mgordon-spiAuthor Commented:
Your help has directed me to learn a whole new aspect of SQL. Indexing!

Cheers.
0
Scott PletcherSenior DBACommented:
>> creating indexes ... and I should see a difference? <<

Yes, definitely!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.