Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load.

It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause performance issues in MySql (eg because it is not cached by the engine)?

Please note that this SP is called from several other SPs. It passes results to parent SPs using a temp table.

UPDATE - problem solved (see my answer below)

CREATE PROCEDURE `CreateAreas`(
    _areas varchar(21844),
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
BEGIN

    -- create temporary table "areas"
    -- fill with area ids

    create temporary table areas (
        id int not null,
        code varchar(30),
        name varchar(100),
        shortName varchar(100),
        levelid int not null,
        sortOrder int not null,
        key (id)
    );

    -- assumes that only one of the 3 options is valid, areas, comparison group, bounded comparison group

    if (_areas is not null) then

        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder) select id, Code, Name, ShortName, LevelID, 0 from GeoArea where Code in (''', replace(_areas, ',', ''','''), ''')');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    elseif (_comparisonGroup is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
        select GeoAreaID, GeoArea.Code, GeoArea.Name, GeoArea.ShortName, GeoArea.LevelID, SortOrder
        from ComparisonGroupGeoAreaLink
        INNER JOIN
        GeoArea
        ON GeoArea.ID = GeoAreaID
        where ComparisonGroupID = (select id from ComparisonGroup where Identifier = _comparisonGroup)
        and IsMember = 1;

    elseif (_parentArea is not null and _areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
    select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from (select id from GeoArea where Code = _parentArea) as t
        INNER JOIN
        GeoAreaLinkCache c
        ON
        c.ParentAreaID = t.id
        inner join GeoArea a
        on c.ChildAreaID = a.ID
        INNER JOIN
        (select id from GeoAreaLevel where Identifier = _areaType) as l
        ON
        a.LevelID = l.id;        

    elseif (_areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder)
        select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from 
        (select id from GeoAreaLevel where Identifier in (''', replace(_areaType, ',', ''','''), ''')) l
        INNER JOIN
        GeoArea a
        ON
        a.LevelID = l.id');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;


    end if;                 

END
share|improve this question
    
Have you tried manually running the same SELECT clauses that feed the INSERTs to see if there are slow queries involved (due to poor indexing etc)? Are all the columns used in the conditions and joins indexed? –  zagrimsan Nov 6 '13 at 10:10
    
Is this a different question than the other one you posted? –  Michael - sqlbot Nov 6 '13 at 21:19
    
use profiling? i think that MySQL needs to convert HEAP into an MyISAM tables because the memory is full.. you may want to increase max_heap_table_size see dev.mysql.com/doc/refman/5.1/en/…... as side note: INSERT .... SELECT isn't free you know this will force 1 thread off your CPU to 100% load if this is an long copy –  Raymond Nijland Nov 6 '13 at 22:19
    
Yes it is different question about same SPs. –  Maxim Eliseev Nov 7 '13 at 10:25

1 Answer 1

Good news - it seems dynamic SQL is NOT a bottleneck.

The solution was to change temp tables to MEMORY engine. It did the trick and made AMAZING difference. Solution was suggested in

Can MySql nested SP be a bottleneck?

create temporary table areas (
    id int not null,
    code varchar(30),
    name varchar(100),
    shortName varchar(100),
    levelid int not null,
    sortOrder int not null,
    key (id)
) ENGINE=MEMORY;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.