1 Million Record XML File 1

[EZEason]

Has anyone ever ran a VB app that uses a large XML file as the DB?

I'm thinking of converting one of my apps backend to xml (between 100k to 1 mill records). The data is static on a cd. No updates, Read Only. It has been in mdb files, msde, and dbf format. We have conflict with different clients pc, plus it is slow.

What doesn't kill you makes you stronger.

 

[sedj]

And you think that an ASCII file with 1 million records in it will be quick ?!

Click here to learn Ways to help with Tsunami Relief
--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[flumpy]

I have written apps that use MDB files before now, and yes they are slow (almost painfully). Sedj is right, making it XML is not going to help matters. XML really isn't for this sort of thing its almost app suicide.

My instict tells me you are not using the DBO recordset API in an efficient way, that you may need to re-normalize your data structure (possibly, go up a level of normalization!!) and possibly also re-index your tables to increase speed. Use numeric primary keys, index all columns that can be searched etc etc.

Also something else you can try is to thread the search part of your application so that the search is carried out in the background and the user is informed how long the search is taking.

But XML? Don't do it.

 

[EZEason]

Flumpy,
Thanks for the input. I have used MDB alot. The problem is that some of the databases are more then 2 gigs, which is more then Access allows. The other tek problem that I have with Access is that when opening jet it makes a virtual copy of the file to the pc, and most pc do not allow enough virtual memory for the data. I have also had issues between different versions. I'm using ADO and have the 16 fields indexed. That data is formated by the government. (It is a sound structure.)

Most of my problem is pc issues between different customer. They all have something different. Speed is second, but since that data is only on a cd/dvd, it will only access the data to the speed of the drive. I have used different database (FoxPro, Codebase) with little resovle. I end up with sercurity issues with them. (I can not password protect the data).

I was thinking that if I used encripted XML file it would resolve me pc issues. I do not think it would solve my speed problems.

Thank you for your advice.

Sedj: I have never used an ASCII file, how does it work an can it be secured?

What doesn't kill you makes you stronger.

 

[chiph]

Do NOT use XML as a database for anything beyond 100 records. You'll need a real database for 1 million rows.

It sounds like you have a problem -- you're distributing your data via optical media, and most real databases won't work on read-only media. You also want to secure the database, and a real DB can do that, but doesn't get you around problem #1.

I don't have an answer for you -- you'll need to change one of the fundamental assumptions about how you distribute your data, and you need to open yourself up to other solutions beyond what you're doing now.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[sedj]

An ASCII file is a file that is encoded in ASCII - like a XML file, or a simple *ASCII* text file ...

Basically, its time to invest a *real* database !

Click here to learn Ways to help with Tsunami Relief
--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[EZEason]

I have tried 4 different database. Access, MSDE, FoxPro, and CodeBase. None of which meet all the requirements that that are put on me. Being new to XML, I was thinking this could be my simple solution.

So XML is out!

Any suggestion on what I sould be using? The data must reside on a cd/dvd, be secured, searchable, handle up to 5 gigs of data, min pc compatiblity issues. The data changes every month and the software, in VB6, on the disk goes inactive after a few months of use.

What doesn't kill you makes you stronger.

 

[chiph]

I think you're looking at writing your own data storage/retrieval code.

It will allow you to optimize it for your application, encrypt it, and since it's proprietary, the users can't just point a reporting tool at it, like they can with a .mdb file.

To do this, you'll need a data structures book, written with disk-based structures in mind. I don't have a book to recommend, but if you searched on disk-based B+ Trees that would get you started. Don't forget to keep the publishing operation (preparing the data for media replication) separate from when the program reads from disk.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[flumpy]

Another solution to try is possibly making a dblink from temp database on the users PC linking to the biggest, bulkiest DB table on the CD/DVD. You could also perhaps make copies of smaller, more frequently accessed tables their too.

You can turn off caching I am sure with ADO,

There is no, hard, fast solution to this speed problem.


However, as for compatiblity issues, it is a pain. But building the application into a good installer and distributing the right DLLs is essential, as well as good platform testing. Use something like InstallAnywhere (

http://www.zerog.com/products_ia.shtml)

good luck!!

m