improve running time...

[clyde11]

Hi all,
I have an access to some table which i can't change, only select statements and stuff.
there are some values in a specific column in this table which are *NULL* values, in each place of this NULL value i want to show a very specific value, that is calculated in a UDF (user defined function), in this function, i'm searching for a specific value in another table, to change the null value to something else,
this is done using a VIEW...

Now, I wrote this:

the view:
==========

CREATE VIEW dbo.my_view
AS
SELECT distinct

isnull( [column1] , my_function( [column2] ) ) AS [column1],
[column2]

FROM dbo.table



where my_function defined as follows:
=====================================


CREATE FUNCTION [dbo].[my_function] (@col2 varchar(200) )

RETURNS datetime

AS

BEGIN


declare @str varchar(20), @date datetime

set @str = substring( @col2 ,2 ,3) --some parsing


select @date = [some_column_1]
from [server_name].dbo.some_table
where [some_column_2] = @str


return @date

END




now, this "some_table" size is about 2000 rows, the number of null
values in "table" are about 1100,


all works just fine, just that it takes about 13-15 seconds to
finish the view, i need to use this view in some kind of a web
page search engine but it's take too much time...

any help of improving (in terms of efficiency)
this udf/view will be appreciated

Best,
C.

 

[bborissov]

How about:

CREATE VIEW dbo.my_view
AS
SELECT DISTINCT isnull(column1,Tbl1.[column2]) AS column1,  
                column2
FROM dbo.table
LEFT JOIN [server_name].dbo.some_table Tbl1 ON 
     substring(table.Column2,2 ,3) = Tbl1.some_column_2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.