Improve the speed of Function

[member27]

Hi all , Iam Using a query which returns a bulk of data. first my query contains sub query to find the count so i panned to change as a function .. So i created a function which returns a table.. create function fnname returns table(name as varchar,....,....,...,...,...,...) I inserted the values into the temp table from the select query and (i want to find the count of receipt numbers so ... i updated the query with another query update temp table set rno =(select count(rno) from table where ....) then i executed the function but it is quite slow please suggest me any way to increase the speed of the function or query.. how to increase the speed of the function

[PIEBALDconsult]

cbenan wrote:

sub query

X| Have you tried a join?

[Mycroft Holmes]

It is unikely that the use of the function will affect the speed, it is more likely to be the number of times you call it. Quite often you can change the performance by totally restructuring your query. Break it down to part and do the part that reduces your data volume the most first. Try using the execution plan to identify table scans that do not use indexes. I have found the using table vars to hold large volumes is a disaster - I have gone back to #temp tables for anything over 2k rows. I also have no compunction about throwing an index onto a temp table. If you have 2005 then you may want to look at rownumber and partitioning in a query to get the count by invoices. Your question does not have 1 answer, this is where science turns into art, good luck!

Never underestimate the power of human stupidity RAH

[Wendelius]

Typically if you break down a single query to multiple different operations using a function or a procedure, the result can be slower. Because if you have separate SQL statements there's no way for the optimizer to search for the best plan to the whole query, only for the subcomponents. Also if you use temp tables and update and select them, you'll most likely cause physical I/O which may/may not be prevented when using a single query. Sometimes it's just impossible to write a single query for a certain task, but if you do have the opportunity, you should test the results using this approach (create a single query, combine few operations thus eliminating intermediate steps etc). Another thing is indexing. Make sure that you have adequate indexes and on the righ columns (or column combinations). For further details, I think you should post the code you currently have.

The need to optimize rises from a bad design.My articles[^]

[member27]