October 3, 2017 at 2:44 pm
I am joining to two very large tables and trying to ensure I am doing it the most efficient method possible. The 2nd join is using two fields from the 1st join to complete the join. I have added a clustered index on the Id field on #Product, and the Detail and Payment tables have indexes on Id and Nbr fields. Additionally I tried to use a attempted to use an IN statement to look for the Id that were in the #Product table, but did not get much improvement on performance.
Currently, the query is taking 10-15 minutes to complete, and am looking for any recommendations on how I can make it perform better.
Here is my SQL.
SELECT D.Id, D.Nbr, D.Unit_Total, D.Type_Code, D.Serv_Code, D.Pro_Code, D.Pro_Mod, G.Paid as Paid
FROM #Product Cl --921,500 records
INNER JOIN Detail D on D.Id = Pr.Id --135,822,369 records in Detail
INNER JOIN (SELECT C.Id as Id, C.Nbr as Nbr, CAST(SUM(C.paid_amt) as MONEY) as Paid
FROM PAYMENT C --142,623,313 records in Payment
WHERE C.Source_Code = 'FTE' AND C.Status_Code = 'KETCHR1' AND C.Flag = 'N'
GROUP BY C.Id, C.Nbr
) G on G.Id = D.Id and G.Nbr = D.Nbr
--WHERE D.Id in (SELECT Id from #Product)
Where D.Source_Code = 'FTE' AND D.Flag = 'N'
GROUP BY D.Id, Nbr, D.Unit_Total, D.Type_Code, D.Serv_Code, D.Pro_Code, D.Pro_Mod, G.Paid
October 3, 2017 at 3:05 pm
In order to allow us to help you we will need the explain plan of the execution of the query.
tables DDL and indexes will also help.
In terms of the volumes it would be of help if you could also give us what are the volumes of the records returned by the group by from the payment table, as well as what are the number of records returned both from the join to the other tables with and without the group by. - although the explain plan should give us this info.
and server spec - CPU, memory available to SQL Server, disks (ssd, san, local), tempdb files and sizes, maxdop settings
October 3, 2017 at 3:13 pm
Try this on for size:SELECT DISTINCT D.Id, D.Nbr, D.Unit_Total, D.Type_Code, D.Serv_Code, D.Pro_Code, D.Pro_Mod,
(
SELECT CONVERT(money, SUM(C.paid_amt))
FROM PAYMENT AS C --142,623,313 records in Payment
WHERE C.Source_Code = 'FTE'
ND C.Status_Code = 'KETCHR1'
AND C.Flag = 'N'
AND C.Id = D.Id
AND C.Nbr = D.Nbr
) AS Paid
FROM #Product AS Cl --921,500 records
INNER JOIN Detail AS D
ON D.Id = Pr.Id --135,822,369 records in Detail
AND D.Source_Code = 'FTE'
AND D.Flag = 'N';
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 3, 2017 at 3:20 pm
Unfortunately, my permissions do not allow me to get the query execution plan otherwise I would provide. The #Product table has one record per Id. The Detail and Payment tables contain multiple records per Id and the multiple records are identified with a unique Nbr. So for instance Id = 1 in the #Product table has 3 records in the Detail and Payment table identified with Nbr 1, Nbr 2, and Nbr 3, Id = 2 has Nbr 1, Id = 3 has Nbr 1, Nbr 2, Nbr 3, and Nbr 4. I am trying to get all the information for each Id in the #Product table where the Id is the same. The Payment table only contains a Id and Nbr on those that had a payment from the Detail table.
The volume of records being returned from the Detail and Payment table are 3,500,000 records.
I would be glad to provide server specs but not sure where to obtain them.
October 3, 2017 at 3:30 pm
sgmunson - Tuesday, October 3, 2017 3:13 PMTry this on for size:SELECT DISTINCT D.Id, D.Nbr, D.Unit_Total, D.Type_Code, D.Serv_Code, D.Pro_Code, D.Pro_Mod,
(
SELECT CONVERT(money, SUM(C.paid_amt))
FROM PAYMENT AS C --142,623,313 records in Payment
WHERE C.Source_Code = 'FTE'
ND C.Status_Code = 'KETCHR1'
AND C.Flag = 'N'
AND C.Id = D.Id
AND C.Nbr = D.Nbr
) AS Paid
FROM #Product AS Cl --921,500 records
INNER JOIN Detail AS D
ON D.Id = Pr.Id --135,822,369 records in Detail
AND D.Source_Code = 'FTE'
AND D.Flag = 'N';
Sgmunson I attempted that and got an error message stating "Warning: Null value is eliminated by an aggregate or other SET operation." UPDATE: I found that I had some null values in two columns. I resolved the error by using ISNULL() around D.Type_Code and D.Serv_Code. Thank you for everyone's assistance.
Final results were the query ran in 22 seconds! :w00t:😀
October 3, 2017 at 3:52 pm
If you can't do explain plans better ask your DBA's to give you that - it is a must for any developer and there is no reason not to be able to do them.
your sample code also has a few errors on it - wrong alias on table, and group by field (nbr) without an alias even though it is mentioned in 2 of the joins so would never work as is.
In any case, and based on the code and volumes you supplied try the following
if object_id('tempdb..#temp_payment') is not null
drop table #temp_payment;
select c.id as id
, c.nbr as nbr
, cast(sum(c.paid_amt) as money) as paid
into #temp_payment
from PAYMENT c --142,623,313 records in Payment
where c.Source_Code = 'FTE'
and c.Status_Code = 'KETCHR1'
and c.Flag = 'N'
group by c.id
, c.nbr
if object_id('tempdb..#temp_detail') is not null
drop table #temp_detail;
select d.id
, d.nbr
, d.Unit_Total
, d.Type_Code
, d.Serv_Code
, d.Pro_Code
, d.Pro_Mod
into #temp_detail
from Detail d
where d.Source_Code = 'FTE'
and d.Flag = 'N'
select d.id
, d.nbr
, d.Unit_Total
, d.Type_Code
, d.Serv_Code
, d.Pro_Code
, d.Pro_Mod
, g.paid as paid
--- is this correct? there is looks like the "cl" below should really be Pr so it can join to detail -
--from #Product cl --921,500 records
from #Product Pr --921,500 records
inner join #temp_detail d
on d.id = Pr.id --135,822,369 records in Detail
inner join #temp_payment g
on g.id = d.id
and g.nbr = d.nbr
--WHERE D.Id in (SELECT Id from #Product)
where d.Source_Code = 'FTE'
and d.Flag = 'N'
group by d.id
, d.Nbr
, d.Unit_Total
, d.Type_Code
, d.Serv_Code
, d.Pro_Code
, d.Pro_Mod
, g.paid
October 3, 2017 at 3:59 pm
frederico_fonseca - Tuesday, October 3, 2017 3:52 PMIf you can't do explain plans better ask your DBA's to give you that - it is a must for any developer and there is no reason not to be able to do them.your sample code also has a few errors on it - wrong alias on table, and group by field (nbr) without an alias even though it is mentioned in 2 of the joins so would never work as is.
In any case, and based on the code and volumes you supplied try the following
if object_id('tempdb..#temp_payment') is not null
drop table #temp_payment;select c.id as id
, c.nbr as nbr
, cast(sum(c.paid_amt) as money) as paid
into #temp_payment
from PAYMENT c --142,623,313 records in Payment
where c.Source_Code = 'FTE'
and c.Status_Code = 'KETCHR1'
and c.Flag = 'N'
group by c.id
, c.nbrif object_id('tempdb..#temp_detail') is not null
drop table #temp_detail;select d.id
, d.nbr
, d.Unit_Total
, d.Type_Code
, d.Serv_Code
, d.Pro_Code
, d.Pro_Mod
into #temp_detail
from Detail d
where d.Source_Code = 'FTE'
and d.Flag = 'N'select d.id
, d.nbr
, d.Unit_Total
, d.Type_Code
, d.Serv_Code
, d.Pro_Code
, d.Pro_Mod
, g.paid as paid
--- is this correct? there is looks like the "cl" below should really be Pr so it can join to detail -
--from #Product cl --921,500 records
from #Product Pr --921,500 records
inner join #temp_detail d
on d.id = Pr.id --135,822,369 records in Detail
inner join #temp_payment g
on g.id = d.id
and g.nbr = d.nbr--WHERE D.Id in (SELECT Id from #Product)
where d.Source_Code = 'FTE'
and d.Flag = 'N'
group by d.id
, d.Nbr
, d.Unit_Total
, d.Type_Code
, d.Serv_Code
, d.Pro_Code
, d.Pro_Mod
, g.paid
You are correct. The alias was incorrect. In any case this solution worked as well. Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply