SET STATISTICS IO ON
GO

... -- Your query here

SET STATISTICS IO OFF
GO

quote:

---

Originally posted by Kristen

"I've left off the other WHERE clauses that necessitate the joins"

In which case I can't comment on why it may be inefficient ...

---

quote:

---

Originally posted by harsh_athalye
I see there are many LEFT JOIN which are going to cause performance hit. I suggest you profile the logical reads for your query and try to minimize them.

For viewing Logical reads, add below statement before your query:

SET STATISTICS IO ON
GO

... -- Your query here

SET STATISTICS IO OFF
GO

---

SET SHOWPLAN_TEXT ON
GO

-- ... put query here - e.g.:

SET SET SHOWPLAN_TEXT OFF
GO

quote:

---

Originally posted by Kristen

Well in that scenario these tables are redundant:

INNER JOIN Entities e ON m.EntityID=e.EntityID
INNER JOIN Individuals i ON e.EntityID=i.IndividualID
LEFT JOIN MemberTypes ON m.MemberTypeID=MemberTypes.MemberTypeID
LEFT JOIN Addresses a ON m.EntityID=a.EntityID
LEFT JOIN AddressTypes at ON a.AddressTypeID=at.AddressTypeID

except that they may be causing duplicate values for

m.Name, m.Date, m.Active

if any of them are one-to-many relationships.

Plus the two INNER JOINs do enforce that related records must exist, but that's irrelevant if there is referential integrity (where FKs exist, or not) would enforce that anyway.

Do as Harsh says and see how what the "scan count" and "logical reads" are. If they are not VERY low numbers then the likelihood is that you don't have the right indexes, and you need to look at the Query Plan to see what is actually being used.

SET SHOWPLAN_TEXT ON
GO

-- ... put query here - e.g.:

SET SET SHOWPLAN_TEXT OFF
GO

but I reckon its a waste of time if this is not your actual real-world WHERE clause, because the query plan will be different when you add in the rest of your WHERE clause

Kristen

---

quote:

---

Originally posted by Lamprey
If you provide a little more information, like table size and the actual query you want to run we might be able to help more.

-Ryan

---

SELECT DISTINCT m.EntityID, m.Member, a.AddressID, MemberTypes.Description AS MemberType, m.StartDate, m.EndDate, m.ActiveStatus, i.PrimaryOrganizationName AS PrimaryRelation FROM 
Membership m
INNER JOIN Entities e ON m.EntityID=e.EntityID
INNER JOIN Individuals i ON e.EntityID=i.IndividualID
LEFT JOIN MemberTypes ON m.MemberTypeID=MemberTypes.MemberTypeID
LEFT JOIN Addresses a ON m.EntityID=a.EntityID
LEFT JOIN AddressTypes at ON a.AddressTypeID=at.AddressTypeID
WHERE m.AssociationID=@AssociationID AND e.DeletedEntity<>1 
AND (a.AddressTypeID=COALESCE((SELECT PreferredAddressTypeID WHERE PreferredAddressTypeID <> 0), 
(SELECT TOP 1 a2.AddressTypeID FROM Addresses a2 INNER JOIN AddressTypes at2 ON a2.AddressTypeID=at2.AddressTypeID WHERE EntityID=e.EntityID ORDER BY DisplayOrder ASC)) OR a.AddressTypeID IS NULL)

quote:

---

Originally posted by Kristen
I'll take a small bet that you will be better off using a parameterized query (if you aren;t already). That will improve the chance that your the Plan for your query is cached.

Hopefully the language you are using supports calling parameterized queries easily, but if not you can do so using sp_ExecuteSQL (but you will need to mess about doubling quotes etc.)

Ah! I've just spotted WHERE m.AssociationID=@AssociationID so hopefully you are using a parameterized query.

What does the SET STATISTICS IO ON setting show for the above example-query??

Kristen

---

quote:

---

Originally posted by dinakar

If the order of records doesnt mattet try using SET ROWCOUNT 10 instead of TOP 10.

---

