Speed of the Browse Filters

SOLVED

I'm calling the browse method and trying to find an order by number. This code takes around 4-5 minutes. Are these method really that slow?

var view = OpenView("OE0520");
view.Browse("ORDNUMBER = 5555555555", true);

Parents
  • 0 in reply to Shane - Ascend Business Solutions

    One more on this, I just want to make sure this is normal from your experience. 

    var view = OpenView("OE0520", DBLinkFlags.ReadOnly);
    view.Browse("", true);

    var gotOne = view.GoTop();
    while (gotOne)
    {

    var orderNumber = (string)view.Fields.FieldByName("ORDNUMBER").Value;


    Console.WriteLine(sageOrder.OrderNumber);


    gotOne = view.GoNext();
    }

    Running this code w/o a filter so it's basically returning all orders - takes 5 minutes for 35,000 orders. That doesn't seem right for a SQL database query

  • 0 in reply to JStrz

    Use While(view.Fetch()), never GoTop or GoNext

  • 0 in reply to JStrz

    The database layer doesn't work the way you're thinking it should. Its history is steeped in transactional database processing vs working with recordsets.

    Typically, if you want to work on a specific order, you will get to the order using the best index for the job and a .Read call. .Read if you have all of the key field values, .Browse if you do not.

    If you need to locate a group of orders to do some processing on then your search loop either needs to use an existing index, or, be clever with finding the records using CS0120 which allows you to say, access a SQL view to locate your records.

    Calling .Browse/.Fetch doesn't return a recordset to navigate through.  It points you to the first record that satisfies the criteria and then .Fetch will go back to the database to find the next record that satisfies the criteria based on your current position. That's important in the event you are updating a field that impacts the index you're using.

  • 0 in reply to Django

    yikes, ok. So there is no way to just return a recordset using the API?

  • +1 in reply to JStrz
    verified answer

    No, not in the way that you're expecting.  What is it that you're trying to do? We likely can get you pointed in the right direction.

  • 0 in reply to Django

    I'm writing an integration that pulls orders from an ecommerce, sends to sage and the pull order, customer info out of sage into text files. So, I don't really need to get all orders - I was just testing. I need to get data since the last time the sync runs and then query on order number, customer number...etc to find if they exist already. I think using filters, index and the fetch will probably work

  • 0 in reply to Django

    FilterFetch works the same way?

  • 0 in reply to JStrz

    So locating an order in Sage to see if it exists will be fastest to do .Order = 1, .put("ORDNUMBER")=<order number>, if .Read then it exists.

    I'm not clear on your requirement around getting "data since the last time".  If that's Sage data then you're going to need to flag the order - you can look at the ORDUNIQ field value - it will increment as orders are added to the system. That's the primary key so you could easily ask for all records where the ORDUNIQ value is greater than the value you last recorded.

Reply
  • 0 in reply to JStrz

    So locating an order in Sage to see if it exists will be fastest to do .Order = 1, .put("ORDNUMBER")=<order number>, if .Read then it exists.

    I'm not clear on your requirement around getting "data since the last time".  If that's Sage data then you're going to need to flag the order - you can look at the ORDUNIQ field value - it will increment as orders are added to the system. That's the primary key so you could easily ask for all records where the ORDUNIQ value is greater than the value you last recorded.

Children