SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 how Increase Trigger Speed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 09/09/2006 :  02:19:26  Show Profile  Reply with Quote
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

Edited by - ranjeetsingh_6 on 09/09/2006 02:37:32

khtan
In (Som, Ni, Yak)

Singapore
17606 Posts

Posted - 09/09/2006 :  02:32:01  Show Profile  Reply with Quote
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.


KH

Go to Top of Page

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 09/09/2006 :  02:48:51  Show Profile  Reply with Quote
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
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 09/09/2006 :  03:01:31  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17606 Posts

Posted - 09/09/2006 :  03:48:14  Show Profile  Reply with Quote
maybe you can explain what the trigger is doing ?


KH

Go to Top of Page

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 09/09/2006 :  04:53:31  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/09/2006 :  05:52:07  Show Profile  Reply with Quote
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
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 09/09/2006 :  23:50:11  Show Profile  Reply with Quote
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!"

Edited by - blindman on 09/09/2006 23:51:01
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 09/10/2006 :  13:56:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think is a sequel to this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71621


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 09/10/2006 :  14:10:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 09/10/2006 14:17:21
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 09/10/2006 :  14:22:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000