Complex selections

[Francois]

Hi,

I have just upgraded to Mapinfo 19.3 (RB43) and I am running into all kinds of problems with selections that take several "where" clauses (clause 1 and clause2 and clause3 etc.). It looks like the inner workings of the select clause have been modified and the old order in which cross references are made have been changed - it used to be that clause 3 was done before 2 and then 1, but the latest version seems to indicate it is not true anymore. This is not a benign issue, it can easily switch a 30 second selection into a 5 hour one.

This will likely force me to rewrite many lines of code, but before I do that, I *really* want to understand how their order works. Is any one of you aware of a document that explains precisely the order in which the clauses are executed? That includes the order of the clauses, existence of indexes, field equality clauses vs. other types of clauses (inequalities, use of functions, etc.). If any one of you can help, I thank you in advance, this is a real roadblock for my business.

[esjbo...@gmail.com]

Francois,

I don't have an answer to your question, but I wonder if you could describe the nature of the parts of your WHERE clause that the MI has to evaluate.  Do any of them involve spatial functions?  What is the size (number of records) you're working with in the table(s)?  Are any fields being referenced in the WHERE clause indexed fields?

I realize this may not have immediate bearing on the question of the order in which things are evaluated, and that it worked much more quickly before.  I'm interested because I would like to stay aware of what is happening in case I run up against something similar.

Thanks,

Eric Johnson

[esjbo...@gmail.com]

Also, do you happen to know at which version the change in behavior occurred?

-- EJ

[Francois]

The change seems to have happened within the 19 release. I am currently on build 43 and this is the first time this happens. When I upgraded a major release in the past (maybe 2 years ago), I had to change all my where clauses because it did not pick the same order anymore. I don't think it has to do with indexes, because MI seems to build indexes on the fly when it realizes it would gain from it. It also does not seems to be due to the use of functions. It is more an issue of the following nature, let's imagine the following queries (A1, A2, B1 and B2 are all indexed):

   select A.A1, A.A2, B.B2 from A, B where A.A1 = B.B1 into C

   select A1, B2 from C where A2 = B2 into E1

   select A.A1, B.B1, B.B2 from A, B where A.A2 = B.B2 into D

   select A1, B2 from D where A1 = B1 into E2

   select A.A1, B.B2 from A, B where A.A1 = B.B1 and A.A2 = B.B2 into E3

   select A.A1, B.B2 from A, B where A.A2 = B.B2 and A.A1 = B.B1 into E4

Presumably, building E3 is equivalent of building E1 and building E4 is equivalent to building E2, unless it is the other way around. Let's say that A has 3,000,000 records and B has 1,000,000 records, and E1, E2, E3, E4 only have 12,000 records (they are identical). Let's now say that C has 378,000,000 records and D has 53,000 records.  Building E1 is 1000 times costlier than building E2. My current code generates E4, which used to be equivalent to generating E2 using the previous MI release. But now, it seems like it is equivalent to building E1. I will need to modify hundreds of queries in MapBasic, but I need to understand exactly how this works. I hope I get the attention of people who have access to that information.

[Eric Johnson]

Thanks, Francois.  I can understand how important it would be to know how things work before making extensive changes to your .mb

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to a topic in the Google Groups "MapInfo-L" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mapinfo-l/CxW7DyGFWko/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mapinfo-l+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mapinfo-l/61a268ae-4755-41b6-80f9-a3fb1356a81bn%40googlegroups.com.

[Francois]

I built a self-contained mbx that shows the problem, if it is of interest to you... Just tell me how I should forward it.

[Andrei]

Can you try to upgrade to build 45? By default old behavior would be restored. You can download latest 2019.3 installer (build 45) from here:  https://support.precisely.com/product-downloads/item/mapinfo-pro-v2019-3-evaluation-version/

Andrei

[Francois]

Hi Andrei,

Thanks for your answer. Can you please send me a document that states precisely the order in which the where clauses are executed?

Some queries that lasted 3 seconds in the previous version take 3 hours in the version 43, so I had to upgrade part of my code. I have built an MBX that checks the orders. I would like to send it to you and have you tell me the results in Build 45 before I upgrade. Can you please contact me by email? Thanks,

Francois
Quickworld Architect

fbl...@quickworld.com