long running time... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

long running time...

Author

Topic

pitt1
Starting Member

16 Posts

Posted - 2006-05-28 : 07:52:31

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-28 : 08:15:54

tried this ? INNER JOIN the 2 tables together instead of using function ?

SELECT distinct
       isnull( a.[column1] , b.[some_column_1] ) AS [column1],
       a.[column2]
FROM   dbo.table a inner join [server_name].dbo.some_table b
       on a.[column2] = b.[some_column_2]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-28 : 08:49:10

Is it necessary to use link server? cant you get the table on the same server ???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

pitt1
Starting Member

16 Posts

Posted - 2006-06-01 : 06:15:16

Hi, thanks all,
it seems that the inner join helps in few seconds and so does copying the table into
the same server...

Thanks !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources