Any ideas on shrinking tables?

[ErikZ]

I've been handed a database 90% of the size is in one table. This really bothers me because there are only 700 records in it!

It averages 25k per row, looking though the table most of the fields are blank. I found a field "Supervisor" that I could normalize, but it made no noticeable difference.

Exporting the table as a CSV file gives me a 230k file!

Deleting the table knocks down a 19.3 Meg file to a 1.8 Meg file.

Any ideas?

 

[JamesMeadlock]

Strange. Have you tried compacting it? Does it have many indexes, some of which you could remove? Does it have any ole object fields?

 

[Maquis]

You said most of the fields are blank. Perhaps you can put those fields in their own tables which would then relate back to the original table with a primary/foreign key link.

That way you don't have to even create a record in the new tables unless there is data in the field. Maq B-)
<insert witty signature here>

 

[MichaelRed]

Normalization, normalization, and more normalization.

Look at the fields which are largely empty. These -in particular- should be normalized out of the &quot;main&quot; table. I would caution you that seperating the Chaff from the wheat -while retaining the connection- will complicate the processing, but it should (based on your statement) offer a dramatic shrinkage of the overall db.


Beyond that, look at the field sizes and the actual (Max) field value width (for text fields).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ErikZ]

Wow, thanks for the quick responses guys!

This was made by an intern, so the Database design is poor. The table I'm talking about has 70 fields. I simply don't have the time to figure out what all the blank fields are and trying to normalize them. Especially since there's a large block of code behind the whole thing to push the content onto the web.

Yes I've tried to compact the database.

There are only four indexes

Hmm, there's only one OLE object field....wait a minute...it contains photos?

I think I've found the problem. The photo field doesn't contain links to pictures; it actually HAS the pictures.

Yes, deleting that field greatly reduced the size of the database. I'll have to see about changing this.

Thanks for pushing me in the right direction!

 

[JamesMeadlock]

Welcome. OLE pictures are horrible in jet. I believe it stores not only the image, but an uncompressed bitmap of the image. If you need to store them, you can... either store the path to the file(s) or use a blob, and write code to handle it... sample from microsoft for blob handling:

Class module blobhandler:
Option Compare Database
Option Explicit

' This object handles storing files in a blob field in a table
'
' Properties:
' TableAndFieldNames (R/W)
' PicFilePath (R)
'
' Methods:
' DeleteBlob
' ExportFile(fName)
' MakeTempFile()
' ImportFile(fName)

' Private Functions:
' GetFileName(bpTableName, bpRecordID)
'

Private Const MAX_PATH As Integer = 255
Private Const AcceptedFileTypes As String = &quot;.jpg;.gif;.bmp&quot;

Private bpTableName As String
Private bpTableBlobFieldName As String
Private bpTableKeyFieldName As String
Private bpTableFileTypeName As String

