U872-settlement cost processing steps and index Processing

U872 requires monthly settlement every month. For manufacturing enterprises, settlement cost processing is an essential part, there are also many records to be estimated for each query (about 30 thousand I have come into contact with). The estimated time is generally about 2-3 hours. If there are too many index fragments in a large table to be called, it will take a longer time. Let's take a look at the main steps and scripts for calling the processing process:
Step 1: Take the latest accounting period of the inventory module
Select max (iPeriod) AS CurMonth FROM GL_mEnd WHERE bFlag_IA = 1 Step 2: Take the warehouse receiving ticket billing record
Select top 1 rdrecords. autoid from rdrecords inner join ia_subsidiary on rdrecords. autoid = ia_subsidiary.id Where ia_subsidiary.CvouType = n'01' and isnull (rdrecords. iquantity, 0) = isnull (rdrecords. isquantity, 0) and rdrecords. autoid = 5403479 And ia_subsidiary.imonth = 6 Step 3: retrieve inventory summary table records
Select * from Ia_Summary where cinvcode = n' 021299000098 'And IsNull (iDirect, 0) = 0 and IsNull (Ia_Summary.iPeriod, 0) <> 0 And Ia_Summary.iMonth = 6 Step 4: retrieve the Master/Slave records of the purchase statement
Select PSVID from pursettlevouch where PSVID = 32128 Select * From PurBillVouchs Where ID = 1709941 Step 5: retrieve inventory detail table records
Select Autoid From ia_subsidiary Where ID = 5403457 And iMonth = 6 And (cVouType = n'01 'Or (cVouType = n'30' and cSrcVouType = n'01') Select iMonth, cPZID, * from ia_subsidiary where (cVouType = n'01' or (cVouType = n'33' and cSrcVouType = n'01') and (bflag = 1 or bflag = 2) and id = 5403457 Select top 1 * from ia_subsidiary where (CVOUTYPE = '01' OR (CVOUTYPE = '33' or CVOUTYPE = '30') and csrcvoutype = '01 ')) and id = 5403457 And bFlag = 1 order by autoid descSelect * from ia_subsidiary where CVOUTYPE = N '24' and id = 5403457 and imonth = 6 and cSRcvoutype = '01' Select Top 1 * from Ia_Subsidiary Where AutoID = 1091064 Step 6: new inventory accounting records
Insert into values (values, cBusType, cBusCode, cVouCode, ID, ValueID, JustID, values, dKeepDate, iMonth, iPZID, cInvHead, values, values, cPTCode, cSTCode, cWhCode, cInvCode, response, cRdCode, cCusCode, cBillCode, cDLCode, cPSPCode, cProCode, cDepCode, cPersonCode, response, iAInPrice, iAOutPrice, response, response, cBatchCode, cMaker, cAccounter, bFlag, clerk, bSale, cMemo, cDefine1, cDefine2, cDefine3, cDefine4, clerk, cDefine6, cDefine7, clerk, cDefine12, cDefine13, cDefine14, cDefine15, clerk, cFree1, cFree2, cFree3, cFree4, Shenzhen, cFree6, cFree7, Shenzhen, Shenzhen, Hangzhou, cdefine22, cdefine23, Shenzhen, Shenzhen, Hangzhou, cdefine27, cdefine28, cdefine29, cdefine30, Shenzhen, example, cdefine33, cdefine34, example, citem_class, citemcode, citemcname, cVenCode, cHandler, cOrderCode, cARVCode, cName, example, example, iMassDateia, example, dVDateia, cproordercode, iproorderid, iproorderids, cworkprocode, metrics, centrustordercode, metrics, idlsid, cAssUnit, inum, metrics, cpurordercode, exoCode, iExRowno, consignMentCode, response, imaterialfee, iprocessfee, response, cDemandCode, cDemandMemo, cIMOrdercode) values (1, N 'General sourcing ', null, n'hangzhou', 5403457, null, null, '1970-05-19 ', '1970-06-25', 6,1603742, null, null, n'24', n'01 ', null, n'07 ', N '000000', n'000000', n'000000', null, n'000000',-20, null, 021299000098, null,-21.54, null, n'lml', n'lh ', n'1', 0911, null, n'20170', null, null, 0, null, N 'po1_1903', null, null, n'4', n'cp140404-040', null, 0, 0, null, null, n'cpwg1404-075', n'po1_1903 ', null, n'2017075', n'yql ', N 'tlwg-100', n' TL201405160263 ', null, null, 1916612, null, n'tlwg-100', null, null, null, n'01', null) Step 7: Calculate the free items of inventory files
Select bCheckFree1, bCheckFree2, bCheckFree3, bCheckFree4, bCheckFree5, bCheckFree6, bCheckFree7, bCheckFree8, bCheckFree9, bCheckFree10 FROM inventory where cInvcode = N '2017080' Step 8: Write
Exec IA_WriSummary is calculated by warehouse. Values: 2, 6, n'07 ', n'000000', n '', N '',-20,-21.54, 0, 0, 0 note: The number is negative.
Step 9: Repeat the script in Step 6
Step 10: Write the general ledger

<Span style = "font-size: 12px;"> Exec IA_WriSummary is calculated by warehouse. Values: 2, 6, n'07 ', n'000000', n '', N '', 20, 21.54, 0, 0 </span>

Note: The number is positive.

The big tables involved in settlement cost processing include the warehouse receiving and receiving table rdrecords, the general ledger table ia_subsidiary, And the purchase statement table PurBillVouchs
To ensure the temporary estimation speed, you need to optimize it:

Step 1: temporarily disable some scheduled tasks in the SQL proxy, such as backup and synchronization.

Step 2: before clicking the [estimate] button, you must rebuild or sort indexes for rdrecords, Ia_Summary, ia_subsidiary, PurBillVouchs, Inventory, and Ia_Summary. You can use dbo to index fragments. fn_ShowIndexSP function. You can use Dyl_ReindexNew to reconstruct the index.

Step 3: Check whether all the index fragments of these tables are below 10!

You can estimate the settlement cost.

/* Function: displays the index fragment created by the specified table: baronyang Creation Time: 2014-07-02select * from dbo. fn_ShowIndexSP ('') */Alter function dbo. fn_ShowIndexSP (@ tablename varchar (255) returns @ table (tablename varchar (255), indexname varchar (255), spbl int) asBEGINDECLARE @ dbid int, @ objid intselect @ dbid = DB_ID (), @ objid = OBJECT_ID (@ tablename) insert into @ table (tablename, indexname, spbl) SELECT c. name, B. name, avg_fragmentation_in_percent FROM sys. dm_db_index_physical_stats (@ dbid, @ objid, NULL, NULL) as a inner JOIN sys. indexes B on. object_id = B. object_id and. index_id = B. index_id inner JOIN sys. objects c on. object_id = c. object_id where B. index_id> 0 and avg_fragmentation_in_percent> = 1 return end

/* Function: the creator of Rebuild: baronyang. Creation Time: 2014-07-02exec Dyl_ReindexNew 'kq _ otherdata' select * from dbo. fn_showindexsp ('kq _ otherdata') */Alter procedure dbo. dyl_ReindexNew @ TableName varchar (255), @ indexname varchar (255) = ''as set nocount on declare @ dbid int, @ objid int, @ SQL varchar (1000) select @ dbid = DB_ID (), @ objid = isnull (OBJECT_ID (@ TableName), 0) if @ objid = 0 BEGINprint @ TableName + 'table does not exist 'Return End IF Exists (SELECT * FROM sys. dm_db_index_physical_stats (@ dbid, @ objid, NULL) where avg_fragmentation_in_percent> 30 and index_id> 0) and @ objid> 0 BEGIN set @ SQL = 'alter Index' + case when isnull (@ indexname ,'') <> ''then @ indexname else 'all' END + 'on' + @ TableName + 'rebuild WITH (online = on, STATISTICS_NORECOMPUTE = on) 'exec (@ SQL) end IF Exists (SELECT * FROM sys. dm_db_index_physical_stats (@ dbid, @ objid, NULL, NULL) where avg_fragmentation_in_percent> 30 and index_id> 0 and @ objid> 0) print @ TableName + 'the table index fragment exceeds 30, please manually recreate the index'