Post reply

Question about performance, CTE vs Subquery on an ODBC connection to a non-relational datasource

  • April 6, 2012 at 3:02 am

    #271592

    Ok, the title is a mouth full 🙂

    But one of our developers had a question.

    He's developing a program that has to report data coming from a non-relational source, atm the data comes from an excel file but will probably be coming from Native (Navision database) in the future. Albeit I'm not fully filled into the details.

    He's using an ODBC to connect to the excel file from within the code written.

    The data has the following structure:

    *Drivedate:datetime

    *Waybill Number:integer

    *Waybill Line Number:integer

    *Number of pallets:integer

    *Number of quality control checks : integer

    *Number of movements: integer

    *Approved: boolean

    Now he wants for each Drivedate in a period the following:

    *Drivedate

    *Amount of Unique Waybills

    *Total amount of Quality checks

    *Total amount of movements

    where approved is TRUE and there has been a movement.

    Now he created an elaborate query to get these results but it wasn't very performant or even readable, the guy is not an SQL guy.

    Now there are at least 2 solutions

    1)Subquering

    2)Common Table Expression (CTE)

    As requested made a CTE solution, but now I'm doubting whether that has a better performance then the subquery solution.

    So anyone know which solution would be generally better.

  • April 6, 2012 at 3:56 am

    #1470011

    A CTE is a just a derived table. It's one with some unique properties in that you can refer to it more than once in the following query, but it's just a derived table like any other derived table.

    When you say sub-query, do you mean a correlated sub-query that is part of the SELECT list, or a sub-query that is a derived table within the FROM clause? If the latter, you won't see a performance difference between that and a CTE under most circumstances.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 6, 2012 at 4:02 am

    #1470014

    Thanks that was exactly the answer I was looking for

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply