I have 6 very large and horribly fragmented database files (MDB and MDF).
They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger ones).
There are up to 19 thousand fragments in them. I've just learned that, even
though the HD is only 79% full, I cannot defrag these. I would have to
somehow get 2.81 GB of CONTIGUOUS free space to do the largest. Looking at
the map in defrag, I doubt there is a single contiguous area of free space
larger than a half GB.

Before going to the expense and effort of buying another HD, installing it,
and copying these files, I want to ask your opinion and experience with the
performance issues involved. Is such a horribly fragmented MDB as this,
scattered back and forth across a 40 GB drive, likely to see much
performance improvement if defragged?

I ran into a client who wanted performance. I decreased the storage of most
of the columns of data, reducing the size by 70% and more, then added an
index. I got about a 10:1 performance improvement in queries. Now I want
to ask the client to defrag what is a very large database (he's already
reused most of the 70% savings by adding more records). I had hoped to
defrag and test here, but that's being held up because I can't defrag.

Do you see much performance improvement with a defrag on a database that is
in such bad condition? Any idea how much? The index still leaves a table
scan of perhaps a million rows (rather than the total database of 10 million
rows, hence the 10:1 improvement).

I know, this is a crazy thing to do with Jet. I'm trying to get it switched
to SQL Express, but if I can illustrate an even greater improvement still
using Jet, my credibility goes up. That will then tend to help move things
toward the ultimate solution.

Tom Ellison

It would certainly seem that the less a hard drive has to work, the faster
the database; however, it's probably not going to make a big improvement. I'm
assuming that you have already done a compact and repair.

Is there a DVD burner on that computer? If so, move the databases to a DVD;
then defrag the hard drive; finally move the databases back. You could do the
same thing with an external hard drive. There's no guarantee that Windows
will put it all in the same area of the hard drive plus it might not take
long for things to defrayment again.

Which brings up another subject - how do they backup the database now?
Hopefully backups are being done very frequently.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tom Ellison" wrote:

> I have 6 very large and horribly fragmented database files (MDB and MDF).
> They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger ones).
> There are up to 19 thousand fragments in them. I've just learned that, even
> though the HD is only 79% full, I cannot defrag these. I would have to
> somehow get 2.81 GB of CONTIGUOUS free space to do the largest. Looking at
> the map in defrag, I doubt there is a single contiguous area of free space
> larger than a half GB.
>
> Before going to the expense and effort of buying another HD, installing it,
> and copying these files, I want to ask your opinion and experience with the
> performance issues involved. Is such a horribly fragmented MDB as this,
> scattered back and forth across a 40 GB drive, likely to see much
> performance improvement if defragged?
>
> I ran into a client who wanted performance. I decreased the storage of most
> of the columns of data, reducing the size by 70% and more, then added an
> index. I got about a 10:1 performance improvement in queries. Now I want
> to ask the client to defrag what is a very large database (he's already
> reused most of the 70% savings by adding more records). I had hoped to
> defrag and test here, but that's being held up because I can't defrag.
>
> Do you see much performance improvement with a defrag on a database that is
> in such bad condition? Any idea how much? The index still leaves a table
> scan of perhaps a million rows (rather than the total database of 10 million
> rows, hence the 10:1 improvement).
>
> I know, this is a crazy thing to do with Jet. I'm trying to get it switched
> to SQL Express, but if I can illustrate an even greater improvement still
> using Jet, my credibility goes up. That will then tend to help move things
> toward the ultimate solution.
>
> Tom Ellison
>
>
>

Hi Tom,

My experience with MDBs is that you will get a 5 to 10% performance increase
after the database is compacted. Compaction is not the same as disk
defragmentation, however, and I suspect that defragmenting will give you a
similar improvement. With 19,000 fragments, you cannot help but improve the
system condiserably. Indexing, as you already know, will give substantial
improvements in performance.

