Question

AVL Trees vs. BTree - Real World Optimizations Needed

Asked by: g0rath

This has been a question bugging me, but I've never gotten it solved the way I wanted....
I know in Theory an AVL tree will have better performance than a BTree in the long run.

Problem Set...

There is a database written useing an index file and a data file of
fixed length data (mostly).
Both of these sets of data are sorted using a Binary Sort tree.
The way this works is via record number, and via other sort critera
such that there are more then one way to sort through this data. These
record pointers ( unsigned int ), are actually doubly linked-lists. This allows
us to traverse the tree in any direction from any point.

The primary operation is Reads, and Updates, and Adds with Deletes
being the rarest of all events. In that order.

Since this methodology allows 22 disk reads to find 1 record in the worst-case
scenario for 5 million users at over 80 TPS, it's VERY fast. Much faster then using
a REAL database for far cheaper. For everything else, the filesystem can act as a
datbase for other interesting variable data that isn't time critical.

But....
Adds are written to the End of the file, so you have unsorted data following down a branch
creating an unbalenced tree. So performance degrades for all "new records" but not for
all current records. But all "new records" have a higher chance of being hit, since newer
is better.
Currently the clients attached that are attached to this database have to be locked out so that the indexes and sort indexes can be reindexed to rebalance the tree.

Hence my research into AVL trees.
While this may allow clients to access 24/7 it adds more to the disk reads/writes for
Adds and Deletes via most implementations that I came up with.

Since I would require exclusive locks per record, but that would lock 6 records for a SWAP(A,B)
operation.

|  parent     |           ---->          |   node   |      ---->         |  child  |
|      A         |          <----          |      A      |     <----         |   A      |

|  parent     |           ---->          |   node   |      ---->         |  child  |
|      B         |          <----          |      B      |     <----          |   B      |

SWAP(a,b)
Exclusive Lock Parent A
Exclusive Lock Parent B
Exclusive Lock Node A
Exclusive Lock Node B
Exclusive Lock Child A
Exclusive Lock Child B

// Remove NodeA, Link NodeB
ParentA.Next = NodeB.current
NodeB.Previous = ParentA.current
NodeB.Next = ChildA.current
ChildA.Previous = NodeB.current

// Node A ophened
// if locks released, the Node A cannot be found...not acceptable

ParentB.Next = NodeA.Ccurrent
NodeA.Previous = ParentB.Current
NodaA.Next = ChildA.Current
ChildB.Previous = NodeA.Current
// write the 6 records
ReleaseLocks(ParentA,NodaA,ChildA,ParentB,NodeB,ChildB)

Current Code:
LastRec = EndofFile-sizeof(rec)
AddRec( new data)
AddRec.Next = 0
AddRec.Previous = LastRec
Lock LastRec
LastRec.Next = AddRec
Unlock LastRec
Done
//4 I/O operations
So what I have here is now 18 file operations and blocking
some of the readers.

If I could allow ophaned records, then I could do this with at most 8 I/O operations.

This only took 4 operations before and was non-blocking.

So how do you solve this? Use a real database :) and pay
millions to M$ or to Larry Elli$on?

One theory I had was to create a shared memory copy that
is used for all reads, perform my writes to disk, and then
at some interval sync the shared memory copy to that which is
on disk or on updates that does update a node list.

The complexity goes up, but not sure if I will go this route.
Would prefer a better alogrithim.

(wow that was a long question)

:)

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2003-11-14 at 07:32:41ID20798196
Tags

avl

,

tree

,

vs

Topic

Programming Languages

Participating Experts
1
Points
500
Comments
6

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Alternative for sizeof() operator
    This question was asked by someone in interview. How to find the size of a variable without using sizeof() operator? I'd thought of using <limits.h> and to decide size using MAX_INT etc.. Don't know if I'm correct...Any ideas?
  2. Implementation of sizeof operator in C
    If I want to use my_sizeof instead of built-in sizeof operator in C, how could I write the code for my_sizeof? Usage of my_sizeof should be as follows: size_t i, j; i = my_sizeof(int); i = my_sizeof(j);
  3. Sizeof()
    if 'char *array' points to an array in dynamic memory, how can i find the size of the array? i know sizeof() doesnt work in this situation...
  4. why is sizeof(STRUCT) returning the wrong size?
    I have some code I borrowed from a unix open source project. It's meant to create a file that is LENGTH * 10 bytes long. What it does is create a file of LENGTH * 12 bytes long. The file is filled with LENGTH number of STRUCTUREDHASH structs (set to null?) The struct looks li...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: sunnycoderPosted on 2003-11-15 at 00:35:47ID: 9753335

