Re: query problem - please help

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 24 Oct 2003 09:59:08 -0700
Message-ID: <1067014763.472186@yasure>

foolishHurts wrote:

>On Fri, 24 Oct 2003 06:23:21 -0700, Daniel Morgan
><damorgan_at_x.washington.edu> wrote:
>
>
>
>>foolishHurts wrote:
>>
>>
>[Trimmed]
>
>
>>A bit confused here.
>>
>>1. If it is their query why not throw it back on them?
>>2. Since it is a vendor query can you modify it?
>>3. Can you convert all of these CHAR columns to VARCHAR2?
>>4. Have you tried omitting various clauses to see which one(s) are
>>taking the time?
>>5. What takes 10+ hours and what takes 8 seconds?
>>6. Why the RULE hint in the insert statement?
>>
>>
>1) They contract it out and the contractor is doing things that management
>deems a higher priority. Mine is not to reason why...
>2) Yes. We take ownership.
>3) Yes, but I was hoping to change it as little as possible until after
>things are better. If you think VARCHAR will fix it, I will do it. If it
>will just save space, I can wait until a maint cycle.
>4) I eliminated the insert and went from "forever" to 8 seconds.
>5) insert/select goes to never never and gets killed.
>select without insert takes 8 seconds.
>
>For my next trick I have created the table seperately and tried to just
>"insert into". Same results so far at one hour.
>
>6) No clue why she stuck hints all over the place. In most cases, stripping
>the hints helped performance by a factor of 10. I wish I knew more about
>them, but for now, I strip them and analyze and compare the performance.
>
>"I will learn to fight fires after I put this one out". Heh.
>
>
>

1. This is nonsense. Tell management, politely, they are not getting their money's worth.
2. Good
3. This has nothing to do with saving space. It just makes string comparisons easier and
there will be a performance benefit from not trimming everything in sight. All of those
trims undoubtedly mean full table scans on everything so indexes are being ignored. This
could be a big part of your performance issue.

4. Makes no sense to me. Maybe it will to someone else.
5. Makes no sense to me. Maybe it will to someone else.
6. Strip the hint. And if the high paid consultant put it in and it is 

actually slowing things
down by even -10% the consultant is incompetent and you can tell management I will
stand behind that statement if they wish to contact me.

 From your posting I would suggest your management start flushing dollars down the
nearest toilet. It is faster, more efficient, and has more integrity as at least it is an open
acknowledgement of what they are doing.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Fri Oct 24 2003 - 11:59:08 CDT