Oracle-L: RE: SQL Power !!

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Fri, 09 May 2003 12:17:21 -0800
Message-ID: <F001.00595C46.20030509121721@fatcity.com>

Hi Steve,
I once worked with Program Generator packages on the Mainframe. We actually had to tune the darn package to generate efficient COBOL code. So, I can understand this auto-generation process for SQL. In this, though, the developers are coding this complex SQL stuff. My question is if it is so darn complex, why can't they take a look at the underlying design of the Application and see if anything can be done there. The stuff works on Vendor's small test databases, and then it's the Production Databases that are the problems when something does not complete in time. The query in question is yet to be identified as a performance problem, but as the volume in those tables grows, I will bet it will be the DBAs job to fix the darn database! Again!!

BTW, it was very nice indeed to have your company at the IOUG Conf. It was lot of fun meeting all the folks...

Regards,

Kirti

-----Original Message-----
Sent: Friday, May 09, 2003 2:39 PM
To: Multiple recipients of list ORACLE-L

Hi Kirti,

Computers can generate queries even more complicated/dumber than humans, well most humans. ;-)

We have a patented dynamic SQLgenerator thingy which spews out some gawd awful stuff. We can't just tune queries, we have to ask duhvelopment to tune the SQLgenerator thingy and they're scared to even look at it. On multiple web pages users can click on a plethora of radio buttons, check boxes, and fill in fields or type in text areas to their hearts' delight. Then the SQLgenerator thingy assembles/scrambles this mass of mish-mash and creates syntactically correct SQL. The thingy often sorts needlessly, joins/self-joins tables needlessly, queries on dates into the next millennia, repeatedly gets the same unchanging GUI presentation data over and over, tracks user clicks, only generates literals and never uses bind variables, etc. ad nauseum. We have entertained moving some data sets from another DBMS to Oracle because it hit the 30 table join limit on the other DBMS. I've been invited to peer inside the thingy but haven't had the nerve yet as it could send me over the edge o! r !
stress my altogether too fragile grip on reality, whatever that is. The thingy is simultaneously wonderful, awesome, awful, dreadful and is growing into a beast which can't be slain.

Looking for the Holy Grail and a magic sword in a stone somewhere... Steve Orr
Going Bozo in Bozeman, Montana

-----Original Message-----
Sent: Friday, May 09, 2003 11:27 AM
To: Multiple recipients of list ORACLE-L

Kirti,

I can challenge that !

I just finished optimising a query produced by developpement, once printed it was 6 pages long and accessed many tables.

But the best part is that a table was accessed 22 times. Yes 22 times (inline views madness).

After spending 1 hour trying to understand the beast without success, I just call the guy and ask what was the qestion the query was supposed to answer.

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel (514) 925-7187
stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>

-----Original Message-----
Kirti
Sent: Friday, May 09, 2003 12:22 PM
To: Multiple recipients of list ORACLE-L

Here is what was caught by my co-worker recently. One of the Developers, of our Preferred Vendor, actually wrote this stuff. This is invoked by the one of the forms on the intranet Web application. I was told there are quite a few scripts like this one. Performance issues? No one is complaining (yet) .... so who cares :) Enjoy...
- Kirti
SELECT DISTINCT CUST1.customer_id, LIST1.finding_name, NVL(LIST1.atn_npa, ' '),

NVL(LIST1.atn_cop, ' '), NVL(LIST1.atn_line_no, ' '), LIST1.lni,
NVL(LIST1.listed_tn_npa, ' '), NVL(LIST1. listed_tn_cop, ' '),
NVL(LIST1.listed_tn_line_no, ' '),

--- cut ---
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Orr, Steve
INET: sorr_at_rightnow.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------