Анализ использования полей в базе данных

Функция GetDatabaseStat (исходный код приведен ниже) позволяет произвести анализ использования полей в заданной таблице на конкретной базе данных. На вход функции передается имя таблицы, на выходе имеем файл в формате HTML.

По умолчанию, файл создается в каталоге c:/temp и имеет имя result.html. Изменив значение локальной переменной FileName можно указать другое место размещения файла и его имя.

Ниже приведено описание колонок таблицы:

Field name -- имя поля;
IC -- Количество индексов по данному полю. Следует помнить, что создавать индекс имеет смысл, если колонка содержит множество уникальных значений;
Distinct count -- Количество уникальных значений;
NULL count -- Количество значений NULL;
NULL % -- Процент пустых значений от общего количества записей. Неиспользуемые поля только засоряют базу данных;
Most frequent value -- Значение, которое встречается чаще других;
Most frequent count -- Количество записей с наиболее часто встречающимся значением;
% -- Процент записей с наиболее часто встречающимся значением от общего количества непустых записей.

Ниже приведены данные для основных таблиц из реальной базы данных. Размер базы 2.5 Гб.

GD_GOOD

Record count: 567857

Field name	IC	Distinct count	NULL count	NULL %	Most frequent value	Most frequent count	%
ACHAG	0	5	0	0	-4097	503959	89
AFULL	0	5	0	0	-4097	503959	89
ALIAS	0	907	555325	98	8708999800	830	7
AVIEW	0	5	0	0	-4097	503959	89
BARCODE	0	0	567857	100		0	0
DESCRIPTION	0	1	567856	100	1	1	100
DISABLED	0	1	348	0	0	567509	100
DISCIPLINE	0	2	558558	98	F	9298	100
EDITIONDATE	0	29930	109299	19	17.11.1858	424550	93
EDITORKEY	1	37	533840	94	148520664	9343	27
GROUPKEY	1	23	0	0	147000008	539653	95
ID	1	567857	0	0	147008659	1	0
ISASSEMBLY	0	2	567851	100	0	3	50
NAME	1	567799	0	0	Административно-производственный корпус	4	0
RESERVED	0	2	29374	5	0	522079	97
SHORTNAME	0	22	567629	100	клей для стекол	206	90
TNVDKEY	1	2	567855	100	151570952	1	50
USR$COSTPOST	0	58426	96131	17	572.46	1092	0
USR$CUSTOMNAME	0	10059	537988	95	Болт	774	3
USR$DANGER	0	2	567052	100	0	803	100
USR$DEMNAME	1	39269	7839	1	ABDECKUNG	24992	4
USR$DISCOUNTGROUPE	1	9	95509	17	160295983	334722	71
USR$DRT	0	2	564784	99	1	2273	74
USR$FACTORY	1	6	36298	6	147681262	269254	51
USR$INV_OLDCODE	1	227	563406	99	01	3380	76
USR$INV_PERCKEY	1	1	62990	11	152517521	504867	100
USR$INV_WEIGHT	0	7767	28455	5	0	98401	18
USR$LAGORT	0	347	559303	98	000	206	2
USR$LP	1	61823	7860	1	438.6	1559	0
USR$MINCOUNT	0	19	567209	100	10	241	37
USR$NOTINLP	0	2	9689	2	0	487736	87
USR$PLASTICPACK	1	3	42	0	2	567585	100
USR$RABATTGRUPPE	1	9	7655	1	147681264	452720	81
USR$REGAL	0	25	559340	99	01	1209	14
USR$RUSNAME	0	9780	537686	95	Болт	810	3
USR$SEARCH	1	564238	0	0	Ключ комбинированный	44	0
USR$SORT	1	565475	0	0	Ключ комбинированный	44	0
USR$SPARTE	1	8	16923	3	147682002	251855	46
USR$STAND	0	36	559125	98	10	4389	50
USR$TNVED_POL	0	3	567854	100	8415	1	33
USR$VP1	0	0	567857	100		0	0
USR$VP2	0	0	567857	100		0	0
USR$WC_DURABILITY	0	0	567857	100		0	0
VALUEKEY	1	47	0	0	152125191	563752	99

