I need a help about sql query performance...
I have a view and when I run view as
select *
from udv_salesAnalyze
where _month=12 and _year=2012
I got result in 2 seconds
but when I add another filter as
select * from udv_salesAnalyze
where _month=12 and _year=2012
and userRef in (1,2,5,6,9,11,12,13,14
,19,22,25,26,27,31,34,35,37,38,39,41,47,48,49,53,54,57,59,61,62
,65,66,67,68,69,70,74,77,78,79,80,83,86,87,88,90,91,92,94)
I got result in 1 min 38 seconds..
I modified query as
select * from udv_salesAnalyze
where _month=12 and _year=2012
and userRef in (select * from udf_dependedUsers(2))
(here udf_dependedUsers is table returned Function) I got result in 38 seconds
I joined table retuned function to view but again I got result in 38-40 seconds...
is there any other way to get result more fastly...
I ll be very appreciated you can give me a solution...
thanks a lot ...
here code fo udf_dependedUsers :
ALTER FUNCTION [dbo].[udfn_dependedUsers] (@userId int)
RETURNS @dependedUsers table (userRef int)
AS
BEGIN
DECLARE @ID INT
SET @ID = @userId
;WITH ret AS(SELECT userId FROM users
WHERE userId = @ID
UNION ALL
SELECT t.userId
FROM users t INNER JOIN ret r ON t.Manager = r.userId
)
insert into @dependedUsers (userRef)
select * from ret
order by userId
RETURN
END
Problem was indicies of table which holds user data
Here is Solution ;
1- write your query to Query Editor and Click "Display Estimated Execution Plan" button ..
2- SQL Server gives you hints and query about index in "Execution Plan Window" that should be created on table
3- Right Click on Execution Plan window and choose "Missing Index Details"
4- In Opend Query Page Rename index name ([] to something else which you want) and run query
5- and Run you own query which was slow as I mentiond in my question... after these steps my query run in 4 second instead of 38