October 18, 2011 at 3:39 am
Hi,
I heard about correlated query. It will help us to improve the performance of the data retrieval
I need a good explanation about correlated query
I'm also need a good explanation about the differences between correlated query and noncorrelated query
Hopefully, you can help me
October 18, 2011 at 3:50 am
I'm sure the search engine you use is as good as the one I do, and I got loads of results for "correlated query". If there's anything specific you don't understand when reading through, then by all means post back.
John
October 18, 2011 at 4:08 am
This was removed by the editor as SPAM
October 18, 2011 at 8:31 am
In my limited knowledge of the comparison between correlated subqueries, inner joins, and CROSS APPLY... It looks like all 3 queries below produce the same execution plan. Why would you use 1 over the other?
select * from sales.Customer c
where Customerid in (select CustomerId from sales.CustomerAddress o where o.AddressId is null and o.CustomerId = c.CustomerId)
and c.CustomerType like 'Joe%';
select c.* from sales.Customer c
inner join sales.CustomerAddress o
on c.customerid = o.customerid
where o.AddressId is null
and c.CustomerType like 'Joe%';
select c.* from sales.Customer c
CROSS APPLY (select CustomerId from sales.CustomerAddress o where o.AddressId is null and o.CustomerId = c.CustomerId) o
WHERE c.CustomerType like 'Joe%';
Thanks,
Jared
Jared
CE - Microsoft
October 18, 2011 at 8:45 am
The first 2 are not the same.
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2011 at 8:55 am
GilaMonster (10/18/2011)
The first 2 are not the same.http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
Thanks Gail! Exactly what I was looking for!
Jared
Jared
CE - Microsoft
October 19, 2011 at 12:48 pm
Since 2005 I don't use correlated sub-queries in the SELECT part of a query for more than a very simple single value. I do still use them in the WHERE clause the same as before.
I find that since correlated sub-queries are limited to a single column and will blow up if more than 1 row is returned, the APPLY operator works much better for me.
Consider a simple query like:
SELECT V.CustomerID, V.FirstName, V.LastName
, V.EmailAddress
, (SELECT TOP 1 H.SalesOrderID
FROM Sales.SalesOrderHeader H
WHERE H.CustomerID = V.CustomerID
ORDER BY H.OrderDate DESC
) AS LastOrder
, (SELECT TOP 1 H.SubTotal
FROM Sales.SalesOrderHeader H
WHERE H.CustomerID = V.CustomerID
ORDER BY H.OrderDate DESC
) AS LastOrderAmount
FROM Sales.vIndividualCustomer V
WHERE V.CountryRegionName = 'Australia'
ORDER BY LastName, FirstName
Since the correlated sub-query can only return a single value in the SELECT, I have to do it twice. In the actual query plan SalesOrderHeader is hit twice. If for some reason a customer had 2 orders on the same date, I might even get an order that didn't match the subtotal (not likely, but there's no guarantee).
The APPLY method only hits the SalesOrderHeader table once:
SELECT V.CustomerID, V.FirstName, V.LastName
, V.EmailAddress
, X.LastOrder, X.LastOrderAmount
FROM Sales.vIndividualCustomer V
OUTER APPLY
(SELECT TOP 1 H.SalesOrderID, H.SubTotal
FROM Sales.SalesOrderHeader H
WHERE H.CustomerID = V.CustomerID
ORDER BY H.OrderDate DESC
) AS X (LastOrder, LastOrderAmount)
WHERE V.CountryRegionName = 'Australia'
ORDER BY LastName, FirstName
In this example APPLY works like a correlated sub-query, but you can get a complete record set instead of a single value.
Todd Fifield
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply