How to show large data

[bharatdhalla]

i am working in vs-2005, c# and sql-server 2005. i want to show a large data about 10000 records in a form at a single time.
Problem is the response time of query, where i am using three functions in the query. tables are already indexed with required fields.
Please advice how can i make it fast.

[Roelf]

well, try to avoid using functions in the where clause of your query. These functions will act on all records in the table, not only on the resultset. Optimize your functions. But if each function takes 1 ms to execute, with 3 functions and 10000 records, there will still be 30 s execution time.

can you post the query and the code for the functions? We can see then if we can help to optimize.

[bharatdhalla]

Thanks for your response.
In following function i am sending a orderid to function and get the related orderids those have same billing address as in requested order.
But Orders table has approx. 3 lacs records.

the function is --

ALTER FUNCTION [dbo].[fn_getOrderNumberMatchByBillAdd]
(
@orderId varchar(30)
)
RETURNS varchar(max)
as
BEGIN

declare @orderList varchar(max),@orderBillingAddress varchar(max)
Set @orderList=''

Select @orderBillingAddress=Replace((isnull(bFullName,'')+isnull(bAddress1,'')+isnull(bAddress2,'')+isnull( bCity,'')
+isnull(bState,'')+isnull(bZip,'')+isnull(bCountry,'')+isnull(bPhone,'')),' ','') From Orders Where OrderID=@orderId

Select @orderList=COALESCE(@orderList + ' ','')+ (Case isnull(S.OrderImage,'') When '' then '' else '<img src='''+S.OrderImage+'''/>' end ) + O.OrderId
From Orders O
left outer join OrderStatus S on O.OrderStatus=S.OrderStatus
Where Replace((isnull(bFullName,'')+isnull(bAddress1,'')+isnull(bAddress2,'')+isnull(bCity,'')+isnull(bSta te,'')+
isnull(bZip,'')+isnull(bCountry,'')+isnull(bPhone,'')),' ','')=@orderBillingAddress and OrderId<>@orderId

RETURN @orderList
END