Solution on a complex sub select query SQL Server

Question

I created a SQL query that requires me to pull data from 2 separate databases, and I noticed that what I did is this:

Select 
    *,
    (Select Z.f From DatabaseZ.TblZ Z where Z.w = A.w) as valueZA,
    (Select Z.k From DatabaseZ.TblZ Z where Z.w = A.w) as valueZB,
From 
    DatabaseA.TblA A 
LEFT JOIN 
    DatabaseA.TblB B A.x = B.x
where 
    A.isActiveBit = '1';

The problem I encounter is that the query is slow, due to the fact that Databasez.TblZ contains about 200K records,and that there is new data every 30 minutes. I think that using a subquery here is the main issue, since DatabaseA.TblA also contains a lot of records, the database is already expending a lot of memory and computing power just for the external query, and it takes more time since the subquery also takes a while.

My question is how can I speed up the query, should I change the way I use my sub queries? I am currently thinking of running the query without the subqueries, then do this:

//PHP Code, just a sample not actual code

//$queryResult is the result of my first query

$subqueryArray = array();

//I make a loop depending on the number of records I got
for($x=0;$x <count($queryResult);$x++)
{
$subqueryArray[] = $this->Main->query("select X.f from Databasez.TblZ Z where ",$queryResult['w']);
//w because it's the field name in the previous query, I'll also do the same for the other subquery
}

I am not sure if the solution I am thinking of is appropriate, since I think the query would take longer if I do it this way. I am also thinking of using indexes but I think that wont make any significant impact on my speed(I am still fairly new with databases) since I need to finish the entire query within 3 seconds(already giving it a lot of time). My current query currently runs at 37 seconds.

What should I do about this?

Answer 1

For starters, you can remove multiple references of the same table:

Select A.w,
    oa.f as valueZA,
    oa.k as valueZB,
From DatabaseA.TblA A 
    LEFT JOIN DatabaseA.TblB B A.x = B.x
    outer apply (
        Select Z.f, Z.k From DatabaseZ.TblZ Z where Z.w = A.w
    ) oa
where A.isActiveBit = '1';

You can replace outer with cross, depending on the logic you need.

Also, looking for index suggestions from SQL Server definitely wouldn't hurt. Either check execution plan suggestions, or even better, google for diagnostic scripts referring sys.dm_db_index_usage_stats() DMV.

Answer 2

An alternative to Roger's answer, would be to LEFT JOIN to TblZ. Personally I think for the example you've posted, an APPLY is probably not the most appropriate method of data retrieval, however it would be worth testing performance against the APPLY to see which is quicker.

I'd also recommend not selecting *, simply because you could be pulling columns that are not needed and causing sub-optimal indexes to be used.

   SELECT A.*, B.*,
      Z.f as valueZA,
      Z.k as valueZB,
    FROM DatabaseA.TblA A 
    LEFT JOIN DatabaseA.TblB B ON A.x = B.x
    LEFT JOIN DatabaseZ.TblZ Z ON Z.w = A.w
    WHERE A.isActiveBit = '1';

I'd also recommend looking at implementing an index on TblZ.w that includes f and k, but it would be best to see what indexes you currently have.