Slow-running query returns incomplete resultset So I'm trying to get some data from an SQL Server 2008 instance, the query is quite simple in structure but it has an extremely poor performance, it takes just under a minute to retrieve a bit over 1,000,000 rows (from SSMS).

Here you can see the execution plan.


The data is intended for statistical purposes and the query is being used from R-Studio (through RODBC), when querying from this environment its 'breaks' somewhere near the 450,000 - 500,000 rows. I figure RODBC is timing out somewhere near the 30 Sec mark...

The query

SELECT
    cc.ReportID,
    cc.ReportDate,
    cc.UnitID,
    cc.Callsign,
    cc.SpeciesCode,
    cc.ComName,
    cc.CatchWeight,
    cc.DayGrid,
    cc.NightGrid,
    cc.UnitEffortType,
    cc.UnitEffort,
    cc.Depth,
    cc.ReportType,
    cc.EffortDesc,
    cc.LicenceUsed,
    vn.ValidFrom,
    vn.Nation,
    vn.NationCode, 
    vg.GRT
FROM
    vwVesselGRT vg 
    INNER JOIN vwVesselNationality vn ON vg.UnitID = vn.UnitID
    INNER JOIN vwClientCatch cc ON vn.UnitID = cc.UnitID AND vn.ValidFrom &lt;= cc.ReportDate AND vn.ValidTo &gt; cc.ReportDate
WHERE
    cc.ReportDate &lt;= '2016-04-30'


I was wondering if anyone has experienced such problem. Should I attempt to change the query or tackle the R side of the problem? I might not be spotting the real issue here, I'm afraid.
In SQL what's how many CPU's do you have for that server? What's your MAXDOP and Cost threshold for parallelism'?

One thing you can do to check to see if your cost threshold is too low is run the same query with OPTION (MAXDOP 1)

SELECT cc.ReportID,
       cc.ReportDate,
       cc.UnitID,
       cc.Callsign,
       cc.SpeciesCode,
       cc.ComName,
       cc.CatchWeight,
       cc.DayGrid,
       cc.NightGrid,
       cc.UnitEffortType,
       cc.UnitEffort,
       cc.Depth,
       cc.ReportType,
       cc.EffortDesc,
       cc.LicenceUsed,
       vn.ValidFrom,
       vn.Nation,
       vn.NationCode,
       vg.GRT
FROM   vwVesselGRT vg
       INNER JOIN vwVesselNationality vn
         ON vg.UnitID = vn.UnitID
       INNER JOIN vwClientCatch cc
         ON vn.UnitID = cc.UnitID
            AND vn.ValidFrom &lt;= cc.ReportDate
            AND vn.ValidTo &gt; cc.ReportDate
WHERE  cc.ReportDate &lt;= '2016-04-30'
OPTION (MAXDOP 1); 


Without a total re-write you should be able to Schema Bind your Views you can add indexes on your views without indexing the underlying tables. 

Best option if this is going to be something you use often though is to re-write your 3 views into a single query and then make your indexes based on that. 

How bad is your query plan for just selecting from each of the views every single bit of that query Plan screams re-write to me though. 

Views are like a saved select I wouldn't really use them for anything an application depends on. 

as a temporary fix without any tuning you can just run this 

SELECT cc.ReportID,
       cc.ReportDate,
       cc.UnitID,
       cc.Callsign,
       cc.SpeciesCode,
       cc.ComName,
       cc.CatchWeight,
       cc.DayGrid,
       cc.NightGrid,
       cc.UnitEffortType,
       cc.UnitEffort,
       cc.Depth,
       cc.ReportType,
       cc.EffortDesc,
       cc.LicenceUsed,
       vn.ValidFrom,
       vn.Nation,
       vn.NationCode,
       vg.GRT
INTO   BadQueryTempFixTable
FROM   vwVesselGRT vg
       INNER JOIN vwVesselNationality vn
         ON vg.UnitID = vn.UnitID
       INNER JOIN vwClientCatch cc
         ON vn.UnitID = cc.UnitID
            AND vn.ValidFrom &lt;= cc.ReportDate
            AND vn.ValidTo &gt; cc.ReportDate
WHERE  cc.ReportDate &lt;= '2016-04-30' 


Then From the other side just query

SELECT ReportID,
       ReportDate,
       UnitID,
       Callsign,
       SpeciesCode,
       ComName,
       CatchWeight,
       DayGrid,
       NightGrid,
       UnitEffortType,
       UnitEffort,
       Depth,
       ReportType,
       EffortDesc,
       LicenceUsed,
       ValidFrom,
       Nation,
       NationCode,
       GRT
FROM   BadQueryTempFixTable