SQL SP performance help

Hi
My web app was timing out
I've found the offending SP, and i ran an execution plan on it after it took over 1.5 mins to run
Here is the offending statement
perfhelp
Can anyone shed some light on this and ways to improve it
Someone else wrote it, but it looks like its just getting all the distinct IMEI numbers
websssCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't think that's all your SELECT. Can you post it here?
Also, it would be more easy if you post the execution plan as file rather than an image. You can save it directly from SSMS.
websssCEOAuthor Commented:
Yes you are correct, here is the full SP
i dont think it needs to do all this!

ALTER procedure [dbo].[sp_GetTrackerPointDataForAllDevices]	
as
Begin
	
	------------select distinct values in variables
	DECLARE @strList VARCHAR(MAX) = '';

	SELECT @strList += x.nIMEINo
	FROM 
	(
	  SELECT DISTINCT (nIMEINo + ',') as nIMEINo
	  FROM dbo.tblCommonTrackingData AS CT
	  inner join Newtbl_DeviceMaster DM on DM.nIMEINo=CT.vpkDeviceID
	) AS x;

	------------select distinct values in variables


	--------------looping through comma seperated ids
	
	declare @vpkDeviceId varchar(50)
		
	declare @tbl table
	(
		ipkDeviceID int,
		vpkDeviceID varchar(50),
		vVehicleSpeed int,
		bIsIgnitionOn bit,
		vEventName varchar(100),
		iTrackerType int,
		vHeading int,
		vDeviceName varchar(100),
		vLatitude varchar(50),
		vLongitude varchar(50),
		GroupID int,
		ParentGroupID int,
		ifkDriverID nvarchar(200),
		dGPSDateTime datetime,
		vTextMessage varchar(max),
		vOdometer numeric,
		vReportID int,
		vSequenceID numeric,
		Fuel float,
		AssetPhotoName varchar(max),
		vLogo varbinary(max),
		vLogoName nvarchar(100),
		DvLogo varbinary(max),
		DvLogoName nvarchar(100),
		DriverName nvarchar(100)
	)


	while len(@strList) > 0
	begin
	  set @vpkDeviceId = left(@strList, charindex(',', @strList+',')-1)

	  Insert INTO @tbl
	  Select top(1) 
			A.ipkDeviceID,
			vpkDeviceID,
			vVehicleSpeed,
			bIsIgnitionOn,
			vEventName,
			A.iTrackerType,
			vHeading,
			A.vDeviceName,
			vLatitude,
			vLongitude,
			GroupID,
			ParentGroupID,
			ifkDriverID,
			dGPSDateTime,
			vTextMessage,
			vOdometer,
			vReportID,
			vSequenceID,
			Fuel,
			AssetPhotoName,			
			B.vLogo,
			B.vLogoName,
			C.vLogo 'DvLogo',
			C.vLogoName 'DvLogoName',
			C.DriverName
	  from vwCommonTrackingData A
	  inner join Newtbl_DeviceMaster B  on B.nIMEINo=vpkDeviceID
	  left outer join wlt_tblAssets_Driver C on C.ipkDriverID=ifkDriverID
	  where vpkDeviceID = @vpkDeviceID 
	  ORDER BY dGPSDateTime DESC

	  set @strList = stuff(@strList, 1, charindex(',', @strList+','), '')
	end


	select * from @tbl
	--------------looping throught comma seperated ids

end

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
What you pretend to do in that SP?
You are looping and inserting from a select with order by. This can be the performance killer and not the first query.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

websssCEOAuthor Commented:
its someone else's  SP

there is a table which is updated every min
I need to select the last result for each IMEI - i.e. distinct

thats all, i can do the other joins etc
I remember having lots of issues with DISTINCT, works fine on one column

i.e.

Select Distinct IMEI, ...list all other fields here
from tblname
order by LastTimeUpdated

