Help Me Optimize My Query

Hi! I have an optimisation problem. I've currently got a query that retrieve about 10K records according the mode that user difined earlier.

At the very beginning I've wrote the same code through the user-defined functions, but I found in publications that it's a wrong way to develop with MSSQL, but it worked more and more faster (3 sec). Now I rewrite my query with inline table functions...CREATE FUNCTION ans_app_name1()


RETURNS TABLE AS RETURN
(SELECT CASE WHEN OBJECT_ID('app_name') > 0 THEN
(SELECT TOP(1) app_name FROM app_name WHERE spid = @@spid)
ELSE NULL END AS app_name )
GO

CREATE FUNCTION ans_get_user_property1(
@property_name VARCHAR(255))
RETURNS TABLE AS RETURN
(SELECT u.value AS user_property FROM user_property u
JOIN ans_app_name1() a ON a.app_name = u.app_name
WHERE property = @property_name AND
username = system_user)
GO

CREATE FUNCTION ans_get_mode()
RETURNS TABLE AS RETURN
(SELECT CAST(user_property AS INT) AS mode FROM ans_get_user_property1('mode'))
GO

CREATE FUNCTION ans_get_state_date()
RETURNS TABLE AS RETURN
SELECT
CASE
WHEN ((SELECT mode FROM ans_get_mode()) IN (3,6)) THEN
CAST(user_property AS DATETIME) END AS state_date
FROM ans_get_user_property1('state_date')
GO

ALTER FUNCTION is_real1(
@mode INT,
@state_date DATETIME)
RETURNS TABLE AS
RETURN (
SELECT uid,
CASE

WHEN (begin_date = 0) or (end_date = 0) THEN 0

WHEN
((@mode IN (1,5)) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP)))

OR ((@mode IN (2,5,6)) AND (begin_date IS NULL) AND (end_date IS NULL) AND ISNULL(is_committed, 0) = 0)

OR ((@mode IN (3,6)) AND (begin_date <= @state_date) AND ((end_date IS NULL) OR (end_date > @state_date)))

OR ((@mode = 4) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP)))
THEN 1
ELSE 0
END AS is_real
FROM host
)
GO

ALTER view v_host as
SELECT
h.uid,
h.id_histland,
h.id_group,
h.numporyadok,
h.numgroup,
h.objidentnum,
h.egrokn_reg_num,
h.kad_num,
h.ansname,
h.objname,
h.id_objprotstatus,
h.id_kategory,
h.id_objkind,
h.id_histcultvalue,
h.uid_objautor,
h.createdate,
h.id_scientifictype,
h.obj_description,
h.id_admdistrict,
h.cityblock,
h.id_settlement,
h.id_admdistrict2,
h.p_index,
h.id_street,
h.housenum,
h.housenumpart,
h.houseliteranum,
h.buildingnum,
h.corneradress,
h.appartmentnum,
h.id_urbanblock,
h.id_microdistrict,
h.kadblock,
h.end_date,
h.objlocation,
h.parent_uid,
h.kaddistrict,
h.mundistrict,
h.is_committed,
h.order_uid,
h.begin_date,
h.cr_date,
h.order_uid_end,
h.uid_parent_host,
h.is_host_object,
h.id_funcassign,
h.id_immovobjtype,
h.hist_data,
CASE WHEN h.obj_code is NULL THEN
'<object registered>'
ELSE h.obj_code
END AS obj_code,
s.sname AS sname_objprotstatus,
k.sname AS sname_kategory,
ok.sname AS sname_objkind,
p.sname AS name_property,
ISNULL(h.ansname + ' ', '') + h.objname as fullname,
h.pre_material,
h.hist_view_change,
h.adr_id,
h.street_id,
h.cod_mun,
h.hist_object_id,
h.scientifictype
FROM
host h
LEFT JOIN sl_objprotstatus s ON h.id_objprotstatus = s.id
LEFT JOIN sl_kategory k ON h.id_kategory = k.id
LEFT JOIN sl_objkind ok ON h.id_objkind = ok.id
LEFT JOIN sl_property p ON h.id_property = p.id
--JOIN is_real1((SELECT mode FROM ans_get_mode()), (SELECT state_date FROM ans_get_state_date())) r ON r.uid = h.uid
WHERE
(CASE

WHEN (begin_date = 0) or (end_date = 0) THEN 0

WHEN
(((SELECT mode FROM ans_get_mode()) IN (1,5)) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP)))

OR (((SELECT mode FROM ans_get_mode()) IN (2,5,6)) AND (begin_date IS NULL) AND (end_date IS NULL) AND ISNULL(is_committed, 0) = 0)

OR (((SELECT mode FROM ans_get_mode()) IN (3,6)) AND (begin_date <= (SELECT state_date FROM ans_get_state_date())) AND ((end_date IS NULL) OR (end_date > (SELECT state_date FROM ans_get_state_date()))))

OR (((SELECT mode FROM ans_get_mode()) = 4) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP)))
THEN 1
ELSE 0
END) = 1
GO


set statistics time on

-- cleaning cache
dbcc dropcleanbuffers
dbcc freeproccache

SELECT * FROM v_host
/*
(9777 row(s) affected)

SQL Server Execution Times:
CPU time = 9718 ms, elapsed time = 10834 ms.
*/