Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Code & Utilities

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 2nd November 2006, 14:58
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
Smile performance dll

Certain programs typically process or print sessions sometimes take inexplicably long time . In order to improve such program a genric performance dll code is written.The performance improvement can be as high as 90% .

How does one identify such programs where the performance dll can be used .

1. When such programs are run , other users too experience slow system.
2. In the process / task manager on server , CPU and memory utilization shoots for the user running the program.
3. Data base drivers eats up substantial portion of the program . This happens due to repeated read / update on the certain transaction tables in the program , causing lot heavy but slow IO algorithms .
4. Such program runs for very long hours.

Typical characteristic of such program is that these programs require mainly sorting , grouping and aggregation operation .

And for that purpose it relies heavily on repeated IO operation by lot of selects on transaction tables and lot of rprt_send(), report sorting and printing . It does not uses very fast memory operations by using dyanamic arrays . Using dynamic arrays in such a program , performance can improve drastically.

What contributes most to the performance improvement through the use of this dll .

1. There should be clear knowledge of the business requirement & of the data structures involved .

2. The possible option for grouping using various sort fields should be explored to use dynamic array . The possible array
length and length of the group variable (along with possible numeric index included therein) must be known . This will give
rough idea of maximum array size in runtime . Though system allows an array size of 5 MB , goal should be to minimize the
max array size in bytes to lower value say 1 MB to allow for future expansions and to gain maximum out of the performance
improvement.

3. Substantial performance gains comes from shifting disk io operations to memory io . Disk IO can be mainly
heavy repeated database driver selects on big transaction table, heavy , expensive & repeated database driver updates on
big transaction table , lot of rprt_sends, sorting and reading of tmp report file and actual generation of report file .

4. Reducing the select / update jobs on big transaction table .e.g. 1000 select jobs on indexed table returning 1 row at a
time is costlier than 1 select job returning the same 1000 rows .


As an example if a program is doing grouping on
1. division (enum), converted 3 chr string
2. date (date) converted to 6 chr l string
3. workcenter (3 character string )
4. Year/Month (year*100 + Month - long converted to 6 chr string )
Integers , enums and date fields also can be used in the grouping strings.

Say a program running the same can have maximum 11 division * 31 days * 20 Work Center * 1 Year month at a time= 6820 combination .
So maximum size of a dynmic string array with an index for accumulators can be division 3 + date 6 + workcenter 3 +
yearmonth 6 + index 5 character long = 23 * 6820 = 156860 bytes = 0.15 MB . So this can be technically viable case for
performance improvement in circumstances described above . In runtime the array size could be lower depending on the runtime
data.

There may be 2 quanity to aggregate
1. Loss
2. Weight

So 2 separate based double variable array required to keep track of double values .

An example how the dll functions are to be called and the actual dll code is attached .

This dll can be used from Baan IV to ERP LN - all version . Earlier versions need to create an include for this dll and they can use the same in earlier versions also .

Do post the the results with array memory size , accumulators , data statistics (like distinct groups / total records ), database driver , and kind of disk IO shifted to the memory grouping so that others too can benefit from such information .
Attached Files
File Type: txt performance dll.txt (7.6 KB, 574 views)
File Type: txt program example.txt (2.8 KB, 517 views)
Reply With Quote
  #2  
Old 12th November 2006, 17:33
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
Data transformation perspective

The dll has been uploaded with minor correction , enhancements and proper disclaimers .

Also the summaries through this dll are speedier than the summaries through disk based Baan report . It's elaborated with an example in the word doc attached .

Also user need to do proper memory calculation before using this dll . So an easy to use excel calculator is uploaded with illustrations and certain requisite editable cells .

I have certain ideas to make this dll more general . But will take some time to put forward . In the meantime , wish some other developers make some contributions to make it more general , it can be great .

Happy speeding .
Attached Files
File Type: txt performance dll.txt (9.0 KB, 223 views)
File Type: doc PERFORMANCE DLL writeup.doc (52.0 KB, 224 views)
File Type: xls memory manipulations.xls (19.0 KB, 211 views)
Reply With Quote
  #3  
Old 19th December 2006, 15:22
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
top down grouping code

Another extra function to do top down grouping in the dll for memory based operations at the speed of light .

