Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

problem in select command Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 5:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
Sergiy (3/18/2013)
Eugene Elutin (3/18/2013)


"extra words" is a greatest argument to declare a "code" winner I'have ever seen.

Did you find anything else different?
Anything?

If you missed it - I responded on this statement:
Lynn Pettis (3/17/2013)
Really, CTEs are complex? I find them to make writing queries easier as you don't have to write derived tables.


So, I compared those 2 ways of writing queries.
Having no other points of differece, less typing makes DT queries easier to write.

Extra typing always makes anything more complex.
By definition.


There is no such definition! Actually, the opposite is quite more common.

Opposite is stupid.
Smeh bez prichiny - priznak durachiny.

If you you do typing in sake of typing - you're wasting your time and wasting time of those who read it (including SQL optimizer).

There must be a reason to add an extra line to a code.
Inline documentation, better formatting, whatever else - everytime there must be a reason.

I cannot see any advantage of that particular extra line.
Can you point on it?


From my experience, most of developers finding CTE is much more clear way to write query and therefore less complex then using "derived tables".


Most developers used to wrire "CSS style" code.
CTE fits there patterns and allows not to learn SQL-specific best practices.

That's why it's so popular.


First of all it's not quite polite to put statements in foreign language on English forum without translation. So, here we are:

Smeh bez prichiny - priznak durachiny.


Translates to: "Laughing without reason is a sign of foolishness".
Very Russian one. That what I've found on this subject:

16. Russians don’t smile out of politeness. It’s considered in poor taste to smile without a reason. A constant polite smile is considered a “smile on duty” in Russia and shows people’s insincerity, and unwillingness to show real feelings.
17. Moscow State University Facebook •It’s not typical for Russians to smile at a person whose eyes meet by chance •A Russian does not normally give a smile in return •Russians do not smile when working or doing something serious (Levine and Adelman 1993)
18. A Russian smile is a sign of personal attraction “It’s a complicated relationship” A Russian smile shows that a smiling person likes you or they are close to you. Russians do not normally show that type of affection to strangers.
19. Indian Prime Minister Manmohan Singh In Russia a smile is the expression of either high spirits or a good attitude to a partner. A Russian needs a reason to smile, which is evident to others. It gives a person the right to smile from others’ point of view. The Russian language has the unique proverb missing in other languages, “Laughter without reasons is the sign of foolishness or psychological problems.” (Wierzbicka 1998)


But actually I would argue that this is not really Russian custom but a Soviet one. As after just few years of life in other countries (Australia and UK), I have easily learned to smile to other people and without any reason. And I'm proud of it!

Now back to SQL subject. What really made me "laughing" (actually does not stay for laughing) is the way Sergiy reasoned why one syntax wins over another: "extra words".
In my opinion it cannot be a serious reasoning (and on this subject there are actually two opinions exist: my one and wrong one! ).

There must be a reason to add an extra line to a code.


Why would you add comments into code (if you do)? Make it a bit more clear? So, you can take use of CTE for the same reason. It does, very often, makes code easier to read and understand without making any negative impact for performance.

Of course, the discussed example is trivial enough to be written in any form without much difference (equal query plans just confirms that), but if you take for example more complicated "derived table" cases, then use of CTE significantly improves code readability and therefore maintainability.
Don't try to argue here or I will use your words:
Opposite is stupid.


Now, your last sample (reference http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx ) of performance issues with CTE. If Tony R. instead of CTE used "derived tables" he would have exactly the same problems. Using temp tables in such cases (large transactional data) very often improves performance significantly.

Actually what "CSS style" code is about? I have never heard this terminology in relation to T-SQL.
Also, could you please advise where I can find anything on "SQL-specific best practices" where it says that CTE is not part of them?

And on the bright note:

If you you do typing in sake of typing - you're wasting your time and wasting time of those who read it

Say it to Leo Tolstoy


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432402
Posted Monday, March 18, 2013 5:56 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
Eugene Elutin (3/18/2013)

First of all it's not quite polite to put statements in foreign language on English forum without translation. So, here we are:

As you pointed out this proverb does not make much sense for non-Russians.
I'm sure your long explanation did not help a lot here.
Actually, it only gave a wrong impression about Russians and wrong meaning to the proverb.

Therefore I left the Russian stuff in Russian, and explained the meaning right after it:

If you you do typing in sake of typing - you're wasting your time and wasting time of those who read it



Eugene Elutin (3/18/2013)
In my opinion it cannot be a serious reasoning

Serious??? What said serious?
Go back and check what did I start that post with!
And how did I ended it.
It was a joke from the beginning. Slightly provocative, I must admit, and look - how many fish I've got on that hook!

You've been too serious! Relax a little bit.


Why would you add comments into code (if you do)? Make it a bit more clear? So, you can take use of CTE for the same reason. It does, very often, makes code easier to read and understand without making any negative impact for performance.


It's not what Lynn said, and what I responded on.
He stated that CTE are easier to write than derived tables.
You may go back and check.
Nothing about readability.

