I was writing an answer for your question and in the meantime you have changed your requirements but you should not have any problems to convert my solution to your specific needs..

But let me start from the begining. I am pretty sure that SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, A ) is not cached in anyway by the Server. It is not that smart ;) So it is calculated multiple times in your main query.

So your first optimization should go in that direction. You should reduce the number of times when Get_ServicesByClientIdAndType is called. You can do it in many ways. But the general rule is that that you should calculate all the possible results of this function for all of your clients. Those results should be put in some temporarty table or they will be puted into a virtual table whis is made by SQL Server itself.

When you got your all possible results you simply join them with your clients table. But you JOIN them only ONCE.

Of course many things and optimization trick depends on your real example. In the example you have given there is even no need for usage Get_ServicesByClientIdAndType. Why not simply join those two tables and perform some calculations on them?

Take a look at this query:

SELECT A.* FROM
(
 SELECT C.ClientID,
  SUM(CASE(S.ServiceType) WHEN  A  THEN 1 ELSE 0 END) AS ServiceA,
  SUM(CASE(S.ServiceType) WHEN  B  THEN 1 ELSE 0 END) AS ServiceB,
  SUM(CASE(S.ServiceType) WHEN  C  THEN 1 ELSE 0 END) AS ServiceC,
  SUM(CASE(S.ServiceType) WHEN  D  THEN 1 ELSE 0 END) AS ServiceD
 FROM Clients AS C
 INNER JOIN Services AS s ON c.ClientId = s.ClientId
 GROUP BY C.ClientID
) A
WHERE ((A.ServiceB > 0) AND (A.ServiceA = 0)) 
 OR ((A.ServiceC > 0) AND (A.ServiceA = 0) AND (A.ServiceB = 0))
 OR ((A.ServiceD > 0) AND (A.ServiceA = 0) AND (A.ServiceB = 0) AND (A.ServiceC = 0))

In the inner query we join the tables. We throw away the function since we dont need it. Instead, we calculate the number of different services for each client. Next over the inner query results we implement your conditions. We simply check for the occurence of given services in a particual set.

The result is like this:

ClientID ServiceA ServiceB ServiceC ServiceD
-------- -------- -------- -------- --------
26915       0        4        2        2
26917       0        0        1        1
26921       0        3        2        3
26927       0        4        2        4

Of course you can strip the final result from Service columns. I ve included them because I like it that way ;-) And it allows to check if the query works properly. You can even write a query which will not calculate the number of given service type for a given client. It will work even faster and give you the proper results.

Also if you really need your function, why not to change its implementation in a way that the function will return and ID after first succesfull join? It will save you a lot of time.

But only you know the bigger picture so all I wrote here might be rubbish ;-)

Anyway, I hope I helped you in some way.

I would guess that sql server calls your function Get_ServicesByClientIdAndType once for each combination of parameter values but that for every row in the Clients table. You have three combinations of values, so for 100 rows in Client table you might see 300 calls of the function.

But to be confident, run the query in sql server management studio and switch on the option "show execution plan". This way you can easily detect which part of your query consumes the most resources and conentrate on optimizing that part.

One thing to keep in mind is to avoid "NOT" if at all possible. "NOT" is non-sargable, it won t be able to take full advantages of indexing. At first glance, I don t see a way to rewrite it to avoid the NOT expressions though. FWIW, YMMV. :-)