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]
