MapInfo Pro Developers User Group

Expand all | Collapse all

Querying table using C# - performance issue

  • 1.  Querying table using C# - performance issue

    Posted 01-31-2020 03:37
    Hello,
    I have this tool written for 32bit version entirely in MapBasic. It is supposed to show dialog with user information (name, last name, address...) with list of water consumptions by months. All data presented in dialog are from table "Consumptions" with indexed household IDs, and it takes about 1 sec to select user records, with table itself containing about 670K records (and getting bigger every month).

    Now I'm trying to upgrade it a little bit, so I decided to rewrite it using C#. When I got to the part of selecting records, it took cca 12 seconds to make selection:

    command = connection.CreateCommand();
    command.CommandText = "SELECT * FROM CONSUMPTION WHERE ID = @ID";
    command.Parameters.Add("@ID", id);

    connection.Open();

    var reader = command.ExecuteReader();


    Then I tried other way:

    var table = Session.Current.Catalog.GetTable("CONSUMPTION");
    var si = MapInfo.Data.SearchInfoFactory.SearchWhere("ID = " + id.ToString()); 
    var featureSet = MapInfo.Engine.Session.Current.Catalog.Search(table, si);

    Again, it took about 12 seconds. I'm I doing something wrong? Is there any faster way to select records from a larger table?

    ------------------------------
    Miroslav Kovacevic
    Knowledge Community Shared Account
    ------------------------------


  • 2.  RE: Querying table using C# - performance issue

    Pitney Bowes
    Posted 01-31-2020 08:09
    Hi Miroslav ,

    Is the table indexed on the ID column?

    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    MapInfo Pro Development Team
    ------------------------------



  • 3.  RE: Querying table using C# - performance issue

    Posted 02-01-2020 12:41
    Hello Bob,

    I cant believe I missed that. In 32bit version I used MapBasic entirely, (for creating index on table among other things). For new version I created stored procedure to generate "Consumption" table and create index on "ID" field, I assumed index will be there in TAB file when I register table, now I see it doesn't correlate, I have to add it manually or from separate MapBasic code. Another thing that prevented me from checking is that this query executed instantly when I run it in MapBasic command line.

    ------------------------------
    Miroslav Kovacevic
    Knowledge Community Shared Account
    ------------------------------



  • 4.  RE: Querying table using C# - performance issue

    Pitney Bowes
    Posted 02-01-2020 17:53
    Hi Miroslav,

    It was just a good guess... The .net api may not create a temporary index exactly in the same way as mapbasic.

    We are always interested in learning more about how developers are using MapInfo. Anything you can share either via public or private message about your application and development approach can help us make things better.

    How did you learn about the .NET MDAL api ? Did you try out our sample apps? Did you know you can mix your old or new mapbasic code with .net in an Add-in?

    -Bob


    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    MapInfo Pro Development Team
    ------------------------------