Code:
function extern long top.down.grouping(ref domain tcmcs.s256 grpstr(),ref double accumulator(),
domain tcbool ascend , long numofelmt ,long search_len , long grplen)
{
	|PURPOSE 
	|After the summary values are calculated to find  number of top / down distinct elements with
	|value of accumulator based on a particular accumulator
	
	|WHEN TO CALL THIS FUNCTION
	|After the group string is sorted and accumulated values come in accumulators and there is no data
	|remaining to be grouped , this function should be called to sort double qty in ascending or descending order .
	|Then for-endfor loop should be run for 1 to top.down number 
	|After the execution of the function accumulator values will be sorted and grpstr elements too will 
	|change in accordance with accumulator element value .
	
	|VARIABLE ARGUMENTS
	|grpstr - sorted string array containing finite number (numofelmt) of  unique distict strings upto first
	|search_len characters and after that index value pointing do double index. In summary , this function can be used 
	|easily in conjuction with other functions in this dll .
	|accumulator - double array containing accumulated values corrsponding index values in grpstr array 
	|		for which top down is required
	|ascend - Whether to sort the double in ascending (true)  or descending  (false)
	|numofelmt - Number of elements  in the string / double array 
	|search_len --> length of  string to search excluding characters for index fixed through out 
	|	the program . Index value in grpstr starts after search_len 
	|grplen --> length of  grpstr single element  to search including  characters for index - fixed through out 
	|	the program 
	
	|RETURN VALUE 
	| return -1 for unsuccessful operation and 0 for succsessful top down grouping 
	|accumulator -- will contain numerically sorted values
	|grpstr - this array will be in accordance with accumulator order (ie it may  become unsorted 
	|alphanumerically )
	
	long dblstrlen , arrctr ,dblindex , srcpos,tmpnum

	if  alloc.mem(topdown,2,numofelmt) < 0 then
		message("Insufficient memory, Can not go ahead")
		free.mem(topdown)
		stop()      
	endif
	if set.mem(topdown,0) < 0 then
		message("Error initializnig memory")
		return(-1)
	endif
	copy.mem(topdown,accumulator)
	for arrctr = 1 to numofelmt
		dblindex = lval(grpstr(search_len +1,arrctr))
		| dblstr(1,dblindex) = edit$(  accumulator(dblindex),dblformat ) & 
				| edit$(arrctr,idxformat )
		topdown(2,dblindex) = arrctr		
	endfor
	qss.start(search_criteria,1,1)
	qss.type(search_criteria,1,db.double)
	if ascend = true then
		qss.way(search_criteria,1,qss.up)
	else
		qss.way(search_criteria,1,qss.down)
	endif
 
	e = qss.sort(accumulator,search_criteria)
	if e < 0 then
		on case e
		case -1:
		case -11:
			message("Definition not of type long or table not array")
			break
		case -13:
			message("Depth must be positive")
			break
		case -14:
			message("Def argnot declared correctly")
			break
		case -15:
			message("Search argument does not fit ")
			break
		case -16:
			message("QSS.Type not correct")
			break
		case -17:
			message("Table arg not correct  ")
			break
		case -18:
			message("No definition found in def ")
			break
		endcase
		return(-1)
	endif
	if  alloc.mem(tmpstr ,  grplen) < 0 then
		message("Insufficient memory, Can not go ahead")
		free.mem(tmpstr)
		stop()      
	endif	
	for arrctr = 1 to numofelmt
		dblindex = topdown(2,arrctr)
		| dblstr(1,dblindex) = edit$(  accumulator(dblindex),dblformat ) & 
				| edit$(arrctr,idxformat )
		srcpos = qss.search(qss.equal + qss.src.is.sorted+qss.src.dupl.allowed ,topdown(1,arrctr) , accumulator,search_criteria)
		on case srcpos 
		case -1:
			message("Data error !  Can not go ahead")
			return(-1)
			break
		case -11:
		case -12:
		case -13:
		case -14:
		case -15:
		case -16:
		case -17:
		case -18:
		case -21:
		case -22:
		case -23:
			message("QSS.SEARCH error %d" , srcpos)
			return(-1)
			break
		default:
			tmpstr = grpstr(1,srcpos)
			grpstr(1,srcpos) = grpstr(1,dblindex)
			grpstr(1,dblindex) = tmpstr
			topdown(2, lval(grpstr(search_len +1,dblindex) ))=dblindex
			topdown(2,lval(grpstr(search_len +1,srcpos) )) = srcpos
			break
		endcase		
	endfor	
	free.mem(topdown)
	free.mem(tmpstr)	
	return(0)
}

Last edited by ~Vamsi : 30th October 2007 at 18:37.
Reply With Quote
Sponsored Links
  #4  
Old 17th January 2007, 06:25
mbdave mbdave is offline
Member
 
Join Date: Oct 2005
Location: Mumbai
Posts: 92
mbdave is on a distinguished road
Baan: BaaN4GL(4),BaaN ERP(5),BaaN LN(6) - DB: Bisam,SQL2000 - OS: Sun Solaris,SQL2003
Great work sir