I read whole of your question .... but your algo is not very clear to me ... maybe due to difference of terminology or may be due to too many node A and too many node B... But your problem is clear...

you must be having some function/code segment that builds the index for a database
place another call to that function and let it build an index in some temporary buffer without interfering with the existing indices ...
at first opportunity (no access to records) replace old index with new one....

this should allow 24/7 access without much performance hit (you can always build indices when you are freer)

 

by: g0rathPosted on 2003-11-17 at 05:34:41ID: 9763348

the problem is that both the index and the data file has binary trees in them, there are connected as doulbly linked lists, while the index is trivial to rebalance, the data file takes alot longer to balance.

There isn't a function to rebuild the index on insert, since that would lock too many records...it is just appended to the end. During the night the database is taken offline, and then both files are sorted by username, then then recursivly divide the tree in half to find the root node, and then on return it balances the rest of the branches...any records marked for deletetion, at this point are not put back into the tree, this can take upwards of 30 minutes to an hour

....which is why I'm looking at AVL trees except for the performance hit for the swaps....

Read/write performance is the most important aspect of this database which is why it was developed using this method.

Using your method I would have to build an index in memory, and when we don't have any access, write it back out....that would suggest I would need some sort of transaction log, that all write transactions that have to be commited. Also there is more write then I realized, since we toggle bits for status in the individual record and update dates...so if this proccess took 60 seconds, we could easily have 100+ write transactions to update during a busy period...

I have thought about this, but haven't looked for all the "What if's" yet so haven't gotten too far...

 

by: sunnycoderPosted on 2003-11-17 at 21:15:17ID: 9769019

may I ask why you need doubly connected structures ?

anyway, I gave your problem a good thought and I think that a B+ tree might be just right...

the lockup will not be vast, effect will be absorbed in max 1 level above leaves ... Given your circumstances, if you start with something like say 60% populated internal nodes, it may be a long time before you see a level split anywhere...
the number of levels in the tree would  be reduced too ...

the size of your database would be a main consideration in deciding the cardianality...

what do you say?

 

by: g0rathPosted on 2003-11-25 at 08:23:22ID: 9818700

doubly linked lists are so that you can traverse the tree in either direction from some arbitrary point.

Each node is user data, but the data is linked for things such as CreatedTime, and other indexes that need to be either sorted asscending or descending....

Do you have a good place that shows the B+ tree algorithm? Either pseudo-code or whatever may also be nice....the B+ tree I never really thought of because it seems that everyone has their own B++ tree modification or something else slightly different. Sorry been really busy these days so haven't looked close enough back on this problem.

 

by: g0rathPosted on 2003-11-25 at 08:30:35ID: 9818748

http://www.cs.duke.edu/~tavi/papers/tpie_paper.pdf

This looks very promising...a discussion of the implementation of Tree alogrithims in both sequential and random I/O by the TPIE (Transparent Parallel I/O Environment) project guys

 

by: sunnycoderPosted on 2003-11-25 at 23:40:54ID: 9823048

>doubly linked lists are so that you can traverse the tree in either direction from some arbitrary point.
So I guess you need sequential access for range searches ... B+ tree would do just fine

>the B+ tree I never really thought of because it seems that everyone has their own B++ tree modification or something else
>slightly different.
LOL, al the more better, you can customize the algorithm to match your needs perfectly

>Do you have a good place that shows the B+ tree algorithm?
http://userpages.umbc.edu/~cmason1/code/cs491i/cs491i/tutorial8.ppt

A book on DBMS concepts by Elmasri Navathe (addison wesley if I remember correctly) has a very good description with algorithm and examples ... If you can find a copy, go through it

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...