Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

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)
0
mgordon-spi
Asked:
mgordon-spi
  • 4
  • 2
  • 2
1 Solution
 
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
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now