INV_CARD

Record count: 889748

Field name	IC	Distinct count	NULL count	NULL %	Most frequent value	Most frequent count	%
COMPANYKEY	1	1	0	0	147000007	889748	100
DOCUMENTKEY	1	826644	0	0	159754444	17	0
FIRSTDATE	0	1895	0	0	31.08.2000	49208	6
FIRSTDOCUMENTKEY	1	138768	0	0	150739700	1728	0
GOODKEY	2	37502	0	0	147012725	3264	0
ID	3	889748	0	0	148135910	1	0
PARENT	1	567064	160576	18	151402497	922	0
RESERVED	0	1	889352	100	99	396	100
USR$CHANGEGOOD	1	2115	859222	97	148544677	338	1
USR$CLAIMKEY	1	22659	184005	21	164916160	7189	1
USR$CODELIST	1	4	844779	95	148507884	27580	61
USR$CONTRACTPRICE	0	12915	276567	31	0.29	3541	1
USR$COUNTRYKEY	1	59	783393	88	158702652	77070	72
USR$CURRATE	0	472	374443	42	0	17328	3
USR$CUSTOM100	0	54714	235963	27	0	112549	17
USR$CUSTOM110	0	26296	235963	27	0	366312	56
USR$CUSTOM200	0	59063	235920	27	0	155494	24
USR$CUSTOM680	0	309	382079	43	0	502444	99
USR$CUSTOMERKEY	1	7064	56799	6	148561340	508565	61
USR$DANGER	0	6	374620	42	0	515111	100
USR$DIL_PRICE	0	4142	851692	96	0.42	177	0
USR$EN_ORDERKEY	0	6	629664	71	154412503	217764	84
USR$FA_AMORTTERM	0	63	871846	98	10	4387	25
USR$FA_COMPLECTKEY	2	0	889748	100		0	0
USR$FA_CORRECTCOEFF	0	3	877159	99	1	12579	100
USR$FA_DESTINATIONKEY	1	2	877161	99	152120887	12484	99
USR$FA_GROUPKEY	1	67	877161	99	152341679	3325	26
USR$FA_INVCARDKEY	1	563	877189	99	152123743	51	0
USR$FA_METHODKEY	1	3	877161	99	152115147	12349	98
USR$FA_OVERWEAR	0	1	877159	99	0	12589	100
USR$FA_OWNINGKEY	1	2	877161	99	152120894	12585	100
USR$FA_PRICENCU	0	1006	877159	99	62930	380	3
USR$FA_REPAIRPERC	0	26	877159	99	10	4721	38
USR$FA_RESOURCE	0	1	889671	100	0	77	100
USR$FA_TYPEKEY	1	3	877161	99	152120891	11982	95
USR$FA_USAGEKEY	1	2	877161	99	152120896	12478	99
USR$FA_WEARACCKEY	1	5	871846	98	156279675	9339	52
USR$FA_WEARCREDITACC	1	3	871846	98	300201	12006	67
USR$FA_WEARSUMNCU	0	13971	871844	98	0	654	4
USR$INSURANCE	0	418	374617	42	0	450009	87
USR$INVOICECLKEY	1	36032	747861	84	162064588	132	0
USR$INVOICEKEY	1	1417	270250	30	150088780	49379	8
USR$INV_ACCOUNTKEY	1	31	789846	89	156279715	62839	63
USR$INV_ADDLINEKEY	1	97388	289869	33	150739700	1728	0
USR$INV_ADDNDS	0	5	174726	20	20	355133	50
USR$INV_BILLLINEKEY	1	10474	870816	98	165317742	19	0
USR$INV_BUYNDSNCU	0	64392	329324	37	0	24773	4
USR$INV_CERTKEY	1	0	889748	100		0	0
USR$INV_COSTACCNCU	0	58960	210726	24	0	19728	3
USR$INV_COSTBUYNCU	0	58441	222872	25	0	19219	3
USR$INV_COSTFULLNCU	0	26466	513852	58	2360	8469	2
USR$INV_COSTGLASSNCU	0	1	889746	100	4.56	2	100
USR$INV_COSTNCU	0	57614	652537	73	950	1585	1
USR$INV_COSTNDSNCU	0	23941	780753	88	190	1136	1
USR$INV_COSTOILTAX	0	0	889748	100		0	0
USR$INV_COSTOPTNCU	0	30815	233687	26	0	35586	5
USR$INV_COSTPROVIDER	0	174	886634	100	3205	1728	55
USR$INV_COSTRETAILNCU	1	13797	218510	25	0	36258	5
USR$INV_COSTSALETAX	0	0	889748	100		0	0
USR$INV_COSTTRADENCU	0	14626	248125	28	0	36427	6
USR$INV_COSTTRWITHNDS	0	14795	218714	25	0	36431	5
USR$INV_COSTWITHNDSNCU	0	26710	662487	74	2248	3183	1
USR$INV_DATERECEIVE	1	1354	277482	31	31.08.2000	49591	8
USR$INV_MOVEDOCKEY	1	3673	884371	99	157157111	70	1
USR$INV_OPTDELIVERYKEY	1	0	889748	100		0	0
USR$INV_OPTPERC	0	46	883865	99	0	5459	93
USR$INV_PACKKEY	1	0	889748	100		0	0
USR$INV_PERCPROVIDER	0	29	889099	100	11.1111	326	50
USR$INV_PERCSELL	0	46562	757592	85	29.98	2418	2
USR$INV_PROVIDER	1	418	164510	18	148661301	490445	68
USR$INV_QUALITYKEY	1	0	889748	100		0	0
USR$INV_QUANTINPACK	0	0	889748	100		0	0
USR$INV_SELLNDS	0	4	194996	22	18	377491	54
USR$INV_TAXSALE	0	3	200258	23	5	678538	98
USR$INV_TRADEPERC	0	5329	218716	25	29.98	290293	43
USR$KULANS	0	0	889748	100		0	0
USR$NOTFORSALE	0	2	888834	100	0	734	80
USR$NUMPLACE	0	408	570708	64	000	13664	4
USR$ORDERKEY	1	5141	307721	35	164918679	6932	1
USR$ORDERSEQ	1	239	307868	35	1	20913	4
USR$PRICEAIR	0	1427	330473	37	0	506177	91
USR$PRICECLAIM	0	7336	678693	76	0	5214	2
USR$PRICECURR	0	19902	272504	31	0	20560	3
USR$PRICEPACK	0	177	374620	42	0	509861	99
USR$PRICETOAVIA	0	311	374620	42	0	489487	95
USR$PRICE_DC	0	15	889729	100	137421.4618	3	16
USR$REGAL	0	36	569496	64	06	44217	14
USR$REPORDERKEY	2	22223	678207	76	160113774	864	0
USR$SALEPRICECURR	0	1154	885558	100	0.84	89	2
USR$STAND	0	48	562969	63	10	170565	52
USR$TNVDKEY	1	0	889748	100		0	0
USR$TOREPAIRKEY	1	24252	732542	82	157424005	250	0
USR$WC_ACCOUNTINGMETHOD	0	2	884433	99	4	4626	87
USR$WC_CARDNUM	0	2	884436	99	б/н	2838	53
USR$WC_DISTRIBUTEDATE	0	31	884433	99	31.03.2004	1086	20
USR$WC_STARTUPDOCKEY	1	992	884433	99	159669676	65	1

GD_DOCUMENT

Record count: 1674651

Field name	IC	Distinct count	NULL count	NULL %	Most frequent value	Most frequent count	%
ACHAG	0	33	0	0	-4097	501434	30
AFULL	0	33	0	0	-4097	501434	30
AVIEW	0	26	0	0	-4097	565354	34
COMPANYKEY	1	1	0	0	147000007	1674651	100
CREATIONDATE	0	1081716	0	0	10.09.2004 12:20:24	5692	0
CREATORKEY	1	53	0	0	650002	449066	27
CURRKEY	1	4	1608262	96	200010	66331	100
DELAYED	0	2	1521417	91	0	147204	96
DESCRIPTION	0	1400	1667425	100	Сумма налога	220	3
DISABLED	0	2	1086	0	0	1673545	100
DOCUMENTDATE	2	2196	0	0	19.04.2003	8969	1
DOCUMENTTYPEKEY	4	81	0	0	152397963	205558	12
EDITIONDATE	0	859029	0	0	02.10.2003 14:59:01	2456	0
EDITORKEY	1	52	0	0	650002	455851	27
ID	2	1674651	0	0	148135892	1	0
NUMBER	1	217877	0	0		63885	4
PARENT	2	309473	433747	26	153038538	8684	1
PRINTDATE	0	1606	1612536	96	14.03.2005	324	1
RESERVED	0	0	1674651	100		0	0
SUMCURR	0	0	1674651	100		0	0
SUMEQ	0	1	343035	20	0	1331616	100
SUMNCU	0	1073	1669563	100	0	404	8
TRANSACTIONKEY	1	100	1324612	79	152427288	88768	25
TRTYPEKEY	1	0	1674651	100		0	0

