I have a loop over a view that has a few loops on nested views. The main loop reads a record and write the contents to a queue. For each record read two procedure are called to do a loop over a view to process the child records of the table in the main loop.
Although all these views only read (simple) fields and assign them to a queue processing those nested views cost a lot of time. To read 500 records (main table) cost approximately 3 seconds which seems to me is long. Most records from the main have 0 to 3 child records for each child view.
What is interesting is that the child view with the attachments is the most time consuming one. These attachments are BLOB’s in the table but I didn’t define the BLOB fields in the view structure, only the name of the attachment. So I would expect that reading that table wouldn’t be delayed.
The database is MS SQL and all the views do have an index with include fields.
The code looks like this:
OPEN(ViewLines)
BUFFER(ViewLines,250)
ViewLines{PROP:Order} = 'LIN:HeaderID,LIN:Sequence'
ViewLines{PROP:Filter} = 'LIN:HeaderID = ' & HEA:HeaderID
ViewLines{PROP:NoKeyProject} = True
SET(ViewLines)
LOOP
NEXT(ViewLines)
IF ERRORCODE() THEN BREAK .
! Code for reading fields and accessing them to queue fields
FillNoticeList(OBJ:ObjectID)
FillAttachmentList(OBJ:ObjectID)
END
CLOSE(ViewLines)
FillAttachmentList PROCEDURE(LONG pObjectID)
CODE
OPEN(ViewAttachments)
BUFFER(ViewAttachments,50)
ViewAttachments{PROP:Order} = 'ATT:ObjectID,ATT:Name'
ViewAttachments{PROP:Filter} = 'ATT:ObjectID = ' & pObjectID
ViewAttachments{PROP:NoKeyProject} = True
SET(ViewAttachments)
LOOP
NEXT(ViewAttachments)
IF ERRORCODE() THEN BREAK .
IF ATT:ObjectID <> pObjectID THEN BREAK .
! Code for reading fields and accessing them to queue fields
! BLOB itsself is not in the view
END
CLOSE(ViewAttachments)
FillNoticeList PROCEDURE(LONG pObjectID)
! Etc.
! Code for reading fields and accessing them to queue fields
END
I have few questions:
Is there a way to speed up this whole process?
How is it possible that a loop over a view is slow when its corresponding table has a BLOB fields but those BLOB fields are not part of the view structure?
The BLOB question is a red herring - this has nothing to do with the Blobs. The code is slow because your approach is ineffecient.
Let’s say you have 100 ViewLines. For each ViewLine you are opening, preparing, reading, closing 200 more VIEWs. This is a lot of work.
A much better approach would be to JOIN the Attachments and Notices to the main view. Let the server do the work of amalgamating them all together. If you do this I think you’ll find the result is much faster.
You don’t really describe your queues structure - I’m not sure if you have a queues-in-queues thing going on here, or whether there is one queue with everything, or 3 queues or something else?
I realise that my code is inefficient but I don’t know how to handle it better. Handling a view with related tables where the relationship is many to one is not a problem.
But handling child tables I have no idea how to do that in a view. Especially when there are multiple child tables for each record in the primary table. And to make it more complex one of these child tables has its own child tables.
Simplified the view structure is like:
ViewLines VIEW(Lines)
PROJECT(LIN:LineID)
PROJECT(LIN:Sequence)
PROJECT(LIN:ObjectID)
PROJECT(LIN:Etc)
JOIN(OBJ:OBJ_pkey,LIN:ObjectID)
PROJECT(OBJ:Name)
PROJECT(OBJ:ObjectID)
PROJECT(OBJ:AreaID)
JOIN(ARE:ARE_pkey,OBJ:AreaID)
PROJECT(ARE:Name)
PROJECT(ARE:AreaID)
END
END
END
ViewAttachments VIEW(Attachments)
PROJECT(ATT:AttachmentID)
PROJECT(ATT:ObjectID) ! Foreign key
PROJECT(ATT:Name)
END
ViewNotices VIEW(Notices) ! Has its own child table (Responses)
PROJECT(NOT:NoticeID)
PROJECT(NOT:Notice)
PROJECT(NOT:Date)
PROJECT(NOT:ObjectID) ! Foreign key
END
ViewResponses VIEW(Response)
PROJECT(RES:ResponseID)
PROJECT(RES:NoticeID) ! Foreign key
PROJECT(RES:Response)
END
Yes there are nested queues up to three levels deep. Ultimately this exported to a JSON structure as a response on the webserver.
Finally there is also last reason to work with multiple views: Keeping oversight what am I doing on what level.
View(parent)
Project(whatever)
Join(first child)
Project(some more)
Join(some other child)
Project some more
End
End
Join(second child of parent)
Project some more
End
End
The view structure I see, just the same way as for on to many relations.
Looping through the parent table I don’t see yet. The same record in the parent table might appear multiple times in this way with different values for the columns of the child tables. So that will start a secondary loop over all the records with the same parentID. For one child record I see how that would work, for multiple child records not yet.
I will start with seeing how the result set looks with this new view structure. I always avoided such select statements.
Lost the response I was working on, so this is a bit terser – I agee with Bruce that your approach is very inefficient:
Not sure that a single clarion view will work. For example an object has 15 responses and 3 attachments, you get 45 rows.
You could create a temporary table on the server with your lines, and import that into your dictionary. Then you can get your notices/responses and your attachments by joining that temporary table to your other tables. That’s three queries total instead of one for the lines and two for every line after that.
Maybe look at tools MSSQL provides. There is a FOR JSON clause of a select statement. I have done a similar approach in Oracle for XML. I had a procedure on the server that wrote the entire XML (the Clarion DCTX) into a CLOB, then all I had to do in my program was write the CLOB into a file.
correct. However jFiles should parse this down to a tree with 1 parent, 15 reponses and 3 attachments. the Clarion VIEW “flattens” the result set, but jFiles parses it back into a tree.
Not sure if I understand you completely. Defining that temporary table and creating relations in the dictionary I get. But then I miss the next step I am afraid. How can I from there loop through notices and responses pro lines record?
However you triggered me. That temporary table is a kind of in memory table on the server. Well I can work with in memory tables for all my child tables. And then instead of a loop over a view I do set and loop over the IM table for each Line record.
I know that jFiles can do that. But that is not working in my situation. It is not just a dump of all the fields. For example, based in a value in the view NULL must be set in a JSON object. So I am stuck build the whole nested structure of queues step by step.
insert into MyLines select * from wherever. MyLines is also set up as a table in your dictionary.
Then you create a view based on MyLines and Attachments, lets call it ViewAttach with whatever columns you need.
Then you
set(viewattach)
loop
if errorcode then break. !go to the end of the file
<transfer ViewAttach columns into queue, and add>
end ! loop
That gives you the entire queue of attachments you need (instead of adding the Attachment rows one Line object at a time). And a second view and loop for the notices/responses
