Author Topic: Reindexing in the background software for databases (Read 19927 times)

Contro · « **on:** July 02, 2014, 09:33 AM »

Reindexing in the background software for databases

I have DBF files to organize.
I would like an application to reindex continually in the background even when these databases are in use.
Sort by all fields to be able to find everything. Even in the memo fields (notes).

Best Regards

Ath · « **Reply #1 on:** July 02, 2014, 10:59 AM »

I would like an application to reindex continually in the background even when these databases are in use
-Contro (July 02, 2014, 09:33 AM)

2 remarks:
- Properly programmed applications (using .dbf files) don't need that
- Most applications don't allow their active (database) files to be changed, during normal operation

Shades · « **Reply #2 on:** July 03, 2014, 12:13 AM »

With Oracle there are systems in place that do maintenance tasks such as (re-)index tables when a database is idling. That is because this consumes a lot of resources and it is better that during (re-)generation as little none to as little amount as possible database transaction take place so indexes are not (too) messed up afterwards.

The online documentation with MS-SQL Server already warns against creating too much indexes as they are more likely to degrade performance than enhance it.

Enterprise class databases don't even do what you want...or warn against these practices at least.

Ath is right, proper applications don't need continuous indexing.

Contro · « **Reply #3 on:** July 04, 2014, 08:00 AM »

I would like an application to reindex continually in the background even when these databases are in use
-Contro (July 02, 2014, 09:33 AM)
2 remarks:
- Properly programmed applications (using .dbf files) don't need that
- Most applications don't allow their active (database) files to be changed, during normal operation
-Ath (July 02, 2014, 10:59 AM)

Thanks Ath.
I don't need to modify the active database. Only reindexing for search purposes.
Is an ancient DBF databases and programme. That's the reason.

Contro · « **Reply #4 on:** July 04, 2014, 08:04 AM »

With Oracle there are systems in place that do maintenance tasks such as (re-)index tables when a database is idling. That is because this consumes a lot of resources and it is better that during (re-)generation as little none to as little amount as possible database transaction take place so indexes are not (too) messed up afterwards.

The online documentation with MS-SQL Server already warns against creating too much indexes as they are more likely to degrade performance than enhance it.

Enterprise class databases don't even do what you want...or warn against these practices at least.

Ath is right, proper applications don't need continuous indexing.
-Shades (July 03, 2014, 12:13 AM)

Thanks Slades.

If the process is complicated i have another idea.
Copy the DBF databases to another folder and reindex there.
I don't need to modify the dbf databases. I know i can't do that because fall in danger the own indexes the ancient program creates when is in use.

I was thinking about a Shadow copy for consulting purposes.

$:-\$

Contro · « **Reply #5 on:** July 06, 2014, 09:16 AM »

This is a important question for me. And I think informatics in the stone era yet.....

http://www.datamystic.com/datapipe.html

http://libguides.col...astate.edu/federated

http://www.datamystic.com/forums

http://www.bcs.org/u...ewic_im98_paper3.pdf

Perhaps there is a commercial interest or a historic handycap. Possibly is not simple to do my dream.

I was thinking about another idea :

There are several environments with forum in the databases world like PostGreSQL or MySQL.
Even you must know better options .
Is possible i can make a copy of the original databases (migrate) and reindex in any new environment.

May be ?

Contro · « **Reply #6 on:** July 06, 2014, 09:50 AM »

With Oracle there are systems in place that do maintenance tasks such as (re-)index tables when a database is idling. That is because this consumes a lot of resources and it is better that during (re-)generation as little none to as little amount as possible database transaction take place so indexes are not (too) messed up afterwards.

The online documentation with MS-SQL Server already warns against creating too much indexes as they are more likely to degrade performance than enhance it.

Enterprise class databases don't even do what you want...or warn against these practices at least.

Ath is right, proper applications don't need continuous indexing.
-Shades (July 03, 2014, 12:13 AM)

I "remember" somewhere in this forum talk about EveryThing . This program fullfill my requirements about locate in my hard disks. Even talks about more powerful apps able to look inside pdf files, and even scan documentation and create text and indexes.
But for me was enough EveryThing.

I don't need certainly remember what I did- usually - a few moments ago. Or a week ago....
So a working copy of my databases perfectly indexed a week ago is enough for me.

