POINT(lng,lat) taking too much time in stored procedure within pipeline

Help Connectors and Pipelines
1

Hi,
I have a simple pipeline ingesting into procedure. That procedure receive lat lng in batch. i have a different table where district (polygon) is present. Now while batchdata comes in, i check GEOGRAPHY_INTERSECTS(other_table.polygon,GEOGRAPHY_POINT(lng,lat)). It takes hell of a time to just pass this statement.
What i did was, created a batchdata_temp table (lat, lng, location computed column as GEOGRAPHY_POINT(lng,lat)). when i inserted batch data into this temp table (doing the same thing with just compute column), this was pretty much efficient.

Why is this so? and what is the best approach to calculate geography_point on the fly?

Thanks

1 Like
2

Can you post both versions of your code, including the create table for other_table? My best guess is that somehow in one version you are able to take advantage of an existing index on other_table.polygon and in the other not.

3

Thanks for your response.
The other table has this shape(polygon) as a KEY

     ...
     ...
    `shape` geography DEFAULT NULL, 
     `version` text CHARACTER SET utf8 COLLATE utf8_general_ci, 
     PRIMARY KEY (`id`), 
     KEY `shape` (`shape`) WITH (RESOLUTION = 8) ) ;

Table to save batch_data temporarily. Table schema is:

CREATE TABLE `batchdata_temp` ( 
`location` as concat('POINT(',lng,' ',lat,')') PERSISTED geographypoint, 
`lat` decimal(8,6) DEFAULT NULL, 
`lng` decimal(9,6) DEFAULT NULL, 
`msisdn` char(64) CHARACTER DEFAULT NULL, 
`update_time` timestamp(6) NULL DEFAULT NULL)

All this because when i use geography_point(lng,lat) directly in my pipeline, it takes forever. So, i save the whole batch (of millions) in table before processing on it.

4

Can you please have a look into this and any workaround would be appreciated as i am same batch twice just because of this issue

5

I still don’t have enough information. Can you post the relevant parts of each version of the SP?