x

Query performance

Hi

I have a stored procedure which queries in 5 secs.

Recently i have added a UDF in select

select col1,col2,col3,UDF_function(col1),col4 from table inner join table 1 ..........11 tables are joined here.

after adding udf_function() my query execution time is taking minimum 15 mins which is horrible. Kindly help me to how to improve the performance.

the function returns the values of a column as comma seperated string. it means i am looping through the records in the function.

The reason why i am using UDF is becos of the below table structure and requirement

Table1

p.k 	
col1    col2    col3
1       x       z
2       v       q
3       n       w
4       m       e

Table2

f.k 
1       a
1       b
1       c
2       a
2       d

Output

col1    col2
1       a,b,c
2       a,d
3
4

This is mainly to avoid duplicates

Thanks in advance Usha

more ▼

asked Nov 03, 2009 at 08:54 AM in Default

Usha gravatar image

Usha
110 7 7 8

Can you provide us with some more details please (table strucutre, sample data)?
Nov 03, 2009 at 09:23 AM John Sansom
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Im not clear behind your reasons for doing this.

You say "This is mainly to avoid duplicates" surely, is that you want the distinct characters in the columns ?

Please provide a better example , as SQL script would be perfect

more ▼

answered Nov 04, 2009 at 05:47 AM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

I have a table1 and table2. table2 is child of table1 and one row od table1 has many rows in table2. when joining i am havind duplicates of table1 in the result. as i need only 1 col of table2 i want all the values for table.id to be shown as comma seperated string. hope am clear with the requirement.
Nov 04, 2009 at 06:03 AM Usha
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x251
x27

asked: Nov 03, 2009 at 08:54 AM

Seen: 1281 times

Last Updated: Nov 04, 2009 at 08:12 AM