how Increase Trigger Speed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how Increase Trigger Speed

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-09-09 : 02:19:26

Hi

Below given trigger works Fine but speed is slow its takes 4 second to return one records which is very slow to my desire speed . how can i increase its speed.

Triggers are more resource expensive, can i use constrains instead of This triggers or can it solve by cascading If any other way give best speed then that way send to me

I have two table VEHICLETRACK,Map_Places_India .I write a trigger on
insert of VEHICLETRACK.when any records insert into VEHICLETRACK
then according to its inserted latitude and longitude i am calculating place of this latitude and longitude .Then this place add
into a TempAddPlace Table.I am using this Place for different purpose.In This trigger where i use

select @count=count(name) from Map_Places_India line

@count variable takes 2500 value means Loop executed 2500 times to Find place of one latitude and longitude

Plz help me how i increase its speed. trigger is below

====================================================

CREATE TRIGGER tr_insertPlaceInVEHICLETRACK_FromMap_Places_India_tbltgr
ON VEHICLETRACK FOR insert
AS
declare @TriggLat float,@TriggLon float
select @TriggLat=Lat from inserted
select @TriggLon=Lon from inserted
declare @Triggplace varchar(128)
--select @Triggplace=dbo.place(@TriggLat,@TriggLon)
--Place Find code Start From Below>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare @la11 float,@lon1 float,@var varchar(30),@latv1 varchar(30),@lonv1 varchar(30),@latmap varchar(30),@lonmap varchar(30),@latv11 varchar(30),@lonv11 varchar(30), @dis float
select @la11 = Lat FROM VEHICLETRACK where lat=@TriggLat and lon=@TriggLon
select @lon1 = Lon FROM VEHICLETRACK where lat=@TriggLat and lon=@TriggLon
select @var=CONVERT(varchar(30),@la11,0)
select @latv1=SUBSTRING(@var,1,9)
select @var=CONVERT(varchar(30),@lon1,0)
select @lonv1=SUBSTRING(@var,1,9)
--select @latv1,@lonv1
declare @count integer
select @count=count(name) from Map_Places_India
declare @i integer,@CID integer
set @i=1
--set @CID=@i
while @i<@count
begin
select @la11 = Lat FROM Map_Places_India where id=@i
select @lon1 = Lon FROM Map_Places_India where id=@i
select @var=CONVERT(varchar(30),@la11,0)
select @latv11=SUBSTRING(@var,1,9)
select @var=CONVERT(varchar(30),@lon1,0)
select @lonv11=SUBSTRING(@var,1,9)
if @i=1
begin
--set @dis=dbo.distance(@latv1,@latv11,@lonv1,@lonv11):::::::::::::::::::Distance Code Start from below:::::::::::::::::::::::
declare @latdis1 float,@latdis2 float,@longdis1 float,@longdis2 float
select @latdis1= CONVERT(float,@latv1)
select @latdis2=CONVERT(float,@latv11)
select @longdis1=CONVERT(float,@lonv1)
select @longdis2=CONVERT(float,@lonv11)
declare @dislat float,@dislon float,@distance1 float
set @dislat = 69.1 * 1.609344 * (@latdis2 - @latdis1)
set @dislon = 69.1 * 1.609344 * (@latdis2 - @latdis1) * Cos(@latdis1 / 57.3)
set @distance1 = Sqrt(square(@dislat) + square(@dislon))
set @dis =@distance1
--set select @CID= id from Map_Places_India where id=@i::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::Distance Code END:::::::::::::::::::::::::::::::
set @CID=@i
end
else
begin
declare @zz float
--set @zz=dbo.distance(@latv1,@latv11,@lonv1,@lonv11)
---Distance Code Start from below>>>>>>:::::::::::::::;>>>>>>>>>>>>>>>>>>>>>>>>>>>>:::::::::::::::::::::::::::::::Distance Code Start from below::::::::::::::::::::::::::::::
declare @latdis11 float,@latdis21 float,@longdis11 float,@longdis21 float
select @latdis11= CONVERT(float,@latv1)
select @latdis21=CONVERT(float,@latv11)
select @longdis11=CONVERT(float,@lonv1)
select @longdis21=CONVERT(float,@lonv11)
declare @dislat1 float,@dislon1 float,@distance2 float
set @dislat1 = 69.1 * 1.609344 * (@latdis21 - @latdis11)
set @dislon1 = 69.1 * 1.609344 * (@latdis21 - @latdis11) * Cos(@latdis11 / 57.3)
set @distance2 = Sqrt(square(@dislat1) + square(@dislon1))