Great work Sir
__________________
Thanks and Regards
Mahesh B D
Baan Professional
Reply With Quote
  #5  
Old 17th January 2007, 15:52
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
Thanks

Thanks Mahesh for the feedback .

There is still something more needed in this dll so that it's usability and adaptability increases substantially. Developer still has to spend some effort to use memory based grouping as against disk based grouping . It must be capable of being done easily , quickly and accurately .
Reply With Quote
  #6  
Old 30th October 2007, 16:26
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
code generator

Speed is very important in data transformation . And in-memory data analysis is a major criteria in evaluation criteria of BI Vendors . In Baan QSS functions are the vehicle for enabling one to do in-memory data transformations very fast. The performance dll written through this thread brings a higher level generalization in data transformation hiding the complexities of qss functions .

Still the developer has to take care off lot off things like calculating array length , encoding and decoding of string for array , error handling , taking care off data specific issues etc . Even these complexities have been taken care off through a code generator which one can download . There is a good report which can tell user which all reports can be imporved with use of this code generator.

Documented memory limit for an array is 5 MB . Though array variables of higher size can work in many OS and later porting sets , this is a very high limit and good error handling to take care of it . In fact one can also say if the array is higher than 5 MB, it's not a good case for summary reporting .

Last edited by Hitesh Shah : 3rd May 2008 at 14:33. Reason: missing link corrected
Reply With Quote
  #7  
Old 31st October 2007, 16:23
steventay steventay is offline
Senior Member
 
Join Date: Jul 2007
Posts: 173
steventay is on a distinguished road
Baan: Baan IVC4 SP10 - DB: SQL 2005 SP2 - OS: Windows 2003 SP2
Quote:
Originally Posted by Hitesh Shah View Post
Another extra function to do top down grouping in the dll for memory based operations at the speed of light .

Code:
function extern long top.down.grouping(ref domain tcmcs.s256 grpstr(),ref double accumulator(),
domain tcbool ascend , long numofelmt ,long search_len , long grplen)
{
	|PURPOSE 
	|After the summary values are calculated to find  number of top / down distinct elements with
	|value of accumulator based on a particular accumulator
	
	|WHEN TO CALL THIS FUNCTION
	|After the group string is sorted and accumulated values come in accumulators and there is no data
	|remaining to be grouped , this function should be called to sort double qty in ascending or descending order .
	|Then for-endfor loop should be run for 1 to top.down number 
	|After the execution of the function accumulator values will be sorted and grpstr elements too will 
	|change in accordance with accumulator element value .
	
	|VARIABLE ARGUMENTS
	|grpstr - sorted string array containing finite number (numofelmt) of  unique distict strings upto first
	|search_len characters and after that index value pointing do double index. In summary , this function can be used 
	|easily in conjuction with other functions in this dll .
	|accumulator - double array containing accumulated values corrsponding index values in grpstr array 
	|		for which top down is required
	|ascend - Whether to sort the double in ascending (true)  or descending  (false)
	|numofelmt - Number of elements  in the string / double array 
	|search_len --> length of  string to search excluding characters for index fixed through out 
	|	the program . Index value in grpstr starts after search_len 
	|grplen --> length of  grpstr single element  to search including  characters for index - fixed through out 
	|	the program 
	
	|RETURN VALUE 
	| return -1 for unsuccessful operation and 0 for succsessful top down grouping 
	|accumulator -- will contain numerically sorted values
	|grpstr - this array will be in accordance with accumulator order (ie it may  become unsorted 
	|alphanumerically )
	
	long dblstrlen , arrctr ,dblindex , srcpos,tmpnum

	if  alloc.mem(topdown,2,numofelmt) < 0 then
		message("Insufficient memory, Can not go ahead")
		free.mem(topdown)
		stop()      
	endif
	if set.mem(topdown,0) < 0 then
		message("Error initializnig memory")
		return(-1)
	endif
	copy.mem(topdown,accumulator)
	for arrctr = 1 to numofelmt
		dblindex = lval(grpstr(search_len +1,arrctr))
		| dblstr(1,dblindex) = edit$(  accumulator(dblindex),dblformat ) & 
				| edit$(arrctr,idxformat )
		topdown(2,dblindex) = arrctr		
	endfor
	qss.start(search_criteria,1,1)
	qss.type(search_criteria,1,db.double)
	if ascend = true then
		qss.way(search_criteria,1,qss.up)
	else
		qss.way(search_criteria,1,qss.down)
	endif
 
	e = qss.sort(accumulator,search_criteria)
	if e < 0 then
		on case e
		case -1:
		case -11:
			message("Definition not of type long or table not array")
			break
		case -13:
			message("Depth must be positive")
			break
		case -14:
			message("Def argnot declared correctly")
			break
		case -15:
			message("Search argument does not fit ")
			break
		case -16:
			message("QSS.Type not correct")
			break
		case -17:
			message("Table arg not correct  ")
			break
		case -18:
			message("No definition found in def ")
			break
		endcase
		return(-1)
	endif
	if  alloc.mem(tmpstr ,  grplen) < 0 then
		message("Insufficient memory, Can not go ahead")
		free.mem(tmpstr)
		stop()      
	endif	
	for arrctr = 1 to numofelmt
		dblindex = topdown(2,arrctr)
		| dblstr(1,dblindex) = edit$(  accumulator(dblindex),dblformat ) & 
				| edit$(arrctr,idxformat )
		srcpos = qss.search(qss.equal + qss.src.is.sorted+qss.src.dupl.allowed ,topdown(1,arrctr) , accumulator,search_criteria)
		on case srcpos 
		case -1:
			message("Data error !  Can not go ahead")
			return(-1)
			break
		case -11:
		case -12:
		case -13:
		case -14:
		case -15:
		case -16:
		case -17:
		case -18:
		case -21:
		case -22:
		case -23:
			message("QSS.SEARCH error %d" , srcpos)
			return(-1)
			break
		default:
			tmpstr = grpstr(1,srcpos)
			grpstr(1,srcpos) = grpstr(1,dblindex)
			grpstr(1,dblindex) = tmpstr
			topdown(2, lval(grpstr(search_len +1,dblindex) ))=dblindex
			topdown(2,lval(grpstr(search_len +1,srcpos) )) = srcpos
			break
		endcase		
	endfor	
	free.mem(topdown)
	free.mem(tmpstr)	
	return(0)
}


