Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Query without max()
Message
De
15/09/2019 17:42:33
Walter Meester
HoogkarspelPays-Bas
 
 
À
15/09/2019 17:09:35
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
01670845
Message ID:
01670891
Vues:
63
>>>Your conclusion has no basis whatsoever. Lets agree to disagree
>>
>>That is a statement from ignorance.
>>Lets see, the example given in this thread.
>>
>>
WITH totals (code, qty) AS 
>>(SELECT code, SUM(qty)
>>    FROM #Table1
>>    GROUP BY code)
>>SELECT t1.code, tn.Names, tn.Country, t1.qty
>>FROM totals t1
>>    CROSS APPLY (SELECT TOP (1) names, country
>>    FROM #Table1 t2
>>    WHERE t1.code = t2.code) tn(Names, Country)
>>ORDER BY t1.code;
>>
>>
>>Vs
>>
>>
>>select x.code, y.Names, y.country, x.Qty
>>	FROM 
>>		(SELECT code, sum(Qty) as Qty
>>			FROM #Table1 T1
>>			GROUP BY code
>>		) AS x
>>	CROSS APPLY (SELECT TOP 1 names, country FROM #Table1 T2 WHERE x.Code = T2.code) as y
>>
>>
>>You really want to argue that the fist one is better readable than the latter. If you really think that, then I agree there is no point in arguing. I'd never use a CTE the for that. Its overkill and inappropriate use of CTEs and to me its a very bad smell of SQL skills.
>>
>>The problem with the CTE in the example above
>>1. Its lenght: There is more to read.
>>2, Its a lot less readable for programmers not comfortable with CTE's. This is a bigger problem that you'd think. The UT is littered with examples of that.
>>3. Its somewhat more difficult to spot where the actual query begins, as it is not at the beginning of the statement
>>4. When not using naming conventions (like the example above) you're not sure what in the SELECT statement is a CTE or an actual table. You'll have to read back to the top.
>>5. When reading the actual query, in your you'll mind have to substitute the CTE into the query to really understand what is going on. This is esspecially a problem in troubleshooting issues in execution and performance.
>>
>>Point 5 would be a strenght if the CTE is a strong abstraction where its definition is universally known. Then you're abstracting away the complexity. However at that point it might be wise to think about an inline function as you might want to use this for other queries as well.
>>
>>Again, my opinion.
>>
>>
>>
>>>>
>>>>And I srongly disagree with that. Most likely you like it from an abstraction point of view. I'd argue that in many cases, if the abstraction is that clear and strong it most likely deserves its ow inline function. In most practical cases the abstraction is complex and flawed and hinders the readbility and makes trouble shooting more difficult in the first place.
>>>>
>>>>In the case of the original topic, I'd never, ever use a CTE. its overkill and does not result in shorter and more readable code.
>>>>
>>>>Walter,
>
>Sorry if I enter the discussion.

You're welcome

>CTE is better if it is used several times on the query because it reduces the probability error

Agreed (as mentioned above). It does not make a difference in performance, but it will reduce the risk of making errors, esspecially when still working on the query. However I'd argue that if the CTE's definition is universally known and used it might be better to create a view or inline view.

>or forced if it is recursive.

Agreed (as mentioned above), In almost all cases CTE's are better for recursive soltuon than any alternative, even though I've had cases where inserting results into a temp table actually was faster.

>Otherwise it becomes an aesthetic issue, which is subjective and therefore it makes no sense to say whether it is better or worse.

To some extend you're right, but I still stand by my points made above.

Walter,
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform