hi all,
i am writing store procedure so please send me steps to follow that increase the performance
debasisdas 580
You only know what you are writing .
You only know what you are writing .
store procedure is used for performance, but in my procedure i need to use cursors and left outer joins so i am asked that question
debasisdas 580
but to come with any solution, one need to know
1. what is the table structure
2. which all tables are joined in your left outer join
3. what are you doing with the cursor.
4. how many records you are processing ....etc
don't expect others to guess and solve your problem.
joing 6 tables and record count may 8-10 lac
and i am using cursor to pass user id and fetch each user id count separately like (array object)
example:
declare getId cursor for
select id from table1;
OPEN getId;
-- Iterate Values In Cursor
repeat
FETCH getId INTO idvalue;
-- Checks For Record
if not recordnotfound then
select * from table1
inner join table2 on table1.id = table3.id
left outer join table4.id = table5.id
left outer join table6.id = table7.id
left outer join table8.id=tabel9.id
inner join table9 on table9.id = table1.id
where table1.id= idvalue
until recordnotfound end repeat;
-- closing cursor
CLOSE getUserId;
debasisdas 580
your join looks weird.
you are saying 6 tables but actually using 9
as per your code
select * from table1
inner join table2 on table1.id = table3.idmay i know why table1 is joined with table2 on table3.id ?
NOTE:-- There should be at least n-1 number of joins for joining n number of tables.
your join looks weird.
you are saying 6 tables but actually using 9
as per your code
select * from table1 inner join table2 on table1.id = table3.idmay i know why table1 is joined with table2 on table3.id ?
NOTE:-- There should be at least n-1 number of joins for joining n number of tables.
debasisdas 580
You need to fix the other joins as well.
smantscheff 265
This looks like rubbish.
How do you join table9 with a link from table6 to table1?
Why don't you post code which you have actually tried?
Submit a test case, complete with table structures, test data and the actual code which you are trying to implement.
This looks like rubbish.
How do you join table9 with a link from table6 to table1?
Why don't you post code which you have actually tried?
Submit a test case, complete with table structures, test data and the actual code which you are trying to implement.
it is not table9 it is table6
This looks like rubbish.
How do you join table9 with a link from table6 to table1?
Why don't you post code which you have actually tried?
Submit a test case, complete with table structures, test data and the actual code which you are trying to implement.
it is not table9 it is table 6
debasisdas 580
So what are you doing after that SQL query ?
So what are you doing after that SQL query ?
i am calling this procedure from my service it is executing fine but it taking time about 15-20 min
debasisdas 580
your procedure contains only the SQL , and nothing else ?
your procedure contains only the SQL , and nothing else ?
i created temporary table
DROP TEMPORARY TABLE IF EXISTS lib_Explode_InierviewerCumm_Performance;
CREATE TEMPORARY TABLE lib_Explode_InierviewerCumm_Performance
(`UserId` int NOT NULL,
`UserName` varchar(50),
`Name` varchar(50),
`EmailId` varchar(50),
`NoOfInterviewsTaken` int,
`NoOfShortlists` int,
`NoOfRejects` int,
`NoOfTechnicalScreening` int,
`NoOfTechnicalScreeningShortlists` int,
`NoOfTechnicalScreeningRejects` int) ENGINE=Memory COMMENT='Explode() results.';
inserting select statement row set to TEMPORARY table
finally i am getting output from TEMPORARY table
as select * from lib_Explode_InierviewerCumm_Performance
debasisdas 580
why not use a view instead.
why not use a view instead.
ya sure i will try with view but Wat is the drawback with temp table
debasisdas 580
if you are simply selecting from other tables , why need to store the result set.
if you are simply selecting from other tables , why need to store the result set.
it is not for only single id ,the final result set i want is collection
debasisdas 580
that can be done using a view also.
If you have Hugh records, the insert into temp table will take a lot of time.
that can be done using a view also.
If you have Hugh records, the insert into temp table will take a lot of time.
thank u so much u have give lot of information