do i add the "function extern long top.down.grouping(ref domain tcmcs...." inside the performance dll.txt at the bottom?

i am new in Baan, how do i add this function to my baan server?
Reply With Quote
  #8  
Old 1st November 2007, 13:41
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
create dll

U need to create a dll in ttadv2131m000 with performance dll.txt and append the function text in the dll . The dll should be compiled and used in used in the program with statement #pragma used dll dll_name" .

If u r new to baan programming , I would recommend u to get familiar with Baan arrays , data types conversions in Baan ,qss functions, reporting structure and the circumstances in which this dll can help improve speed(
This dll is not grand panacea for all kinds of performance issues ) .
Reply With Quote
  #9  
Old 5th November 2007, 10:44
klixy23's Avatar
klixy23 klixy23 is offline
Senior Member
 
Join Date: Feb 2002
Location: Germany
Posts: 116
klixy23 is on a distinguished road
Baan: BaaN IVc4 - DB: Informix - OS: Windows Sever 2003
I try to compile the dll and missing the declaration of topdown and tmpstr. Where should declared these variables ?

Have a nice day!
__________________
Member of Baan Board

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #10  
Old 5th November 2007, 10:58
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
declare in the dll

Code:
string		tmpstr(1)  based
double 	topdown(1,1)  based |2 dimensional array for top down grouping 

Last edited by Hitesh Shah : 5th November 2007 at 12:55.
Reply With Quote
  #11  
Old 2nd March 2009, 07:08
NirajKakodkar's Avatar
NirajKakodkar NirajKakodkar is offline
Guru
 
Join Date: May 2006
Location: Finland
Posts: 339
NirajKakodkar is on a distinguished road
Baan: BAAN4C4 , BAAN 5c , LN - DB: MS SQL SERVER, ORACLE, Informix, HP Unix, AIX - OS: Windows, UNIX
Thumbs up

Really great work , I think I am really late to check out this post .
I will try out the dll .

Regards,
Niraj
__________________

Niraj N. S. Kakodkar |Linkedin
Reply With Quote
  #12  
Old 2nd March 2009, 13:29
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
Smile Thanks

Thanks. U got some time to touch to this thread finally .

Do post ur feedback after testing (for the benefit of others on this ) with enhancements if any.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Use of performance boosters - errors... r_nagu Tools Administration & Installation 17 17th August 2004 17:24
Passing Record Buffer to a DLL shaboo Tools Development 3 14th July 2004 10:30
Performance Indicator itconsultant DEM & Workflow 2 22nd October 2002 05:44


All times are GMT +2. The time now is 07:57.


©2001-2017 - Baanboard.com - Baanforums.com