Beermann, Albert wrote:
>
> 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
>
Hi,
Why do you call the dbproc recursively?
A recursive select like this:
DECLARE C CURSOR FOR
WITH RECURSIVE PX (level, m_headerid, m_objected) AS
(SELECT 1, headerid, objectid FROM objects WHERE headerid = startid
UNION ALL
SELECT level + 1, headerid, objectid FROM objects, PX
WHERE m_objectid = headerid)
SELECT level, objects.*, services.* /* '*' for both will not work
because of duplicate columnnames, but to show that here the full
records should be fetched, not within the recursion */
From px, objects, services
Where m_objectid = objects.objectid
And m_objectid = services.objectid
Order by level, objects.objectid
Should a) work and b) do the job faster than with all the intermediate
communication and start of new cursors as you use nowadays.
Elke
SAP Labs Berlin
> 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]
