All Forums Database
steve66 4 posts Joined 10/14
04 Dec 2014
bad query

Hi all,
 I have the follwing query which either runs out of spool space or keeps executing for a long time which clearly means this one is inefficient , can you please help me to query efficiently,
 
 select top 5 SpoolUsage, SqlTextInfo from NewTest.DBQLogtbl_tmp a, NewTest.DBQLSqltbl_tmp b where a.QueryID = b.QueryID order by SpoolUsage desc
I am alreading collecting statistics on the tables i am using.
 

CarlosAL 512 posts Joined 04/08
04 Dec 2014

You may try:
 
select a.SpoolUsage,
         b.SQLTextInfo
  from (select QueryID, SpoolUsage
              from NewTest.DBQLogtbl_tmp
           qualify row_number() over (order by SpoolUsage desc) < = 5 ) a,
         NewTest.DBQLSqltbl_tmp b
 where a.QueryID = b.QueryID
  order by a.SpoolUsage desc;
 
HTH.
Cheers.
Carlos.

dnoeth 4628 posts Joined 11/04
04 Dec 2014

Additional to Carlos' suggestion (which also avoids returning multiple SQL rows for huge queries):
Can you show the (P)PI of both tables? Are they clones of the dbc tables or are they partitioned. 
Explain?

Dieter

steve66 4 posts Joined 10/14
04 Dec 2014

Thanks carlos and dieter,
I am using Logdate , QueryID and ProcID as NUPI, also i have partitioned with the range of Logdate by each day . 
PARTITION BY RANGE_N(LogDate BETWEEN DATE '2014-01-01' AND DATE '2014-12-31' EACH INTERVAL '1' DAY )
A quick question , If there is a huge difference in the amount of logging between days, is the partition by logdate still efficient ?
for ex : On monday 6 million records are logged and for the remaining week combined, only 1 million records are logged.

dnoeth 4628 posts Joined 11/04
05 Dec 2014

If the tables are partitioned you must change the join to:

select a.SpoolUsage,
         b.SQLTextInfo
  from (select QueryID, ProcID, LogDate, SpoolUsage
              from NewTest.DBQLogtbl_tmp
           qualify row_number() over (order by SpoolUsage desc) < = 5 ) a,
         NewTest.DBQLSqltbl_tmp b
 where a.QueryID = b.QueryID
   and a.ProcId = b.ProcId
   and a.LogDate = b.LogDate
  order by a.SpoolUsage desc;

To get an efficient join you need to join on the full PI and the partitioning column.
 
Partitioning is still efficient if the number of rows varies.
I'd just suggest to remove the ProcId from the PI, QueryId is already unique.

Dieter

steve66 4 posts Joined 10/14
05 Dec 2014

Great Solution, Thanks a lot dieter.

teradatauser2 236 posts Joined 04/12
09 Dec 2014

I believe, you need to specify a range of dates for which you need to take out the top 5 spool consuming query. Logdate is PPI on all of the dbql tables (atleast dbqllogtbl for sure i know). The data in these tables is very huge as all data gets logged depending on the logging rules that has been enabled. So, it makes sense to query logtable for a period of time and not the full table. the data is dbql tables is purged after a period of time (or backed up to tapes) as it grows very fast. for example in my current installatiom, data older that 3 months is purged. So, if you specify a range of value for Logdate , it will work very fast.
--Samir

You must sign in to leave a comment.