MSSQl computed values and functions

Posted on 2009-02-16
Last Modified: 2012-05-06
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]
/****** Object:  UserDefinedFunction [dbo].[edu_gpa_max]    Script Date: 02/17/2009 13:27:25 ******/
ALTER FUNCTION [dbo].[edu_gpa_max]
      @cn_id decimal
RETURNS decimal(4,2)

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


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??
Question by:isaackhazi
    1 Comment
    LVL 51

    Accepted Solution

    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


    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now