Sql-server – Query Speed CTE Or View

postgresqlsql-server-2008-r2t-sql

We have a postgresql database that I am using OPENQUERY to query from a SQL Server 2008 R2 install. From an optimization standpoint is it quicker to

OPTION 1:

1) Create a view of the postgresql data on SQL Server

2) Write a CTE to aggregate the data from the view & query

OPTION 2:

1) Write a CTE using OPENQUERY and aggregate the data in one query

I have run tests using both methods and in the instances I ran there is roughly a 2 – 3 second difference with either way. In the guru's opinions what is the quickest/less resource intensive way to run a query of this nature?

****And let me add that since the views are created from using OPENQUERY there is no way to index the views.

    SELECT *
FROM OPENQUERY(555.187.331.191,'WITH CTE_Employee AS
    (
        select 
        cm.empID As employeeID
        ,RTRIM(LTRIM(cm.employeeName)) As employeeName
        ,cm.employeeStatus As employeeStatus
        FROM employee cm
        WHERE employeeStatus IN (''I'', ''A'', ''R'', ''F'')
        ORDER BY employeeName ASC
    )
    , CTE_TSA AS
    (
        Select
        cm.employeeName employee
        ,SUM(CAST(COALESCE(rl.salesamount,0) AS DOUBLE PRECISION)) As "TotalSaleAmount"
        ,rc.saleDate TransDate
        FROM saleLog rc
        JOIN saleLogLine rl
        ON rc.saleID = rl.saleLog
        JOIN saleType sc
        ON rl.saleType = sc.saleTypeID
        JOIN employee cm
        ON rc.empID = cm.empID
        WHERE rl.saleLog = rc.saleID
        AND sc.saleVerified = ''True''
        AND rc.saleDate BETWEEN ''2017-01-01'' AND ''2017-01-31''
        GROUP BY cm.employeeName,rc.saleDate
    )
    , CTE_TB AS
    (
        Select 
        cm.employeeName employee
        ,SUM(CAST(COALESCE(jc.bonusAmt,0) AS DOUBLE PRECISION)) As totalBonus
        ,jc.saleID saleID
        ,iv.saleDate saleDate
        FROM saleBonus jc
        INNER JOIN saleBonusStructure ac
        ON jc.saleID = ac.saleID
        LEFT OUTER JOIN saleCategory wp
        ON wp.saleCatID = ac.saleCatID
        Left join sales iv
        ON jc.saleID = iv.saleID
        LEFT JOIN hoursWorkred jb
        ON jc.saleID = jb.saleID
        INNER JOIN employee cm
        ON cm.empID = jb.empID
        WHERE (ac.ice) 
        AND jc.saleID IN (Select saleID from sales)
        AND iv.saleDate BETWEEN ''2017-01-01'' AND ''2017-01-31''
        GROUP BY cm.employeeName,jc.saleID,iv.saleDate
    )
    , CTE_Tres AS
    (
        SELECT hoursWorkredarchive.saleID,
        CAST(SUM(hoursWorkredarchive.actualSaleAmt) AS DOUBLE PRECISION) "ActualAmt"
        ,employee.employeeName As "employee"
        ,hoursWorkredarchive.saleDate
        ,hoursWorkredarchive.saleDescription
        FROM  ((hoursWorkredarchive hoursWorkredarchive 
        LEFT OUTER JOIN employee employee 
        ON hoursWorkredarchive.empID=employee.empID ) 
        LEFT OUTER JOIN salesperson salesperson 
        ON hoursWorkredarchive.salesid=salesperson.salesid )  
        LEFT OUTER JOIN hoursWorkred hoursWorkred 
        ON hoursWorkredarchive.saleID=hoursWorkred.saleID 
        WHERE ((hoursWorkredarchive.saleDate BETWEEN ''2017-01-01'' AND ''2017-01-31''))
        GROUP BY hoursWorkredarchive.saleID,employee.employeeName,hoursWorkredarchive.saleDate, hoursWorkredarchive.empID, hoursWorkred.hoursWorkredtype,hoursWorkredarchive.saleDescription
        ORDER BY hoursWorkredarchive.empID, hoursWorkred.hoursWorkredtype, employee.employeeName
    )
    , CTE_TotalSales AS
    (
        with MaxVerNumber as
        (   
            select max(saleVersionNumber) as "MaxSaleNumber"   
            ,saleNumber    
            from sales    
            group by saleNumber
        )
        select 
        es.saleNumber As "saless"
        ,cm.employeeName employeeName
        ,es.saleDate saleDate
        FROM MaxVerNumber vn
        INNER JOIN sales es
        ON vn.saleNumber=es.saleNumber
        AND COALESCE(vn."MaxSaleNumber",0)=COALESCE(es.saleVersionNumber,0)
        INNER JOIN employee cm
        ON es.employeeID = cm.empID
        WHERE es.saleDate BETWEEN ''2017-01-01'' AND ''2017-01-31''
    )
    , CTE_hoursWorked As
    (
        Select 
        iv.saleID As hoursWorked
        ,cm.empID employeeID
        ,cm.employeeName employeeName
        ,iv.saleDate saleDate
        from sales iv
        INNER JOIN hoursWorkred jb
        ON jb.saleID = iv.saleID
        INNER JOIN employee cm
        ON cm.empID = jb.empID
        WHERE iv.saleDate BETWEEN ''2017-01-01'' AND ''2017-01-31''
        GROUP BY cm.empID, cm.employeeName, hoursWorked, iv.saleDate
    )
    , CTE_salessUnder500 As
    (
        WITH salesAmt AS 
            (Select 
            cm.employeeName As "employee"
            ,jb.saleID saleID
            ,CAST(COALESCE(jp.saleAmt,0) As DOUBLE PRECISION) salesAmt
            ,CAST(jp.ccsaleDate As Date) saleDate
            FROM hoursWorkredpart jp
            Inner Join hoursWorkred jb
            on jp.saleID = jb.saleID
            Inner join employee cm
            on cm.empID = jb.empID
            WHERE jp.ccsaleDate BETWEEN ''2017-01-01'' AND ''2017-01-31''
            AND cm.employeeStatus IN (''I'', ''A'', ''R'', ''F''),
            employees As
            (select 
            cm.employeeName As employee
            ,cm.employeeStatus As employeeStatus
            FROM employee cm
            ORDER BY employee ASC)
            Select
            DISTINCT(RTRIM(LTRIM(employee))) As "employeeName"
            ,COUNT(1) AS "Count"
            FROM employees cm
            JOIN (
            Select
            RTRIM(LTRIM("employee")) As employee
            ,SUM(salesAmt) As "TotalsalesAmt"
            ,saleDate
            ,saleID
            FROM salesAmt 
            GROUP BY RTRIM(LTRIM("employee")),saleDate,saleID
            ) As ia
            ON RTRIM(LTRIM(ia.employee)) = RTRIM(LTRIM(cm.employee))
            WHERE CAST(saleDate As Date) BETWEEN ''2017-01-01'' AND ''2017-01-31''
                AND "TotalsalesAmt" <= ''500.00''
                GROUP BY "employeeName"
    )
    , CTE_saless As
    (
        WITH salesAmt AS 
            (Select 
            cm.employeeName employee
            ,jb.saleID saleID
            ,CAST(COALESCE(jp.saleAmt,0) As DOUBLE PRECISION) salesAmt
            ,CAST(jp.ccsaleDate As Date) saleDate
            FROM hoursWorkredpart jp
            Inner Join hoursWorkred jb
            on jp.saleID = jb.saleID
            Inner join employee cm
            on cm.empID = jb.empID
            WHERE jp.ccsaleDate BETWEEN ''2017-01-01'' AND ''2017-01-31''
            AND cm.employeeStatus IN (''I'', ''A'', ''R'', ''F''),,
            employees As
            (select 
            cm.employeeName As employee
            ,cm.employeeStatus As employeeStatus
            FROM employee cm
            ORDER BY employee ASC)
            Select
            DISTINCT(RTRIM(LTRIM(employee))) As "employeeName"
            ,COUNT(1) AS "Count"
            FROM employees cm
            JOIN (
            Select
            RTRIM(LTRIM(employee)) As employee
            ,SUM(salesAmt) As "TotalsalesAmt"
            ,saleDate
            ,saleID
            FROM salesAmt 
            GROUP BY RTRIM(LTRIM(employee)),saleDate,saleID
            ) As ia
            ON RTRIM(LTRIM(ia.employee)) = RTRIM(LTRIM(cm.employee))
            WHERE CAST(saleDate As Date) BETWEEN ''2017-01-01'' AND ''2017-01-31''
                GROUP BY "employeeName"
    )
SELECT S.employeeName
    , COUNT(JB.hoursWorked) As "NumberOfhoursWorkreds"
    , COUNT(ES."saless") As "NumberOfsaless"
    , COALESCE(IU500."Count",0) As "salessUnder500"
    , COALESCE(IV."Count",0) As "saless"
    , CAST(COALESCE(TR."TotalSaleAmount",0) AS INT) As "TotalSales"
    , CAST(COALESCE(TR."TotalSaleAmount",0) AS INT)-COALESCE(TF.totalBonus,0) As "EmpBenefit"
    , COALESCE(TF.totalBonus,0) As "FreightRev"
    , COALESCE(TF.totalBonus,0)*.75 As "FreightCost"
    , (COALESCE(CAST(TR."TotalSaleAmount" AS DOUBLE PRECISION),0)-COALESCE(CAST(TF.totalBonus  AS DOUBLE PRECISION),0))+(COALESCE(CAST(TF.totalBonus AS DOUBLE PRECISION),0)*.75) As "Log1"
    , COALESCE(CAST(DAC."ActualAmt" AS DOUBLE PRECISION),0) As "Log2"
    , COALESCE(CAST(TR."TotalSaleAmount" AS DOUBLE PRECISION),0)-COALESCE(CAST(DAC."ActualAmt" AS DOUBLE PRECISION),0) As "Log3"
    , case
            when CAST((COALESCE(CAST(TR."TotalSaleAmount" AS DOUBLE PRECISION) ,0)-COALESCE(CAST(DAC."ActualAmt" AS DOUBLE PRECISION),0))/NULLIF(COALESCE(CAST(TR."TotalSaleAmount" AS DOUBLE PRECISION),0),0) AS DECIMAL(10,2)) IS NULL THEN ''0.00''
            else CAST((COALESCE(CAST(TR."TotalSaleAmount" AS DOUBLE PRECISION) ,0)-COALESCE(CAST(DAC."ActualAmt" AS DOUBLE PRECISION),0))/NULLIF(COALESCE(CAST(TR."TotalSaleAmount" AS DOUBLE PRECISION),0),0) AS DECIMAL(10,2))
      end As "Log4"
    FROM CTE_Employee AS S
    LEFT OUTER JOIN CTE_TSA AS TR ON TR.employee = S.employeeName
    LEFT OUTER JOIN CTE_TB AS TF ON TF.employee = S.employeeName
    LEFT OUTER JOIN CTE_Tres AS DAC ON DAC."employee" = S.employeeName
    LEFT OUTER JOIN CTE_TotalSales AS ES ON ES.employeeName = S.employeeName
    LEFT OUTER JOIN CTE_hoursWorked AS JB ON JB.employeeName = S.employeeName
    LEFT OUTER JOIN CTE_salessUnder500 AS IU500 ON IU500."employeeName" = S.employeeName
    LEFT OUTER JOIN CTE_saless AS IV ON IV."employeeName" = S.employeeName
GROUP BY S.employeeName, IU500."Count",IV."Count",TR."TotalSaleAmount",TF."totalBonus",dac."ActualAmt"
ORDER BY S.employeeName ASC')

Best Answer

Doing aggregations of remote data in a SQL Server view is going to be expensive, because SQL Server will have to first copy the un-aggregated data to the SQL Server (in memory/tempdb) before it can do the aggregation you want.

If at all possible, do the aggregation on the PostgreSQL side. Create a view in the Postgres DB that does any GROUP BYs you need, and then select from that view on the SQL Server side.

Related Solutions

Does Detach/Attach or Offline/Online Clear Buffer Cache for Database?

I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...

Your test is flawed.

What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.

And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument?

Yes. You're interpreting physical reads 0 as meaning there were not any physical reads

Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24.

As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.

When SQL Server performs a sequential scan of a large table, the storage engine initiates the read ahead mechanism to ensure that pages are in memory and ready to scan before they are needed by the query processor. The read ahead mechanism tries to stay 500 pages ahead of the scan.

None of the pages required to satisfy your query were in memory until read-ahead put them there.

As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.

SQL Server – Query Optimization for CTE with OPENQUERY

While I haven't used OPENQUERY, I'll add some - more or less common sense - notes:

You are not doing any joins between the remote (Postgres) server tables and the SQL Server ones. I suggest you move the whole query inside the Openquery call.
The COALESCE() suggests that the 2 columns are nullable? Are they?
If not, remove the COALESCE() calls which prohibit the use of indexes. If they are nullable, you can try to modify the predicate to something like (if they can be 0, it'd have to be more complex):
```
AND ( c2."MCN" = c1.cc2  OR  c2."MCN" IS NULL AND c1.cc2 IS NULL )
```
The CAST(Saledate As Date) >= '01/01/2017' looks like a killer, too. There is no reason to use CAST. The expression can be written as (the cast may not be needed at all in Postgres, depending on the column type):
```
Saledate >= CAST('2017-01-01' AS date)
```
After pushing the whole query to the linked server, there is no reason to have the WHERE and GROUP BY in the external query. You can collapse the query into one level.
After the rewrite, and if it is still slow, check the execution plan in the remote server. Besides indexes, examine the plan regarding the CTE. CTEs in Postgres are optimization fences, and their use restricts the optimizer into certain options - which may be good or bad for efficiency - depending on too many variables.

The query, in Postgres syntax:

SELECT *
FROM OPENQUERY(192.155.10.22, '
    WITH MaxNumber AS
      (   
        SELECT 
            MAX(cc2) AS "MCN"   
            Customernumber    
        FROM employeeinfo    
        GROUP BY Customernumber
      )
    SELECT 
        c1.employeename           AS "Employee Name",
        c1.Customernumber         AS "Customer Number",
        AVG(eq.quantityordered)   AS "AverageQty",
        AVG(eq.price)             AS "AveragePrice",
        CAST(c1.Saledate AS Date) AS "Sale Date"
    FROM
        MaxNumber c2
      INNER JOIN Customer c1
        ON  c2.Customernumber = c1.Customernumber
        AND COALESCE(c2."MCN", 0) = COALESCE(c1.cc2, 0)
      INNER JOIN orderpart ep
        ON  ep.Customer = c1.Customerid
      INNER JOIN orderquantity eq
        ON  eq.employeename = ep.employeename
      INNER JOIN employee cm
        ON  c1.employee = cm.employee
    WHERE 
        Saledate >= CAST('2017-01-01' AS date)
    GROUP BY 
        c1.customername, 
        c1.Customernumber, 
        CAST(c1.Saledate AS date)
')

(will still need fixing, there is a mismatch between the openquery which has select employeename and the external query which has group by customername)

Best Answer

Related Solutions

Does Detach/Attach or Offline/Online Clear Buffer Cache for Database?

SQL Server – Query Optimization for CTE with OPENQUERY

Related Question