SELECT [Build1].dbo.TBL_TYPE_DEF.TYPE_NAME, [Build1].dbo.TBL_INSTANCES.DOC_NAME,[Build1].dbo.TBL_INSTANCES.DOC, [Build1].dbo.TBL_INSTANCES.SEC, [Build1].dbo.TBL_INSTANCES.VER,[Build1].dbo.TBL_RELATIONSHIPS.JUSTIFICATION FROM [Build1].dbo.TBL_INSTANCES INNER JOIN [Build1].dbo.TBL_TYPE_DEF ON [Build1].dbo.TBL_INSTANCES.TYPE_ID = [Build1].dbo.TBL_TYPE_DEF.TYPE_ID LEFT OUTER JOIN [Build1].dbo.TBL_RELATIONSHIPS ON [Build1].dbo.TBL_INSTANCES.DOC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_DOC AND [Build1].dbo.TBL_INSTANCES.SEC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_SEC AND [Build1].dbo.TBL_INSTANCES.VER = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_VER WHERE (([Build1].dbo.TBL_INSTANCES.DOC + [Build1].dbo.TBL_INSTANCES.SEC + ISNULL(CONVERT(VARCHAR(10), [Build1].dbo.TBL_INSTANCES.VER), '')) NOT IN (SELECT PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '') FROM [Build1].dbo.TBL_RELATIONSHIPS AS TBL_RELATIONSHIPS_1 WHERE ((PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '')) IN (SELECT DOC+SEC+ISNULL(CONVERT(VARCHAR(10), VER),'') FROM [Build1].dbo.TBL_INSTANCES AS TBL_INSTANCES_1 WHERE (TYPE_ID IN (SELECT TYPE_ID FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1 WHERE (TYPE_ID NOT IN (SELECT CHILD_TYPE FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED)))))) AND (CHILD_DOC IS NOT NULL))) AND ([Build1].dbo.TBL_INSTANCES.TYPE_ID IN (SELECT TYPE_ID FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1 WHERE (TYPE_ID NOT IN (SELECT CHILD_TYPE FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED AS TBL_RELATIONSHIPS_ALLOWED_1)))) ORDER BY [Build1].dbo.TBL_TYPE_DEF.TYPE_ID,DOC,SEC,VER

I am a total newbie to query optimization, so any help, guidance or direction appreciated

Lynn Pettis (another long time poster around here) pointed out there is a LOT more wrong with your query. I didn't bother to look past the hideous formatting but he ran it through a formatter. Once I did, my eyes started bleeding.

You need to use table aliases. IIRC using the full qualification in the select list has been deprecated. Just using aliases would increase the readability a lot. Then there are all the subqueries and column concatenation. You also need to avoid using sql keywords as object or column names. It makes things a lot tougher to work with.

The indexes and stats I mentioned are crucial for this to have a chance, but you could greatly improve this query. It probably needs a complete rewrite.

If you post the DDL (CREATE TABLE statement) for the table(s), some sample data (not real data) as a series of INSERT INTO statements for the table(s), and expected results based on the sample data we can help with the rewrite.

thanks for the kind offer ... that may be more than I am allowed to share (will have to check).... understand why you would need this info.... I guess I am still very perplexed why I have had the massive increase in execution time, doesn't fit right in my brain at least.

-- code rewrites are iterative in nature. You make a change or two,-- test thoroughly, amend if necessary, then make another change.-- Here's two changes (with no data to test against):-- 1: use of table aliases and USE to eliminate unnecessary characters-- and 2: a rewrite of the subquery in the WHERE clause. USE [Build1]

SELECT DISTINCT TYPE_IDINTO #TypeIDNoChildFROM TBL_TYPE_DEF WHERE TYPE_ID NOT IN (SELECT CHILD_TYPE FROM TBL_RELATIONSHIPS_ALLOWED)