is this relatively simple, or do i need to do anything different?
Vitor MontalvãoMSSQL Senior EngineerCommented:
That was a very odd way to do get the data you want.
Try to replace the call of that SP with this single command:
;WITH CTE_RecentDevice (DeviceID, GPSDateTime)
AS (
	SELECT A.vpkDeviceID, MAX(dGPSDateTime)
	from vwCommonTrackingData A
	inner join Newtbl_DeviceMaster B  on B.nIMEINo=A.vpkDeviceID
	left outer join wlt_tblAssets_Driver C on C.ipkDriverID=ifkDriverID
	GROUP BY A.vpkDeviceID
	)
 SELECT 
	A.ipkDeviceID,
	vpkDeviceID,
	vVehicleSpeed,
	bIsIgnitionOn,
	vEventName,
	A.iTrackerType,
	vHeading,
	A.vDeviceName,
	vLatitude,
	vLongitude,
	GroupID,
	ParentGroupID,
	ifkDriverID,
	dGPSDateTime,
	vTextMessage,
	vOdometer,
	vReportID,
	vSequenceID,
	Fuel,
	AssetPhotoName,			
	B.vLogo,
	B.vLogoName,
	C.vLogo 'DvLogo',
	C.vLogoName 'DvLogoName',
	C.DriverName
 from vwCommonTrackingData A
inner join CTE_RecentDevice R on R.DeviceID = A.vpkDeviceID and R.GPSDateTime=dGPSDateTime
inner join Newtbl_DeviceMaster B  on B.nIMEINo=A.vpkDeviceID
left outer join wlt_tblAssets_Driver C on C.ipkDriverID=ifkDriverID

Open in new window

websssCEOAuthor Commented:
Great thanks. that runs without an error

However, the results seem somewhat duplicated
VpkDeviceID should be distinct and ordered by last dGPSDateTime
see here for results:
resultsset2
any ideas?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I need to review the code.
I can see that the query took 3 seconds. How much was the average execution time for the SP?
websssCEOAuthor Commented:
the last SP was between 30 secs and 2.5 mins!
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, was watching your data. There are more records with the same datetime. Can you check for those records if there is any field than can distinguish them?
websssCEOAuthor Commented:
yes you could use ipkCommanTrackingID (the most recent) or sequenceID, however, when i try include these, they are duplicated, the GROUPID's seem to differ

output3
Vitor MontalvãoMSSQL Senior EngineerCommented:
dGPSDateTime is from which table?
ipkCommanTrackingID isn't unique.
Where the Group information comes from?
websssCEOAuthor Commented:
dGPSDateTime and ipkCommanTrackingID is from the table tblCommonTrackingData
However its not accessed directly, instead using this view

The GROUP info is also referenced in this view: tblGroupAssets_Details

Here is the view:
SELECT     CT.ipkCommanTrackingID, CT.vSequenceID, CT.vpkDeviceID, CT.dGPSDateTime, CT.vLongitude, CT.vLatitude, CT.vHeading, CT.vReportID, CT.vOdometer, 
                      CT.vVehicleSpeed, CT.vTextMessage, CT.ifkDriverID, CT.bIsIgnitionOn, CT.vEventName, (CASE WHEN FuelVoltage IS NULL THEN '0.0' ELSE FuelVoltage END) 
                      AS FuelVoltage, DM.vDeviceName, DM.ipkDeviceID, DM.vLogoName AS AssetPhotoName, DM.iTrackerType, dbo.CalculateFuel(CT.FuelVoltage, DM.MinFuelValue, 
                      DM.MaxFuelValue) AS Fuel, DM.MaxFuelValue, CT.vDistance, GD.ifkGroupMID AS GroupID, GD.iParentGMID AS ParentGroupID, 
                      dbo.wlt_tblAssets_Driver.DriverName
FROM         dbo.tblCommonTrackingData AS CT LEFT OUTER JOIN
                      dbo.Newtbl_DeviceMaster AS DM ON DM.nIMEINo = CT.vpkDeviceID LEFT OUTER JOIN
                      dbo.tblGroupAssets_Details AS GD ON GD.nfkIMEINo = CT.vpkDeviceID LEFT OUTER JOIN
                      dbo.wlt_tblAssets_Driver ON dbo.wlt_tblAssets_Driver.ipkDriverID = CT.ifkDriverID
