- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
select top 1 comment from comments; --all of comments!!
Admin
:)
Admin
What language is this with "SELECT TOP 10"? I'm used to seeing "SELECT .... LIMIT 10".
Admin
10 orders ought to be enough for everybody
Admin
I just want to say, you explained this topic very well. Website is must now for every business and writer to express the information so we are here to help you Chicago web Design. thank for the post and all the best.
Admin
T-SQL (for MSSQL) uses the syntax of "SELECT TOP [number] [fieldList]..."
Admin
Count your blessings. With limit 10 (postgresql, MySQL) or top 10 (SQL server) the limit is not part of the query and the database will figure things out.
With our friend Oracle, you have to add a where clause on the fake column rownum, which interestingly is applied before the order by, leading to all kinds of interesting side effects.
Admin
Admin
Was this query called from anywhere else? Perhaps it was a stub that was never completed. In that case, the WTF is not pruning unused code.
Admin
Someone leaving in some debugging code counts as a WTF?
Admin
Please do not ever require all your queries to be backed by stored procedures, why would you even do that to yourself ><
Admin
Yea
I can understand that all inserts or updates have to go through SP's, but selects???, that's what views are for...
Yazeran
Plan: To go to Mars one day with a hammer
Admin
LIMIT 10 at the end would be MySQL but T-SQL used by MS SQL does a SELECT TOP X (column list or *)
Admin
I'd say any query that is to be ran automatically as part of an app would benefit a lot from being a stored procedure unless you have an excellent reason to use an 'anti-pattern'. Maybe it's a query that's to be ran during a upgrade which only happens once. A Stored Procedure also lets you avoid having to recompile your application and instead just change the proc in the background, submitting the latest version to Git.
A problem and a benefit with a stored proc is a plan reuse. If the table changes heavily and statistics aren't kept up to date reusing a plan could cause a out of date bad plan to be executed which could take longer than just recreating a new execution plan. In most cases though stats are kept up to date and the query plans work out very well saving the app and more importantly the DB CPU a lot of time.
Admin
WTF is guessing what ANYTHING does just by the name? Book <> Cover.
Admin
Vestigial organs make it to Production all the time - both in cyberspace and meatspace.
Admin
Actually the name is GetA11Orders and it was working correctly.
Admin
That obvious point appears to have escaped both Remy and every other commentor so far.
There's probably an actual WTF behind this, in which they feed every single customer order into an Excel spreadsheet and then do ... something ... to it.
But, as presented, it's just an "oh well. Too bad the name doesn't feature the number 10. Who really cares?" sort of thing.
Admin
"A problem and a benefit with a stored proc is a plan reuse"
Plan Reuse is not really an issue with Microsoft SQL since SQL 2005 due to auto-parametarization of queries. If you are writing proper code and using a data layer (ORM or otherwise), or at least using parameterized queries it is not a problem anyway because those get sent to the server parameterized and get into the query plan cache just like stored procedures. If you are doing string-concatenation inside of a stored proc, all of the claimed security and performance benefits of stored procedures go out the window.
To be honest, the added complexity, lookups, and maintenance etc of stored procedures in combination with developer code can make stored procedures to be more work for no gain at all. Usually I recommend a proper ORM setup for most things and stored procedures sparingly. There are times where stored procedures can be significantly faster - especially when working with temp tables and avoiding round trips. For most work, though, it is just extra overhead.
Admin
Is that Dutch?
Admin
Unless, of course, you use Oracle's FETCH FIRST n ROWS ONLY clause, which behaves pretty much like the others.
Admin
No, that would be "programmeur".
Admin
No, it's in Typo.
Admin
Not specifically Oracle's FETCH FIRST. It is ANSI SQL:2008 standard, unlike all the other dialects like TOP and LIMIT.
Admin
Funny, I feel that way about people who DON'T use sprocs across the board... They simply not handling the complexity in a helpful way... Whereas procedures encapsulate a level of complexity and give a layer of separation that helps me to research more DB-centric scenarios. Making it easier to navigate the complexity.
Muddied-up DALs and hiding complexity behind Linq-to-SQL will make research and review on very large teams problematic, in my opinion/experience. Especially when multiple applications perhaps must interface w/ this DB.
Admin
To each their own tho. I'm no hater. Sprocs work great as a tool for me, however.
Admin
1998 calling - they want their data access style back!
Admin
It's always about context. If you have widget, where you need to display 10 orders, you might write query getAllOrders() so you get all orders you need to display.