Re: Optimisation of a sql query

From: Abhijat <ursabhijat_at_gmail.com>
Date: 19 Jul 2006 13:47:00 -0700
Message-ID: <1153342020.867611.60420@m73g2000cwd.googlegroups.com>

Hi Sybrand/David/Brian/Charles/Mladen,
First of all many thanks for your valuable thoughts and invaluable time.
I understand my mistake and below I m going to mention the steps that I am going to take to correct. Please look into it too.

My actual code doesn't contain select *, it has fixed selections, hence what David is mentioning about select * is automatically taken care of. About using group by and having, I have to confess that the query I have mentioned here is slightily different from the actual query. The actual query does contain selections like "SUM(var1)". The actual query that I intend to execute is like this

	CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
	SELECT
	 DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
NEW_ISSUER,"DISCOVER" AS ISSUER,
	 B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
SUM(A.AUTH_AMT) AS AUTHORIZED ,
	 SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - CALCULATED
CASHOVER ) AS MERCHANDISE
	FROM rd_at2.AUTH_MAY06 A LEFT JOIN dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
	ON   A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
	GROUP BY   B.MCC, B.MERCHANT
	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";

So now that I m using aggregated functions, please suggest the best way to improve my code. Shall I go ahead with the where clause as suggested by you all or anything else. Please suggest with regards to the fact that the AUTH_MAY06 is about 25 GB in size and the other table is about 500 MB.

A quick note to Sybrand and David: I guess I could not make myself very clear in my last mail. I am not working on Oracle tables. I am working in SAS and working with SAS datasets. Hence, the version still remains to be SAS 9.1. Please help with regards to this.

Once again thanks to all for your overwhelming response.

Regards,
Abhijat

Mladen Gogala 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) What are you grouping/having? There is nothing to group: no sum,
> average, count or anything like that.
> 2) Are you table indexed? With the condition like
> A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
> I would expect both involved columns to have indexes.
> 3) Are the tables analyzed? Do all the columns mentioned in the query
> have histograms?
> 4) Do you have an execution plan? Database will tell you "master, I have
> a cunning plan". Then, you have to revise that plan and approve it.
> It's known as the Baldrick mechanism. Cunning plans are visible in
> V$SQL_PLAN table.
> 5) Are you really, really sure that you need @#$%! ANSI syntax? Every
> patchset installation notes mention bugs of @#$%! ANSI joins and CBO
> combination. What are you trying to create? An application that will
> suck equally on every platform? That's what "portable SQL" is for.
>
> --
> Mladen Gogala
> http://www.mgogala.com
Received on Wed Jul 19 2006 - 15:47:00 CDT