user1874594 user1874594 - 7 months ago 25
SQL Question

Teradata SQL PDCR query- filtering out to get only Unique Queries with High CPU rank ( Database Performance tuning )

I am running a standard Query joining PDCRLogtbl with PDCRSQLTbl for pulling up high impact CPU. I am sure a similar one has been used / seen before , most places . Nothing exiting about it

SELECT
RANK(ImpactCPU) AS CPURank
,USERNAME
,sessionid
,AcctString
,/*--including additionally */ Hashrow ( qrytext) Unique_SQL_ID
/* qrytext comes from SQLtbl.SQLTextinfo if available or its logtbl.querytext*/
<col list>
from
(SELECT a.USERNAME
, sessionid
-- , a.logdate
, a.ProcId
, a.QueryId
, a.expandacctstring
<calculations for CPU skew, Impact CPU , other skew's here>
from PDCRinfo.DBQLogtbl
where <filters here>
group by clause
) a
LOJ ( sel < col list> from FROM PDCRInfo.DBQLSQLTbl s
where <filters here> ) s on <join cols >
order by CPURank asc , spoolusage desc


o/p is something like this


CPUrank Username <other cols> impactCPU Querytext Unique_SQL_ID
1 JohnD 50000 Sel foobar 1F-0C-A1-EB
5 JaneD 60000 sel yadaya A9-CE-55-1D
6 JohnD 35000 Sel foobar 1F-0C-A1-EB
9 JohnD 25000 Sel foobar 1F-0C-A1-EB
10 BobD 24000 sel Daddy 6E-1C-18-08
11 JaneD 23000 sel yadaya A9-CE-55-1D
12 JohnD 22500 Sel foobar 1F-0C-A1-EB
15 GeorgeD 22400 sel holahu 9B-4A-D1-F4


Here the SAME
Unique_SQL_ID
shows up in varying spots 1 , 6 and 9.I just want the 1st Instance - one with the
highest CPU rank
to show up and rest of 'em ignored. After all its the same SQL being run over on different occasions.


Desired



CPUrank Username <other cols> impactCPU Querytext Unique_SQL_ID
1 JohnD 50000 Sel foobar 1F-0C-A1-EB
5 JaneD 60000 sel yadaya A9-CE-55-1D
10 BobD 24000 sel Daddy 6E-1C-18-08
15 GeorgeD 22400 sel holahu 9B-4A-D1-F4


What should I be doing ? maybe put in a qualifying or some similar filter ... some sort of an OLAP function to pass those rows through that will filter em out. I guess I could do it through an extra pass of all those rows through a
dt
but wanted to see if there's a simple filter I can stick in there that can do the job just as well
Thanks !

Answer

Adding this filter at the end of the query , will give the fix .

  qualify 
        ROW_NUMBER() OVER (PARTITION BY Unique_SQL_ID
                               ORDER BY impactCPU DESC
                          )
                             = 1

The Original answer elegantly gave the row_num () over (PARTITION BY Unique_SQL_ID ORDER BY impactCPU DESC ) which is exactly what I was looking for. But this being an Ordered Analytical Function - you can't stick it in the where clause or having either. This is just a minor modification. The gist of the answer given by MatBailie is abs. right . Thanks Mat