Hi Albert, The designe of the tables and indexes looks ok to me.
To eliminate communication overhead between application and MaxDB kernel you could use a recursive select and generate all needed information by one select. For mor information have a look into this http://dev.mysql.com/doc/maxdb/en/a7/41ee26605911d3a98800a0c9449261/fram eset.htm Maybe it helps. Kind regards Holger > -----Original Message----- > From: Beermann, Albert [mailto:[EMAIL PROTECTED] > Sent: Mittwoch, 19. Oktober 2005 09:43 > To: [email protected] > Subject: Performance problem ! Bad database design or stupid > sql-commands ????? > > Hello Everbody > > We are a service company that does everything around > "customer objects" > (security,house cleaning,maschine maintenance,gardening ...) > > Each customer has different structures. > Customer a has 1 building with 5 floors and n rooms in the floors > customer b has germany , north and south, 3 aeras in north, n > buildings > in aera north with n floors with n rooms in each building > customer c has just a floor with 12 rooms > ...... > > On each "object" we do 1 or more services > > We have two main tables in our 7.5 maxdb database to handle this. > table objekts: > objectid as primary key > headerobject (the objectid of the object above) > (index on headerobject) > 20 columns to describe the objekt > > The startpoint of the customer structure has objectid = headerobject > > Example: > start = aera x (object=111,headerobject=111) > building a in aera x (object=4711,headerobject = 111) > building b in aera x (object=4813,headerobject = 111) > building c in aera x (object=4333,headerobject = 111) > > floor 1 in building a (object=5711,headerobject = 4711) > floor 2 in building a (object=6711,headerobject = 4711) > floor 3 in building a (object=5731,headerobject = 4711) > > floor 1 in building b (object=35711,headerobject = 4813) > floor 2 in building a (object=6711,headerobject = 4813) > > room a in floor 1 of building a (object=46711,headerobject = 5711) > > .... > > The second tabe (services) holds the services we do on each object > column serviceid = primary key > column objectid = index > + 15 other columns the describe the services we do for each object > > > I have an application that shows strucured customer data in a treeview > All objects are treeview nodes that you can expand or shrink > Under the objekts we show the services. > > Unfortunately our company is not the smalles one ;-) > Table objects holds 700000 records > Table services holds 3000000 records > and both are still growing > > Performancs is dieing !!! > Resolving a customer aera with 5000 object and 20000 services takes > more then 5 minutes ??? > > We do this with a recursive procedure(resolving) in our application > (sql-pass-through) > startid = 111 > depth = 1 > create a treeview node > > do resolving with startid,depth > > procedure resolving > select * from objects where headerid = startid into > objectcursor(Startid) > scan throught the result cursor (objectcursor111) > create a treeviewnode for the the first object in objectcurso111 > select * from services where objectid = resultcursor.objectid > into servicecursor > scan through service cursor > create a treeview node for each service > endscan > startid = objectcursor.objectid > do resolving with startid,depth + 1 > > endscan > endproc > > This works well with little structures(up to 500 objects with > services), > but no one wants to wait 5 minutes to resolve bigger structures. > > Is this a design error ???? > Do i need more or different indexes ???? > Can i avoid the number of select commands ???? > > > Any help or ideas welcomed > > Best regards > Albert > > ''''' > ''''''''' > (0 0) > +---------oOO-----------(_)------------------------------+ > | Tel: 0541/5841-868 | > | Fax: 0541/5841-869 | > | Mail: mailto:[EMAIL PROTECTED] | > | Internet: http://www.piepenbrock.de | > +--------------------------------------oOO---------------+ > |__|__| > || || > ooO Ooo > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