You might suggest burning everything to a DVD if the client has one. I've
been buying 80GB USB drives for use in backup as cheap as $100 (and even $50
dollars when there's a big sale).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

"Tom Ellison" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have 6 very large and horribly fragmented database files (MDB and MDF).
> They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger
ones).
> There are up to 19 thousand fragments in them. I've just learned that,
even
> though the HD is only 79% full, I cannot defrag these. I would have to
> somehow get 2.81 GB of CONTIGUOUS free space to do the largest. Looking
at
> the map in defrag, I doubt there is a single contiguous area of free space
> larger than a half GB.
>
> Before going to the expense and effort of buying another HD, installing
it,
> and copying these files, I want to ask your opinion and experience with
the
> performance issues involved. Is such a horribly fragmented MDB as this,
> scattered back and forth across a 40 GB drive, likely to see much
> performance improvement if defragged?
>
> I ran into a client who wanted performance. I decreased the storage of
most
> of the columns of data, reducing the size by 70% and more, then added an
> index. I got about a 10:1 performance improvement in queries. Now I want
> to ask the client to defrag what is a very large database (he's already
> reused most of the 70% savings by adding more records). I had hoped to
> defrag and test here, but that's being held up because I can't defrag.
>
> Do you see much performance improvement with a defrag on a database that
is
> in such bad condition? Any idea how much? The index still leaves a table
> scan of perhaps a million rows (rather than the total database of 10
million
> rows, hence the 10:1 improvement).
>
> I know, this is a crazy thing to do with Jet. I'm trying to get it
switched
> to SQL Express, but if I can illustrate an even greater improvement still
> using Jet, my credibility goes up. That will then tend to help move
things
> toward the ultimate solution.
>
> Tom Ellison
>
>

How do you determine how many fragments for a particular file?

Won't the HD defrag incrementally? You might have to do it a couple of
times to get enough free space but won't that eventually happen?

"Tom Ellison" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have 6 very large and horribly fragmented database files (MDB and MDF).
>They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger
>ones). There are up to 19 thousand fragments in them. I've just learned
>that, even though the HD is only 79% full, I cannot defrag these. I would
>have to somehow get 2.81 GB of CONTIGUOUS free space to do the largest.
>Looking at the map in defrag, I doubt there is a single contiguous area of
>free space larger than a half GB.
>
> Before going to the expense and effort of buying another HD, installing
> it, and copying these files, I want to ask your opinion and experience
> with the performance issues involved. Is such a horribly fragmented MDB
> as this, scattered back and forth across a 40 GB drive, likely to see much
> performance improvement if defragged?
>
> I ran into a client who wanted performance. I decreased the storage of
> most of the columns of data, reducing the size by 70% and more, then added
> an index. I got about a 10:1 performance improvement in queries. Now I
> want to ask the client to defrag what is a very large database (he's
> already reused most of the 70% savings by adding more records). I had
> hoped to defrag and test here, but that's being held up because I can't
> defrag.
>
> Do you see much performance improvement with a defrag on a database that
> is in such bad condition? Any idea how much? The index still leaves a
> table scan of perhaps a million rows (rather than the total database of 10
> million rows, hence the 10:1 improvement).
>
> I know, this is a crazy thing to do with Jet. I'm trying to get it
> switched to SQL Express, but if I can illustrate an even greater
> improvement still using Jet, my credibility goes up. That will then tend
> to help move things toward the ultimate solution.
>
> Tom Ellison
>
>

Hi, Pat,

Sorry we haven't yet met. I'll try to remedy that. Hope to see you at the
next Summit.

When I attempt the defrag on WinXP Pro, and it doesn't defrag some of the
files, it gives statistics on them, including the number of fragments.

Tom Ellison