AC_RECORD

Record count: 347704

Field name	IC	Distinct count	NULL count	NULL %	Most frequent value	Most frequent count	%
ACHAG	0	20	0	0	-1	197172	57
AFULL	0	20	0	0	-1	197172	57
AVIEW	0	15	0	0	-1	205147	59
COMPANYKEY	1	1	0	0	147000007	347704	100
CREDITCURR	0	1653	0	0	0	345573	99
CREDITNCU	0	101429	0	0	18000	1514	0
DEBITCURR	0	1795	0	0	0	345360	99
DEBITNCU	0	101477	0	0	18000	1514	0
DELAYED	0	1	0	0	0	347704	100
DESCRIPTION	0	108288	69639	20	Работы по ремонтному заказу	15040	5
DISABLED	0	1	0	0	0	347704	100
DOCUMENTKEY	2	252943	0	0	167527882	9	0
ID	2	347704	0	0	156297555	1	0
INCORRECT	0	2	0	0	0	346019	100
MASTERDOCKEY	1	131375	0	0	157030874	886	0
RECORDDATE	1	1090	0	0	30.04.2004	2555	1
RESERVED	0	0	347704	100		0	0
TRANSACTIONKEY	1	99	0	0	152531062	55298	16
TRRECORDKEY	1	87	0	0	156356257	55296	16

INV_MOVEMENT

Record count: 1802716

Field name	IC	Distinct count	NULL count	NULL %	Most frequent value	Most frequent count	%
CARDKEY	2	839215	0	0	151402497	923	0
CONTACTKEY	5	11969	0	0	148477348	524784	29
CREDIT	0	1301	0	0	0	900100	50
DEBIT	0	1298	0	0	0	903039	50
DISABLED	0	2	0	0	0	1802686	100
DOCUMENTKEY	1	887573	0	0	159754444	34	0
GOODKEY	2	33808	0	0	147012725	7440	0
ID	1	1802716	0	0	148135913	1	0
MOVEMENTDATE	4	2178	0	0	31.08.2002	16848	1
MOVEMENTKEY	2	910104	0	0	148135911	2	0
RESERVED	0	0	1802716	100		0	0

GetDatabaseStat

option explicit

' Входящий параметр ATableName строковый!

sub GetDatabaseStat(ATableName)

  ' изменить значение переменной, если
  ' Вы хотите, чтобы результат выполнения
  ' функции располагался в другом месте
  Dim FileName
  FileName = "c:\temp\result.html"

  ATableName = UCase(ATableName)

  Dim Fields
  call gdcBaseManager.ExecSingleQueryResult(_
    "SELECT f.rdb$field_name FROM rdb$relation_fields f " &_
    "  JOIN rdb$fields d ON d.rdb$field_name = f.rdb$field_source " &_
    "WHERE f.rdb$relation_name = :RN AND d.rdb$computed_blr IS NULL AND d.rdb$segment_length IS NULL " &_
    "ORDER BY 1",_
    ATableName, Fields, nil)
    
  if IsEmpty(Fields) then exit sub
     
  Dim Field, Stat, FieldStat, Res, TotalCount
  call gdcBaseManager.ExecSingleQueryResult(_
    "SELECT COUNT(*) FROM " & ATableName, 0, Res, nil)
  TotalCount = Res(0, 0)
  Set Stat = CreateObject("Scripting.Dictionary")
  For Each Field in Fields
    Set FieldStat = CreateObject("Scripting.Dictionary")
   
    call gdcBaseManager.ExecSingleQueryResult(_
      "SELECT COUNT(s.rdb$index_name) FROM rdb$index_segments s " &_
      "  JOIN rdb$indices i ON i.rdb$index_name = s.rdb$index_name " &_
      "WHERE s.rdb$field_name = :FN AND i.rdb$relation_name = :RN", _
      Array(Field, ATableName), Res, nil)
    FieldStat.Add "IC", Res(0, 0)

    call gdcBaseManager.ExecSingleQueryResult(_
      "SELECT COUNT(DISTINCT " & Field & ") FROM " & ATableName,_
      0, Res, nil)
    FieldStat.Add "DC", Res(0, 0)
     
    call gdcBaseManager.ExecSingleQueryResult(_
      "SELECT COUNT(*) FROM " & ATableName & " WHERE " & Field & " IS NULL ",_
      0, Res, nil)
    FieldStat.Add "NC", Res(0, 0)
    FieldStat.Add "NP", CInt(Res(0, 0) / TotalCount * 100)

    call gdcBaseManager.ExecSingleQueryResult(_
      "SELECT FIRST 1 " & Field & ", COUNT(" & Field & ") FROM " & ATableName &_
      " GROUP BY " & Field & " ORDER BY 2 DESC ",_
      0, Res, nil)
    FieldStat.Add "FV", Res(0, 0)
    FieldStat.Add "FC", Res(1, 0)
    if Res(1, 0) = 0 then
      FieldStat.Add "FP", 0
    else
      FieldStat.Add "FP", CInt(Res(1, 0) / (TotalCount - FieldStat.Item("NC")) * 100)
    end if

    Stat.Add Field, FieldStat
  Next
   
  Dim fso, MyFile
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set MyFile= fso.CreateTextFile(FileName, True)
  MyFile.WriteLine("<html><head></head><body>")
  MyFile.WriteLine("<h1>" & ATableName & "</h1>")
  MyFile.WriteLine("Record count: " & TotalCount & "<p/>")
  MyFile.WriteLine("<table border=""1""><tr><td>Field name</td><td>IC</td><td>Distinct count</td><td>NULL count</td><td>NULL %</td><td>Most frequent value</td><td>Most frequent count</td><td>%</td></tr>")

  Dim S, F, R, I
  For Each F In Stat
    S = "<tr><td>" & F & "</td>"
    Set I = Stat.Item(F)
    For Each R In I
      S = S & "<td>" & I.Item(R) & "</td>"
    Next
    S = S & "</tr>"
    MyFile.WriteLine(S)
  Next
  MyFile.WriteLine("</table></body></html>")

  MyFile.Close

end sub

06.11.2006