mysql - Is there any way to optimize this stored procedure? -

-

so have wicked long , complex stored procedure have constructed based on tutorials "pivoting". wondering if on here proficient in mysql mind discussing me, how make cleaner, and/or perform better. also, when query returns no results between dates specified, produces mysql syntax error in "call reading_pivot(...)" clause.

here code:

delimiter $$ create definer=`csonet_web_user`@`%` procedure `reading_pivot`(     in begin_date datetime,     in end_date datetime,     in node_string varchar(255),     in sensor_string varchar(255) ) begin set session group_concat_max_len = 4096; set @sql = null; select group_concat(distinct concat('max(if(sensorid = ''', sensorid, ''', collectedvalue * multiplier + offset, null)) ''', sensorid,'''')) @sql     (         select inodes.id nodeid, from_unixtime(300 * round(unix_timestamp(inodes_data.time)/300)) datecollected, trim(inodes.descr) parentid,         1 sensornumber, concat('c', trim(cast(inodes.id char(6) charset latin1)), '_1') sensorid, inodes.senstype1 sensortitle, inodes.a1 multiplier, inodes.b1 offset, inodes_data.sens1 collectedvalue,          inodes_structure_data.sensor1_units unitsofmeasure             cso_city_15.inodes             inner join cso_city_15.inodes_structure_data                 on inodes.id = inodes_structure_data.id             inner join cso_city_15.inodes_data inodes_data                 on inodes.id = inodes_data.i_id             find_in_set(trim(cast(inodes.id char(6) charset latin1)), node_string)                 , inodes_data.time between begin_date , end_date          union          select inodes.id, from_unixtime(300 * round(unix_timestamp(inodes_data.time)/300)), trim(inodes.descr),         2, concat('c', trim(cast(inodes.id char(6) charset latin1)), '_2'), inodes.senstype2, inodes.a2, inodes.b2, inodes_data.sens2, inodes_structure_data.sensor2_units             cso_city_15.inodes             inner join cso_city_15.inodes_structure_data                 on inodes.id = inodes_structure_data.id             inner join cso_city_15.inodes_data inodes_data                 on inodes.id = inodes_data.i_id             find_in_set(trim(cast(inodes.id char(6) charset latin1)), node_string)                 , inodes_data.time between begin_date , end_date          union          select inodes.id, from_unixtime(300 * round(unix_timestamp(inodes_data.time)/300)), trim(inodes.descr),         3, concat('c', trim(cast(inodes.id char(6) charset latin1)), '_3'), inodes.senstype3, inodes.a3, inodes.b3, inodes_data.sens3, inodes_structure_data.sensor3_units             cso_city_15.inodes             inner join cso_city_15.inodes_structure_data                 on inodes.id = inodes_structure_data.id             inner join cso_city_15.inodes_data inodes_data                 on inodes.id = inodes_data.i_id             find_in_set(trim(cast(inodes.id char(6) charset latin1)), node_string)                 , inodes_data.time between begin_date , end_date          union          select inodes.id, from_unixtime(300 * round(unix_timestamp(inodes_data.time)/300)), trim(inodes.descr),         4, concat('c', trim(cast(inodes.id char(6) charset latin1)), '_4'), inodes.senstype4, inodes.a4, inodes.b4, inodes_data.sens4, inodes_structure_data.sensor4_units             cso_city_15.inodes             inner join cso_city_15.inodes_structure_data                 on inodes.id = inodes_structure_data.id             inner join cso_city_15.inodes_data inodes_data                 on inodes.id = inodes_data.i_id             find_in_set(trim(cast(inodes.id char(6) charset latin1)), node_string)                 , inodes_data.time between begin_date , end_date      ) sensor_reading     find_in_set(sensorid, sensor_string) order datecollected;  set @sql = concat('select datecollected, ', @sql, ' (         select inodes.id nodeid, from_unixtime(300 * round(unix_timestamp(inodes_data.time)/300)) datecollected, trim(inodes.descr) parentid,         1 sensornumber, concat(''c'', trim(cast(inodes.id char(6) charset latin1)), ''_1'') sensorid, inodes.senstype1 sensortitle, inodes.a1 multiplier, inodes.b1 offset, inodes_data.sens1 collectedvalue,          inodes_structure_data.sensor1_units unitsofmeasure             cso_city_15.inodes             inner join cso_city_15.inodes_structure_data                 on inodes.id = inodes_structure_data.id             inner join cso_city_15.inodes_data inodes_data                 on inodes.id = inodes_data.i_id             find_in_set(trim(cast(inodes.id char(6) charset latin1)), ''',node_string,''')                 , inodes_data.time between ''',begin_date,''' , ''',end_date,'''          union          select inodes.id, from_unixtime(300 * round(unix_timestamp(inodes_data.time)/300)), trim(inodes.descr),         2, concat(''c'', trim(cast(inodes.id char(6) charset latin1)), ''_2''), inodes.senstype2, inodes.a2, inodes.b2, inodes_data.sens2, inodes_structure_data.sensor2_units             cso_city_15.inodes             inner join cso_city_15.inodes_structure_data                 on inodes.id = inodes_structure_data.id             inner join cso_city_15.inodes_data inodes_data                 on inodes.id = inodes_data.i_id             find_in_set(trim(cast(inodes.id char(6) charset latin1)), ''',node_string,''')                 , inodes_data.time between ''',begin_date,''' , ''',end_date,'''          union          select inodes.id, from_unixtime(300 * round(unix_timestamp(inodes_data.time)/300)), trim(inodes.descr),         3, concat(''c'', trim(cast(inodes.id char(6) charset latin1)), ''_3''), inodes.senstype3, inodes.a3, inodes.b3, inodes_data.sens3, inodes_structure_data.sensor3_units             cso_city_15.inodes             inner join cso_city_15.inodes_structure_data                 on inodes.id = inodes_structure_data.id             inner join cso_city_15.inodes_data inodes_data                 on inodes.id = inodes_data.i_id             find_in_set(trim(cast(inodes.id char(6) charset latin1)), ''',node_string,''')                 , inodes_data.time between ''',begin_date,''' , ''',end_date,'''          union          select inodes.id, from_unixtime(300 * round(unix_timestamp(inodes_data.time)/300)), trim(inodes.descr),         4, concat(''c'', trim(cast(inodes.id char(6) charset latin1)), ''_4''), inodes.senstype4, inodes.a4, inodes.b4, inodes_data.sens4, inodes_structure_data.sensor4_units             cso_city_15.inodes             inner join cso_city_15.inodes_structure_data                 on inodes.id = inodes_structure_data.id             inner join cso_city_15.inodes_data inodes_data                 on inodes.id = inodes_data.i_id             find_in_set(trim(cast(inodes.id char(6) charset latin1)), ''',node_string,''')                 , inodes_data.time between ''',begin_date,''' , ''',end_date,'''      ) sensor_reading     find_in_set(sensorid, ''',sensor_string,''')  group datecollected');  prepare stmt @sql; execute stmt;  end$$ delimiter ;

sample results call cso_city_3.reading_pivot('2015-07-07','2015-07-08','26121','c26121_1,c26121_2')

+---------------------+--------------------+--------------------+ | datecollected       | c26121_1           | c26121_2           | +---------------------+--------------------+--------------------+ | 2015-07-07 00:00:00 | 0.8928999044001102 |   1.73075295612216 | | 2015-07-07 00:05:00 | 0.8787019047886133 |   1.73075295612216 | | 2015-07-07 00:10:00 | 0.8928999044001102 |  1.759125955402851 | | 2015-07-07 00:15:00 | 0.8787019047886133 |   1.73075295612216 | | 2015-07-07 00:20:00 | 0.8928999044001102 | 1.7023799568414688 | | 2015-07-07 00:25:00 | 0.8928999044001102 |  1.759125955402851 | | 2015-07-07 00:30:00 | 0.8928999044001102 |   1.73075295612216 | | 2015-07-07 00:35:00 | 0.9070979040116072 |  1.759125955402851 | | 2015-07-07 00:40:00 | 0.8787019047886133 |   1.73075295612216 | | 2015-07-07 00:45:00 | 0.8928999044001102 |   1.73075295612216 | | 2015-07-07 00:50:00 |  0.935493903234601 | 1.7023799568414688 | | 2015-07-07 00:55:00 | 0.8928999044001102 |   1.73075295612216 | | 2015-07-07 01:00:00 | 0.8928999044001102 |   1.73075295612216 | | 2015-07-07 01:05:00 | 0.8928999044001102 |   1.73075295612216 |

any , or guidance appreciated!

i suggest dynamically construct query, execute it. here example , how works.


Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more