set @zz=@distance2

----Distance Code End>>>>>>>>>>>>>>>>>>>::::::::::::::::::::::>>>>>>>>>>>>>>>>>>>>>>>>>>>::::::::::::::::::::::::::Distance Code End:::::
if @zz<@dis
begin
set @dis=@zz
--set select @CID= id from Map_Places_India where id=@i
set @CID=@i
end
end
set @i=@i+1
end
declare @position varchar(50)
--select @i as Counter
--select @CID as CID
select @position= name from Map_Places_India where id=@CID
declare @place varchar(50)
--select @dis as Distance + 'Km From'+ @position
--select @position as Position
declare @place1 varchar(128)
SELECT @place1=CONVERT(varchar(30), @dis) +' ' + 'Km. From'+' ' + CONVERT(varchar(30), @position)
set @Triggplace=@place1

---Place Code End>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Place Code End>
declare @Jrid1 varchar(50),@Deviceid1 varchar(50),@Timerecorded1 datetime,@Triggid1 decimal,@State1 varchar(10),@Speed1 float
select @Jrid1=Jrid from inserted
select @Deviceid1=Deviceid from inserted
select @Timerecorded1=Timerecorded from inserted
select @Triggid1=id from inserted
select @State1=State from inserted
select @Speed1 =Speed from inserted
INSERT INTO TempAddPlace(id ,Jrid ,Deviceid ,Lat ,lon ,Timerecorded ,State,Speed,place )
values( @Triggid1,@Jrid1, @Deviceid1,@TriggLat,@TriggLon,@Timerecorded1,@State1,@Speed1,@Triggplace)

Ranjeet Kumar Singh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-09 : 02:32:01

change the while loop to set base processing. Which means you should process sets of records rather than one by one.

The while loop is performing for each record in Map_Places_India this will be time consuming.

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-09-09 : 02:48:51

Hi
what means set base processing .If i will change while loop then How i will search Map_Places_India to find place

Ranjeet Kumar Singh

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-09-09 : 03:01:31

can i use constraints instead of This triggers if trigger expensive or can it solve by cascading If any other way give best speed then that way send to me

Ranjeet Kumar Singh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-09 : 03:48:14

maybe you can explain what the trigger is doing ?

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2006-09-09 : 04:53:31

Hi
I have two table
1:VEHICLETRACK(Jrid,Deviceid,Latitude,Longitude,Timerecorded,id,State,Speed)

2:Map_Places_India(id,Latitude,Longitude,place)

when any records insert into VEHICLETRACK then i Find place from Map_Places_India according to matching inserted VEHICLETRACK latitude and longitude if there are no place in Map_Places_India for inserted latitude and longitude then i find nearest place of inserted latitude and longitude

Ranjeet Kumar Singh

Kristen
Test

22859 Posts

Posted - 2006-09-09 : 05:52:07

Note that

select @TriggLat=Lat from inserted
select @TriggLon=Lon from inserted

"inserted" table may contain multiple rows, and your Trigger should be written to handle that

I don't understand this:

select @TriggLat=Lat from inserted
select @TriggLon=Lon from inserted
select @la11 = Lat FROM VEHICLETRACK where lat=@TriggLat and lon=@TriggLon
select @lon1 = Lon FROM VEHICLETRACK where lat=@TriggLat and lon=@TriggLon

You have just inserted a row in VEHICLETRACK , you are getting the @TriggLat and @TriggLon of that inserted row, and then using those values to find matching row(s) in VEHICLETRACK - which will include the row you just inserted ... and possibly multiple other rows, but only one row's value will be stored in @la11 / @lon1 - and that choice will be entirely random as you have written it, and quite possibly from a different row for each of the two SELECT statements ...

select @count=count(name) from Map_Places_India

This counts the number of rows in Map_Places_India table. It does NOT tell you what the maximum value in the [id] column is, or whether the [id] column has contiguous values - there is a very high chance that the values are not contiguous if it is an IDENTITY column - however, you may have created the [id] by other means and be sure that it is contiguous?.

I would have used:

