slow running query - help with speeding it up

Are there going to possibly be duplicates between the two tables? If NOT, or you can live with dups in the output, change UNION to UNION ALL.

Can you please post the List of indexes and definition, current execution plan.

<knee-jerk reaction> In addition to the above excellent comments...

• Lose the ORDER BY if possible, especially if this is a large set, as sorting can be an expensive operator.

• Both queries have a DISTINCT.  Is that really necessary?  This forces grouping and removal of duplicates for each set, which takes time, so if that's not needed the DISTINCT can be removed.

• What are the @dateFrom and @dateTo values being passed?  If the range is huge then explore doing this as an incremental load that only moves data a day or so old into a staging table, instead of a big range every time.

• Re-read the above two expert comments and nail those down.

• Everything else looks fine.

with (nolock)

This is NOT a performance gaining option - it simply allows a query to read uncommitted (dirty) data.  

---

Did you know that the default option for the select keyword is ALL (instead of DISTINCT)
and that the default option for the keyword UNION is DISTINCT (instead of ALL)
nb: these default options are almost always just omitted from code

In other words, the DEFAULT behaviour of UNION is actually UNION DISTINCT (to remove all duplicate rows)

So: there is simply no point using the bold bits: "select distinct" + "union distinct" + "select distinct"

Oh, and once you have considered all the advice already given, please provide an execution plan (attached as an .sqlplan file)  if you are still having performance issues.

Sorry, I just noticed this:

            AND ImeiNumber = ISNULL( @vpkDeviceID, vpkDeviceID )

Using functions such as isnull() in a where clause is bad for performance. Using boolean logic instead will make better use of indexes if they are available.

            AND ( ImeiNumber =  @vpkDeviceID OR (ImeiNumber = vpkDeviceID and @vpkDeviceID IS NULL) )

Overall it looks to me like you can do this by combining the where clauses:

SELECT /* DISTINCT is distinct really necessary? */
    ipkCommanTrackingID
  , ImeiNumber
  , Name            AS vDeviceName
  , dGPSDateTime
  , vLongitude
  , vLatitude
  , vOdometer
  , bIsIgnitionOn
  , vVehicleSpeed
  , ifk_TrackerType AS iTrackerType
  , vpkDeviceID
  , vTextMessage
  , vReportID
  , cl.vEventName
FROM tblCommonTrackingData /* WITH (NOLOCK) this just permits dirty reads, is that what you really want? */
INNER JOIN wlt_tblDevices A ON ImeiNumber = vpkDeviceID
INNER JOIN wlt_tblAssets B ON B.Id = ifk_AssignedAssetId
INNER JOIN wlt_tblEvents_CommonEvents_Lookup cl ON ipkCommonEventLookupId = vReportID
WHERE (
        dGPSDateTime >= @dateFrom
    AND dGPSDateTime <= @dateTo
    )
OR (
        ipkCommanTrackingID > @top_ipkCommanTrackingID
    AND ipkCommanTrackingID <= @extended_ipkCommanTrackingID
   )
AND ( ImeiNumber =  @vpkDeviceID OR (ImeiNumber = vpkDeviceID and @vpkDeviceID IS NULL) )
AND A.iParent = @iParent

Select allOpen in new window