| Author |
 Topic  |
|
|
ranjeetsingh_6
Posting Yak Master
India
125 Posts |
 Posted - 09/09/2006 : 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 |
Edited by - ranjeetsingh_6 on 09/09/2006 02:37:32
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 09/09/2006 : 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.
KH
|
 |
|
|
ranjeetsingh_6
Posting Yak Master
India
125 Posts |
Posted - 09/09/2006 : 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
India
125 Posts |
Posted - 09/09/2006 : 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)
Singapore
16769 Posts |
Posted - 09/09/2006 : 03:48:14
|
maybe you can explain what the trigger is doing ?
KH
|
|
|
|
ranjeetsingh_6
Posting Yak Master
India
125 Posts |
Posted - 09/09/2006 : 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
United Kingdom
22191 Posts |
Posted - 09/09/2006 : 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
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 09/09/2006 : 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!" |
Edited by - blindman on 09/09/2006 23:51:01 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/10/2006 : 14:10:38
|
You only have to search for TOP 1 record in Places table. What you want is something like this codeselect 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/10/2006 : 14:22:54
|
Or something like thisSELECT 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 |
|
|
| |
Topic |
|
|
|
how Increase Trigger Speed
