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

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

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

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

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

Ниже приведены данные для основных таблиц из реальной базы данных. Размер базы 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