Slow performance with joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Slow performance with joins

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2015-01-03 : 18:32:57

I am unsure where to go here.
I have a database with some tables with more than 10 million rows of data.

Now, when querying the views, which have 1 or 2 tables in them with a join, the performance is real fast - almost immediately I get the results.

This involves 5-6 views - again, when running them individually, the performance is real faster.

however, when I create a view which has 5 other views and join them together, the query is REALLY slow and takes more than 35 minutes (yes, minutes) to come back with filtered results.

I do believe the relevant tables have the correct indexes in place. Some of the tables do not have a PK as they are not needed (and no integer field to base a PK on anyway).

Any ideas how to make it quicker but also exactly where to pinpoint the bottleneck?

to me it seems weird that if I query each of the views, they perform so much better than when a bunch of views are being joined on together.

one note - "Ship" field here is a varchar field.
Also, the query below is just doing a where clause for a specific piece of data and only 1-10 records exist for them but takes more than 35 mins to appear.

quote:

SELECT dbo.v_DA_SAPrev12MoByShip.Branch, dbo.v_DA_SAPrev12MoByShip.TM, dbo.v_DA_SAPrev12MoByShip.Route,
dbo.v_DA_SAYTDByShip.ShipName, dbo.v_DA_SAYTDByShip.CurrYTDSales, dbo.v_DA_SAPrev12MoByShip.Sales AS [12MoSales],
dbo.v_DA_WKMinandMaxPerShip.MinWk, dbo.v_DA_WKMinandMaxPerShip.MaxWk, dbo.v_DA_SAYTDByShip.[YTD%], dbo.v_DA_WKMinandMaxPerShip.YR,
dbo.v_DA_SAYTDByShip.EndDate, dbo.tbl_DA_DaysForShips.Days, dbo.v_DA_SAYTDByShip.Ship, dbo.v_DA_CNTDeliveriesPerShip.CNTDEL,
dbo.v_DA_CNTDeliveriesPerShip.YR AS YRDel
FROM dbo.v_DA_SAYTDByShip
INNER JOIN dbo.v_DA_CNTDeliveriesPerShip ON dbo.v_DA_SAYTDByShip.Ship = dbo.v_DA_CNTDeliveriesPerShip.SHIP
INNER JOIN dbo.tbl_DA_DaysForShips ON dbo.v_DA_SAYTDByShip.Ship = dbo.tbl_DA_DaysForShips.Ship
INNER JOIN dbo.v_DA_SAPrev12MoByShip ON dbo.v_DA_SAYTDByShip.Ship = dbo.v_DA_SAPrev12MoByShip.Ship
INNER JOIN dbo.v_DA_WKMinandMaxPerShip ON dbo.v_DA_SAYTDByShip.Ship = dbo.v_DA_WKMinandMaxPerShip.Ship

WHERE (dbo.v_DA_CNTDeliveriesPerShip.YR = YEAR(dbo.v_DA_SAYTDByShip.EndDate))
AND dbo.v_DA_SAPrev12MoByShip.Branch = '001' AND dbo.v_DA_CNTDeliveriesPerShip.YR = 2014 AND
v_DA_SAYTDByShip.Ship = '123456'
GROUP BY dbo.v_DA_SAPrev12MoByShip.Branch, dbo.v_DA_SAPrev12MoByShip.TM, dbo.v_DA_SAPrev12MoByShip.Route, dbo.v_DA_SAYTDByShip.ShipName,
dbo.v_DA_SAYTDByShip.CurrYTDSales, dbo.v_DA_SAPrev12MoByShip.Sales, dbo.v_DA_WKMinandMaxPerShip.MinWk, dbo.v_DA_WKMinandMaxPerShip.MaxWk,
dbo.v_DA_SAYTDByShip.[YTD%], dbo.v_DA_WKMinandMaxPerShip.YR, dbo.v_DA_SAYTDByShip.EndDate, dbo.tbl_DA_DaysForShips.Days,
dbo.v_DA_SAYTDByShip.Ship, dbo.v_DA_CNTDeliveriesPerShip.CNTDEL, dbo.v_DA_CNTDeliveriesPerShip.YR
HAVING (dbo.v_DA_SAPrev12MoByShip.Branch = '001') AND (dbo.v_DA_WKMinandMaxPerShip.YR = YEAR(dbo.v_DA_SAYTDByShip.EndDate))

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-03 : 18:48:09

basically you should never join views. Joining views is a good way to guarantee a suboptimal plan.unpack the views and write the join against the base tables. Then, read the execution plan. Look for missing indices for scans when there should be seeks. make sure the predicates are indexed. Also make sure all the statistics are up to date.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-01-03 : 19:30:59

This should be equivalent to your query:

select d.Branch
      ,d.TM
      ,d.Route
      ,a.ShipName
      ,a.CurrYTDSales
      ,d.Sales AS [12MoSales]
      ,e.MinWk
      ,e.MaxWk
      ,a.[YTD%]
      ,e.YR
      ,a.EndDate
      ,c.Days
      ,a.Ship
      ,b.CNTDEL
      ,b.YR AS YRDel
  from dbo.v_DA_SAYTDByShip as a
       inner join dbo.v_DA_CNTDeliveriesPerShip as b
               on b.SHIP=a.Ship
       inner join dbo.tbl_DA_DaysForShips as c
               on c.Ship=a.Ship
       inner join dbo.v_DA_SAPrev12MoByShip as d
               on d.Ship=a.Ship
       inner join dbo.v_DA_WKMinandMaxPerShip as e
               on e.Ship=a.Ship
 where a.Ship='123456'
   and a.EndDate>=convert(date,'20140101',112)
   and a.EndDate<convert(date,'20150101',112)
   and b.YR=2014
   and d.Branch='001'
   and e.YR=2014
 group by d.Branch
         ,d.TM
         ,d.Route
         ,a.ShipName
         ,a.CurrYTDSales
         ,d.Sales
         ,e.MinWk
         ,e.MaxWk
         ,a.[YTD%]
         ,e.YR
         ,a.EndDate
         ,c.Days
         ,a.Ship
         ,b.CNTDEL
         ,b.YR

I removed the having clause, as this (in this case) can be done in the where clause. Also I changed the way the date is searched. Finally I used aliases and rearranged the query, as this makes the whole query easier to read (in my opinion).

tech_1
Posting Yak Master

129 Posts

Posted - 2015-01-04 : 03:46:06

Thank you.
do you think that the way the date is being searched on makes that much of a difference?
also, how do you update the statistics?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-04 : 10:13:09

http://msdn.microsoft.com/en-ca/library/ms187348.aspx

Also, have you verified that the join and where predicates are covered by an appropriate index? Have you tried to rewrite this without using the views at all?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources