MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2013

Re: Stored Procedure Speed/Visual Explain



fixed

As to cardinality, I just had a thought - look at the statistics - so I did - and in Navigator, if you get to the list of tables, right-click on a table and look for "Statistic data" - from there you can see details for each column of the table. This includes cardinality, as well as frequent values and ranges.

On 10/18/2013 6:09 PM, CRPence wrote:
On 10/18/13 11:22 AM, Michael Ryan wrote:
<<SNIP>> What can I do to make this more efficient? Create indexes
over the first/second/more values? <<SNIP>>

<<SNIP SQL>>

The results of the following query request would reveal something
about the cardinality of the data. That can assist to decide which
column(s) might best have an index, and what type of index. The minimum
and maximum requests are optional, because they might yield results that
are not to be shared, but the results could be munged for obfuscation
and still help portray something more about the data.

select 'Imvnda' as column
, cast(min(Imvnda) as varchar(25)) as low
, cast(max(Imvnda) as varchar(25)) as hi
, count(distinct Imvnda) as card
, (select avg(c)
from (select count(*) as c from INVMSTP group by Imvnda ) s
) as avg_card
from INVMSTP
union all
select 'Immdl' , min(Immdl) , max(Immdl), count(distinct Immdl)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Immdl ) s)
from INVMSTP
union all
select 'Impdcd' , min(Impdcd) , max(Impdcd), count(distinct Impdcd)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Impdcd ) s)
from INVMSTP
union all
select 'Imminr' , min(Imminr) , max(Imminr), count(distinct Imminr)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Imminr ) s)
from INVMSTP
union all
select 'Imfeat' , min(Imfeat) , max(Imfeat), count(distinct Imfeat)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Imfeat ) s)
from INVMSTP
union all
select 'Imdesc' , min(Imdesc) , max(Imdesc), count(distinct Imdesc)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Imdesc ) s)
from INVMSTP
union all
select 'Imszcl' , min(Imszcl) , max(Imszcl), count(distinct Imszcl)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Imszcl ) s)
from INVMSTP
union all
select 'Imdel' , min(Imdel) , max(Imdel), count(distinct Imdel)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Imdel ) s)
from INVMSTP
union all
select 'Imsys' , min(Imsys) , max(Imsys), count(distinct Imsys)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Imsys ) s)
from INVMSTP
union all
select 'Imbinl' , min(Imbinl) , max(Imbinl), count(distinct Imbinl)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Imbinl ) s)
from INVMSTP
union all
select 'Imdree' , min(Imdree) , max(Imdree), count(distinct Imdree)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Imdree ) s)
from INVMSTP
union all
select 'Immrch' , min(Immrch) , max(Immrch), count(distinct Immrch)
, (select avg(c)
from (select count(*) as c from INVMSTP group by Immrch ) s)
from INVMSTP
union all /* get COUNT(*) from file; no group */
select cast(null as char), cast(null as char), cast(null as char)
, count(*), cast(null as int)
from INVMSTP








Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact