September 13, 2011 at 10:38 pm
Hi All,
The below query works fine without the function(countDocumentFunction) in subquery and in main query. But when i define the function, performance is very slow...
Can any one advise the way to improve the perfomance of the query ??
select
pj.pname,
md.date,
(select count(pm.pname) from MainProjects pm
INNER JOIN mmaster m ON m.numid = pm.numid
where (CountDocumentFunction(pm.id) =0)and pm.type ='work'),
from MainProjects pj
INNER JOIN mmaster md ON md.numid = pj.numid
where (CountDocumentFunction(pj.id) =0) and pj.type ='work' and d.classtype= 'casual'
September 13, 2011 at 11:03 pm
Need to see the definition of the function to optimize it 🙂
September 13, 2011 at 11:21 pm
ALTER FUNCTION CountDocumentFunction
(
@ID float
)
RETURNS INT
AS
BEGIN
DECLARE @Out int
SELECT @Out = COUNT(DISTINCT B2.ITID) FROM MainProjects B1
INNER JOIN ITEMS B2 ON B1.id = B2.id WHERE B1.TID = @ID
RETURN @Out
END
September 14, 2011 at 12:14 am
Things look weird here 😀
Can you please explain what you trying to get out the queries. It sure can be modified, but i need some information to test my solution.
September 14, 2011 at 3:57 am
In general, a correlated sub-query in the SELECT list can lead to poor performance. You're usually better off finding some way to move that down into the FROM clause.
If you're not sure why a query is running slow, the place to start is with the execution plan. That's how you can tell how the optimizer has decided to handle your query.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 14, 2011 at 3:16 pm
I think there are three tables (MainProjects, mmaster and ITEMS) related to your query. If you can post the definitions of these tables and what you want to do, we may help you to improve the query performance.
I think you might miss one or more fields in your SELECT statement in the main query since right after comma (,) you cannot use FROM clause.
--------------------------------------------------------------
DBA or SQL Programmer? Who Knows. :unsure:
September 15, 2011 at 6:15 am
Hi,
SELECT COUNT(*) is usually bad if you will simply test if some records exists, better if you use the EXISTS instruction, in your original function or in a new adhoc function.
ALTER FUNCTION CountDocumentFunction (@ID float)
RETURNS INT
AS
BEGIN
IF EXISTS (SELECT B2.ITID FROM MainProjects B1
INNER JOIN ITEMS B2 ON B1.id = B2.id
WHERE B1.TID = @ID AND B2.ITID IS NOT NULL)
RETURN 1
RETURN 0
END
As a detail, playing with an ID as FLOAT is a danger, the consequences of a rounding error at 20th decimal are painful when you tries to identify a key.
And better if you include all this code in your SQL instruction:
select
pj.pname,
md.date,
(select count(pm.pname) from MainProjects pm
INNER JOIN mmaster m ON m.numid = pm.numid
LEFT JOIN MainProjects B1 ON B1.TId = pm.id
LEFT JOIN ITEMS B2 ON B1.id = B2.id
where B2.ITID IS NULL and pm.type ='work',
(select count(pm.pname) from MainProjects pj
INNER JOIN mmaster md ON md.numid = pj.numid
LEFT JOIN MainProjects B1 ON B1.TId = pj.id
LEFT JOIN ITEMS B2 ON B1.id = B2.id
where (B2.ITID IS NULL and pj.type ='work' and d.classtype= 'casual')
but this option will work only if you cal ensure some uniqueness of records; if for one B1.TId you can find several B2 rows with a null ITID then your SELECT COUNT(pm.pname) will obtain a different result.
In this case maybe you can code this,
select
pj.pname,
md.date,
(select count(pm.pname) from MainProjects pm
INNER JOIN mmaster m ON m.numid = pm.numid
LEFT JOIN (SELECT DISTINCT B2.ITID, B1.Tid FROM MainProjects B1
LEFT JOIN ITEMS B2 ON B1.id = B2.id) AS X
ON X.TId = pm.id
where X.ITID IS NULL and pm.type ='work',
(select count(pm.pname) from MainProjects pj
INNER JOIN mmaster md ON md.numid = pj.numid
LEFT JOIN (SELECT DISTINCT B2.ITID, B1.Tid FROM MainProjects B1
LEFT JOIN ITEMS B2 ON B1.id = B2.id) AS X
ON X.TId = pm.id
where (X.ITID IS NULL and pj.type ='work' and d.classtype= 'casual')
Hope this helps,
Francesc
September 15, 2011 at 10:44 am
Using a scalar function in SELECT statements like this also tends to slow things WAY down because SQL Server has to call the function separately for each input parameter value. If you really need to encapsulate that logic as a function, try rewriting it as an inline table-valued function.
Jason Wolfkill
September 16, 2011 at 1:02 am
Many Thanks frfernan!!
I have changed my function as advised... The output return within secs..Cheers!!
September 16, 2011 at 1:03 am
Thank you all!!
September 16, 2011 at 1:39 am
How about :
;
With cteDocCounter
as (
SELECT B1.TID
, COUNT(DISTINCT B2.ITID) as ITIDCounter
FROM MainProjects B1
INNER JOIN ITEMS B2
ON B1.id = B2.id
)
select pj.pname
, md.date
, isnull(Counters.Count_pname, 0) as Count_pname
, 1
from MainProjects pj
INNER JOIN mmaster md
ON md.numid = pj.numid
-- replace nested expression in select clause
Left join (
select pm.numid
, count(pm.pname) as Count_pname
from MainProjects pm
INNER JOIN mmaster m
ON m.numid = pm.numid
inner join cteDocCounter DC1
on DC1.TID = pm.id
and DC1.ITIDCounter = 0
where pm.type = 'work'
group by pm.numid
) Counters
on Counters.numid = md.numid
-- REPLACE WHERE-part ( CountDocumentFunction(pj.id) = 0 ) -- <<<<<-------- FUNCTION --->>>> HIDDEN JOIN MAY MESS UP YOUR QUERY !!
INNER JOIN cteDocCounter DC
on DC.TID = pJ.id
and DC.ITIDCounter = 0
where pj.type = 'work'
and d.classtype = 'casual'
Can you give it a try and provide some feedback ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply