MapInfo Pro Developers User Group

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
------------------------------

Hi Miroslav ,

Is the table indexed on the ID column?

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

Original Message:
Sent: 01-31-2020 03:37
From: Miroslav Kovacevic
Subject: Querying table using C# - performance issue

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
------------------------------

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
------------------------------

Original Message:
Sent: 01-31-2020 08:08
From: Bob Fortin
Subject: Querying table using C# - performance issue

Hi Miroslav ,

Is the table indexed on the ID column?

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

Original Message:
Sent: 01-31-2020 03:37
From: Miroslav Kovacevic
Subject: Querying table using C# - performance issue

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
------------------------------

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
------------------------------

Original Message:
Sent: 02-01-2020 12:41
From: Miroslav Kovacevic
Subject: Querying table using C# - performance issue

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

Original Message:
Sent: 01-31-2020 08:08
From: Bob Fortin
Subject: Querying table using C# - performance issue

Hi Miroslav ,

Is the table indexed on the ID column?

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

Original Message:
Sent: 01-31-2020 03:37
From: Miroslav Kovacevic
Subject: Querying table using C# - performance issue

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
------------------------------