MSSQl computed values and functions

In the master table i have the following column :

[cn_latest_position]  AS ([dbo].[last_position]([cn_id]))

This is the function that corresponds to ([dbo].[last_position]

USE [PRTR_recruit_live]
GO
/****** Object:  UserDefinedFunction [dbo].[edu_gpa_max]    Script Date: 02/17/2009 13:27:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[edu_gpa_max]
      (
      @cn_id decimal
      )
RETURNS decimal(4,2)
AS

      BEGIN
         
      RETURN (SELECT     MAX(edu_gpa) AS MaxGpa
FROM         tblEducation
WHERE     (edu_candidate = @cn_id))

end


When i query the database It takes around 90 seconds to return the top 1000 rows

If i exclude the column from the SELECT statement it returns 1000 rows in 1 sec.

This used to be a database on a webserver which i downloaded and attached to be used on the local intranet.

Any idea why it is so slow??
LVL 8
isaackhaziAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well part of the problem is that it is kind of a recursive / runtime function being a computed column. So for each row, it needs to resolve the function before it can retrieve the row and that really stuffs up any plans.

given the structure, what about trying an inline query ?

select  cn_id, (SELECT MAX(edu_gpa) FROM  tblEducation  WHERE edu_candidate = @cn_id)  AS cn_latest_position
from mytable

or

select cn_id, maxgpa
from mytable
left outer join (select edu_candidate, max(edu_gpa) as maxgpa from tbleducation group by edu_candidate) g on g.edu_candidate = mytable.cn_id
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.