Query Optimization Needed - 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

Transact-SQL (2000)

Query Optimization Needed

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-12-08 : 07:05:11

I have written the query using a function, please review the query and optimize it if it could be optimized as its taking days on a 4 million data
select distinct Responses,
dbo.udf_question(51, response) as first_name,
dbo.udf_question(52, response) as last_name,
dbo.udf_question(53, response) as Address,
dbo.udf_question(55, response) as City,
dbo.udf_question(56, response) as State,
dbo.udf_question(57, response) as Zip,
dbo.udf_question(104, response) as Phone,
dbo.udf_question(270, response) as Breed,
dbo.udf_question(268, response) as DName,
dbo.udf_question(269, response) as DAge,
dbo.udf_question(294, response) as OptinForEmailUpdate,
dbo.udf_question(295, response) as OptinForMailUpdate
from Responses o
where question_id = 1
and survey_id = 1
and start_time between '2005-10-10 03:41:42.530' and '2006-01-01 03:41:42.530'

/*************/
--Function
create function udf_question(@question_id int, @response varchar(750))
returns varchar(750)
as
begin
declare @response varchar(750)

select top 1 @response = response
from Responses
where question_id = @question_id
and participant_id in(select participant_id from Responses where question_id = 287 and response = @response)
and response is not null and response <> ''

return @response
end

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-08 : 08:27:13

What you have posted cannot represent the real situation. It's not possible to create a UDF that has a parameter and a local variable declaration with the same name.

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-12-08 : 08:29:53

sorry, in my code the local variable is @res.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-08 : 08:40:53

Are there really two different columns in the Responses table, one called response, the other called Responses?

At the very least post the DDL for the Responses table, and explain what the response column is supposed to represent.

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-12-08 : 08:50:53

you can understand from the function that there is response for each question in the Responses table. And I want to display this horizontal data into vertical form. But the rows in responses could be different for the same question at different times. that's why we need only one response for each question.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-08 : 09:29:43

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Will we ever discover The Mystery of Question 287?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources