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
midrange.com
