The following function gets called from a query - from the WHERE CLAUSE - (which is/must be generated dynamically by web page).
As expected, the function is called millions of times by the app and is causing significant performance degradation.
I want to design the query so that it uses a join to a table (that I want to create ... how? based on the function ...) rather than expect the result from the UDF.
How do I do so?
The function basically just returns the max(student_app_id) which is the PKey (clustered) of the main table the UDF queries.
The UDF, the table structure, and the query that calls the UDF are included below, for completness.
---- UDF CODE
CREATE FUNCTION [dbo].[fnStAppId_Prospect](@iuserId int,@iappId int)
DECLARE @rv int,@SID int
Set @rv = 0
--get latest student_app_id which has been modified.
select @SID = max(student_app_id)
where user_id = @iuserId and confirmation_number is null
AND (date_last_modified = (SELECT MAX(date_last_modified)
WHERE user_id = @iuserId))
AND application_id = @iappId
IF @SID > 0
IF EXISTS(select student_app_id from personal_info where student_app_id = @SID)
ELSE IF EXISTS(select student_app_id from people_names where student_app_id = @SID)
ELSE IF EXISTS(select student_app_id from special_cccapply_personal_info where student_app_id = @SID)
select distinct prospect_info.prospect_status_id as field_1_prospect_status_id, prospect_info.date_stamp as field_5_date_stamp, prospect_info.slots_downloaded
as field_6_slots_downloaded, table_1_people_names.last_name as field_2_last_name, table_1_people_names.first_name
as field_3_first_name, table_2_personal_info.ssn as field_4_ssn
from prospect_info left outer join system_xap.dbo.people_names as table_1_people_names
on table_1_people_names.user_id=prospect_info.user_id and table_1_people_names.application_id=0 and table_1_people_names.question_number=3
and (((dbo.fnStAppId_Prospect(table_1_people_names.user_id,5))>0 and table_1_people_names.student_app_id = dbo.fnStAppId_Prospect(table_1_people_names.user_id,5))
or ((dbo.fnStAppId_Prospect(table_1_people_names.user_id,5) = 0 and table_1_people_names.student_app_id is null)) )
left outer join system_xap.dbo.personal_info as table_2_personal_info on table_2_personal_info.user_id=prospect_info.user_id
and table_2_personal_info.application_id=0 and (((dbo.fnStAppId_Prospect(table_2_personal_info.user_id,5))>0
and table_2_personal_info.student_app_id = dbo.fnStAppId_Prospect(table_2_personal_info.user_id,5))
or ((dbo.fnStAppId_Prospect(table_2_personal_info.user_id,5) = 0 and table_2_personal_info.student_app_id is null)) )
where 1=1 and prospect_info.application_id=5 and prospect_info.prospect_status_id in
FROM prospect_info LEFT OUTER JOIN system_xap.dbo.application_status AS application_status
ON prospect_info.user_id=application_status.user_id and prospect_info.application_id=application_status.application_id
WHERE (prospect_info.user_id NOT IN
(SELECT user_id FROM application_status aps WHERE (aps.application_id = 5) AND (confirmation_number IS NOT NULL)))
AND (prospect_info.application_id = 5)) and CAST(prospect_info.date_stamp AS DATETIME) between '1/1/2013' and '1/31/2013 23:59:59'
--- *********** TABLE STRUCTURE
CREATE TABLE [dbo].[application_status](
[student_app_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[user_id] [int] NOT NULL,
[application_id] [int] NOT NULL,
[date_started] [smalldatetime] NULL,
[date_last_modified] [smalldatetime] NULL,
[application_type] [char](1) NULL,
[on_page] [int] NULL,
[confirmation_number] [char](40) NULL,
[order_id] [int] NULL,
[payment_type_id] [int] NULL,
[date_completed] [smalldatetime] NULL,
[date_batched] [smalldatetime] NULL,
[date_downloaded] [smalldatetime] NULL,
[certification_info] [varchar](64) NULL,
[date_submitted] [smalldatetime] NULL,
[submitted_term_year] [varchar](12) NULL,
[app_method] [char](3) NULL,
[intended_term] [varchar](12) NULL,
[date_replica_downloaded] [smalldatetime] NULL,
[slots_downloaded] [int] NULL,
[submitted_major] [varchar](96) NULL,
[date_downloaded2] [smalldatetime] NULL,
CONSTRAINT [PK_StudAppID] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]