LutzM: I did change to a union all, I will give your additional suggestion a try later, for now our customer is very happy.

Fredy James: I tried your query against...

Thank you for the explaination, that change has really made the difference, the query now runs on average less then 1 second which is well within acceptable limits.

Thank you for...

Yeah sorry the sample data did not include any slots that would be picked up the the union, To get a data set that would get data from that second...

Attached is everything needed to run the query.

Sample execute:

exec dbo.procDSGetWorkPackageSlots 16927, -1

I am not sure what happed with the Execution Plan but I will attach it again.

I had never known the difference between an Union and a Union All, thanks for...

I updated my two table fuctions to include the "WITH SCHEMABINDING".

Here are the two functions:

ALTER FUNCTION [DSUSER].[BDP_PTS_App_Mon_XML] (@lWPSID AS INT)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

(SELECT

...

LutzM, I implemented your suggestion, however running the query the old way and the your way resulted in the same duration (12 seconds with my test case).

it seems that there...

The test data I posted was just for the table involved with the subqueries, If I can get something that will allow me to join the data from these two...

WayneS - Thank you for the information, it makes a lot of sense to post the scripts to allow people to help me with this issue and I will in...

.6 seconds

Ok so then I have used set based logic in the past just never knew that was what it was called. Thank...

The message from peter worked great. Thank You.

I am interested in how you would use set based logic to do this. Can you explain by either using the sample I...

I know that there is another way to do this not using a cursor but I have a complete block on how it can be done. I will try the...

I just relized I posted the actual sql this one is modified to match the sample data posted:

SET NOCOUNT ON

CREATE TABLE #TMP (lDOCID INT, ADDRESS VARCHAR(40), MLSNO VARCHAR(40), AGENTID VARCHAR(40))...