"Pat Hartman(MVP)" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How do you determine how many fragments for a particular file?
>
> Won't the HD defrag incrementally? You might have to do it a couple of
> times to get enough free space but won't that eventually happen?
>
> "Tom Ellison" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I have 6 very large and horribly fragmented database files (MDB and MDF).
>>They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger
>>ones). There are up to 19 thousand fragments in them. I've just learned
>>that, even though the HD is only 79% full, I cannot defrag these. I would
>>have to somehow get 2.81 GB of CONTIGUOUS free space to do the largest.
>>Looking at the map in defrag, I doubt there is a single contiguous area of
>>free space larger than a half GB.
>>
>> Before going to the expense and effort of buying another HD, installing
>> it, and copying these files, I want to ask your opinion and experience
>> with the performance issues involved. Is such a horribly fragmented MDB
>> as this, scattered back and forth across a 40 GB drive, likely to see
>> much performance improvement if defragged?
>>
>> I ran into a client who wanted performance. I decreased the storage of
>> most of the columns of data, reducing the size by 70% and more, then
>> added an index. I got about a 10:1 performance improvement in queries.
>> Now I want to ask the client to defrag what is a very large database
>> (he's already reused most of the 70% savings by adding more records). I
>> had hoped to defrag and test here, but that's being held up because I
>> can't defrag.
>>
>> Do you see much performance improvement with a defrag on a database that
>> is in such bad condition? Any idea how much? The index still leaves a
>> table scan of perhaps a million rows (rather than the total database of
>> 10 million rows, hence the 10:1 improvement).
>>
>> I know, this is a crazy thing to do with Jet. I'm trying to get it
>> switched to SQL Express, but if I can illustrate an even greater
>> improvement still using Jet, my credibility goes up. That will then tend
>> to help move things toward the ultimate solution.
>>
>> Tom Ellison
>>
>>
>
>

Hey, Arvin,

It's always a fine thing to have contact with you!

There's no DVD drive on this system. The one on my other computer is
temporarily unavailable as the HD there crashed completely. Have to rebuild
a system AGAIN! Sigh!

I think I'll compress the directories where these files are. Maybe then
I'll be able to defrag one. If I get one, then maybe I'll be able to defrag
another, and another.

I had no idea the ability of Windows to defrag was so poor. Theoretically,
a single file filling only 0.03 % of the hard drive, and being the only file
on the hard drive, could be spread out across a drive an you'd be unable to
defrag! The rule is, there must be a single contiguous free space large
enough to hold the file or it won't defrag. A single 10 megabyte file on my
40 gigabyte drive could be too much for the defrag utility, if it were
spread out in uniformly spaced chunks that are less than 10 megabytes apart.
What a mess. I wrote my own defrag for unix systems about 20 years ago and
it worked better than this one does! Really nuts!

Tom Ellison


"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:%23HxOI$(E-Mail Removed)...
> Hi Tom,
>
> My experience with MDBs is that you will get a 5 to 10% performance
> increase
> after the database is compacted. Compaction is not the same as disk
> defragmentation, however, and I suspect that defragmenting will give you a
> similar improvement. With 19,000 fragments, you cannot help but improve
> the
> system condiserably. Indexing, as you already know, will give substantial
> improvements in performance.
>
> You might suggest burning everything to a DVD if the client has one. I've
> been buying 80GB USB drives for use in backup as cheap as $100 (and even
> $50
> dollars when there's a big sale).
> --
> Arvin Meyer, MCP, MVP
> Microsoft Access
> Free Access downloads
> http://www.datastrat.com
> http://www.mvps.org/access
>
> "Tom Ellison" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> I have 6 very large and horribly fragmented database files (MDB and MDF).
>> They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger
> ones).
>> There are up to 19 thousand fragments in them. I've just learned that,
> even
>> though the HD is only 79% full, I cannot defrag these. I would have to
>> somehow get 2.81 GB of CONTIGUOUS free space to do the largest. Looking
> at
>> the map in defrag, I doubt there is a single contiguous area of free
>> space
>> larger than a half GB.
>>
>> Before going to the expense and effort of buying another HD, installing
> it,
>> and copying these files, I want to ask your opinion and experience with
> the
>> performance issues involved. Is such a horribly fragmented MDB as this,
>> scattered back and forth across a 40 GB drive, likely to see much
>> performance improvement if defragged?
>>
>> I ran into a client who wanted performance. I decreased the storage of
> most
>> of the columns of data, reducing the size by 70% and more, then added an
>> index. I got about a 10:1 performance improvement in queries. Now I
>> want
>> to ask the client to defrag what is a very large database (he's already
>> reused most of the 70% savings by adding more records). I had hoped to
>> defrag and test here, but that's being held up because I can't defrag.
>>
>> Do you see much performance improvement with a defrag on a database that
> is
>> in such bad condition? Any idea how much? The index still leaves a
>> table
>> scan of perhaps a million rows (rather than the total database of 10
> million
>> rows, hence the 10:1 improvement).
>>
>> I know, this is a crazy thing to do with Jet. I'm trying to get it
> switched
>> to SQL Express, but if I can illustrate an even greater improvement still
>> using Jet, my credibility goes up. That will then tend to help move
> things
>> toward the ultimate solution.
>>
>> Tom Ellison
>>
>>
>
>

Thanks, Jerry:

No DVD and the CD burner is too small for these files, unless maybe they
compress well. I may yet try that.

"They" is ME. This is my development copy. I have backups of the
programming, and I have T2 bandwidth access to copies of the client's
backups that are kept on their HDs.

I suspect one client may have a fragmented HD and could really get some
benefit. Unfortunately, defrag seems to be bunk.

And, yah, I hope they're backing up their stuff, too. However, this is an
unusual database, made up of hundreds of imports from Excel. There is no
Access interface to insert, delete, or update anything. Just to search it.
Their search was originally about 20 hours daily. I got that down to 2-1/2
hours, and hope defrag will improve it even more. I'm also thinking having
it in a compressed folder would be a good thing for the table scan that
still occurs.

Just talking with all of you really helps me think about things I would have
missed otherwise.

Tom Ellison


"Jerry Whittle" <(E-Mail Removed)> wrote in message
news:B2074016-F2E1-43E3-8370-(E-Mail Removed)...
> It would certainly seem that the less a hard drive has to work, the faster
> the database; however, it's probably not going to make a big improvement.
> I'm
> assuming that you have already done a compact and repair.
>
> Is there a DVD burner on that computer? If so, move the databases to a
> DVD;
> then defrag the hard drive; finally move the databases back. You could do
> the
> same thing with an external hard drive. There's no guarantee that Windows
> will put it all in the same area of the hard drive plus it might not take
> long for things to defrayment again.
>
> Which brings up another subject - how do they backup the database now?
> Hopefully backups are being done very frequently.
>
> --
> Jerry Whittle
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Tom Ellison" wrote:
>
>> I have 6 very large and horribly fragmented database files (MDB and MDF).
>> They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger
>> ones).
>> There are up to 19 thousand fragments in them. I've just learned that,
>> even
>> though the HD is only 79% full, I cannot defrag these. I would have to
>> somehow get 2.81 GB of CONTIGUOUS free space to do the largest. Looking
>> at
>> the map in defrag, I doubt there is a single contiguous area of free
>> space
>> larger than a half GB.
>>
>> Before going to the expense and effort of buying another HD, installing
>> it,
>> and copying these files, I want to ask your opinion and experience with
>> the
>> performance issues involved. Is such a horribly fragmented MDB as this,
>> scattered back and forth across a 40 GB drive, likely to see much
>> performance improvement if defragged?
>>
>> I ran into a client who wanted performance. I decreased the storage of
>> most
>> of the columns of data, reducing the size by 70% and more, then added an
>> index. I got about a 10:1 performance improvement in queries. Now I
>> want
>> to ask the client to defrag what is a very large database (he's already
>> reused most of the 70% savings by adding more records). I had hoped to
>> defrag and test here, but that's being held up because I can't defrag.
>>
>> Do you see much performance improvement with a defrag on a database that
>> is
>> in such bad condition? Any idea how much? The index still leaves a
>> table
>> scan of perhaps a million rows (rather than the total database of 10
>> million
>> rows, hence the 10:1 improvement).
>>
>> I know, this is a crazy thing to do with Jet. I'm trying to get it
>> switched
>> to SQL Express, but if I can illustrate an even greater improvement still
>> using Jet, my credibility goes up. That will then tend to help move
>> things
>> toward the ultimate solution.
>>
>> Tom Ellison
>>
>>
>>

