Mapfile - filter by parameter - full scan

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Mapfile - filter by parameter - full scan

Lane, Michael

Hi,

 

Experiencing something not expected with the filter condition in mapfile.

 

I add this code in my mapFile to have the possibility to void the filter if no parameter is given:

 

VALIDATION
       no_diag '^[0-9]{1,2}$'
       default_no_diag '0'
 END
 DATA "geom_surf from LOCTS_PARC_AGRI_AN_COUR using srid 4326"
  FILTER  (([no_diag]='%no_diag%') or ('%no_diag%' = '0'))  

I compare my execution time without parameter and it’s going to an execution time of 3 seconds before filter to one of 3 minutes with the filter.

 

After debugging, I realize that the « where clause » generated by MapServe were bad with the filter and that generate a full table scan :

WHERE   ( '0' = '0' ) OR ( no_diag = '0' )  AND …

I was expecting something more like :

WHERE   (( '0' = '0' ) OR ( no_diag = '0' ))  AND …

So I had to add more parenthesis to the filter to get it correct

((('%no_diag%' = '0') or ([no_diag]='%no_diag%')))

Is it normal that I have to do that? The examples I found on the internet, have always single parenthesis. Do I code it right?

 

I use Mapserver 7 with database Oracle 12c. My parameter no_diag is a number.

 

Thanks

 

Michael Lane
Technicien en informatique

Direction des solutions d'affaires
La Financière agricole du Québec
1400, boul. Guillaume-Couture
Lévis (Québec) G6W 8K7
Tél. : 418 838-5614, poste 6034
[hidden email]




_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Mapfile - filter by parameter - full scan

Steve.Toutant
How using (), [], "", '' is still a great mystery to me in filters. Every time I do many tries to got it right
Your expression seems to be ok so I don't know why you need extra ()

You can try without '' and [] like this
FILTER  ((no_diag=%no_diag%) or (%no_diag% = 0))

Or try using processing instead of FILTER
PROCESSING 'NATIVE_FILTER=((%no_diag% = 0 OR no_diag=%no_diag%))'

Steve

-----"mapserver-users" <[hidden email]> a écrit : -----
A : "'[hidden email]'" <[hidden email]>
De : "Lane, Michael"
Envoyé par : "mapserver-users"
Date : 25/01/2017 16:13
Objet : [mapserver-users] Mapfile - filter by parameter - full scan

Hi,

 

Experiencing something not expected with the filter condition in mapfile.

 

I add this code in my mapFile to have the possibility to void the filter if no parameter is given:

 

VALIDATION
       no_diag '^[0-9]{1,2}$'
       default_no_diag '0'
 END
 DATA "geom_surf from LOCTS_PARC_AGRI_AN_COUR using srid 4326"
  FILTER  (([no_diag]='%no_diag%') or ('%no_diag%' = '0'))  

I compare my execution time without parameter and it’s going to an execution time of 3 seconds before filter to one of 3 minutes with the filter.

 

After debugging, I realize that the « where clause » generated by MapServe were bad with the filter and that generate a full table scan :

WHERE   ( '0' = '0' ) OR ( no_diag = '0' )  AND …

I was expecting something more like :

WHERE   (( '0' = '0' ) OR ( no_diag = '0' ))  AND …

So I had to add more parenthesis to the filter to get it correct

((('%no_diag%' = '0') or ([no_diag]='%no_diag%')))

Is it normal that I have to do that? The examples I found on the internet, have always single parenthesis. Do I code it right?

 

I use Mapserver 7 with database Oracle 12c. My parameter no_diag is a number.

 

Thanks

 

Michael Lane
Technicien en informatique

Direction des solutions d'affaires
La Financière agricole du Québec
1400, boul. Guillaume-Couture
Lévis (Québec) G6W 8K7
Tél. : 418 838-5614, poste 6034
[hidden email]



_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Si vous n'êtes pas le destinataire de ce message, veuillez le détruire près avoir informé l'expéditeur de son erreur. Par ailleurs, il est interdit de copier ou de modifier ce courriel sans l'autorisation de l'auteur.

L'Institut national de santé publique du Québec n'assume aucune responsabilité à l'égard du contenu des messages personnels envoyés par ses employés.

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Mapfile - filter by parameter - full scan

Steve.Toutant
In reply to this post by Lane, Michael
or use a subquery in the DATA statement instead of FILTER or PROCESSING

-----"mapserver-users" <[hidden email]> a écrit : -----
A : "'[hidden email]'" <[hidden email]>
De : "Lane, Michael"
Envoyé par : "mapserver-users"
Date : 25/01/2017 16:13
Objet : [mapserver-users] Mapfile - filter by parameter - full scan

Hi,

 

Experiencing something not expected with the filter condition in mapfile.

 

I add this code in my mapFile to have the possibility to void the filter if no parameter is given:

 

VALIDATION
       no_diag '^[0-9]{1,2}$'
       default_no_diag '0'
 END
 DATA "geom_surf from LOCTS_PARC_AGRI_AN_COUR using srid 4326"
  FILTER  (([no_diag]='%no_diag%') or ('%no_diag%' = '0'))  