Shades · « **Reply #7 on:** July 06, 2014, 01:45 PM »

Sorry, with RMDB solutions (Oracle, SQL Server, Postgres, MySQL and the likes) indexing might not mean what you think it means.

Database queries that often occur are put into and index. With more complex ones That quickly becomes very resource demanding. It will make your processor/core(s) work very hard and it will start to do a lot of reading and writing to disk. Those two alone will make your PC a lot slower. I know that Oracle databases can be very demanding when they are indexed. Hence they do such tasks at times when the database is not or hardly accessed and when the DB has detected that there is a lot new rows added or removed.

However, I get the impression that you think your database is slow and that indexing is the only cure. Dedicate a computer to be a database server + giving that PC lots of RAM, a decent processor and the fastest hard disks and network card(s) you can afford, together with a monthly/weekly indexing of your database will help a lot more.

Then again, if your database is badly designed, then it will be slow no matter the amount of hardware or indexing you throw at it. Database design is (a lot) more difficult than you think.

I have no experience with this type of database, but you could try to use NoSQL-based database. Perhaps that concept will help you more. What I can tell you is that with choosing your database, you will always be subject to compromise.

If you want to use a different database, and your database is small (10 GByte of storage space or less) than you can use the express version of SQL Server for free. It isn't slow and comes with quite nice management software. But if your database needs more 10 GByte of storage space, you will have to pay quite a handsome amount of money for a license. Although that is still nothing when compared with an Oracle license. Although I must say that the Oracle Express database (also free, but limited to 4GByte of storage space) is very useful.

However, Postgres is open source, not (too) limited in storage space, uses an SQL dialect and features that are quite compatible with Oracle and works on practically any operating system.

Contro · « **Reply #8 on:** July 06, 2014, 02:31 PM »

Sorry, with RMDB solutions (Oracle, SQL Server, Postgres, MySQL and the likes) indexing might not mean what you think it means.

Database queries that often occur are put into and index. With more complex ones That quickly becomes very resource demanding. It will make your processor/core(s) work very hard and it will start to do a lot of reading and writing to disk. Those two alone will make your PC a lot slower. I know that Oracle databases can be very demanding when they are indexed. Hence they do such tasks at times when the database is not or hardly accessed and when the DB has detected that there is a lot new rows added or removed.

However, I get the impression that you think your database is slow and that indexing is the only cure. Dedicate a computer to be a database server + giving that PC lots of RAM, a decent processor and the fastest hard disks and network card(s) you can afford, together with a monthly/weekly indexing of your database will help a lot more.

Then again, if your database is badly designed, then it will be slow no matter the amount of hardware or indexing you throw at it. Database design is (a lot) more difficult than you think.

I have no experience with this type of database, but you could try to use NoSQL-based database. Perhaps that concept will help you more. What I can tell you is that with choosing your database, you will always be subject to compromise.

If you want to use a different database, and your database is small (10 GByte of storage space or less) than you can use the express version of SQL Server for free. It isn't slow and comes with quite nice management software. But if your database needs more 10 GByte of storage space, you will have to pay quite a handsome amount of money for a license. Although that is still nothing when compared with an Oracle license. Although I must say that the Oracle Express database (also free, but limited to 4GByte of storage space) is very useful.

However, Postgres is open source, not (too) limited in storage space, uses an SQL dialect and features that are quite compatible with Oracle and works on practically any operating system.
-Shades (July 06, 2014, 01:45 PM)

Nice ideas indeed.
By the way Goldmine 5.5 is a great CRM. I don't want to change or substitute by another. Not even Goldmine Premium that works in SQL language. My databases are in DBF format, but SQL queries are still posible in goldmine 5.5.

The size of all my goldmine databases set is about 3.41 GB including the indexes files.
I have more of 800000 registers at the present time. I presumed (suppose) i will have 1000000 in a year.
The limit of my version for each database size is 2 TB.
So I have plenty of space.

