Click here to Skip to main content
14,452,838 members

Inserting Data to temporary table in stored procedure taking too long time to execute in mysql

ppdeva asked:

Open original thread
Hi,
While inserting data to a temporary table inside the stored procedure in mysql database..
it is taking too long time (8-11 mins) to insert approx 1300 records..i don't understand why..please help.

my lines inside procedure are:
create temporary table mr(PageNo int,RecordNO int ,GranteeFirstName varchar(50),GranteeLastName varchar(50),GrantorFirstName varchar(50),GrantorLastName varchar(50),Deeds varchar(50),RType varchar(50),Book varchar(50),
Volume varchar(50),Page varchar(50),DocumentDate varchar(50),FiledDate varchar(50),
Region varchar(50),Sector varchar(50),Block varchar(50),Survey varchar(50),Description varchar(50)); 

insert into mr(PageNo,RecordNO,GranteeFirstName,GranteeLastName,GrantorFirstName,GrantorLastName,Deeds,RType,Book,Volume,Page,DocumentDate,FiledDate,Region,Sector,Block,Survey,Description)
select PageNo,RecordNO,GranteeFirstName,GranteeLastName,GrantorFirstName,GrantorLastName,Deeds,RType,Book,Volume,Page,DocumentDate,FiledDate,Region,Sector,Block,Survey,Description from
(select PageNo,RecordNO,GranteeFirstName,GranteeLastName,GrantorFirstName,GrantorLastName,Deeds,RType,Book,Volume,Page,DocumentDate,FiledDate,Region,Sector,Block,Survey,Description from User1DataEntryTable 
union all
select PageNo,RecordNO,GranteeFirstName,GranteeLastName,GrantorFirstName,GrantorLastName,Deeds,RType,Book,Volume,Page,DocumentDate,FiledDate,Region,Sector,Block,Survey,Description from User2DataEntryTable) as p
group by PageNo,RecordNO,GranteeFirstName,GranteeLastName,GrantorFirstName,GrantorLastName,Deeds,RType,Book,Volume,Page,DocumentDate,FiledDate,Region,Sector,Block,Survey,Description 
having count(*)>1;


any help will be appreciated.. thanks in advance.
Tags: MySQL

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100