I compare my execution time without parameter and it’s going to an execution time of 3 seconds before filter to one of 3 minutes with the filter.

 

After debugging, I realize that the « where clause » generated by MapServe were bad with the filter and that generate a full table scan :

WHERE   ( '0' = '0' ) OR ( no_diag = '0' )  AND …

I was expecting something more like :

WHERE   (( '0' = '0' ) OR ( no_diag = '0' ))  AND …

So I had to add more parenthesis to the filter to get it correct

((('%no_diag%' = '0') or ([no_diag]='%no_diag%')))

Is it normal that I have to do that? The examples I found on the internet, have always single parenthesis. Do I code it right?

 

I use Mapserver 7 with database Oracle 12c. My parameter no_diag is a number.

 

Thanks

 

Michael Lane
Technicien en informatique

Direction des solutions d'affaires
La Financière agricole du Québec
1400, boul. Guillaume-Couture
Lévis (Québec) G6W 8K7
Tél. : 418 838-5614, poste 6034
[hidden email]



_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Si vous n'êtes pas le destinataire de ce message, veuillez le détruire près avoir informé l'expéditeur de son erreur. Par ailleurs, il est interdit de copier ou de modifier ce courriel sans l'autorisation de l'auteur.

L'Institut national de santé publique du Québec n'assume aucune responsabilité à l'égard du contenu des messages personnels envoyés par ses employés.

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Mapfile - filter by parameter - full scan

Lime, Steve D (MNIT)
In reply to this post by Steve.Toutant
You can just put the where clause in the data statement using a sub select using the native SQL of the underlying database. The substitution will work there to and you avoid the translation step from MapServer expressions to native SQL. --Steve

________________________________________
From: mapserver-users [[hidden email]] on behalf of [hidden email] [[hidden email]]
Sent: Thursday, January 26, 2017 8:28 AM
To: Lane, Michael
Cc: '[hidden email]'
Subject: Re: [mapserver-users] Mapfile - filter by parameter - full scan

How using (), [], "", '' is still a great mystery to me in filters. Every time I do many tries to got it right
Your expression seems to be ok so I don't know why you need extra ()

You can try without '' and [] like this
FILTER  ((no_diag=%no_diag%) or (%no_diag% = 0))

Or try using processing instead of FILTER

PROCESSING 'NATIVE_FILTER=((%no_diag% = 0 OR no_diag=%no_diag%))'

Steve

-----"mapserver-users" <[hidden email]> a écrit : -----
A : "'[hidden email]'" <[hidden email]>
De : "Lane, Michael"
Envoyé par : "mapserver-users"
Date : 25/01/2017 16:13
Objet : [mapserver-users] Mapfile - filter by parameter - full scan

Hi,

Experiencing something not expected with the filter condition in mapfile.

I add this code in my mapFile to have the possibility to void the filter if no parameter is given:

VALIDATION
       no_diag '^[0-9]{1,2}$'
       default_no_diag '0'
 END
 DATA "geom_surf from LOCTS_PARC_AGRI_AN_COUR using srid 4326"
  FILTER  (([no_diag]='%no_diag%') or ('%no_diag%' = '0'))
I compare my execution time without parameter and it’s going to an execution time of 3 seconds before filter to one of 3 minutes with the filter.

After debugging, I realize that the « where clause » generated by MapServe were bad with the filter and that generate a full table scan :
WHERE   ( '0' = '0' ) OR ( no_diag = '0' )  AND …
I was expecting something more like :
WHERE   (( '0' = '0' ) OR ( no_diag = '0' ))  AND …
So I had to add more parenthesis to the filter to get it correct
((('%no_diag%' = '0') or ([no_diag]='%no_diag%')))
Is it normal that I have to do that? The examples I found on the internet, have always single parenthesis. Do I code it right?

I use Mapserver 7 with database Oracle 12c. My parameter no_diag is a number.

Thanks

Michael Lane
Technicien en informatique

Direction des solutions d'affaires
La Financière agricole du Québec
1400, boul. Guillaume-Couture
Lévis (Québec) G6W 8K7
Tél. : 418 838-5614, poste 6034
[hidden email]<mailto:[hidden email]>

[cid:image001.jpg@01D27723.BAB8F550]
[cid:image002.jpg@01D27723.BAB8F550]
[cid:image003.jpg@01D27723.BAB8F550]
_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Si vous n'êtes pas le destinataire de ce message, veuillez le détruire près avoir informé l'expéditeur de son erreur. Par ailleurs, il est interdit de copier ou de modifier ce courriel sans l'autorisation de l'auteur.

L'Institut national de santé publique du Québec n'assume aucune responsabilité à l'égard du contenu des messages personnels envoyés par ses employés.

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Image.image001.jpg@01D27723.BAB8F550.jpg (2K) Download Attachment
Image.image002.jpg@01D27723.BAB8F550.jpg (3K) Download Attachment
Image.image003.jpg@01D27723.BAB8F550.jpg (2K) Download Attachment