slow running query - help with speeding it up

websss
websss used Ask the Experts™
on
I have the following query that is way too slow

select distinct ipkCommanTrackingID,ImeiNumber,Name as vDeviceName,dGPSDateTime,vLongitude,vLatitude,vOdometer,bIsIgnitionOn,vVehicleSpeed,
	 ifk_TrackerType as iTrackerType, vpkDeviceID, vTextMessage,vReportID,cl.vEventName FROM tblCommonTrackingData  with (nolock)
	 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
	 and ImeiNumber=isnull(@vpkDeviceID,vpkDeviceID) and A.iParent =@iParent 
	 
	 UNION 

	 select distinct ipkCommanTrackingID,ImeiNumber,Name as vDeviceName,dGPSDateTime,vLongitude,vLatitude,vOdometer,bIsIgnitionOn,vVehicleSpeed,
	 ifk_TrackerType as iTrackerType, vpkDeviceID, vTextMessage,vReportID,cl.vEventName FROM tblCommonTrackingData with (nolock)
	 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  ipkCommanTrackingID>@top_ipkCommanTrackingID and ipkCommanTrackingID <= @extended_ipkCommanTrackingID
	 and ImeiNumber=isnull(@vpkDeviceID,vpkDeviceID) and A.iParent =@iParent
	 ORDER BY vpkDeviceID desc,dGPSDateTime DESC,ipkCommanTrackingID desc

Open in new window


i've tried using group by instead of distinct but it only works if i select one column, so maybe i need another solution?
any help on this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Doug BishopDatabase Developer

Commented:
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.
AneeshDatabase Consultant
Top Expert 2009

Commented:
Can you please post the List of indexes and definition, current execution plan.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
<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.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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"
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial