August 30, 2010 at 8:59 am
Had one years ago from a third party vendor <cough>'CommerceOne'</cough> that was almost that long but it had a bunch of subqueries (about 40 or so if I recall). Got into a bit with their dev guys. They thought it was highly efficient and tried tossing a lot of theoretical db theory. However in the wild it was no great shakes...
Sometimes folks like to write queries to show smart they think they are....
August 30, 2010 at 9:00 am
Ninja's_RGR'us (8/30/2010)
Grant Fritchey (8/30/2010)
Lots of columns, one table... eh.Try messing with an 86 table join. That's when life gets interesting.
especially if the columns are not qualified and that you don't have intellissence (from any cie) ;-).
It would also be a miracle to have the query running in the first place... unless this is a null-less table design which would make it more probable.
August 30, 2010 at 10:29 am
coolwebs (8/30/2010)
Here is the SELECT statement that was captured....Seems pretty simple right?
. . .
. . .
. . .
The SQL SELECT statement you provided is actually quite simple, and it just selects from a single table and summarizes an expression on each column. It's actually the table [tbl_Prepped_Vehicle_ver01_20100823] that complicated. It appears to be a fact table in a data warehouse containing hundreds of demographic columns.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 30, 2010 at 10:38 am
You are correct, this is from a data wharehouse that does not adhere to OLAP standards.
😀
August 30, 2010 at 10:44 am
Whatever SQL inserts this table [tbl_Prepped_Vehicle_ver01_20100823], that's what you need to be concerned about.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 1, 2010 at 4:03 am
Nice one! Why building a complicated database with lots of tables when we can jam everything into one table? 😉
brgds
Philipp Post
September 1, 2010 at 5:19 am
Brian O'Leary (8/30/2010)
That does look like the result of a lost argument between a dba and a business user 😀
Made me smile, as where I am currently freelancing this would be considered a simple query. I've seen ones similar to that, only they join up to twelve other tables together, sometimes with 3 or 4 left joins thrown into the mix, with the SUMs in the select based on several different tables, and with a where clause almost equally as long with subselects, calculations and functions in there and an ORDER BY on the end.
It feels like we have to rescue one of the servers every week from being brough to its knees weeping by queries like that, usually run by financial business users who have just enough SQL knowledge to be dangerous. Thier in-house DBA's are fighting to get theis access cut off the servers, but so far it's been a losing battle as the whole thing is driven by marketing departments.
September 1, 2010 at 6:28 am
It feels like we have to rescue one of the servers every week from being brough to its knees weeping by queries like that, usually run by financial business users who have just enough SQL knowledge to be dangerous.
That's good stuff, I would say that our situation is very much like that. So I can feel your pain.
😀
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply