Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting latest data

Posted on 2004-09-15
16
Medium Priority
?
349 Views
Last Modified: 2013-12-18
I need to generate a report containing latest data. Data is put into an Excel spreadsheet.

Database is medium-sized, approx 700 MB currently. About 50 forms and 100 views. It is for web but maintainance people do use Notes client.

I create a view that contains all the data I need (simple view, based on on form and a simple selection criteria). The view selects 7500 documents let us say. What will be the best approach?

Do I scroll thru using GetFirstDocument/GetNextDocument? Or should I use NotesViewEntry.Document? Its a flat view, no response documents, no totals, nothing. Please bear in mind that concurrency of data is important. Equally important is performance. This will be done many times a day, so more optimized the code the better it will be.

What is your opinion?
0
Comment
Question by:RanjeetRain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +3
16 Comments
 
LVL 24

Assisted Solution

by:HemanthaKumar
HemanthaKumar earned 160 total points
ID: 12065856
Scrolling thru the view doesn't preserve the order .. But using NotesViewNavigator does !

Use NotesViewEntry navigational methods (getnext) this is much faster than normal view navigational methods.

~Hemanth
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 120 total points
ID: 12066559
IMHO the fastest is just to copy the view and paste it into a Lotus 1-2-3 .wk4 file, and then process it in Excel. Unfortunately, this must be done at least twice (I think), because the .wk4 file cannot handle more than 4095 lines (I seem to remember; anyway, there is a limit).
0
 
LVL 2

Assisted Solution

by:kalios
kalios earned 80 total points
ID: 12067505
Hi Ranjeet,

Do you want to export all the documents into a excel spread sheet directly. I can send you the code for that.

Kalios
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 19

Author Comment

by:RanjeetRain
ID: 12068101
>> Scrolling thru the view doesn't preserve the order .. But using NotesViewNavigator does !

THat is interesting Hemantha! I am however not thinking about ordering here per se. My concern is - a view with 1000s of rows of info it it, how to export it the fastest way. I'd also like to reduce the disk I/O overhead on the server.

Sjef, this shouldn't be done manually. It would be a schduled agent that will send the report to designated people on predetermined frequency. Also, that method is guarnteed to run out of steam, as the number of rows to export will easily overwhelm the size limit of WK? file format.

Kalios, thanks for offer to help, but I am not looking for code to export a view. I have several variants of it and some are faster than others. Just wondering which is best.

My concern is optimizations since I am looking forward to export several thousand records several times a day. I wouldn't want server to respond slowly when report generation agent was running.

I was of opinion (and it has not changed so far) that NotesViewEntryCollection would give the fastest access. But there is an aspect I am not satisfied with. If I use NotesViewEntryCollection and NotesViewEntry, the data may or may not be concurrent, right? Can I gurantee that?

Please correct me if I am wrong. I am just toying with various ideas to select the best method.
0
 
LVL 3

Assisted Solution

by:Andrea Ercolino
Andrea Ercolino earned 80 total points
ID: 12068112
You could use the (new) button "Copy Selected as Table" in the Notes client, and then paste it in Excel. I don't know if it works for 7500 documents, but it does for 750.
0
 
LVL 3

Expert Comment

by:Andrea Ercolino
ID: 12068172
If you cannot use the "Copy Selected as Table" button, I think it's faster if you access data displayed in the view through the array notesDocuments.ColumnValues or the entry navigation (ss HemanthaKumar said). But it is a supposition, I have no experience.
0
 
LVL 14

Accepted Solution

by:
p_partha earned 160 total points
ID: 12068190
0
 
LVL 2

Expert Comment

by:kalios
ID: 12068939
Ranjeet,

ViewEntry does not support show multiple values as seperate entries. This is a bug !

http://www-1.ibm.com/support/docview.wss?rs=899&uid=swg21089632

If any of your view columns have this option checked then it wont work.

Also I've this in one of my db's and it works great....It exports approximately 30000 records at timely intervals and i feel it pretty fast.

Sub Initialize
     Dim s As New notesSession    
     Dim view As NotesView
     Set view = s.currentDatabase.getView("MValues")
     Set doc = view.GetFIrstDocument    
     Open "C:\bh.txt" For Output As #1  
     While Not doc Is Nothing          
          Forall x In doc.columnValues
               If Isarray(x) Then
                    Forall colmember In x
                         Print #1 , colmember;
                    End Forall
               Else
                    Print #1 , x ;
               End If
          End Forall
          Print #1 ,          
          Set doc = view.GetNextDOcument(doc)
     Wend
     Close #1
     Dim temp As Variant
     temp = Shell("C:\bhini.bat")
End Sub

Kalios
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12072815
Ranjeet,

Manually? There is a @Command([FileExport]; filetype; filename). Notes will export the current view. I just tested with the Designer Help database, it contains some 8000 documents: an export to a .wk4 file is no problem, nor is the import in Excel.

As easy as 1-2-3!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12072914
Just found that there is a maximum of 8192 documents... So please keep the # selected documents below 8192, I'd say :)

See
    http://www-912.ibm.com/s_dir/slkbase.NSF/0/fb0ffc353e7c9e218625664f00589bad?OpenDocument
0
 
LVL 19

Author Comment

by:RanjeetRain
ID: 12075066
Thanks for all the responses. But there are a few limitations.

1. Any method that puts a limit on the number of rows is out of discussion. Tomorrow it will fail even if it works today. So they are out.
2. FileExport command requires a current view. Not usable from scheducled agents.
3. Methods that rely on manual selection are out of question. They will not be useful in an scheduled agent.

I guess my best bet would be scrolling thru the NotesViewEntryCollection. This works well and i do feel its faster. But I was apprehensive about the concurrency of data. How useful is NotesView.Refresh? I read it just refreshes the view, doesn't rebuild the index. What exactly is that suposed to mean?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12075585
I heard that Notes R66 will contain addidional parameters for the [FileExport] command. Can you wait that long? ;)

Sorry, didn't see the sched agent requirement. Better next time :|
0
 
LVL 19

Author Comment

by:RanjeetRain
ID: 12076592
Partha,

I am looking at the code given in the linked post. Its looking like some clever trick. However I really have to check its performance. Do you know how it performs?

Kalios,

Your code is what is currently written and its very slow. Some agents run for over an hour. I took one of the simplest examples becasue the idea is to arrive at the best method. THere are agents that run forever during peak hours. I really need optimized code. At least it should not run slow because of poor code (we tried).

Sorry Sjef, find out something before that edition comes. Its a dull life with many agents running in the background :|

Also, what about the View refresh problem? If I have a view that is set to "Auto refresh", can I trust domino to keep the value in the view concurrent? (Please keep in mind that no one will explicitly refresh the view or rebuild the index).
0
 
LVL 19

Author Comment

by:RanjeetRain
ID: 12189053
I am still awaiting responses here. Anybody wants to throw some light on the concurrency aspect?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12189347
No cents left :(
0
 
LVL 19

Author Comment

by:RanjeetRain
ID: 12643515
Time to close this. Thanks for participating guys.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question