Hi all, I have created a program what calculating quantites based on company independent View of Inventtrans. This calculation is extremly slow. The number of records over 7million. While the program is running the AOS and SQL server loads are only about 15% or less. We did our best to optimise our code but the speed is still unacceptable. there are tons of questions we had no answare. But what we can see if we run our code via multiple clients the result is much faster. Is there any possibility to run threads, or multiple batches on 1 client? Our customer want to run this job in every 2-3 days. But if they Start the processing on 35k item and on a half year interval then this code not finishing on 12 hour:( Any idea? Thx, Gyorgy Racz
Any of you Know any backdraw of the threads in Axapta?
Szia György, by now you must have found the “thread” class in Axapta. The problem is: it’s almost undocumented (only the available methods are put into System Documentation). There is a “Tutorial_thread” class in the AOT, try to learn from it. Baráti üdvözlettel, Helmut
Hi, Adding threads is not a silver bullet to make things faster. Threads make things to happen parallel but in the bottom line threre is always the same CPU’s to do the physical work. By documentation only Axapta 3 AOS and Business connector are thread safe, client is not. It seems your code isn’t fully optimized because it utilizes only 15 % of the CPU power of the servers. There might be network problems, firewalls, virusmonitors or some other issues which make things slower Check classes for the tier on which they run. Put it all run at AOS. Use also the SQL trace and Code profiler to find slow points in your code. br,
Hi, first of all let me thanks fo your answares. Now i playing with threads Tutorial and learning. Some additional information. The code is running in a class on the AOS Server. there are several SQL on the View. First of all the view not only containing company independent inventtrans but we had joined the InventTable, InventDim and InventLocation to avoid several plus query. On this View i running a query with group by on Itemid,DatePhysical,InventlocationId,InventSizeId and sum on qty and some criteria like InventTransType::sales,Purch, etc. While i processing the result there are other queries on the sales qty and purch qty at the given date intervall ( these queries not makes things faster at all) by item,inventlocation and size. The query on the view itself can take 1 to 2 minutes to run. What is acceptable on 35.000 item 40 inventlocation and on avereage 10 size. I tried to put some query into SQL statement instead of axapta query. The result made me sad. What the SQL Server runned in no time the result arrived to axapta over then 1000 ms. this is fetch time for 1 row. I am sure i made something wrong but i dont know what is it. That is why i turned to the threads way. I hope it is helped you to see my problem more precise then my first Post. Sorry for my poor English. Regards and thx again, RGy
Hello all! I just woud like to tell you the solution of the original problem. As K.K mentioned before the threads wasnt the best idea to solve the proble. Threads are fine but it had problems with queryrun objects. Anyway i wrote the full logic into SQL Stored Procedure runned via connecton object. It was better but not the best. The Stored procedure used cursors and temp tables to process the datas as the bussiness logic needed. We realised it is two or three times faster then it was in axapta but it wasnt enought fast for our customer (5 hours) . We reorganised the entire stored procedure to a set base type SQL. The result was 22 minutes. I hope you remember it was 12 hours in Axapta. Anyway thanks for the helps here to all of you. Best regards, Gyorgy Racz -------------------------------------------------------------------- Microsoft Certified Professional Microsoft Certified Soulution Developer Microsoft BS Certified Professional - Axapta Programming
Have you looked at your SQL statistics? If not, updating them can make a difference in query execution in Axapta.