Now, your last sample

I believe I pointed out that I question that article myself.

I wonder why all the CTE believers "did not notice" the 1st link?
Does not match their beliefs?


Actually what "CSS style" code is about? I have never heard this terminology in relation to T-SQL.
Also, could you please advise where I can find anything on "SQL-specific best practices" where it says that CTE is not part of them?

Declare styles (objects in OO languages), fill them up and then refer in the following code.
That's how you code CSS, that's how CTE code is formed.

Why it's not in line with SQL best practices?
Because SQL operates with databases.
Where all the objects are already declared (with various CREATE sratements) and populated (with INSERT/UPDATE/DELETE) statements.

Even the keyword used for CTE.
Type "WITH" in BOL and see what articles it will show to you.
The CTE use of "WITH" is totally foreign to SQL, it was mindlessly brought across from other languages, with the single purpose - give front end developers a construction they are familiar with to make them more comfortable while cutting cr..py SQL code.

Say it to Leo Tolstoy

Actually, Tolstoy did not write a line for no reason.
His code is just extremely well in-line documented.
Post #1432414
Posted Monday, March 18, 2013 5:57 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
David Webb-200187 (3/18/2013)
From the MS site:

\
A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation.


http://msdn.microsoft.com/en-us/library/ms345368(v=SQL.105).aspx


What about "When the query plan for a common table expression query does not use a spool operator"?
Post #1432416
Posted Monday, March 18, 2013 7:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
Sergiy (3/18/2013)
Eugene Elutin (3/18/2013)

First of all it's not quite polite to put statements in foreign language on English forum without translation. So, here we are:

As you pointed out this proverb does not make much sense for non-Russians.
I'm sure your long explanation did not help a lot here.
Actually, it only gave a wrong impression about Russians and wrong meaning to the proverb.


I would love to know the right one. I don't know, but what I've quoted in English is well in line with some Russian linguists finding http://commbehavior.narod.ru/RusFin/RusFin2000/Sternin4.htm
non-English site.


You've been too serious! Relax a little bit.


I'm hardly ever serious. But thanks, I'm going to jacuzzi for relaxation, a bit...


Why would you add comments into code (if you do)? Make it a bit more clear? So, you can take use of CTE for the same reason. It does, very often, makes code easier to read and understand without making any negative impact for performance.


It's not what Lynn said, and what I responded on.
He stated that CTE are easier to write than derived tables.
You may go back and check.
Nothing about readability.


