?
Solved

MSSQl computed values and functions

Posted on 2009-02-16
1
Medium Priority
?
283 Views
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]
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??
0
Comment
Question by:isaackhazi
1 Comment
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1500 total points
ID: 23657062
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

840 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