WHERE     (CT.vLongitude IS NOT NULL) AND (CT.vLongitude <> '') AND (CT.vLatitude IS NOT NULL) AND (CT.vLatitude <> '')

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Since they are all from the same view then we can simplify the query. I also made some modifications that will reduce the number of returned records but will still return duplicate records:
;WITH CTE_RecentDevice (DeviceID, CommanTrackingID, GPSDateTime)
AS (
	SELECT vpkDeviceID, MAX(ipkCommanTrackingID), MAX(dGPSDateTime)
	FROM vwCommonTrackingData
	GROUP BY vpkDeviceID
	)
 SELECT 
	A.ipkDeviceID,
	vpkDeviceID,
	vVehicleSpeed,
	bIsIgnitionOn,
	vEventName,
	A.iTrackerType,
	vHeading,
	A.vDeviceName,
	vLatitude,
	vLongitude,
	GroupID,
	ParentGroupID,
	ifkDriverID,
	dGPSDateTime,
	vTextMessage,
	vOdometer,
	vReportID,
	vSequenceID,
	Fuel,
	AssetPhotoName,			
	B.vLogo,
	B.vLogoName,
	C.vLogo 'DvLogo',
	C.vLogoName 'DvLogoName',
	C.DriverName
  from vwCommonTrackingData A
	inner join CTE_RecentDevice R on R.DeviceID = A.vpkDeviceID and R.CommanTrackingID = A.ipkCommanTrackingID and R.GPSDateTime=dGPSDateTime
	inner join Newtbl_DeviceMaster B  on B.nIMEINo=vpkDeviceID
	left outer join wlt_tblAssets_Driver C on C.ipkDriverID=ifkDriverID

Open in new window

Need to check better the groups stuff since the key may be there.

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
websssCEOAuthor Commented:
Great thanks

I've tweaked it a little
	WITH CTE_RecentDevice (DeviceID, CommanTrackingID, GPSDateTime)
	AS (
		SELECT vpkDeviceID, MAX(ipkCommanTrackingID), MAX(dGPSDateTime)
		FROM vwCommonTrackingData
		GROUP BY vpkDeviceID
		)
	 SELECT distinct
		A.ipkDeviceID,
		vpkDeviceID,
		vVehicleSpeed,
		bIsIgnitionOn,
		vEventName,
		A.iTrackerType,
		vHeading,
		A.vDeviceName,
		vLatitude,
		vLongitude,
		--GroupID,
		--ParentGroupID,
		ifkDriverID,
		dGPSDateTime,
		vTextMessage,
		vOdometer,
		vReportID,
		vSequenceID,
		Fuel,
		AssetPhotoName,			
		B.vLogo,
		B.vLogoName,
		C.vLogo 'DvLogo',
		C.vLogoName 'DvLogoName',
		C.DriverName
	  from vwCommonTrackingData A
		inner join CTE_RecentDevice R on R.DeviceID = A.vpkDeviceID and R.CommanTrackingID = A.ipkCommanTrackingID and R.GPSDateTime=dGPSDateTime
		inner join Newtbl_DeviceMaster B  on B.nIMEINo=vpkDeviceID
		left outer join wlt_tblAssets_Driver C on C.ipkDriverID=ifkDriverID

Open in new window


It was taking 9 secs to complete
I've created 1 new index on tblCommonTrackingData with these fields:
vpkDeviceID, ipkCommanTrackingID, dGPSDateTime

This then took it to 4/5 seconds for running

Any ideas what i can do (indexes?) to get it around 3 secs?
Vitor MontalvãoMSSQL Senior EngineerCommented:
The query returns the records as you want?
For the indexes you'll need to index in all JOIN fields and also the fields used in the View's WHERE clause.
websssCEOAuthor Commented:
yes all fields are correct now thanks

... would creating statistics on the view help performance?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Depends. Are the statistics up to date? If not the run an update statistics command.
websssCEOAuthor Commented:
the view doesn't have any statistics
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't think you can create statistics for the View since isn't an indexed View. The better you can do is to check the statistics on the tables used in the View.
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

From novice to tech pro — start learning today.