I did. Checked. He did not state that CTE are easier to write. For a mater of fact, he said that writing queries using CTE is easier than using derived tables. I tend to agree as CTE helps code readability (I agree that Lynn didn't mentioned this point there).


Now, your last sample

I believe I pointed out that I question that article myself.


Great we have something in common (apart of mother tongue )


I wonder why all the CTE believers "did not notice" the 1st link?
Does not match their beliefs?

I'm agnostic. So, don't hold much of any beliefs...
I've looked into your first one. This one is even worse than second.
There is no sample of code which allows author to claim that


You can simply see that a CTE does not write anything to the tempdb, hence it has a higher performance compared with the other two options.


At the end, if there is enough memory for use by SQL Server, regardless of what you are using temp tables, table variabes or CTE, you will see no writes into tempdb (except ones to create references in its sys.objects for #tables and table variables). If there is not enough memory - SQL Server will use tempdb to store the data.
Actually author calling CTE's as "temporary views" - I never heard anything like that before.
Just using standard MS description from BoL:

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.


So, I prefer to call it as MS - temporary result set.

The second line I've marked in bold is another point. CTE is like derived table, especially it will be for the sample author of the above article talking about. Therefore, he would see the same performance while using derived tables.
And just while I don't forget. Have you seen an example where recursive CTE is used to find distinct values and does it faster than standard SELECT DISTINCT?



Declare styles (objects in OO languages), fill them up and then refer in the following code.
That's how you code CSS, that's how CTE code is formed.


I almost wanted to use some rude word here, but I've behaved...
So, I simply say that I strongly disagree with you on this. CTE is not formed the same way as objects in OO languages.


Why it's not in line with SQL best practices?
Because SQL operates with databases.
Where all the objects are already declared (with various CREATE sratements) and populated (with INSERT/UPDATE/DELETE) statements.

Even the keyword used for CTE.
Type "WITH" in BOL and see what articles it will show to you.


Type "FROM" in Google and see what articles it will show to you.


The CTE use of "WITH" is totally foreign to SQL...


It's not. Common Table Expressions are part of ANSI SQL 99, or SQL3.
Since 1999 the SQL standard allows named subqueries called common table expression (named and designed after the IBM DB2 version 2 implementation; Oracle calls these subquery factoring). CTEs can be also be recursive by referring to themselves; the resulting mechanism allows tree or graph traversals (when represented as relations), and more generally fixpoint computations.



Say it to Leo Tolstoy

Actually, Tolstoy did not write a line for no reason.
His code is just extremely well in-line documented.

O'Yes he did and plenty! There are quite few literature critics agree on this and even call him graphoman!
Graphomania (from Greek γραφειν — writing, and μανία — insanity), also known as scribomania, refers to an obsessive impulse to write.


That's enough for today, it's 5 past 1 am here. Time for jacuzzi and good sleep!


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432430
Posted Monday, March 18, 2013 7:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 1,787, Visits: 5,693
Just to stick up for the old CTE...

"On-the-fly" tally tables...

CTE method:

WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (
SELECT 1 FROM E1 a,E1 b
),
E4(N) AS (
SELECT 1 FROM E2 a,E2 b
),
cteTally (N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT N
FROM cteTally;

Derived Table method (to get the same execution plan, which has been proven to be an excellent performer)

SELECT N
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (
SELECT 1
FROM (
SELECT 1
FROM (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
) a(N), (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
) b(N)
) c(N), (
SELECT 1
FROM (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
) d(N), (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
) e(N)
) f(N)
) g(N)
) DTTally(N)

I know which one is more readable to me (CTE) and which one takes less typing (CTE).

I realise this is a bit off-topic for the original question, but just in case anyone who is unsure about the use of CTEs reads this thread, I feel it important to point out a situation where CTE is both more compact and more readable.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1432433
    Posted Monday, March 18, 2013 7:57 PM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Monday, August 18, 2014 8:36 AM
    Points: 2,836, Visits: 5,066
    Just replace

     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1

    with

     SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V(N)



    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help
    Post #1432434
    Posted Monday, March 18, 2013 8:11 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:29 PM
    Points: 1,787, Visits: 5,693
    Eugene Elutin (3/18/2013)
    Just replace

     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1

    with

     SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V(N)



    Like this? (Assuming you have SQL2008 + )

    WITH 
    E1(N) AS ( SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(N) ),
    E2(N) AS ( SELECT 1 FROM E1 a,E1 b ),
    E4(N) AS ( SELECT 1 FROM E2 a,E2 b ),
    cteTally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 )
    SELECT N
    FROM cteTally;

    SELECT N
    FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (
    SELECT 1
    FROM (
    SELECT 1
    FROM
    (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(N),
    (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(N)
    ) c(N), (
    SELECT 1
    FROM
    (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(N),
    (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) e(N)
    ) f(N)
    ) g(N)
    ) DTTally(N);

    The CTE version is still much shorter (nearly half the typing) and much easier to read, isn't it?

    And be careful you don't drip water on your keyboard


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1432436
    Posted Monday, March 18, 2013 8:21 PM
    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Monday, August 18, 2014 9:16 PM
    Points: 4,576, Visits: 8,347
    Eugene Elutin (3/18/2013)
    I did. Checked. He did not state that CTE are easier to write. For a mater of fact, he said that writing queries using CTE is easier than using derived tables.


    OK. It's definitely enough for you for today.
    Go to sleep.
    Post #1432437
    Posted Monday, March 18, 2013 8:37 PM
    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Monday, August 18, 2014 9:16 PM
    Points: 4,576, Visits: 8,347
    mister.magoo (3/18/2013)
    I realise this is a bit off-topic for the original question,

    I believe the whole thread by now is off-topic for the original question.

    but just in case anyone who is unsure about the use of CTEs reads this thread, I feel it important to point out a situation where CTE is both more compact and more readable.

    Only if you're not aware of this method:

     SELECT TOP 10000 N
    FROM dbo.Tally AS T
    ORDER BY N

    Post #1432440
    Posted Monday, March 18, 2013 8:55 PM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 3:23 PM
    Points: 13,272, Visits: 12,103
    Sergiy (3/18/2013)
    Sean Lange (3/18/2013)
    Yes I don't think there is a clear cut advantage for either approach as far as performance is concerned. I think it boils down to preference.


    If you come back to where it's started you'll see it's exactly what I said.
    The only difference is as minor as an extra line of code required for syntax sake only.

    I'm not so sure about that "in-memory" thing, therefore I did not bring it up, until you guys forced me.
    But I know there is controversy around it.
    That's why I prefer (and suggest) not to use CTE where it's not necessary.

    Again, it's my preference.


    I don't think we disagreed on this point. I joined the conversation simply because you said that more keystrokes is more complex. That to me was completely ridiculous so I joined in.

    It makes no sense at all to think that a CTE cannot live at least partially in tempdb. There is no way that the sql team was that short sighted. This is nothing more than extension of the fallacy that temp tables are memory only. It just doesn't make sense.

    As for the article discussing CTE's doing a self join, that is a well known performance issue with CTEs.



    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Moden's splitter.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
    Understanding and Using APPLY (Part 1)
    Understanding and Using APPLY (Part 2)
    Post #1432445
    « Prev Topic | Next Topic »

    Add to briefcase «««12345»»

    Permissions Expand / Collapse