How to reduce the time when using a variable

Hi,

I have a table with column value like '123 345 678 143 648' like that. What I need to do is I have to take each code value and put it as a new record in another table. So, if I say 'Select substring(column_name,1,3) from table' then it is very fast (fraction of second). But since I need to take each code and the # of codes in each record may vary, I am using a while loop to take each code and so I delclared a variable @.i and now my select statement is like this: 'Select substring(column_name,@.i,3) from table'. Interesting now this select statement is taking almost 2 mins for each iteration.

Why it is like this? Is there any way I can reduce the time taken to execute each iteration?

Thanks.

how many records of this kind are you processing.

the number of records must ne put into consideration.

a cursor that iterate with every records first must be put in place

then place the while loop inside every fetch of the cursor

do the processing(while loop) of the column

i think there should be two loops to handle this case.

one that iterates with the records

and one that iterates for every codes in the the column.

if this is an SP. a with recompile option will make your query faster

regards.

|||

What about

INSERT INTO table1

SELECT SUBSTRING(column_name,1,3) from table WHERE SUBSTRING(column_name,1,3) <> ''

UNION

SELECT SUBSTRING(column_name,4,3) from table WHERE SUBSTRING(column_name,4,3) <> ''

UNION

...

? Copy/paste above line until substring index matches column_name size; or, if you don't want to hardcode it - construct this query referring to column_name size and run it through EXEC.

This could be faster - try it out...

|||

If you are using SQL Server 2005 then you can do the following:

1. Create a table-valued function that takes a string and splits it based on space character. You can search the web for several sample functions that does this operation

2. Use it in a SELECT statement using APPLY operator like:

insert into tbl(i, ...)

select s.val

from table as t

cross apply splitstr(t.column_name) as s

In SQL Server 2000, you can do similar operation except there is no APPLY operator so you cannot use a TVF. You can use a table of numbers to split the string and then do a cross join with this table instead. Or the approach using UNION as suggested in another post will also work. Make sure you use UNION ALL though for better performance and to eliminate the unnecessary sorting.

|||Here is an article on handling arrays and lists - quite a few methods and instruments, may be useful.|||

Hi Umachandar,

Your tip was very helpful to me. Now the whole process completes in 2 mins. Thx.

sql