I found a use for CTE in SQL views



  • @Groaner showed me that you could use Common Table Expression in views. I thought it was interesting at the time but didn't think I would have any use for it.

    We have a dynamic questionnaire application. It is used once per year and questions do not change during usage. Questions vary slight by day of the week performed.

    Our tester has read only access to the database and want to view the answers of the questions she has filled out.

    So I created a view something like this.

    create view vAnswers
    as
    
    with tmpQuestionaires as (
    	select *
    	from Questionaires
    ), tmpA1 as (
    	select QuestionaireId, Description A1
    	from [dbo].[QuestionaireAnswers] qa inner join AnswerLookup a on qa.AnswerId = a.AnswerId
    	where QuestionId = 1
    
    ), tmpA2 as (
    	select QuestionaireId, Description A2
    	from [dbo].[QuestionaireAnswers] qa inner join AnswerLookup a on qa.AnswerId = a.AnswerId
    	where QuestionId = 2
    
    ), tmpA3 as (
    	select QuestionaireId, Description A3
    	from [dbo].[QuestionaireAnswers] qa inner join AnswerLookup a on qa.AnswerId = a.AnswerId
    	where QuestionId = 3
    )
    
    select q.QuestionaireId, A1 as 'MoreDescriptiveColumnname1'
    	, A3 as 'MoreDescriptiveColumnname2'
    	, A3 as 'MoreDescriptiveColumnname3'
    from tmpQuestionaires q
    	left join  tmpA1 a1 on q.QuestionaireId = a1.QuestionaireId
    	left join  tmpA2 a2 on q.QuestionaireId = a2.QuestionaireId
    	left join  tmpA3 a3 on q.QuestionaireId = a3.QuestionaireId
    

    Question Ids are hard-coded for simplicity, this is just quick and dirty.

    It is pretty slow as a direct query (20 seconds), run from the view 3 seconds. I put it in DataZen and it updates every 15 minutes and is easier for tester to view.

    There were over 25 actual questions and thanks to someone here I used NimbleText to be able to quickly put the SQL together.

    Probably took less than an hour to finish while also reading here, checking my email, etc.

    I'm pretty sure I got help from @boomzilla and @Weng last year when I was doing the initial database design. I apologize if I forgot others.

    Fake edit: I found the thread: https://what.thedailywtf.com/topic/18655/alternatives-to-eav-anti-pattern/

    Also: @Lorne-Kates , @Tsaukpaetra , @Captain , @c62 (who hasn't been online since May 2016), @swayde , @Jarry

    I love me some CTEs now.



  • oracle CTEs are better.

    in oracle you can have something like

    in view

    create view myview as select * from bigasstable

    and then you can do queries like

    with bigasstable as (select * from bigasstableproxy where type='blah')
    select * from myview

    this helps you whittle down the big table before the view has a chance to use it.

    Not some much in SQL server



  • @dangeRuss said in I found a use for CTE in SQL views:

    oracle CTEs are better.

    in oracle you can have something like

    in view

    create view myview as select * from bigasstable

    and then you can do queries like

    with bigasstable as (select * from bigasstableproxy where type='blah')
    select * from myview

    this helps you whittle down the big table before the view has a chance to use it.

    Not some much in SQL server

    I don't see what couldn't be done in SQL Server...that all looks legit to me.

    EDIT: NVM...I missed bigasstableproxy...how does that work?



  • @dangeRuss said in I found a use for CTE in SQL views:

    oracle CTEs are better.

    in oracle you can have something like

    in view

    create view myview as select * from bigasstable

    and then you can do queries like

    with bigasstable as (select * from bigasstableproxy where type='blah')
    select * from myview

    this helps you whittle down the big table before the view has a chance to use it.

    Not some much in SQL server

    That looks both interesting and frightening at the same time, like having a connection-scoped synonym or something.

    What does the whittling? Seems like at best, you're going to have to scan bigasstable indices regardless to apply the where clause and produce bigasstableproxy.

    The equivalent implementation in SQL Server would probably involve a temp table, which would perform the same scans and then persist the results. If the results are too big to fit into memory, seems like this method wouldn't be any more efficient, although it might be slightly more convenient.



  • @Karla said in I found a use for CTE in SQL views:

    I don't see what couldn't be done in SQL Server...that all looks legit to me.

    EDIT: NVM...I missed bigasstableproxy...how does that work?

    It's just a synonym in oracle since you're not allowed to select from the table that you're calling the cte.

    @Groaner said in I found a use for CTE in SQL views:

    What does the whittling? Seems like at best, you're going to have to scan bigasstable indices regardless to apply the where clause and produce bigasstableproxy.

    well the view is obviously going to be much bigger and involve many more tables. The idea is that I know that I only want a subset of records from that giant ass table and half the time oracle is going to make the wrong decision when optimizing things. Very often we can get several orders of mangitude speed ups using CTEs like this in oracle.

    SQL server doesn't let you redefine a table that the view selects from like that (as far as I know).

    The equivalent implementation in SQL Server would probably involve a temp table, which would perform the same scans and then persist the results. If the results are too big to fit into memory, seems like this method wouldn't be any more efficient, although it might be slightly more convenient.

    You mean you would do this manually or do you mean the SQL Server query optimizer will do this for you?



  • @dangeRuss said in I found a use for CTE in SQL views:

    SQL server doesn't let you redefine a table that the view selects from like that (as far as I know).

    I'm not quite sure what you mean by "redefine" the table.

    I think this would be the equivalent in SQL Server:

    
    create view myview as select * from bigasstable
    go
    
    ;with bigasstableproxy as (select * from bigasstable where type='blah')
    
    select * from bigasstableproxy
    


  • @Karla said in I found a use for CTE in SQL views:

    @dangeRuss said in I found a use for CTE in SQL views:

    SQL server doesn't let you redefine a table that the view selects from like that (as far as I know).

    I'm not quite sure what you mean by "redefine" the table.

    I think this would be the equivalent in SQL Server:

    
    create view myview as select * from bigasstable
    go
    
    ;with bigasstableproxy as (select * from bigasstable where type='blah')
    
    select * from bigasstableproxy
    

    I think you're missing the point. The point is to select from the view, but have it use your redefined (whittled down) table.



  • @dangeRuss said in I found a use for CTE in SQL views:

    @Karla said in I found a use for CTE in SQL views:

    @dangeRuss said in I found a use for CTE in SQL views:

    SQL server doesn't let you redefine a table that the view selects from like that (as far as I know).

    I'm not quite sure what you mean by "redefine" the table.

    I think this would be the equivalent in SQL Server:

    
    create view myview as select * from bigasstable
    go
    
    ;with bigasstableproxy as (select * from bigasstable where type='blah')
    
    select * from bigasstableproxy
    

    I think you're missing the point. The point is to select from the view, but have it use your redefined (whittled down) table.

    Maybe. Probably.

    What difference does it make selecting from the redefined view (Oracle) versus selecting from the CTE (SQL Server)?



  • @dangeRuss said in I found a use for CTE in SQL views:

    well the view is obviously going to be much bigger and involve many more tables. The idea is that I know that I only want a subset of records from that giant ass table and half the time oracle is going to make the wrong decision when optimizing things. Very often we can get several orders of mangitude speed ups using CTEs like this in oracle.

    SQL server doesn't let you redefine a table that the view selects from like that (as far as I know).

    Ah, so it's more like a query hint, then.

    You mean you would do this manually or do you mean the SQL Server query optimizer will do this for you?

    Manually.

    select *
    into #SmallerTableOfRowsICareAbout
    from bigasstable
    where [condition]
    
    select *
    from NotSoBigTable n
    inner join #SmallerTableOfRowsICareAbout s on
    (n.fooID = s.fooID)
    ...
    
    


  • A better example would be something like this

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)

    so instead of doing something like

    select * from customerOrders where orderDate between sysdate-1 and sysdate

    I would do

    with orders as (select * from ordersProxy where orderDate between sysdate-1 and sysdate)
    select * from customerOrders

    so yea it is basically like a hint and is a lot cleaner then messing with temp tables.



  • @dangeRuss said in I found a use for CTE in SQL views:

    A better example would be something like this

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)

    so instead of doing something like

    select * from customerOrders where orderDate between sysdate-1 and sysdate

    I would do

    with orders as (select * from ordersProxy where orderDate between sysdate-1 and sysdate)
    select * from customerOrders

    so yea it is basically like a hint and is a lot cleaner then messing with temp tables.

    Maybe I am being dense...

    I don't see the difference between that and this:

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    
    ;with ordersProxy as (select * from customerOrders where customerOrders between sysdate-1 and sysdate)
    select * from ordersProxy
    

    EDT: copy paste error



  • @Karla said in I found a use for CTE in SQL views:

    @dangeRuss said in I found a use for CTE in SQL views:

    A better example would be something like this

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)

    so instead of doing something like

    select * from customerOrders where orderDate between sysdate-1 and sysdate

    I would do

    with orders as (select * from ordersProxy where orderDate between sysdate-1 and sysdate)
    select * from customerOrders

    so yea it is basically like a hint and is a lot cleaner then messing with temp tables.

    Maybe I am being dense...

    I don't see the difference between that and this:

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    
    ;with ordersProxy as (select * from customerOrders where customerOrders between sysdate-1 and sysdate)
    select * from ordersProxy
    

    well you don't need a CTE in your case, it basically becomes

    select * from customerOrders where orderDate between sysdate-1 and sysdate
    

    The difference is that in your case SQL Server is going to optimize the query. It will take a look at the two tables, and hopefully whittle down the orders table before it joins it to the customer table, not after. But you never know. Especially if you're selecting from 20+ tables.

    In my case, I am explicitly whittling down the orders table to just orders for that one date first, and all the joins, etc happen later.



  • @dangeRuss said in I found a use for CTE in SQL views:

    @Karla said in I found a use for CTE in SQL views:

    @dangeRuss said in I found a use for CTE in SQL views:

    A better example would be something like this

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)

    so instead of doing something like

    select * from customerOrders where orderDate between sysdate-1 and sysdate

    I would do

    with orders as (select * from ordersProxy where orderDate between sysdate-1 and sysdate)
    select * from customerOrders

    so yea it is basically like a hint and is a lot cleaner then messing with temp tables.

    Maybe I am being dense...

    I don't see the difference between that and this:

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    
    ;with ordersProxy as (select * from customerOrders where customerOrders between sysdate-1 and sysdate)
    select * from ordersProxy
    

    well you don't need a CTE in your case, it basically becomes

    select * from customerOrders where orderDate between sysdate-1 and sysdate
    

    Results-wise yes...but is it the same performance-wise?

    The difference is that in your case SQL Server is going to optimize the query. It will take a look at the two tables, and hopefully whittle down the orders table before it joins it to the customer table, not after. But you never know. Especially if you're selecting from 20+ tables.

    When I am joining 20+ table I use multiple CTEs in a single select to whittle it down as quickly as possible which has had an enormous improvement on performance.

    In my case, I am explicitly whittling down the orders table to just orders for that one date first, and all the joins, etc happen later.



  • @dangeRuss said in I found a use for CTE in SQL views:

    @Karla said in I found a use for CTE in SQL views:

    @dangeRuss said in I found a use for CTE in SQL views:

    A better example would be something like this

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)

    so instead of doing something like

    select * from customerOrders where orderDate between sysdate-1 and sysdate

    I would do

    with orders as (select * from ordersProxy where orderDate between sysdate-1 and sysdate)
    select * from customerOrders

    so yea it is basically like a hint and is a lot cleaner then messing with temp tables.

    Maybe I am being dense...

    I don't see the difference between that and this:

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    
    ;with ordersProxy as (select * from customerOrders where customerOrders between sysdate-1 and sysdate)
    select * from ordersProxy
    

    well you don't need a CTE in your case, it basically becomes

    select * from customerOrders where orderDate between sysdate-1 and sysdate
    

    The difference is that in your case SQL Server is going to optimize the query. It will take a look at the two tables, and hopefully whittle down the orders table before it joins it to the customer table, not after. But you never know. Especially if you're selecting from 20+ tables.

    In my case, I am explicitly whittling down the orders table to just orders for that one date first, and all the joins, etc happen later.

    OK I think I got it....

    This would be the equivalent:

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    
    ;with ordersProxy as (select * from orders where orderDate between sysdate-1 and sysdate)
    select *
    from ordersProxy o inner join customerOrders co on o.customerId = co.customerId
    
    

    I agree the Oracle syntax is more elegant.



  • @Karla said in I found a use for CTE in SQL views:

    OK I think I got it....

    This would be the equivalent:

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    
    ;with ordersProxy as (select * from orders where orderDate between sysdate-1 and sysdate)
    select *
    from ordersProxy o inner join customerOrders co on o.customerId = co.customerId
    
    

    I agree the Oracle syntax is more elegant.

    Aren't you getting columns from orders twice then? I don't think sql server will like that.

    also it will likely have already done the joining when you select from the customerOrders view (the optimizer will not necessarily select things in the order that you have listed them. )



  • @dangeRuss I would add the proper column list...just used * to type quickly.

    ETA: If it is a straight select and not into a table variable or into temp table SQL Server does allow duplicate columns.



  • @Karla said in I found a use for CTE in SQL views:

    @dangeRuss I would add the proper column list...just used * to type quickly.

    So basically you're joining the orders table twice, one time in the hopes of having sql server whittle that table down first.

    It may or may not work and may just be equivalent to

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    select * from customerOrders  where orderDate between sysdate-1 and sysdate
    

    I'm not sure how much weight sql server gives to the order in which you list the tables. It may likely end up being a lot slower with that CTE.

    Something I think equivalent but maybe a bit less ugly

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    select * from customerOrders  where orderID in (select orderID from orders where orderDate between sysdate-1 and sysdate)
    

    or maybe something using exists instead

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    select * from customerOrders co where exists (select orderID from orders o where orderDate between sysdate-1 and sysdate and o2.orderid=co.orderId)
    

    Again, may or may not work. The oracle thing is guaranteed to work.



  • @dangeRuss said in I found a use for CTE in SQL views:

    @Karla said in I found a use for CTE in SQL views:

    @dangeRuss I would add the proper column list...just used * to type quickly.

    So basically you're joining the orders table twice, one time in the hopes of having sql server whittle that table down first.

    It may or may not work and may just be equivalent to

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    select * from customerOrders  where orderDate between sysdate-1 and sysdate
    

    I'm not sure how much weight sql server gives to the order in which you list the tables. It may likely end up being a lot slower with that CTE.

    I'll do some playing around and see.

    Something I think equivalent but maybe a bit less ugly

    create view customerOrders as (select * from customer c inner join orders o on c.customerid=o.customerid)
    select * from customerOrders  where orderID in (select orderID from orders where orderDate between sysdate-1 and sysdate)
    

    In the optimization that I have done joins are often faster than subqueries.

    Again, may or may not work. The oracle thing is guaranteed to work.

    ETA: @Groaner thoughts?



  • @dangeRuss said in I found a use for CTE in SQL views:

    In my case, I am explicitly whittling down the orders table to just orders for that one date first, and all the joins, etc happen later.

    Is that very different than just doing this?:

    select * from 
        customer c
        inner join (  select * from orders
                      where orderDate between sysdate-1 and sysdate) as filteredOrders
            on c.customerid = filteredOrders.customerid
    


  • @djls45 said in I found a use for CTE in SQL views:

    @dangeRuss said in I found a use for CTE in SQL views:

    In my case, I am explicitly whittling down the orders table to just orders for that one date first, and all the joins, etc happen later.

    Is that very different than just doing this?:

    select * from 
        customer c
        inner join (  select * from orders
                      where orderDate between sysdate-1 and sysdate) as filteredOrders
            on c.customerid = filteredOrders.customerid
    

    Probably not, except in my case I have a perfectly usable view which I can speed up for certain use cases (such as looking at orders from yesterday).

    maybe someone wants to look at just orders from a certain customer.

    select * from customerOrders where customerId=5

    If it's slow, you can again speed it up with a CTE, but most of the time you would just use the view as is and you're not repeating the view code elsewhere.



  • And nevermind that I was initially talking about using the CTE inside the definition of the view.

    No more of this Oracle-splaining!! :trollface:


  • Notification Spam Recipient

    said in I found a use for CTE in SQL views:

    @Tsaukpaetra

    I have been summoned, and so I shall eventually appear....

    Wait, why did select-to-quote not work?!



  • Discussions like this make me realize how much I don't know about databases.


Log in to reply