Hi all,

My application is coming along at record speed (mainly because it has to now), and I've begun importing and testing my data.

I'm running my test on a standalone P4 2.80 GHz with 1.0 MB of Ram on Windows XP.  I've imported 85,000 records to play with.

One thing I'm noticing is that upon starting Sesame, it has to load the database which takes about 60 seconds -- no problem.

The problem I am having is I have a retrieve spec that pulls files diarized for "=;<{@date};{@date}", and looks in the staffid field for "{@userid}", and on a radio list box for records marked "active".  Currently to pull my 200 records for the day's work out of the 85,000 that are there the retrieve takes about 3-5 minutes.  That's really long -- especially considering when I'm done I'll be importing over 300,000 records.  Searching by name or client number is much faster - 5 to 8 seconds.

I'm finding that searching by a specific userid (ie "BW") is much faster than userid "{@userid}".  Is there something I can do to work around this bog-down?

Hi Erika!

That will definitely fix my problem -- I've tried the same search and sort with non-programmed retrieve specs, and it's 10-15 seconds!  Now, can you point me in the right direction for programming ...

Right now I have a command button that executes the retrieve and sort specs, which are saved.  The code is below.  Where in the program can I set conditions on Retreive Spec Open?

[code]
var vButtonOn as int

vButtonOn = @SelectTreeItem("Search Menu!Spec Manager (Alt-F8)")
vButtonOn = @LoadRetrieveSpec("Todays Files")
vBUttonOn = @LoadSortSpec("Todays Files")
vButtonOn = @SelectTreeItem("Search Menu!Search Commands!Retrieve New Results (F10)")
[/code]

Hi Ray,

I tried that, and it *mostly* worked.  It doesn't seem to like putting a string of "<={@date} into the followup field - is this because it is non-date data being put into a date field?

That did it!  I'm back to 5-10 second searches in 85,000 records!  Whoo!

Next speed/functionality question.  On Form Entry, I'm using an @XLookupSourceList to pull client information, such as client name, etc.  This @XlookupSourceList on entry takes about 5 seconds.  Is there a way to speed that up?  Is there a better "live" linking method to get one database to talk to another?

It is the first X application to run ... I haven't added any others as yet.  I'm looking to walk, then run.

Here's my code on form entry

[code]
var vCltData as string
var vCount as int
var vRecord as int
var vPdHere as double
var vPdDir as double
var vPdHereInt as double
var vPdDirInt as double
var vIntDeferred as int

// Restrict field security by @Group and gray out restricted fields - BW 2005-10-19

if @group = "Users" then
{
     Readonly(DBName,2)
     RGBColor(DBName,-1,-1,-1,204,204,204)
     Readonly(DBColl#,2)
     RGBColor(DBColl#,-1,-1,-1,204,204,204)
     Readonly(DBTrac#,2)
     RGBColor(DBTrac#,-1,-1,-1,204,204,204)
     Readonly(DBLgl#,2)      
     RGBColor(DBLgl#,-1,-1,-1,204,204,204)
     Readonly(DBComRate,2)
     RGBColor(DBComRate,-1,-1,-1,204,204,204)
     Readonly(DBListed,2)
     RGBColor(DBListed,-1,-1,-1,204,204,204)
     Readonly(DBList,2)
     RGBColor(DBList,-1,-1,-1,204,204,204)
}

if @group = "Supervisors" or @Group = "Management" then
{
     ReadOnly(DBColl#,0)
     RGBColor(DBColl#,-1,-1,-1,255,255,255)
}

// Pull Up Client Data

if @Mode() = 1 then
{
     vCltData = (@Xlookupsourcelist(@FN, DBCltNo, "Sales Screen!CltNo", "CltName;CltSales;CltInstr;CltIntRate;CltIntDeferred"))
     if DBClt <> @AccessStringArray(vCltData,1) then DBClt = @AccessStringArray(vCltData,1)
     if DBSales <> @AccessStringArray(vCltData,2) then DBSales = @AccessstringArray(vCltData,2)
     if DBCltInstr <> @AccessStringArray(vCltData,3) then DBCltInstr = @AccessStringArray(vCltData,3)
     if DBIntRate <> @AccessStringArray(vCltData,4) then DBIntRate = @AccessStringArray(vCltData,4)
     vIntDeferred = @AccessStringArray(vCltData,5)
}

// If Interest is deferred, only allow it if the file is over 6 months old

if vIntDeferred = 1 and @date-DBListed < 180 then DBIntRate = 0

// Default unassigned files to Active status

IF @IsBlank(DBQueue) THEN
{
     DBQueue = DBActive
}

// Give Total Collected

vRecord = 1

vCount = @FormResultSetTotal("Pmt Data")

if vCount >0 then
{
     While vRecord <= vCount
     {
           // Loop through subrecords adding up totals
           vPdHere = vPdHere + @TM(@FormFieldValue("Pmt Data","PdHere",vRecord))
           vPdHereInt = vPdHereInt + (@TM(@FormFieldValue("Pmt Data","PdHere",vRecord))*@Exp(1+DBIntRate,((@date-(@todate(@formFieldValue("Pmt Data","LE0",vRecord))))/365)))
           vPdDir = vPdDir + @TM(@FormFieldValue("Pmt Data","PdDir",vRecord))
           vPdDirInt = vPdDirInt + (@TM(@FormFieldValue("Pmt Data","PdDir",vRecord))*@Exp(1+DBIntRate,((@date-(@todate(@formFieldValue("Pmt Data","LE0",vRecord))))/365)))
//            vPdDirInt = vPdDirInt + (@TM(@FormFieldValue("Pmt Data","PdDir",vRecord))*@Exp(1+DBIntRate,((@date-DBListed)/365)))
           vRecord = vRecord +1
     }
     // Place Collected Tally on Form
     DBPd = vPdHere + vPDDir
}

// Work out Balance with interest

DBBal = (DBList * @Exp(1+DBIntRate,((@date-DBListed)/365))) - vPdHereInt - vPdDirInt

ThrowFocus(DBNote0)

Clear(DBNote0)
[/code]