Arvin,

Another thing, if you have a minute. Do you or any of the others have any
experience with the speed of table scans in a huge Jet database if the file
is both defragged and compressed. It would seem to me compression would be
a good thing, too.

The searches being done in this database are on numeric columns, and there's
not hint of uniqueness in any of them. The database really is a
spreadsheet, and there's no apology for that. It's supposed to be a huge
spreadsheet, imported daily from Excel (and, yes, that's got to make the
fragmentation all the worse.) I was just tinking of using compression to
get the file size down so I might have a shot at defragging it, and realized
that this would be a good thing for a table scan, too. I have eliminated
80-90% of the table scan by indexing the most beneficial column of data, but
there's still a lot of table scan needed. Compression sounds like a very
good thing then, doesn't it?

I guess we'll see!

Tom Ellison


"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:%23HxOI$(E-Mail Removed)...
> Hi Tom,
>
> My experience with MDBs is that you will get a 5 to 10% performance
> increase
> after the database is compacted. Compaction is not the same as disk
> defragmentation, however, and I suspect that defragmenting will give you a
> similar improvement. With 19,000 fragments, you cannot help but improve
> the
> system condiserably. Indexing, as you already know, will give substantial
> improvements in performance.
>
> You might suggest burning everything to a DVD if the client has one. I've
> been buying 80GB USB drives for use in backup as cheap as $100 (and even
> $50
> dollars when there's a big sale).
> --
> Arvin Meyer, MCP, MVP
> Microsoft Access
> Free Access downloads
> http://www.datastrat.com
> http://www.mvps.org/access
>
> "Tom Ellison" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> I have 6 very large and horribly fragmented database files (MDB and MDF).
>> They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger
> ones).
>> There are up to 19 thousand fragments in them. I've just learned that,
> even
>> though the HD is only 79% full, I cannot defrag these. I would have to
>> somehow get 2.81 GB of CONTIGUOUS free space to do the largest. Looking
> at
>> the map in defrag, I doubt there is a single contiguous area of free
>> space
>> larger than a half GB.
>>
>> Before going to the expense and effort of buying another HD, installing
> it,
>> and copying these files, I want to ask your opinion and experience with
> the
>> performance issues involved. Is such a horribly fragmented MDB as this,
>> scattered back and forth across a 40 GB drive, likely to see much
>> performance improvement if defragged?
>>
>> I ran into a client who wanted performance. I decreased the storage of
> most
>> of the columns of data, reducing the size by 70% and more, then added an
>> index. I got about a 10:1 performance improvement in queries. Now I
>> want
>> to ask the client to defrag what is a very large database (he's already
>> reused most of the 70% savings by adding more records). I had hoped to
>> defrag and test here, but that's being held up because I can't defrag.
>>
>> Do you see much performance improvement with a defrag on a database that
> is
>> in such bad condition? Any idea how much? The index still leaves a
>> table
>> scan of perhaps a million rows (rather than the total database of 10
> million
>> rows, hence the 10:1 improvement).
>>
>> I know, this is a crazy thing to do with Jet. I'm trying to get it
> switched
>> to SQL Express, but if I can illustrate an even greater improvement still
>> using Jet, my credibility goes up. That will then tend to help move
> things
>> toward the ultimate solution.
>>
>> Tom Ellison
>>
>>
>
>

