regarding storepocedure performance

[tatarao25]

hi all,

i am writing store procedure so please send me steps to follow that increase the performance

[debasisdas]

You only know what you are writing .

[tatarao25]

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]

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.

[tatarao25]

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]

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.id

may 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.

[tatarao25]

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.id

may 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.

by mistake i added that it is with table2.id = table1.id

[debasisdas]

You need to fix the other joins as well.

[tatarao25]

You need to fix the other joins as well.

select * from table1
inner join table2 on table2.id = table1.id
left outer join table3.id = table2.id
left outer join table4.id = table1.id
left outer join table5.id=tabel4.id
inner join table9 on table6.id = table1.id

[smantscheff]

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.

[tatarao25]

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

[tatarao25]

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]

So what are you doing after that SQL query ?

[tatarao25]

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]

your procedure contains only the SQL , and nothing else ?

[tatarao25]

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]

why not use a view instead.

[tatarao25]

why not use a view instead.

ya sure i will try with view but Wat is the drawback with temp table

[debasisdas]

if you are simply selecting from other tables , why need to store the result set.

[tatarao25]

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]

that can be done using a view also.

If you have Hugh records, the insert into temp table will take a lot of time.

[tatarao25]

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