Geek Answers Handbook

The difference in performance CROSS APPLY

I have an SQL query that takes about 30 seconds to run, which returns 1 record. The function used in CROSS APPLY is instantly launched using the BrandId of this entry.

SELECT b.BrandId, b.Name, ah.Type, c.ContactEmails, c.ContactNumbers, c.ContactLinks FROM @IdsToFilterBy ids JOIN dbo.AccountHandler ah ON ah.AccountHandlerId = ids.Id JOIN dbo.Brand b ON ah.RepresentedByBrandId = b.BrandId CROSS APPLY dbo.[fn_GetBrandContactDetails](b.BrandId) AS c

However, if I just changed the table, I get BrandId for CROSS APPLY ..

 SELECT b.BrandId, b.Name, ah.Type, c.ContactEmails, c.ContactNumbers, c.ContactLinks FROM @IdsToFilterBy ids JOIN dbo.AccountHandler ah ON ah.AccountHandlerId = ids.Id JOIN dbo.Brand b ON ah.RepresentedByBrandId = b.BrandId CROSS APPLY dbo.[fn_GetBrandContactDetails](ah.RepresentedByBrandId) AS c <-- change here

now the request takes only 2 seconds. When I join dbo.Brand b ON cah.RepresentedByBrandId = b.BrandId , I expect them to be the same.

Can someone explain why there is a huge difference in performance?

UPDATE

The difference is that CROSS APPLY works throughout the Brand table when I use b.BrandId and the entire AccountHandler table when I use ah.RepresentedByBrandId. The AccountHandler table is significantly smaller.

However, I expected CROSS APPLY to work only on the results of JOINs, which is one 1 record. Is this possible, or did I miss an understanding of CROSS APPLY?

+7
sql sql-server
source share
2 answers

Found.

To get CROSS APPLY to work on a subset of the results from JOINs, not the whole table, before JOINS I used OPTION (FORCE ORDER)

 SELECT b.BrandId, b.Name, ah.Type, c.ContactEmails, c.ContactNumbers, c.ContactLinks FROM @IdsToFilterBy ids JOIN dbo.AccountHandler ah ON ah.AccountHandlerId = ids.Id JOIN dbo.Brand b ON ah.RepresentedByBrandId = b.BrandId CROSS APPLY dbo.[fn_GetBrandContactDetails](b.BrandId) AS c OPTION (FORCE ORDER)

Now it starts instantly and, looking at the execution plan, the function is called only for one result, and not for the entire db table.

+14
source share

I had the same problem and decided to use it with OUTER APPLY instead of CROSS APPLY.

-one
source share

More articles:


All Articles