LEFT JOIN Addresses a ON m.EntityID=a.EntityID
...
AND (a.AddressTypeID= ...

  |--Stream Aggregate(GROUP BY:([m].[EntityId]) DEFINE:([m].[Member]=ANY([TestTEMP].[dbo].[Membership].[Member] as [m].[Member]), [m].[StartDate]=ANY([TestTEMP].[dbo].[Membership].[StartDate] as [m].[StartDate]), [m].[EndDate]=ANY([TestTEMP].[dbo].[Membership].[EndDate] as [m].[EndDate]), [m].[ActiveStatus]=ANY([TestTEMP].[dbo].[Membership].[ActiveStatus] as [m].[ActiveStatus])))
       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([e].[EntityId], [i].[PreferredAddressTypeId], [a].[AddressTypeId]))
            |--Nested Loops(Inner Join, PASSTHRU:([IsBaseRow1010] IS NULL), OUTER REFERENCES:([a].[AddressId]))
            |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([m].[EntityId], [Expr1022]) WITH ORDERED PREFETCH)
            |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([i].[IndividualId], [Expr1021]) WITH ORDERED PREFETCH)
            |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([m].[EntityId], [Expr1020]) WITH ORDERED PREFETCH)
            |    |    |    |    |--Clustered Index Scan(OBJECT:([TestTEMP].[dbo].[Membership].[PK_Membership] AS [m]),  WHERE:([TestTEMP].[dbo].[Membership].[AssociationId] as [m].[AssociationId]=(1)) ORDERED FORWARD)
            |    |    |    |    |--Clustered Index Seek(OBJECT:([TestTEMP].[dbo].[Individuals].[PK_Individuals] AS [i]), SEEK:([i].[IndividualId]=[TestTEMP].[dbo].[Membership].[EntityId] as [m].[EntityId]) ORDERED FORWARD)
            |    |    |    |--Clustered Index Seek(OBJECT:([TestTEMP].[dbo].[Entities].[PK_Entities] AS [e]), SEEK:([e].[EntityId]=[TestTEMP].[dbo].[Individuals].[IndividualId] as [i].[IndividualId]),  WHERE:([TestTEMP].[dbo].[Entities].[DeletedEntity] as [e].[DeletedEntity]<>(1)) ORDERED FORWARD)
            |    |    |--Index Seek(OBJECT:([TestTEMP].[dbo].[Addresses].[IX_Addresses_1] AS [a]), SEEK:([a].[EntityId]=[TestTEMP].[dbo].[Membership].[EntityId] as [m].[EntityId]) ORDERED FORWARD)
            |    |--Clustered Index Seek(OBJECT:([TestTEMP].[dbo].[Addresses].[PK_Address] AS [a]), SEEK:([a].[AddressId]=[TestTEMP].[dbo].[Addresses].[AddressId] as [a].[AddressId]) LOOKUP ORDERED FORWARD)
            |--Concatenation
                 |--Filter(WHERE:(STARTUP EXPR([TestTEMP].[dbo].[Addresses].[AddressTypeId] as [a].[AddressTypeId] IS NULL)))
                 |    |--Constant Scan
                 |--Filter(WHERE:([TestTEMP].[dbo].[Addresses].[AddressTypeId] as [a].[AddressTypeId]=CASE WHEN [TestTEMP].[dbo].[Individuals].[PreferredAddressTypeId] as [i].[PreferredAddressTypeId] IS NOT NULL THEN [TestTEMP].[dbo].[Individuals].[PreferredAddressTypeId] as [i].[PreferredAddressTypeId] ELSE [TestTEMP].[dbo].[Addresses].[AddressTypeId] as [a2].[AddressTypeId] END))
                      |--Nested Loops(Left Outer Join, PASSTHRU:([TestTEMP].[dbo].[Individuals].[PreferredAddressTypeId] as [i].[PreferredAddressTypeId] IS NOT NULL))
                           |--Nested Loops(Left Outer Join, PASSTHRU:(IsFalseOrNull [TestTEMP].[dbo].[Individuals].[PreferredAddressTypeId] as [i].[PreferredAddressTypeId] IS NOT NULL))
                           |    |--Nested Loops(Left Outer Join)
                           |    |    |--Constant Scan
                           |    |    |--Compute Scalar(DEFINE:([i].[PreferredAddressTypeId]=[TestTEMP].[dbo].[Individuals].[PreferredAddressTypeId] as [i].[PreferredAddressTypeId]))
                           |    |         |--Filter(WHERE:(STARTUP EXPR([TestTEMP].[dbo].[Individuals].[PreferredAddressTypeId] as [i].[PreferredAddressTypeId]<>(0))))
                           |    |              |--Constant Scan
                           |    |--Compute Scalar(DEFINE:([i].[PreferredAddressTypeId]=[TestTEMP].[dbo].[Individuals].[PreferredAddressTypeId] as [i].[PreferredAddressTypeId]))
                           |         |--Filter(WHERE:(STARTUP EXPR([TestTEMP].[dbo].[Individuals].[PreferredAddressTypeId] as [i].[PreferredAddressTypeId]<>(0))))
                           |              |--Constant Scan
                           |--Sort(TOP 1, ORDER BY:([at2].[DisplayOrder] ASC))
                                |--Nested Loops(Inner Join, OUTER REFERENCES:([a2].[AddressTypeId]))
                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([a2].[AddressId]))
                                     |    |--Index Seek(OBJECT:([TestTEMP].[dbo].[Addresses].[IX_Addresses_1] AS [a2]), SEEK:([a2].[EntityId]=[TestTEMP].[dbo].[Entities].[EntityId] as [e].[EntityId]) ORDERED FORWARD)
                                     |    |--Clustered Index Seek(OBJECT:([TestTEMP].[dbo].[Addresses].[PK_Address] AS [a2]), SEEK:([a2].[AddressId]=[TestTEMP].[dbo].[Addresses].[AddressId] as [a2].[AddressId]) LOOKUP ORDERED FORWARD)
                                     |--Clustered Index Seek(OBJECT:([TestTEMP].[dbo].[AddressTypes].[PK_AddressTypes] AS [at2]), SEEK:([at2].[AddressTypeId]=[TestTEMP].[dbo].[Addresses].[AddressTypeId] as [a2].[AddressTypeId]) ORDERED FORWARD)