select @i=MIN([id]), @count=MAX([id]) from Map_Places_India

for what you then proceed to do (notwithstanding that SetBased would be better as khtan has mentioned)

select @la11 = Lat FROM Map_Places_India where id=@i
select @lon1 = Lon FROM Map_Places_India where id=@i

require two select statements, why don't you do:


select @la11 = Lat,
       @lon1 = Lon
FROM Map_Places_India where id=@i

??

Better still replace:

select @la11 = Lat FROM Map_Places_India where id=@i
select @lon1 = Lon FROM Map_Places_India where id=@i
select @var=CONVERT(varchar(30),@la11,0)
select @latv11=SUBSTRING(@var,1,9)
select @var=CONVERT(varchar(30),@lon1,0)
select @lonv11=SUBSTRING(@var,1,9)

with


select @latv11=SUBSTRING(CONVERT(varchar(30), Lat, 0), 1, 9),
       @lonv11=SUBSTRING(CONVERT(varchar(30), Lon, 0), 1, 9)
FROM Map_Places_India
where id=@i

This looks horribly slow:

set @dislat = 69.1 * 1.609344 * (@latdis2 - @latdis1)
set @dislon = 69.1 * 1.609344 * (@latdis2 - @latdis1) * Cos(@latdis1 / 57.3)
set @distance1 = Sqrt(square(@dislat) + square(@dislon))

and if you must do it that way why didn't you reuse the value for @dislat in the calculation for @dislon to save SQL Server having to calculate "69.1 * 1.609344 * (@latdis2 - @latdis1)" a second time ??

If you are trying to use the Great Circle algorithm to find the nearest point, with decent efficiency, you need to select a handful of records which have the smallest difference by Lat & Long from your target into a temporary table, and then select the best from that small data set using the Great Circle / Haversine algorithm, so that the COS, SQUARE and SQRT etc functions are used sparingly and minimally to reduce CPU overhead.

Its probably sufficient to SELECT TOP 1 WITH TIES ordered based on the combined difference of Lat & Long, and then SELECT TOP 1 from that using the Great Circle algorithm. With a covering index on Lat, Long and the PK this should be very efficient.

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-09-09 : 23:50:11

quote:
Originally posted by Kristen

This looks horribly slow:

set @dislat = 69.1 * 1.609344 * (@latdis2 - @latdis1)
set @dislon = 69.1 * 1.609344 * (@latdis2 - @latdis1) * Cos(@latdis1 / 57.3)
set @distance1 = Sqrt(square(@dislat) + square(@dislon))

And yet again someone is using the "Great Circle Algorithm" when old reliable Pythagorus is more than adequate for finding the closest location. About the only time I could see using the Great Circle Algortihm is when you want to find the point that is FARTHEST AWAY. Then it might actually make a difference.
I propose that we, as a team, scour the internet for all references to the Great Circle Algorithm, delete them, and then lock the algorithm up in a vault. An application would be required from then on if anybody actually wanted to see it.
And that would take care of all the developers who think they are so smart because they found a way to increase accuracy from 99.9% to 99.99%, with only a mere 100-fold increase in execution time.

"I have HAD it with these muthu-f$#%in' cursors in my muthu-f$#%in' database!"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-10 : 13:56:36

I think is a sequel to this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71621

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-10 : 14:10:38

You only have to search for TOP 1 record in Places table. What you want is something like this code

select    d.Place,
          d.xlat,
          d.ylat,
          69.1 * 1.609344 * SQRT(d.t) as Distance
from      (
             SELECT    TOP 1 Place,
                       xlat,
                       ylat, 
                       power(xlat - @xpos, 2) + power(ylat - @ypos, 2) t
             FROM      Places
             Order By  4
          ) d

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-10 : 14:22:54

Or something like this

SELECT		v.VehicleID,
		v.Latitude,
		v.Longitude,
		69.1 * 1.609344 * MIN(POWER(p.Latitude - v.Latitude, 2) + POWER(p.Longitude - v.Longitude, 2)) Dist
FROM		(
			SELECT		VehicleID,
					Latitude,
					Longitude
			FROM		viewVehiclesLastKnownPosition
		) v
CROSS JOIN	(
			SELECT		Place,
					Latitude,
					Longitude
			FROM		Places
		) p
GROUP BY	v.VehicleID,
		v.Latitude,
		v.Longitude

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources