How to improve the performance of this query that spans across multiple databases?

This might help. Give it a try. SELECT DISTINCT CONVERT(VARCHAR(10), car.

Notice_date, 120) AS NoticeDate , brc. Branch_name , COUNT(CASE WHEN car. Status = 'broken' AND car.

Branch_name = brc. Branch_name THEN 1 END) Broken , COUNT(CASE WHEN car. Status = 'Lote' AND car.

Branch_name = brc. Branch_name THEN 1 END) Lost , mat. Mat_count , cat.

Cat_count , pat. Pat_count FROM DB1.dbo. Cars car , DB2.dbo.

Branch brc CROSS APPLY ( SELECT COUNT(mat. Car_no) mat_count FROM DB2.dbo. Z_mat mat WHERE DATEDIFF(d, mat.

Notice_date, car. Notice_date) = 0 AND mat. Branch_name = brc.

Branch_name ) mat CROSS APPLY ( SELECT COUNT(cat. Car_no) cat_count FROM DB2.dbo. Z_cat cat WHERE DATEDIFF(d, cat.

Notice_date, car. Notice_date) = 0 AND cat. Branch_name = brc.

Branch_name ) cat CROSS APPLY ( SELECT COUNT(pat. Car_no) pat_count FROM DB2.dbo. Z_pat pat WHERE DATEDIFF(d, pat.

Notice_date, car. Notice_date) = 0 AND pat. Branch_name = brc.

Branch_name ) pat WHERE car. Notice_date > '2011-01-01' GROUP BY CONVERT(VARCHAR(10), car. Notice_date, 120) , brc.

Branch_name.

I'm not sure if DATEDIFF(d, '2011-01-01', car. Notice_date) > 0 uses the index of car. Notice_date.

The query may benefit from witing that as car. Notice_date > '2011-01-01' (I suppose Cars. Notice_date is of DATE type.) – ypercube Jul 29 at 8:01 @ypercube: Thanks for your suggestion.

Updated the WHERE clause. – Siva Jul 29 at 8:09 @Siva: Thanks . It is now taking 4.20 mins.. you hv joined 2 tables cars and Branch on branch_name.. but what I want is all notice_date and all branch... I want distinct notice_date,Branch_name from cars,Branch...Thanks – ARB Jul 29 at 8:15 @ARB: Please see if the updated query will help you.

– Siva Jul 29 at 11:42.

SELECT CONVERT(varchar(10),x. Notice_date,120) Date,Y. Branch_name, COUNT(case when x.

Status='broken' then 1 end) Broken, COUNT(case when x. Type='Lote' then 1 end) Lost, SUM(mat) mat, SUM(cat) cat,SUM(pat) pat FROM DB1.dbo. Cars x JOIN DB2.dbo.

Branch Y ON x. Branch_name=y. Branch_name -- group by date and branch name for z_mat table LEFT JOIN (select COUNT(car_no) mat,branch_name,DateAdd(Day, DateDiff(Day,notice_date), 0)) notice_date from DB2.dbo.

Z_mat GROUP BY branch_name,DateAdd(Day, DateDiff(Day,notice_date), 0)) AS a ON a. Branch_name = y. Branch_name AND DateAdd(Day, DateDiff(Day, 0,x.

Notice_date), 0) = a. Notice_date -- group by date and branch name for z_cat table LEFT JOIN (select COUNT(car_no) cat,branch_name,DateAdd(Day, DateDiff(Day,notice_date), 0)) notice_date from DB2.dbo. Z_cat GROUP BY branch_name,DateAdd(Day, DateDiff(Day,notice_date), 0)) AS be ON b.

Branch_name = y. Branch_name AND DateAdd(Day, DateDiff(Day, 0,x. Notice_date), 0) = b.

Notice_date -- group by date and branch name for z_pat table LEFT JOIN (select COUNT(car_no) pat,branch_name,DateAdd(Day, DateDiff(Day,notice_date), 0)) notice_date from DB2.dbo. Z_pat GROUP BY branch_name,DateAdd(Day, DateDiff(Day,notice_date), 0)) AS c ON c. Branch_name = y.

Branch_name AND DateAdd(Day, DateDiff(Day, 0,x. Notice_date), 0) = c. Notice_date WHERE DateAdd(Day, DateDiff(Day, 0,x.

Notice_date), 0)>'2011-01-01' GROUP BY CONVERT(varchar(10),x. Notice_date,120),DateAdd(Day, DateDiff(Day, 0,x. Notice_date), 0),y.

Branch_name.

Try also using tablename. Notice_date instead of CONVERT(varchar(10),x. Notice_date,120).

It may cancel a use of index on notice_date if it exists – niktrs Jul 29 at 7:23 Updated to use left join's for subquery's – niktrs Jul 29 at 7:51.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.