Mysql too many while loops inside one stored procedure

views:

661

answers:

Mysql too many while loops inside one stored procedure

Hi! I have one stored procedure in while I am getting comma separated values in parameters. I have three parameters which has comma separated values. and i need to put them in table's columns so I am using while loop. but i am scared when too many(say lakhs of users) users will connect to my website then my procedure will have performance issues.
Does anyone have better solution on this issue?

Thanks in advance.
Regards,
MySQL DBA

SQL is interpreted, even though it is said to be 'compiled'. This means that your code might run faster if you simply break up the strings outside the DB and execute multiple inserts instead of 1 call to a SQL proc which using interpreted SQL loops through the input.

Frans Bouma 2009-05-18 07:55:22

Hi Frans! Thanks for your response. But can you elaborate what do you mean by break up strings outside DB?

MySQL DBA 2009-05-22 05:33:14

I meant that instead of doing the string processing inside the DB, you might want to rewrite outside code + sql so that you can do string processing outside the SQL and use SQL only for operations directly on the data.

Frans Bouma 2009-05-22 09:51:03

Your question is not clear to me. And please let know which version of MySQL r u using?

2009-06-29 08:21:08

Hi! I am using MySQL 5.1. Also What my question was in my SP, there is one parameter which has multiple column values but comma separated. For ex: Create procedure SP_Test(IN InputTestValues VARHCAR(500)beginInputParameter = (1,2,3);In this procedure i want to insert data in my table TB1which has 3 columns and in these three columns i want to insert procedure's InputTestValues parameter value.So my concern is using while loop will decrease my performance in future.End;

MySQL DBA 2009-06-29 09:45:27

ansaurus

tags:

views:

answers:

Mysql too many while loops inside one stored procedure

related questions