Compression is a bad idea.

The file has to be uncompressed on the fly everytime you
access it.

Also, compression takes place in the background, and is
a pain in the butt, because you can't move or delete
files while the compression action is going on. Which
may take overnight as a background task.

Also, compression tends to make fragmentation even worse,
and defragmenting even more difficult.

Compact the MDB files, then zip them into zipped folders
(not into compressed folders), then delete the unzipped file.
If you have room, do the same to the SQL Databases. If not,
just zip and delete them. Do the zip/delete one file at
a time, so that the fragmentation does not just get worse.

A good third party defragmenter will defragment your
directory structure, which will make far more difference
than defragmenting your files ever will.

For these big files, you will get better speed if you
reformat with big clusters (but you won't be able to
defrag at all, so you shouldn't do this where you are
updating the data as the client is).
In fact, you will get better speed if you format your
40GB drive as FAT32, with very large clusters.

For the MDB files, connect using Exclusive. Also, try
connecting Read-only: I can't remember if that made
any difference.

And, consider re-indexing between searches. For some
kinds of searches, it is worth adding an index before
the search.

(david)


"Tom Ellison" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hey, Arvin,
>
> It's always a fine thing to have contact with you!
>
> There's no DVD drive on this system. The one on my other computer is
> temporarily unavailable as the HD there crashed completely. Have to
> rebuild a system AGAIN! Sigh!
>
> I think I'll compress the directories where these files are. Maybe then
> I'll be able to defrag one. If I get one, then maybe I'll be able to
> defrag another, and another.
>
> I had no idea the ability of Windows to defrag was so poor.
> Theoretically, a single file filling only 0.03 % of the hard drive, and
> being the only file on the hard drive, could be spread out across a drive
> an you'd be unable to defrag! The rule is, there must be a single
> contiguous free space large enough to hold the file or it won't defrag. A
> single 10 megabyte file on my 40 gigabyte drive could be too much for the
> defrag utility, if it were spread out in uniformly spaced chunks that are
> less than 10 megabytes apart. What a mess. I wrote my own defrag for unix
> systems about 20 years ago and it worked better than this one does!
> Really nuts!
>
> Tom Ellison
>
>
> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
> news:%23HxOI$(E-Mail Removed)...
>> Hi Tom,
>>
>> My experience with MDBs is that you will get a 5 to 10% performance
>> increase
>> after the database is compacted. Compaction is not the same as disk
>> defragmentation, however, and I suspect that defragmenting will give you
>> a
>> similar improvement. With 19,000 fragments, you cannot help but improve
>> the
>> system condiserably. Indexing, as you already know, will give substantial
>> improvements in performance.
>>
>> You might suggest burning everything to a DVD if the client has one. I've
>> been buying 80GB USB drives for use in backup as cheap as $100 (and even
>> $50
>> dollars when there's a big sale).
>> --
>> Arvin Meyer, MCP, MVP
>> Microsoft Access
>> Free Access downloads
>> http://www.datastrat.com
>> http://www.mvps.org/access
>>
>> "Tom Ellison" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> I have 6 very large and horribly fragmented database files (MDB and
>>> MDF).
>>> They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger
>> ones).
>>> There are up to 19 thousand fragments in them. I've just learned that,
>> even
>>> though the HD is only 79% full, I cannot defrag these. I would have to
>>> somehow get 2.81 GB of CONTIGUOUS free space to do the largest. Looking
>> at
>>> the map in defrag, I doubt there is a single contiguous area of free
>>> space
>>> larger than a half GB.
>>>
>>> Before going to the expense and effort of buying another HD, installing
>> it,
>>> and copying these files, I want to ask your opinion and experience with
>> the
>>> performance issues involved. Is such a horribly fragmented MDB as this,
>>> scattered back and forth across a 40 GB drive, likely to see much
>>> performance improvement if defragged?
>>>
>>> I ran into a client who wanted performance. I decreased the storage of
>> most
>>> of the columns of data, reducing the size by 70% and more, then added an
>>> index. I got about a 10:1 performance improvement in queries. Now I
>>> want
>>> to ask the client to defrag what is a very large database (he's already
>>> reused most of the 70% savings by adding more records). I had hoped to
>>> defrag and test here, but that's being held up because I can't defrag.
>>>
>>> Do you see much performance improvement with a defrag on a database that
>> is
>>> in such bad condition? Any idea how much? The index still leaves a
>>> table
>>> scan of perhaps a million rows (rather than the total database of 10
>> million
>>> rows, hence the 10:1 improvement).
>>>
>>> I know, this is a crazy thing to do with Jet. I'm trying to get it
>> switched
>>> to SQL Express, but if I can illustrate an even greater improvement
>>> still
>>> using Jet, my credibility goes up. That will then tend to help move
>> things
>>> toward the ultimate solution.
>>>
>>> Tom Ellison
>>>
>>>
>>
>>
>
>

