Ko,
Thanks for the reply. Yes, I think your ideas +Ed+ Daniel's + my selves finally helped me to create an efficient program. I will compare speed again and run more tests, but currently this program seems to work pretty fast (1 min. for 200000 records) comparing with the original implementation, which took more than 20 min. I'll see, if I can optimize it further.
Regarding CloseView program see this message: Databases,Tables, Views, Indexing and SQL syntax Re: View simple problem Thread #
439246 Message #
439698I added closing database in this function since then, but the idea remains. This is really nice piece of code, that Daniel wrote for the parsing SQL.
>Nadya,
>
>I think this is the most optimal form that can be as far as i can see it now.
>The only thing you could do is calculating how long the different steps take, so that if there is one that takes a very long time, you can try to resolve it.
>And you can look at not creating the results for the things the user don't need at that moment. (i've seen that you already did that for town and state level)
>
>I have only one simple question for now.
>What does the CloseView() function
>
>Greetings,
>
>Ko
>
>PS. Sorry for responding so late, but i had a week of tentamns.
>
>>Hi Ko,
>>
>>This is another version of this program. It works much faster (~1min for the whole county).
>>
>>********************************************************************
>>* Description.......: RunConfig - runs saved configuration
>>* Calling Samples...: RunConfig('StdTrxnSale','vAllConfigs','\redp\work\query\qry000001.dbf','mastats.dbf'
>>* Parameter List....: pcConfigName, pcConfigFile, infile_arg, outfile_arg
>>* Created by........: Nadya Nosonovsky 11/01/2000 03:27:58 PM
>>* Modified by.......: Nadya Nosonovsky 11/10/2000 05:12:54 PM
>>****************************************************************************************
>>lparameters pcConfigName, pcConfigFile, inFile_arg, outFile_arg, pcLevel, plRecalculate
>>* Validate passed parameters first
>>if empty(pcConfigName) or vartype(pcConfigName)'C'
>> pcConfigName=upper('stdTrxnSale')
>>else
>> pcConfigName=upper(pcConfigName)
>>endif
>>if empty(pcConfigFile) or vartype(pcConfigFile)'C'
>> pcConfigFile='vAllConfigs'
>>endif
>>if empty(pcLevel) or vartype(pcLevel)'C'
>> pcLevel='County'
>>else
>> pcLevel=proper(pcLevel)
>>endif
>>
>>if vartype(inFile_arg)='C' and not file(forceext(inFile_arg,'DBF'))
>> inFile_arg=getfile('DBF', '', '', 0, 'Select Input Table')
>> if empty(inFile_arg)
>> return .f.
>> endif
>>endif
>>if vartype(inFile_arg)='C' and not file(forceext(outFile_arg,'DBF'))
>> outFile_arg=getfile('DBF', '', '', 0, 'Select or Create Output Table')
>> if empty(outFile_arg)
>> return .f.
>> endif
>>endif
>>
>>local lnOldSelect
>>lnOldSelect=select()
>>if not dbused('Stats')
>> open database Stats
>>endif
>>set database to Stats
>>if used('WorkConfig')
>> use in WorkConfig
>>endif
>>use (pcConfigFile) in 0 alias WorkConfig nodata
>>select WorkConfig
>>lcConfigName=pcConfigName
>>requery()
>>if empty(inFile_arg) or vartype(inFile_arg)'C'
>> if not empty(WorkConfig.InputTble)
>> inFile_arg=alltrim(WorkConfig.InputTble)
>> else
>> inFile_arg=getfile('DBF', '', '', 0, 'Select Input Table')
>> if empty(inFile_arg)
>> return .f.
>> endif
>> endif
>>endif
>>if empty(outFile_arg) or vartype(outFile_arg)'C'
>> if not empty(WorkConfig.OutTble)
>> outFile_arg=alltrim(WorkConfig.OutTble)
>> else
>> outFile_arg=getfile('DBF', '', '', 0, 'Select or create Output Table')
>> if empty(outFile_arg)
>> return .f.
>> endif
>> endif
>>endif
>>if not file(forceext(inFile_arg,'DBF'))
>> =messagebox (inFile_arg + ' does not exist! ',48, 'Error')
>> return .f.
>>endif
>>if WorkConfig.UpdExist
>> if not file(forceext(outFile_arg,'DBF'))
>> =messagebox ('File to update: '+outFile_arg + ' does not exist! ',48, 'Error')
>> return .f.
>> endif
>>else
>> select WorkConfig
>> local lcCreateTableCommand, lnIndexLines, i, lcIndexCmd
>> lnIndexLines=alines(laIndexLines, alltrim(IndexOn))
>> lcCreateTableCommand='create table '+outFile_arg+ ' free ('
>> scan
>> lcCreateTableCommand=lcCreateTableCommand+;
>> iif(!empty(FieldName),FieldName,F_NewName)+ ;
>> space(1)+FieldType+ iif(not empty(FieldLen),'('+transform(FieldLen)+ ;
>> iif(not empty(FieldDec),','+transform(FieldDec),'')+')','') + ','
>> endscan
>> lcCreateTableCommand=left(lcCreateTableCommand,len(lcCreateTableCommand)-1)+')'
>> &lcCreateTableCommand
>> for i=1 to lnIndexLines
>> lcIndexCmd=laIndexLines[i]
>> &lcIndexCmd
>> next
>> local lcOutTble
>> lcOutTble=juststem(outFile_arg)
>> if used(lcOutTble)
>> use in (lcOutTble)
>> endif
>>endif
>>=CloseView('WorkConfig')
>>**************************************************************************************************
>>local lnSeconds, lnMinutes, lnStartTime
>>* note clock reading for generating final timing statistics
>>lnStartTime = seconds()
>>* Now let's deal with the specific configuration
>>* More general code will come later
>>if used('InputTable')
>> use in InputTable
>>endif
>>use (inFile_arg) in 0 alias InputTable
>>wait window nowait 'Wait while counting statistics...'
>>* First of all, prepare cursors of all possible periods
>>* The code bellow is created by Ed Rauh
>>local dIncDate, aDateRange[1,2], cDateToStick, cMonthPart
>>** First select * Determine min and max date (could be parameters)
>>select min(date(val(cYear),val(cMnth),1)) as dMin, ;
>> max(date(val(cYear),val(cMnth),1)) as dMax ;
>> from InputTable ;
>> into array aDateRange
>>
>>select 0
>>create cursor tblMonths (cYr C(4), ;
>>cMn C(2), cQtr C(2) )
>>
>>dIncDate = aDateRange[1,1]
>>do while dIncDate
>> cDateToStick = dtos(dIncDate)
>> cMonthPart = substr(cDateToStick,5,2)
>> insert into tblMonths values (left(cDateToStick,4), ;
>> cMonthPart, 'Q' + transform(ceiling(val(cMonthPart)/3)))
>> dIncDate = gomonth(dIncDate,1)
>>enddo
>>** End of Ed Rauh's code - cursor tblMonths is created **********
>>local lcOldSetTalk
>>lcOldSetTalk=set('talk')
>>set talk window
>>set talk on
>>
>>************ Start of Ko Wisse code ************************
>>********* 1 SQL ********************************************
>>select ccode, town, cYear, Qtr, cMnth as Period, UseGrp, ;
>> count(price) as NumSales, sum(price) as VolSales ;
>> from InputTable ;
>> group by 1, 2, 3, 5, 6 ;
>> into cursor curMnResult
>>********* 2 SQL ***************************************************
>>select ccode, town, cYear, Qtr as Period, UseGrp, ;
>> sum(NumSales) as NumSales, sum(VolSales) as VolSales ;
>> from curMnResult ;
>> group by 1, 2, 3, 4, 5 ;
>> into cursor curQtrResult
>>******** 3 SQL ****************************************************
>>******* Now prepare Year to date cursor ***************************
>>select ccode, town, cYear, cMn as Period, UseGrp, cYr+cMn as tmpOrder, ;
>> inlist(cMn,'03','06','09','12') as lQtr, cQtr as Qtr, ;
>> count(price) as YtdNumSale, sum(price) as YtdVolSale ;
>> from InputTable, tblMonths ;
>> where cYear=cYr and cMnth between '01' and cMn ;
>> group by 1, 2, 5, 6 ;
>> into cursor curYtdMnResult
>>use in tblMonths
>>******** 4 SQL ************************************************************
>>select ccode, town, cYear, Qtr as Period, UseGrp, YtdNumSale, YtdVolSale ;
>> from curYtdMnResult ;
>> where lQtr=.t. ;
>> into cursor curYtdQtrResult
>>********** Finished 4 temporary SQLs *****************************************************
>>
>>local lnTally, i
>>for i=1 to 18
>> local ('lnTally'+transform(i))
>>next
>>
>>******** Statrt creating 18 final result cursors ********************************
>>** Now prepare the final result1 (group by ccode, town, usegrp)
>>select crYtd.ccode, crYtd.town, crYtd.cYear, crYtd.Period, crYtd.UseGrp, ;
>> nvl(NumSales,0) as NumSales, nvl(VolSales,0) as VolSales, ;
>> YtdNumSale, YtdVolSale, ;
>> 0000000000 as MedSale, 0000000000 as YtdMedSale ;
>> from curYtdMnResult crYtd left join curMnResult cr ;
>> on crYtd.ccode+crYtd.town+crYtd.cYear+crYtd.Period+crYtd.UseGrp = ;
>> cr.ccode+cr.town+cr.cYear+cr.Period+cr.UseGrp ;
>> into cursor curFinalResult1
>>lnTally1=_tally
>>** Now prepare the final result2 (group by ccode, town, usegrp)
>>select crYtd.ccode, crYtd.town, crYtd.cYear, crYtd.Period, crYtd.UseGrp, ;
>> nvl(NumSales,0) as NumSales, nvl(VolSales,0) as VolSales, ;
>> YtdNumSale, YtdVolSale, ;
>> 0000000000 as MedSale, 0000000000 as YtdMedSale ;
>> from curYtdQtrResult crYtd left join curQtrResult cr ;
>> on crYtd.ccode+crYtd.town+crYtd.cYear+crYtd.Period+crYtd.UseGrp = ;
>> cr.ccode+cr.town+cr.cYear+cr.Period+cr.UseGrp ;
>> into cursor curFinalResult2
>>lnTally2=_tally
>>** Now prepare the final result3 (group by ccode, town, usegrp)
>>select ccode, town, cYear, 'YR' as Period, UseGrp, ;
>> sum(NumSales) as NumSales, sum(VolSales) as VolSales, ;
>> sum(NumSales) as YtdNumSale, sum(VolSales) as YtdVolSale, ;
>> 0000000000 as MedSale, 0000000000 as YtdMedSale ;
>> from curQtrResult ;
>> group by 1, 2, 3, 5 ;
>> into cursor curFinalResult3
>>lnTally3=_tally
>>* Close temporary cursors and no needed tables
>>use in curMnResult
>>use in curYtdMnResult
>>use in curQtrResult
>>use in curYtdQtrResult
>>
>>*****************************************************************
>>for i=4 to 6
>>** Now group by ccode, town
>> select ccode, town, cYear, Period, 'ZZZ' as UseGrp, ;
>> sum(NumSales) as NumSales, sum(VolSales+000000000000) as VolSales, ;
>> sum(YtdNumSale) as YtdNumSale, sum(YtdVolSale+000000000000) as YtdVolSale, ;
>> MedSale, YtdMedSale ;
>> from ('curFinalResult'+transform(i-3)) ;
>> group by 1 , 2, 3, 4 ;
>> into cursor ('curFinalResult'+transform(i))
>> store _tally to ('lnTally'+transform(i))
>>next
>>if pcLevel'Town'
>> for i=7 to 9
>>** Now group by ccode, UseGrp
>> select ccode, 'ZZZZ' as town, cYear, Period, UseGrp, ;
>> sum(NumSales) as NumSales, sum(VolSales+000000000000) as VolSales, ;
>> sum(YtdNumSale) as YtdNumSale, sum(YtdVolSale+000000000000) as YtdVolSale, ;
>> MedSale, YtdMedSale ;
>> from ('curFinalResult'+transform(i-6)) ;
>> group by 1,3,4,5 ;
>> into cursor ('curFinalResult'+transform(i))
>> store _tally to ('lnTally'+transform(i))
>> next
>>
>> for i=10 to 12
>>** Now group by ccode
>> select ccode, town, cYear, Period, 'ZZZ' as UseGrp, ;
>> sum(NumSales) as NumSales, sum(VolSales+000000000000) as VolSales, ;
>> sum(YtdNumSale) as YtdNumSale, sum(YtdVolSale+000000000000) as YtdVolSale, ;
>> MedSale, YtdMedSale ;
>> from ('curFinalResult'+transform(i-3)) ;
>> group by 1, 3, 4 ;
>> into cursor ('curFinalResult'+transform(i))
>> store _tally to ('lnTally'+transform(i))
>> next
>>endif
>>if pcLevel='State'
>>** Now All Towns, counties - state level - specific UseGrp
>> for i=13 to 15
>> select 'ZZ' as ccode, town, cYear, Period, UseGrp, ;
>> sum(NumSales) as NumSales, sum(VolSales+000000000000) as VolSales, ;
>> sum(YtdNumSale) as YtdNumSale, sum(YtdVolSale+000000000000) as YtdVolSale, ;
>> MedSale, YtdMedSale ;
>> from ('curFinalResult'+transform(i-6)) ;
>> group by 3, 4, 5 ;
>> into cursor ('curFinalResult'+transform(i))
>> store _tally to ('lnTally'+transform(i))
>> next
>>
>>** Now summary - state level
>> for i=16 to 18
>> select 'ZZ' as ccode, town, cYear, Period, 'ZZZ' as UseGrp, ;
>> sum(NumSales) as NumSales, sum(VolSales+000000000000) as VolSales, ;
>> sum(YtdNumSale) as YtdNumSale, sum(YtdVolSale+000000000000) as YtdVolSale, ;
>> MedSale, YtdMedSale ;
>> from ('curFinalResult'+transform(i-3)) ;
>> group by 3, 4 ;
>> into cursor ('curFinalResult'+transform(i))
>> store _tally to ('lnTally'+transform(i))
>> next
>>endif
>>
>>********** We prepared 18 Final results (each type of groupings)
>>********************************
>>
>>if lcOldSetTalk='OFF'
>> set talk off
>>endif
>>********************
>>if used('OutTable')
>> use in OutTable
>>endif
>>
>>local i, lcOldExact
>>lcOldExact=set('exact')
>>set exact off
>>
>>use (outFile_arg) in 0 alias OutTable
>>local lcSearchExpr, lnStart, lnEnd
>>do case
>>case pcLevel='Town'
>> lnStart=1
>> lnEnd=6
>>case pcLevel='County'
>> lnStart=1
>> lnEnd=12
>>case pcLevel='State'
>> lnEnd=18
>> if plRecalculate
>> lnStart=1
>> else
>> lnStart=13
>> endif
>>endcase
>>
>>local PrevOnEsc, PrevEscape, lnCount, ;
>> halt, lnUpdateNumber, msgTail, loTherm, lnCount, mnEnd, loTemp
>>
>>* support user Escapes for interrupting the main loop
>>PrevOnEsc = on('escape')
>>PrevEscape = set('escape')
>>set escape on
>>halt = .f.
>>on escape halt = .t.
>>
>>for i=lnStart to lnEnd
>> store 0 to lnCount
>> lnTally=evaluate('lnTally'+transform(i))
>> set message to 'Calculating statistics for '+transform(lnTally)+' records'
>> do case
>> case lnTally
>> lnUpdateNumber=1
>> case between(lnTally,100,10000)
>> lnUpdateNumber=100
>> case lnTally>10000
>> lnUpdateNumber=val('1'+replicate('0',len(transform(lnTally))-3))
>> endcase
>>* assemble fixed portion of status bar message outside of loop, for speed
>> msgTail = "/" + transform(lnTally) + ". Wait or press Esc to cancel ..."
>>
>>*--- instantiate thermometer bar class....
>> loTherm = newobject("thermometer", "wg.vcx","","Calculation Progress for: "+dbf('OutTable'),lnTally)
>> loTherm.show()
>> select InputTable
>> set order to (tag(i))
>> select ('curFinalResult'+transform(i))
>> scan
>> lnCount=lnCount+1
>>* check for user Escape
>> if m.halt
>> exit
>> endif
>>
>>** Update status message
>> if mod(lnCount,100) = 0
>> set message to 'Record # '+transform(lnCount)+m.msgTail
>> endif
>>
>>** Update thermometr
>> if mod(lnCount,lnUpdateNumber) = 0
>> loTherm.update(lnCount)
>> endif
>>
>> scatter name loTemp
>>
>>******** First let's deal with Median ***********************
>> do case
>> case loTemp.NumSales=0
>> loTemp.MedSale=0
>> case loTemp.NumSales=1
>> loTemp.MedSale=loTemp.VolSales
>> case loTemp.NumSales=2
>> loTemp.MedSale=round(loTemp.VolSales/2,0)
>> otherwise
>> lcSearchExpr=iif(loTemp.ccode='ZZ','',loTemp.ccode)+ ;
>> iif(loTemp.town='ZZZZ','',loTemp.town) + ;
>> iif(loTemp.UseGrp='ZZZ','',loTemp.UseGrp)+ ;
>> loTemp.cYear+ ;
>> iif(loTemp.Period='YR','',loTemp.Period)
>> loTemp.MedSale=GetMedian('InputTable','price',loTemp.NumSales,lcSearchExpr)
>> endcase
>>** Let's now deal with YTD Median
>> do case
>> case loTemp.NumSales=loTemp.YtdNumSale
>> loTemp.YtdMedSale=loTemp.MedSale
>> case loTemp.YtdNumSale=1
>> loTemp.YtdMedSale=loTemp.YtdVolSale
>> case loTemp.YtdNumSale=2
>> loTemp.YtdMedSale=round(loTemp.YtdVolSale/2,0)
>> otherwise
>> lcSearchExpr=iif(loTemp.ccode='ZZ','',loTemp.ccode)+ ;
>> iif(loTemp.town='ZZZZ','',loTemp.town) + ;
>> iif(loTemp.UseGrp='ZZZ','',loTemp.UseGrp)+ ;
>> loTemp.cYear
>> loTemp.YtdMedSale=GetMedian('InputTable','price',loTemp.YtdNumSale,lcSearchExpr)
>> endcase
>> lcSearchExpr=loTemp.ccode+loTemp.town+loTemp.cYear+loTemp.Period+loTemp.UseGrp
>>***********************************************************
>> select OutTable
>> if tagno('Geo')>0 and seek(lcSearchExpr,'OutTable','Geo')
>>** Now we are on the right record
>> else
>> append blank
>> endif
>> gather name loTemp
>> select ('curFinalResult'+transform(i))
>> endscan
>> use in ('curFinalResult'+transform(i))
>> wait clear
>> release loTherm
>>next
>>** Restore previous settings
>>on escape &PrevOnEsc
>>set escape &PrevEscape
>>set message to
>>* Close everything, which is not longer needed
>>
>>use in InputTable
>>use in OutTable
>>for i=1 to 18
>> if used('curFinalResult'+transform(i))
>> use in ('curFinalResult'+transform(i))
>> endif
>>next
>>** Determine elapsed time
>>mnEnd = seconds()
>>lnSeconds=mnEnd - lnStartTime
>>lnMinutes=int(lnSeconds/60)
>>lnSeconds=round(lnSeconds-lnMinutes*60,0)
>>wait "Elapsed time for calculating statistics is " + ;
>> iif(lnMinutes>0, transform(lnMinutes)+" min. ","")+;
>> transform(lnSeconds) + " sec." window nowait
>>return>>
>>>Nadya,
>>>
>>>Looks fine, but you forgat to update the time stamp <g>
>>>
>>>
>>>
>>>Greetings,
>>>
>>>Ko ;-)
>>>
>>>>Ko,
>>>>
>>>>I added unions and it works like a charm. I was incorrect in my assumption on maximum number of unions. The code is bellow:
>>>>>>>>********************************************************************
>>>>* Description.......: RunConfig - runs saved configuration
>>>>* Calling Samples...: RunConfig('StdTrxnSale','vAllConfigs','\redp\work\query\qry000001.dbf','mastats.dbf'
>>>>* Parameter List....: pcConfigName, pcConfigFile, infile_arg, outfile_arg
>>>>* Created by........: Nadya Nosonovsky 11/01/2000 03:27:58 PM
>>>>* Modified by.......: Nadya Nosonovsky 11/01/2000 04:16:06 PM
>>>>********************************************************************
If it's not broken, fix it until it is.
My Blog 
