Performance while cleaning prices

Jeroen Swanborn

Hi Everybody,



I need to put the prices for 2 pricelists for all items in a certain itemgroup to 0. Now I do a select statement on the OITM table to get all the itemcodes, and for each of those itemcodes I go through the DI to reset the prices. This works, but it's very slow. It takes up to a second for an item to get its prices reset. Since there are about 4000 products in this itemgroup it takes a long time.



Is there a faster (but still legit way (so no direct SQL)) way to do this?



Tnx!

int EINKAUF_Pricelist = 1;

int VERKAUF_Pricelist = 2;

string itmsgrpcod = "103";

SAPbobsCOM.Company oCompany = SwissAddonFramework.B1Connector.GetB1Connector().Company;

SAPbobsCOM.Items items = (SAPbobsCOM.Items) oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItems);



string sqlGC = "SELECT itemcode FROM OITM WHERE OITM.Itmsgrpcod='" + itmsgrpcod + "'";

SwissAddonFramework.Messaging.StatusBar.WriteSucess("Clearing prices for Items Group Code " + itmsgrpcod);

System.Data.SqlClient.SqlCommand cmdGC = new System.Data.SqlClient.SqlCommand();

cmdGC.CommandText = sqlGC;

using (System.Data.SqlClient.SqlDataReader sdr = SwissAddonFramework.B1Connector.GetB1Connector().ExecuteQuery(cmdGC))

{

while(sdr.Read()) { 

string itemcode = sdr.GetString(0).ToString();



SwissAddonFramework.Messaging.StatusBar.WriteSucess("Removing prices for " + itemcode);



if(items.GetByKey(itemcode)){

bool need2update = false;



int countLists = 0;

for (int p = 0; p < items.PriceList.Count; p++)

{

items.PriceList.SetCurrentLine(p);

if (items.PriceList.PriceList == EINKAUF_Pricelist | items.PriceList.PriceList == VERKAUF_Pricelist)

{

items.PriceList.Price = 0;

items.PriceList.Currency = null;

need2update = true;

countLists++;

if (countLists == 1) break;



}

}

if (need2update)

{

int lretcode = items.Update();

if (lretcode != 0)

throw new Exception("ERROR removing prices. RetCode: "+lretcode+" error: "+oCompany.GetLastErrorDescription());

}

}

} 

}

Paolo Manfrin

Hi Jeroen,

as far as I know this is not possible.



If you can do this process overnight you could eventually create a windows service which calls only the DI and then you perform all your updates.



Something like...

int errorCode;

            SwissAddonFramework.B1Connector oB1 =  SwissAddonFramework.B1Connector.GetB1DIOnlyConnector("SAP_manager_id", "SAP_manager_password", "127.0.0.1:30000", SAPbobsCOM.BoSuppLangs.ln_German, "localhost", SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008, "Demo_CH", "SQL_user", "SQL_password", out errorCode);

            string errMsg = "";

            SwissAddonFramework.B1Connector.GetB1Connector().Company.GetLastError(out errorCode, out errMsg);

            // do something via DI

hth, paolo