Hi Tom

There are many files that WinXP defrag simply will not touch.

However, I can strongly recommend Raxco's PerfectDisk
(http://www.raxco.com/). It does a very good job, even defragging system
files, MFT and pagefiles.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Tom Ellison" <(E-Mail Removed)> wrote in message
news:ur$(E-Mail Removed)...
> Hi, Pat,
>
> Sorry we haven't yet met. I'll try to remedy that. Hope to see you at
> the next Summit.
>
> When I attempt the defrag on WinXP Pro, and it doesn't defrag some of the
> files, it gives statistics on them, including the number of fragments.
>
> Tom Ellison
>
>
> "Pat Hartman(MVP)" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> How do you determine how many fragments for a particular file?
>>
>> Won't the HD defrag incrementally? You might have to do it a couple of
>> times to get enough free space but won't that eventually happen?
>>
>> "Tom Ellison" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>I have 6 very large and horribly fragmented database files (MDB and MDF).
>>>They range from 1.59 GB to 2.81 GB in size (SQL Express on the larger
>>>ones). There are up to 19 thousand fragments in them. I've just learned
>>>that, even though the HD is only 79% full, I cannot defrag these. I
>>>would have to somehow get 2.81 GB of CONTIGUOUS free space to do the
>>>largest. Looking at the map in defrag, I doubt there is a single
>>>contiguous area of free space larger than a half GB.
>>>
>>> Before going to the expense and effort of buying another HD, installing
>>> it, and copying these files, I want to ask your opinion and experience
>>> with the performance issues involved. Is such a horribly fragmented MDB
>>> as this, scattered back and forth across a 40 GB drive, likely to see
>>> much performance improvement if defragged?
>>>
>>> I ran into a client who wanted performance. I decreased the storage of
>>> most of the columns of data, reducing the size by 70% and more, then
>>> added an index. I got about a 10:1 performance improvement in queries.
>>> Now I want to ask the client to defrag what is a very large database
>>> (he's already reused most of the 70% savings by adding more records). I
>>> had hoped to defrag and test here, but that's being held up because I
>>> can't defrag.
>>>
>>> Do you see much performance improvement with a defrag on a database that
>>> is in such bad condition? Any idea how much? The index still leaves a
>>> table scan of perhaps a million rows (rather than the total database of
>>> 10 million rows, hence the 10:1 improvement).
>>>
>>> I know, this is a crazy thing to do with Jet. I'm trying to get it
>>> switched to SQL Express, but if I can illustrate an even greater
>>> improvement still using Jet, my credibility goes up. That will then
>>> tend to help move things toward the ultimate solution.
>>>
>>> Tom Ellison
>>>
>>>
>>
>>
>
>