Private Declare Function apiGetTempDir Lib &quot;Kernel32&quot; _
Alias &quot;GetTempPathA&quot; (ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long

Public Property Let TableAndFieldNames(TableName As String, TableBlobFieldName As String, TableKeyFieldName As String, TableFileTypeName As String)
bpTableName = TableName
bpTableBlobFieldName = TableBlobFieldName
bpTableKeyFieldName = TableKeyFieldName
bpTableFileTypeName = TableFileTypeName
End Property


Public Sub DeleteBlob(RecordID As Long)
' Deletes the blob record
' and the temp file *WTD
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

If IsNull(DLookup(&quot;[&quot; & bpTableKeyFieldName & &quot;]&quot;, bpTableName, &quot;[&quot; & bpTableKeyFieldName & &quot;]=&quot; & RecordID)) Then
MsgBox (&quot;No Picture to Delete.&quot
Exit Sub
End If

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
Set cn = CurrentProject.Connection
rs.Delete
rs.Update

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Public Sub bpSaveBlob(RecordID As Long, FName As String)
' Saves the blob to a file
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mySql As String
Dim BytesWritten As Long

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

mySql = &quot;Select * from &quot; & bpTableName & &quot; Where &quot; & bpTableKeyFieldName & &quot;=&quot; & bpRecordID & &quot;;&quot;
rs.Open mySql, cn, adOpenDynamic, adLockPessimistic

BytesWritten = WriteBLOB(rs, bpTableBlobFieldName, FName)

End Sub

Public Sub bpLoadPic(picToLoad As String, RecordID As String)
' Loads a file into a blob field
Dim fExtension As String

On Error GoTo HandleErr
fExtension = Mid(picToLoad, Len(picToLoad) - 4, 4)

If Not fIsFileDIR(picToLoad) Then
MsgBox (&quot;File Not Found: &quot; & picToLoad & &quot;. File not imported.&quot
Exit Sub
End If

Select Case fExtension
Case &quot;.jpg&quot;, &quot;.bmp&quot;, &quot;.gif&quot;
bpFileType = fExtension
Case Else
MsgBox (&quot;Unrecognized file type: &quot; & fExtension & &quot;. File not imported.&quot
Exit Sub
End Select

'
' We have a valid filetype
'
If IsNull(DLookup(&quot;[&quot; & bpTableKeyFieldName & &quot;]&quot;, bpTableName, &quot;[&quot; & bpTableKeyFieldName & &quot;]=&quot; & RecordID)) Then
' Make a new record
AddRec bpTableName, bpTableKeyFieldName, RecordID, picToLoad
Else
' Modify existing record
ModifyRec bpTableName, bpTableBlobFieldName, bpTableKeyFieldName, RecordID, picToLoad
End If

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox &quot;Error &quot; & Err.Number & &quot;: &quot; & Err.Description, vbCritical, &quot;bpHandler.bpLoadPic&quot; 'ErrorHandler:$$N=bpHandler.bpLoadPic
End Select

End Sub

Private Sub AddRec(tTableName, tTableBlobFieldName, tTableKeyFieldName, tRecordID, tpicToLoad)
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
If Not IsPicOk(tpicToLoad) Then
MsgBox (&quot;Unrecognized file: &quot; & tpicToLoad & &quot;Record not Added.&quot
Exit Sub
End If
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
Set cn = CurrentProject.Connection
rs.Open tTableName, cn, adOpenDynamic, adLockPessimistic
rs.AddNew
rs(tTableBlobFieldName) = FileToBlob(tpicToLoad)
rs(tTableKeyFieldName) = tRecordID
rs(tTableFileTypeName) = ExtensionOfFile(tpicToLoad)
rs.Update
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Private Sub ModifyRec(tTableName, tTableBlobFieldName, tTableKeyFieldName, tRecordID, tpicToLoad)
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim fld As ADODB.Field
Dim mySql As String
If Not IsPicOk(tpicToLoad) Then
MsgBox (&quot;Unrecognized file: &quot; & tpicToLoad & &quot;Record not Added.&quot
Exit Sub

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
Set cn = CurrentProject.Connection
mySql = &quot;Select * From &quot; & tTableName & &quot; Where &quot; & tTableKeyFieldName & &quot;=&quot; & tRecordID & &quot;;&quot;
rs.Open mySql, cn, adOpenDynamic, adLockPessimistic
rs(tTableBlobFieldName) = FileToBlob(tpicToLoad)
rs(tTableKeyFieldName) = tRecordID
rs(tTableFileTypeName) = ExtensionOfFile(tpicToLoad)
rs.Update
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub


Private Function fReturnTempDir() As String
'Returns Temp Folder Name
Dim strTempDir As String
Dim lngx As Long

strTempDir = String$(MAX_PATH, 0)
lngx = apiGetTempDir(MAX_PATH, strTempDir)
If lngx <> 0 Then
fReturnTempDir = Left$(strTempDir, lngx)
Else
fReturnTempDir = &quot;c:\&quot;
End If
End Function

Private Function fIsFileDIR(stPath As String, Optional lngType As Long) As Integer
'Fully qualify stPath
'To check for a file
' ?fIsFileDIR(&quot;c:\winnt\win.ini&quot
'To check for a Dir
' ?fIsFileDir(&quot;c:\msoffice&quot;,vbdirectory)
'
On Error Resume Next
fIsFileDIR = Len(Dir(stPath, lngType)) > 0
End Function

Private Sub BlobToFile(tPicTableName As String, tPicIDName As String, tPicBlobFieldName As String, tPicID As Long)
'
'Input: TableName, KeyFieldName, BlobFieldName, KeyFieldValue
'Action: Create the .jpg for the ItemID from the blob
Dim numBytes As Long

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim mySql As String

mySql = &quot;SELECT &quot; & tPicBlobFieldName & &quot; FROM &quot; & tPicTableName & &quot; WHERE &quot; & tPicIDName & &quot;=&quot; & tPicID & &quot;;&quot;
Set cn = CurrentProject.Connection
rs.Open mySql, cn, adOpenStatic, adLockReadOnly
numBytes = WriteBLOB(rs, &quot;ItemPicture&quot;, PicFilePath)
rs.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Function WriteBLOB(T As ADODB.Recordset, sField As String, Destination As String)
' Copied from msdn, modified to use ado
Dim NumBlocks As Integer, DestFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
On Error GoTo Err_WriteBLOB

' Get the size of the field.
FileLength = T(sField).ActualSize
If FileLength = 0 Then
WriteBLOB = 0
Exit Function
End If
' Calculate number of blocks to write and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' Remove any existing destination file.
DestFile = FreeFile
Open Destination For Output As DestFile
Close DestFile
' Open the destination file.
Open Destination For Binary As DestFile
' SysCmd is used to manipulate the status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, _
&quot;Writing BLOB&quot;, FileLength / 1000)
' Write the leftover data to the output file.
FileData = T(sField).GetChunk(LeftOver)
Put DestFile, , FileData
' Update the status bar meter.
RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
' Write the remaining blocks of data to the output file.
For i = 1 To NumBlocks
' Reads a chunk and writes it to output file.
FileData = T(sField).GetChunk(BlockSize)
Put DestFile, , FileData

RetVal = SysCmd(acSysCmdUpdateMeter, _
((i - 1) * BlockSize + LeftOver) / 1000)
Next i
' Terminates function
RetVal = SysCmd(acSysCmdRemoveMeter)
Close DestFile
WriteBLOB = FileLength
Exit Function
Err_WriteBLOB:
WriteBLOB = -Err
Exit Function
End Function

Function ReadBLOB(Source As String, T As ADODB.Recordset, sField As String)
' Copied from msdn, modified to use ado
Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
On Error GoTo Err_ReadBLOB
' Open the source file.
SourceFile = FreeFile
Open Source For Binary Access Read As SourceFile
' Get the length of the file.
FileLength = LOF(SourceFile)
If FileLength = 0 Then
ReadBLOB = 0
Exit Function
End If
' Calculate the number of blocks to read and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' SysCmd is used to manipulate status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, &quot;Reading BLOB&quot;, _
FileLength \ 1000) ' Put first record in edit mode.
' Read the leftover data, writing it to the table.
FileData = String$(LeftOver, 32)
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
' Read the remaining blocks of data, writing them to the table.
FileData = String$(BlockSize, 32)
For i = 1 To NumBlocks
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter, BlockSize * i / 1000)
Next i ' Update the record and terminate function.
T.Update
RetVal = SysCmd(acSysCmdRemoveMeter)
Close SourceFile
ReadBLOB = FileLength
Exit Function
Err_ReadBLOB:
ReadBLOB = -Err
MsgBox &quot;ReadBLOB Error &quot; & Err & &quot; : &quot; & Error$
Exit Function
End Function


Public Property Get PicInFile() As Boolean
' Check to see if this pic has already been written to a file
If fIsFileDIR(PicFilePath) Then
PicInFile = True
Else
PicInFile = False
End If
End Property

Private Property Get PicInTable() As Boolean
' Check to see if this pic's blob field exists
If IsNull(DLookup(PicBlobName, PicTableName, &quot;[&quot; & PicIDName & &quot;]=&quot; & PicID)) Then
PicInTable = False
Else
PicInTable = True
End Property

Private Property Get PicPath() As String
If Not PicInTable Then
' We have no picture, so return empty string
PicPath = &quot;&quot;
End Property
End If
If Not PicInFile Then
' We need to export the blob to the file
BlobToFile PicTableName, PicIDName, PicBlobFieldName, PicID
End Property
End If
' We have already exported the pic at some time
PicPath = PicFilePath
End Property

Private Function ExtensionOfFile(FName As String) As String
ExtensionOfFile = Mid(FName, 1, Len(FName) - 4)
End Function

Private Function IsPicOk(tPicPath As String) As Boolean
Dim Counter As Integer

If IsNull(tPicPath) Or _
(Mid(tPicPath, Len(tPicPath) - 3) <> &quot;.&quot Or _
(Len(tPicPath) < 5) Then
IsPicOk = False
Else


For Counter = 1 To Len(AcceptedFileTypes) Mod 4
If ExtensionOfFile(tPicPath) = Mid(tPicPath, Counter, 4) Then IsPicOk = True
Next Counter
End If
End Function

Private Function PicFilePath(RecordID As Long) As String
PicFilePath = fReturnTempDir() & &quot;tmp&quot; & bpTableName & RecordID & FileType
End Function


module basBlob:
Option Compare Database
Option Explicit
' The procedures in this module are copied from the Microsoft Access Knowledge Base,
' article Q103257, which was obtained 3/26/1999 from the following web address:
'

http://support.microsoft.com/support/kb/articles/q103/2/57.asp

-- that is why
' they do not follow the Reddick naming conventions used elsewhere in this example
' database

Const BlockSize = 32768

'**************************************************************
' FUNCTION: ReadBLOB() ' ' PURPOSE:
' Reads a BLOB from a disk file and stores the contents in the
' specified table and field. ' ' PREREQUISITES:
' The specified table with the OLE object field to contain the
' binary data must be opened in Visual Basic code (Access Basic
' code in Microsoft Access 2.0 and earlier) and the correct record
' navigated to prior to calling the ReadBLOB() function. '
' ARGUMENTS:
' Source - The path and filename of the binary information
' to be read and stored.
' T - The table object to store the data in.
' Field - The OLE object field in table T to store the data in. '
' RETURN: ' The number of bytes read from the Source file.
'**************************************************************
Function ReadBLOB(Source As String, T As Recordset, sField As String)
Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
' On Error GoTo Err_ReadBLOB
' Open the source file.
SourceFile = FreeFile
Open Source For Binary Access Read As SourceFile
' Get the length of the file.
FileLength = LOF(SourceFile)
If FileLength = 0 Then
ReadBLOB = 0
Exit Function
End If
' Calculate the number of blocks to read and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' SysCmd is used to manipulate status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, &quot;Reading BLOB&quot;, _
FileLength \ 1000) ' Put first record in edit mode.
' T.MoveFirst
' T.Edit
T.AddNew
' Read the leftover data, writing it to the table.
FileData = String$(LeftOver, 32)
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
' Read the remaining blocks of data, writing them to the table.
FileData = String$(BlockSize, 32)
For i = 1 To NumBlocks
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
RetVal = SysCmd(acSysCmdUpdateMeter, BlockSize * i / 1000)
Next i ' Update the record and terminate function.
T.Update
RetVal = SysCmd(acSysCmdRemoveMeter)
Close SourceFile
ReadBLOB = FileLength
Exit Function
Err_ReadBLOB:
ReadBLOB = -Err
MsgBox &quot;ReadBLOB Error &quot; & Err & &quot; : &quot; & Error$
Exit Function
End Function
'**************************************************************
' FUNCTION: WriteBLOB() ' ' PURPOSE:
' Writes BLOB information stored in the specified table and field
' to the specified disk file. ' ' PREREQUISITES:
' The specified table with the OLE object field containing the
' binary data must be opened in Visual Basic code (Access Basic
' code in Microsoft Access 2.0 or earlier) and the correct
' record navigated to prior to calling the WriteBLOB() function. '
' ARGUMENTS:
' T - The table object containing the binary information.
' sField - The OLE object field in table T containing the
' binary information to write.
' Destination - The path and filename to write the binary
' information to. ' '
' RETURN:
' The number of bytes written to the destination file.
'**************************************************************
Function WriteBLOB(T As Recordset, sField As String, _
Destination As String)
Dim NumBlocks As Integer, DestFile As Integer, i As Integer
Dim FileLength As Long, LeftOver As Long
Dim FileData As String
Dim RetVal As Variant
On Error GoTo Err_WriteBLOB ' Get the size of the field.
FileLength = T(sField).FieldSize()
If FileLength = 0 Then
WriteBLOB = 0
Exit Function
End If
' Calculate number of blocks to write and leftover bytes.
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
' Remove any existing destination file.
DestFile = FreeFile
Open Destination For Output As DestFile
Close DestFile
' Open the destination file.
Open Destination For Binary As DestFile
' SysCmd is used to manipulate the status bar meter.
RetVal = SysCmd(acSysCmdInitMeter, _
&quot;Writing BLOB&quot;, FileLength / 1000)
' Write the leftover data to the output file.
FileData = T(sField).GetChunk(0, LeftOver)
Put DestFile, , FileData ' Update the status bar meter.
RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
' Write the remaining blocks of data to the output file.
For i = 1 To NumBlocks
' Reads a chunk and writes it to output file.
FileData = T(sField).GetChunk((i - 1) * BlockSize _
+ LeftOver, BlockSize)
Put DestFile, , FileData
RetVal = SysCmd(acSysCmdUpdateMeter, _
((i - 1) * BlockSize + LeftOver) / 1000)
Next i
' Terminates function RetVal = SysCmd(acSysCmdRemoveMeter)
RetVal = SysCmd(acSysCmdRemoveMeter)
Close DestFile
WriteBLOB = FileLength
Exit Function
Err_WriteBLOB:
WriteBLOB = -Err
MsgBox &quot;WriteBLOB Error &quot; & Err & &quot; : &quot; & Error$
Exit Function
End Function
'**************************************************************
' SUB: CopyFile ' ' PURPOSE:
' Demonstrates how to use ReadBLOB() and WriteBLOB(). '
' PREREQUISITES:
' A table called BLOB that contains an OLE Object field called
' Blob. ' ' ARGUMENTS:
' Source - The path and filename of the information to copy.
' Destination - The path and filename of the file to write
' the binary information to. ' ' EXAMPLE:
' CopyFile &quot;c:\windows\winfile.hlp&quot;, &quot;c:\windows\winfil_1.hlp&quot;
'**************************************************************
Sub CopyFile(Source As String, Destination As String)
Dim BytesRead As Variant, BytesWritten As Variant
Dim msg As String
Dim db As Database
Dim T As Recordset ' Open the BLOB table.
Set db = CurrentDb()
Set T = db.OpenRecordset(&quot;BLOB&quot;, dbOpenTable)
' Create a new record and move to it. T.AddNew
T.Update
T.MoveLast
BytesRead = ReadBLOB(Source, T, &quot;Blob&quot
msg = &quot;Finished reading &quot;&quot;&quot; & Source & &quot;&quot;&quot;&quot;
msg = msg & Chr$(13) & &quot;.. &quot; & BytesRead & &quot; bytes read.&quot;
MsgBox msg, 64, &quot;Copy File&quot;
BytesWritten = WriteBLOB(T, &quot;Blob&quot;, Destination)
msg = &quot;Finished writing &quot;&quot;&quot; & Destination & &quot;&quot;&quot;&quot;
msg = msg & Chr$(13) & &quot;.. &quot; & BytesWritten & &quot; bytes written.&quot;
MsgBox msg, 64, &quot;Copy File&quot;

End Sub