I have a function which is causing a time out it is only being used on 1700 records and the problem seems to happen in the afternoon The client gets the error on a live system and so do I on the dev system
ALTER FUNCTION[8977].[isClosed] (@.Irefnvarchar(30),@.clidint)
RETURNS BIT
AS/* if the event is closed return true */
BEGIN
RETURN(SELECTclosed
FROMdbo.tblOCompEvents
WHERE(ClientID = @.clid)AND(IRef = @.Iref)
GROUP BYclosed
HAVING(closed = 1))
END
I am using in like this
AND([8977].isClosed(tblOCompEvents.IRef, tblOCompEvents.ClientID)IS NULL)
I have taken the IRef Argument out still times out
I have changed it from IS NULL to = 0 and to = 1, all still cause a time out
any ideas please?
When you write a function like that, it is making SQL Server go through every row to evaluate the isClosed bit, and then go through your original query to evaluate the AND. Try incorporating the function logic into the WHERE clause instead of calling it like you are.|||
See if you can join this SQL statement to your original query and get the value of the function logic directly in the SELECT stmt.
|||
This would probably run much faster:
ALTER FUNCTION[8977].[isClosed] (@.Irefnvarchar(30),@.clidint)
RETURNS BIT
AS/* if the event is closed return true */
BEGIN
RETURN(SELECT TOP 1closed
FROMdbo.tblOCompEvents
WHERE(ClientID = @.clid)AND(IRef = @.Iref) AND (closed = 1))
END
This may be even faster yet, but you need to do some performance testing.
CREATEFUNCTION [8977].[isClosed](@.Irefnvarchar(30),@.clidint)
RETURNSBIT
AS/* if the event is closed return true */
BEGIN
IFEXISTS(SELECT closedFROM dbo.tblOCompEvents
WHERE(ClientID= @.clid)AND(IRef= @.Iref)AND(closed= 1))
RETURN 1
ELSE
RETURN 0
END
Ultimately, changing your where clause to something like:
WHERE NOT EXISTS(SELECT closedFROM dbo.tblOCompEventsWHERE(ClientID= @.clid)AND(IRef= @.Iref)AND(closed= 1))
would be much much faster than using a scalar function. You can also change your query to use a join like:
SELECT ...
FROM ... a
LEFT JOIN dbo.tblOCompEvents e ON a.ClientID=e.ClientID AND a.IRef=e.IRef AND e.Closed=1
WHERE ...
AND e.ClientID IS NULL
|||
yes I suspected that was part of the problem
Thanks
|||
tried that thanks but I think the function it's self is not right
|||
thanks but I think it is more fundemental than that I don't think the function is doing what I want. it is working fine now there is less pressure on the server (after 5pm in the UK)
But I am sure I could do better
these are the rows
clientid1,row 1, false, ref1
clientid2,row 2, false, ref2
clientid2,row 2, false, ref2
clientid2,row 4, true, ref2
clientid1,row 5, false, ref1
if a series of rows have the same ref and the same client id and JUST one of the rows is trueref2 then return true if the series of rows are all false then return false
However in this case I think it will be better to return the client id and use that to include or exclude clients
|||
The function does achieve your goals, but it's very very inefficient. Any of the above 3 methods I gave will greatly reduce the resources needed to run your queries.
Is this possible?
clientid1,row 1, false, ref1
clientid2,row 2, false, ref2
clientid2,row 3, false, ref2
clientid2,row 4, true, ref2
clientid1,row 5, false, ref1
clientid2,row 6, false, ref2
clientid2,row 7, false, ref2
If so, what should it return? You said if a series of rows have the same ref and the same client id, which there are two series of rows, one series has a true, the other does not. Or did I misunderstand?
|||
your example is possible and client2 ref2 would return true and client1 ref1 false you do understand it
I have tried you option :
NOT EXISTS(SELECTclosedFROMdbo.tblOCompEventsWHERE(ClientID = dbo.tblClients.ClientID)AND(IRef = tblOCompEvents.Iref)AND(closed = 1))
thanks
