Re: Optimisation of a sql query

Hi Sybrandb,
You are right and I must confess, I am kind of novice in this field. I should have looked into the options. Here is my explanation to each of your query.
Since I m working in SAS, I will provide you the answers with respect to it.
1 No version. How do you expect help
Ans - SAS version 9.1
2 No explain plan. How do you expect help Ans. I don't know how do we get explain plan in SAS. 3 How did you establish there is a 'having issue'? Did you look in a  crystal ball, or went to your work on a broom stick? Ans. I concluded to this issue because I had a rough idea that the execution of a sql query starts from the last mapping, hence, we should ideally give the biggest filter at the bottom moving upwards ! Please correct me if I m wrong here.
4 Did you check where your bottlenecks are located? Disk? Logwr?  Database archiving like hell? If you didn't do any of those things, how do you expect anyone can help you out. Ans. The disk space is enough and so is the processor speed. In fact, its not in my hands to alter them as well. I have to work in the given disk space (which is enough) and processor speed.

Please let me know if the provided suffices.

Thanks and Regards,
Abhijat.

sybrandb wrote:
> Abhijat wrote:
> > Hi group,
> > I have a simple create table query.
> > CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
> > SELECT *
> > FROM rd_at2.AUTH_MAY06 A LEFT JOIN
> > dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
> > ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
> > GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM
> > HAVING A.RSPNS_CDE = "00"
> > AND A.TST_ACCT_CDE = ""
> > AND A.AUTH_TRAN_CDE IN ("0110","0120")
> > AND A.AUTH_PROC_1_CDE = "09";
> >
> > The data table AUTH_MAY06 is of size 25 GB and the other table is about
> > 500MB!
> > I have two questions regarding the performance tuning of this query.
> > 1. What should be the order of having clause so that the processing
> > time is decreased ?
> > 2. Is there another way out (ex. using some options in oracle/sas)
> > which would further decrease the processing speed (assuming we have
> > fixed the "having" issue, if any)?
>
>
> 1 No version. How do you expect help
> 2 No explain plan. How do you expect help
> 3 How did you establish there is a 'having issue'? Did you look in a
> crystal ball, or went to your work on a broom stick?
> 4 Did you check where your bottlenecks are located? Disk? Logwr?
> Database archiving like hell? If you didn't do any of those things, how
> do you expect anyone can help you out.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
Received on Wed Jul 19 2006 - 12:49:19 CDT