Hi,
This is the kind of thing you can do in PostgreSQL and it is amazingly very
fast. I like MySQL but come on guys, this is 2015. SQL92 is 23 years
old. By the way this adds two more requests: stored aggregate functions
and stored table functions
-- Table stored function which combines shuttle radar topography raster
data (SRTM)
-- and Open street map geometry data
CREATE FUNCTION cache_1m_tile(integer) RETURNS SETOF tile
LANGUAGE sql
AS $_$
with lands as -- common table expressions
-- Use raster and geometry objects together with GiST index
(select (st_pixelaspoints(st_clip(rast, geometry),1,false)).x,
(st_pixelaspoints(st_clip(rast,geometry),1,false)).y, array_accum(type) as
"types", array_accum(name) as names, array_accum(z_order) as z_orders
from import.osm_landusages
join srtm_1m on st_intersects(rast, geometry)
where rid = $1
group by 1,2
),
buildings as
( select * from srtm_buildings where rid = $1),
places_and_amenities as
-- array_accum is a stored AGGREGATE function that works like group_concat
(select x,y, array_accum(name) thing_name, array_accum(type) thing_type
from
( select (st_pixelaspoints(st_clip(rast, geometry),1,false)).x,
(st_pixelaspoints(st_clip(rast,geometry),1,false)).y, 'place::' || name as
name, 'place::' || type as type
from import.osm_places
join srtm_1m on st_intersects(rast, geometry)
where rid = $1
union all
select (st_pixelaspoints(st_clip(rast, geometry),1,false)).x,
(st_pixelaspoints(st_clip(rast,geometry),1,false)).y, 'place::' || name as
name, 'place::' || type as type
from import.osm_amenities
join srtm_1m on st_intersects(rast, geometry)
where rid = $1
) foo
group by 1,2
)
insert into srtm_1m_tiles
select p.rid,p.x,
p.y,
p.val height,
p.geom,
floor(b.area) building_area,
b.id building_id,
r.id road_ids ,
r.z_order road_z_order,
r.bridge road_bridge,
r.tunnel road_tunnel,
r.rtype road_type,
w.val is not null has_water,
lands.names as land_names,
lands.types as land_types,
lands.z_orders as land_z,
pa.thing_name,
pa.thing_type
from srtm_1m_pixels p
left join buildings b
on b.rast_geom && p.geom
and st_contains(b.geometry, p.geom)
and p.rid = b.rid
left join srtm_road_accum r
on b.rid = r.rid
and p.x = r.x
and p.y = r.y
left join srtm_water_pixels w
on b.rid = w.rid
and p.x = w.x
and p.y = w.y
left join srtm_transport_pixels tp
on b.rid = tp.rid
and p.x = tp.x
and p.y = tp.y
left join lands
on p.x = lands.x
and p.y = lands.y
left join places_and_amenities pa
on p.x = pa.x
and p.y = pa.y
where p.rid=$1
and not exists (select 1 from srtm_1m_tiles t2 where p.rid = t2.rid and
p.x = t2.x and p.y = t2.y)
returning *;
$_$;
On Tue, Mar 3, 2015 at 7:05 AM, Justin Swanhart
Hi,
Just to avoid ambiguity: the stored function is executed as byte code in each call, and the native function is called by the byte code for each call.
--Justin
On Tue, Mar 3, 2015 at 7:04 AM, Justin Swanhart
wrote: Hi,
The server parses the function into a form of bytecode which is then cached until the routine changes. In the past any routine change would invalidate cache for all routines and that sometimes can still happen but it is rare. The server then interprets the bytecode for every call to the function and yes, the function is executed every time unless you memoize the function ( if(function_input != @last_input) then @last_input = function_input; return @last_result := function(function_input); else return @last_result;
On Tue, Mar 3, 2015 at 6:59 AM, Peter Laursen
wrote: I really don't know if it should take 2, 5 or 60 seconds. The numbers surprise me (in the bad maner). There is an incredible overhead when wrapping this query in a function. I wonder if there is an invocation of the function for every iteration.
-- Peter
On Tue, Mar 3, 2015 at 2:51 PM, Federico Razzoli
wrote: SELECT 1 was .42. What I find amazing is not the absolute numbers (it's an old computer with desktop installed, etc) but the difference between the tests. If SELECT 1 takes .42, test 2 shouldn't take more than 1 min... do you agree?
Federico
-------------------------------------------- Mar 3/3/15, Justin Swanhart
ha scritto: Oggetto: Re: [Maria-discuss] stored programs A: "Peter Laursen"
Cc: "Federico Razzoli" , "Maria Discuss" < maria-discuss@lists.launchpad.net> Data: Martedì 3 marzo 2015, 14:28 You probably have either a faster CPU or bigger cache on your cpu. How long does benchmark select 1 take? You should find it is faster on your system too, right? --Justin
Sent from my iPhone On Mar 3, 2015, at 6:18 AM, Peter Laursen
wrote: Your 3rd test case takes 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system (when otherwise idle) . -- Peter On Tue, Mar 3, 2015 at 1:58 PM, Federico Razzoli
wrote: I made some quick test to show what I mean by "performance problem". Note that I'm not saying the the first and the second test cases should perform equally. But there is too much difference - see the conclusion. 1)
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT 1));
+---------------------------------+
| BENCHMARK(50000000, (SELECT 1)) |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.42 sec)
2)
DELIMITER ||
CREATE FUNCTION f()
RETURNS TINYINT
BEGIN
RETURN 1;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f()));
+-----------------------------------+
| BENCHMARK(50000000, (SELECT f())) |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (2 min 5.70 sec)
3)
DELIMITER ||
CREATE FUNCTION f(x TINYINT)
RETURNS TINYINT
BEGIN
RETURN x;
END ||
DELIMITER ;
MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT f(1)));
+------------------------------------+
| BENCHMARK(50000000, (SELECT f(1))) |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (3 min 35.20 sec)
-- Conclusions:
Times in seconds:
0.42
125.70
215.20
I don't know which ratio would be acceptable, but the difference betweem a trivial query and a trivial function is too high.
Also, the difference between 2) and 3) is that the function in 3) accepts and returns a parameter. Again, the performance difference seems to me too high.
Regards
Federico
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp