bad query

Database

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.

6 REPLIES
Junior Supporter

Re: bad query

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.

Junior Contributor

Re: bad query

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?

Re: bad query

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.

Junior Contributor

Re: bad query

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.

Re: bad query

Great Solution, Thanks a lot dieter.

Junior Supporter

Re: bad query

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