Usually i search in the main data fields (i was a not professional programmer in the ancient dBASE files : dBASEIII Plus, dBASE IV, dBASE for DOS, Visual dBASE. 16 bit and 32 bit.

Hey I am wrong. I found this :

"Assuming that you have a standard version of GoldMine (License starts with "D"), you only face two limitations. (1) as previously stated, the dbt files have a 2 BG limit though this is seldome reached on a stand-alone system. (2) Hard Drive space. You need to have enough free hard drive space (2.5 x your largest GoldMine tables) so that there is room enough to perform maintenance."

What is 2 BG limit ?

$:-\$

P.D. I tried some years ago to get familiar with other databases like PostGreSQL, MySQL and others.
Even tried SQL language, but always end exhausted and unable to memorize.
I don't understand, but the dBase III plus commands are still in my mind. Curious thing !!!!!!!!!

Contro · « **Reply #9 on:** July 06, 2014, 03:29 PM »

Ejem. Is 2 GB i think. I am consultin in the goldmine forum....

CWuestefeld · « **Reply #10 on:** July 07, 2014, 11:32 AM »

Shades, your description of Oracle doing index maintenance offline isn't quite right. Any real relational database (Oracle, SQL Server, etc.) requires that the database be entirely consistent at any point in time (see ACID). Thus, index maintenance is occurring in real time, as the data to which they refer is being updated.

This is crucial to some more advanced DBA optimization techniques. In particular, covering indexes allow a query to see data that would otherwise be locked by another process's locking operation, by taking the data out of the index rather than the real data tables.

Keeping any indexes up-to-date is indeed a resource drain on your system, but it's not really correct to say that indexes should be avoided as a result. Rather, you need to consider how your system is being used -

When SELECTing data, what are the most common patterns (e.g., are you looking for "all the orders submitted yesterday", or "all the orders from this customer"?)
When INSERTing new data, will it tend to be appended onto the end of the index structure, or must it be stuffed into the middle?
Does the kind of multi-process access going on suggest that you'll need covering indexes?
How much can you trade off additional storage (or faster more expensive storage devices) to improve overall system speed?

...and so on

An increasingly common way to get around some of these problems is to have two distinct databases, once that's updated in realtime for the live transaction processing, and a second that gets updated periodically, and has different indexing structures (maybe even different table structures) to better facilitate reporting on the data.

Shades · « **Reply #11 on:** July 07, 2014, 09:13 PM »

@ CWuestefeld:
If my posts indicated that one should not index, I'm sorry. I was trying to convey that you better let the database software itself take care of it, that continuous indexing is a bad idea and that compromises have to be made, no matter what database software you choose.

Since Oracle 10 automatic maintenance is done quite well in my experience. MS SQL Server is a bit more work to get indexing right. I have only played with the 2012 version and reading through the manuals, both off-and online, a lot of people (incl. Microsoft) mention that making and maintaining indexes within SQL Server can be tricky. There used to be a page that described how to design indexes, but for some reason it is inaccessible at the MSDN site. That was the page where I read about the do's and dont's with indexes on SQL Server (I think).

Contro · « **Reply #12 on:** July 09, 2014, 08:58 AM »

Nice indeed.
so background reindexing is not necessary, but a way to talk.......

The limit in my databases is 2 Gb per file. I have almost 900.000 kb in the biggest one.

What i need is a free tool to import eventually my DBF to another platform easy for dummies like me.
Then i will index in the new platform . This is for me enough to do eventually one a week or a month.

What free desktop platform do you recommend me for this purpose ?

Best Regards

Note : I know postgreSQL (the forums are for professional people and are distribution lists i think)
I know mySQL. I would only for desktop purposes. It has forums in the way of donationcoder.
The database management better is have the appearance of my Visual dBASE from Borland or dBASE IV for DOS . These platforms don't go well in windows 7 64 bit.
The ultimate dBASE is not free. I would like a free tool.
When we had the databases imported we can index on all fields to have any character string under control.

Best Regards

Ath · « **Reply #13 on:** July 09, 2014, 02:00 PM »

There are, obviously, 2 types of indexing discussed in this thread: database indexes, and application independent textsearch indexes. The OP is imho meant to point at the latter, so for this request, dtsearch could be a viable solution. It supports a whole bunch of file-types, including dbf files, and can be found at http://dtsearch.com (payware, no affiliation, found using a google search)

anandcoral · « **Reply #14 on:** July 10, 2014, 03:21 AM »

Reindexing in the background software for databases

I have DBF files to organize.
I would like an application to reindex continually in the background even when these databases are in use.
Sort by all fields to be able to find everything. Even in the memo fields (notes).

Best Regards

-Contro (July 02, 2014, 09:33 AM)

Hi Contro,

I use DBF files as database for my applications. I started with dBASEII and reached Xbase++ now.

A dbf file can be indexed by a foxpro/clipper application even if opened in shared mode, but is not advisable. One should have exclusive lock on the dbf file before creating idx/ntx.

Memo field values are stored in fpt/dbt files which can not be indexed.

For searching a dbf file you can use locate command of foxpro/clipper program, without any index.

I have made DBU8 utility based on DBU of Clipper, for Win 64 bit. It uses Xbase++ dlls, which are available with the demo version of our accounting program Ace9. DBU8 can be used to open a DBF/DBT file and create index NTX files. Search and Locate can be performed.
I made this utility for our in house staff as we have to view and manipulate data of our application.

You can rename a dbf file as xls and Excel will open it. In excel you can filter and search normally. A dbt file can not be opened in excel.

In case you need any further information, please tell me.

Regards,

Anand

Contro · « **Reply #15 on:** July 10, 2014, 05:35 AM »

i`ll try anand . I will download the demo and comment.
goldmine have the ability to look or search inside the memo fields, but lasts.....
So a parallel tool is desirable.

Best Regards

Contro · « **Reply #16 on:** July 10, 2014, 12:21 PM »

Ath i'll take a look at dtsearch too.

Best Regards

A Comment :
"APIs for SQL-type data, including BLOB data"

Sure is simple and direct ?

Contro · « **Reply #17 on:** July 10, 2014, 02:23 PM »

Downloading from

ACE ver 9.9.004 - Main File (with Addon DLL)

DBU - Utility to create / modify dbf files in the utilities section. There are a lot of utilities for several purposes.

Now I think need your help for configuring the DLL files according with the utility.

do you need the version of my DBF files ? Are from goldmine 5.5 spanish version.
ejem...... In the hearte of the databases seems not exist the letter ñ or the acentuated words like José

Best Regards

Contro · « **Reply #18 on:** July 11, 2014, 11:18 AM »

installation done
Ready to try

Contro · « **Reply #19 on:** July 11, 2014, 11:25 AM »

Something goes wrong.
I download the dbu.exe utility, but goes well under xp 32 bit, but not in w7 64 bits. What am i doing bad ?
It's the utility included in the ace9 demo ?

Ath · « **Reply #20 on:** July 11, 2014, 11:29 AM »

I download the dbu.exe utility, but goes well under xp 32 bit, but not in w7 64 bits. What am i doing bad ?
-Contro (June 15, 1974, 01:26 AM)

16 bit executables can't run on 64 bit Windows, because MS didn't that feature in Win x64.

Contro · « **Reply #21 on:** July 11, 2014, 02:40 PM »

I download the dbu.exe utility, but goes well under xp 32 bit, but not in w7 64 bits. What am i doing bad ?
-Contro (June 15, 1974, 01:26 AM)
16 bit executables can't run on 64 bit Windows, because MS didn't that feature in Win x64.
-Ath (July 11, 2014, 11:29 AM)

Yeapp. But :
"I have made DBU8 utility based on DBU of Clipper, for Win 64 bit. It uses Xbase++ dlls,"

So i download the wrong version....

anandcoral · « **Reply #22 on:** July 12, 2014, 02:31 AM »

Hi Contro,

Sorry, looks like main website do not still have DBU8 link as this app has some errors still. Normal add/edit/index/search etc. works OK. Relation, setting etc. causing error which I did not get time to fix yet.

DBU8 download link
https://sites.google.../dbu8-for-win-64-bit

Try it and revert to me.

Regards,

Anand

Contro · « **Reply #23 on:** July 12, 2014, 03:36 PM »

Hi Contro,

Sorry, looks like main website do not still have DBU8 link as this app has some errors still. Normal add/edit/index/search etc. works OK. Relation, setting etc. causing error which I did not get time to fix yet.

DBU8 download link
https://sites.google.../dbu8-for-win-64-bit

Try it and revert to me.

Regards,

Anand

-anandcoral (July 12, 2014, 02:31 AM)

Wonderful i will be the rabbit for the proofs of DBU8

Downloadinggggg !!!!!!!!!!!!!!!!!11

I will comment

Contro · « **Reply #24 on:** July 12, 2014, 03:37 PM »

I remember the command Set relation into with dBASE IV

Good memory passing by.....