[MDEV-23293] Stored procedure having just one plain insert stmt takes 10 times longer that executing same insert directly Created: 2020-07-25  Updated: 2020-11-15  Resolved: 2020-11-15

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Shamim Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

mariadb in ubuntu


 Description   

I have created a stored procedure which has just one insert statement to a table which takes 10 times longer to execute as compared to sending the exact same insert statement executed using mariadb client.

I am calling the stored procedure from a python program. Tested the same insert SQL directly executing from python program.

It takes one second to do 50 inserts (row) by calling stored procedure each time
It takes one second to do 500 inserts (no stored procedure call) by executing insert SQL stmt each time

 Comments   
Comment by Marko Mäkelä [ 2020-07-27 ]

shamim.ahmad@nttdata.com, can you please provide more details, such as:

  • the software version
  • a complete test case, at least including the CREATE TABLE and INSERT statements
Comment by Shamim [ 2020-07-27 ]

Following the maraidb version, i am using:

mariadb -V
mariadb Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

I will post here in next comments the DDL, DML, i am using

Comment by Shamim [ 2020-07-27 ]

Following is the create table, DDL:

CREATE TABLE `MIS` (
`RECNO` bigint(20) NOT NULL AUTO_INCREMENT,
`PNAME` varchar(128) DEFAULT NULL,
`PID` varchar(64) DEFAULT NULL,
`ANO` varchar(16) DEFAULT NULL,
`STUID` varchar(64) DEFAULT NULL,
`SOUID` varchar(64) DEFAULT NULL,
`FNAME` varchar(12) DEFAULT NULL,
`DUP` varchar(1) DEFAULT NULL,
`MD5` varchar(32) DEFAULT NULL,
PRIMARY KEY (`RECNO`),
KEY `mis_pname_ix1` (`PNAME`) USING BTREE,
KEY `mis_pid_ix1` (`PID`) USING BTREE,
KEY `mis_ano_ix1` (`ANO`) USING BTREE,
KEY `mis_stuid_ix1` (`STUID`) USING BTREE,
KEY `mis_souid_ix1` (`SOUID`) USING BTREE,
KEY `mis_fname_ix1` (`FNAME`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Below is the insert stmt, using dummy data here:

insert into MIS (PNAME, PID, ANO, STUID, SOUID, FNAME, DUP, MD5) values ('pname123', '123', '567', '280.100.12345.98765', '1.2.3.100.200.300.1', 'file1.001', NULL, 'JSKJKS98978979KJKJKJKJ');

Comment by Shamim [ 2020-07-27 ]

Exact same insert, i am using in stored procedure which takes 10 time longer to complete execution:
insert into MIS (PNAME, PID, ANO, STUID, SOUID, FNAME, DUP, MD5) values (var1, var2,.........)

Initially i suspected "START Transaction", row level locking for select/updates... then i removed all those business logic from stored procedure, and just used one plain insert stmt, just to test the performance, and found this issue.

Comment by Shamim [ 2020-07-27 ]

My application is written in python, i will put some code snippets here for SP call versus insert's SQL execution.

Comment by Elena Stepanova [ 2020-10-17 ]

shamim.ahmad@nttdata.com,

Any luck with the code snippets? I can't reproduce the described effect so far.

Generated at Thu Feb 08 09:21:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.