Re: Qusetion re regexexp_split_to_array and last occurence

--000000000000f8aebd05946b25da
Content-Type: text/plain; charset="UTF-8"

On Tue, Oct 8, 2019 at 10:31 AM Mike Martin <[email protected]> wrote:

> arrfile=(regexp_split_to_array(NEW.tagfile,'/'))[2:];
>

string_to_array()?  You aren't providing a regexp so it seems wasteful to
use the regexp engine to perform the task.

NEW.filearr
> :=arrfile[1:cardinality(arrfile)-1]||regexp_matches(arrfile[cardinality(arrfile)],'(.*)\.(.*)');
>

regexp_match()?  You aren't returning a set of matches so use the scalar
function.  Probably still need to deal with null properly.

Which works perfectly, except performance is 50% slower on a dataset of
> around 20k
>
> Is there a better solution
>

Probably not materially - it seems like you have to do it in two parts -
one to break apart the paths and then one to handle the fact that you want
the part subsequent to the final period (i.e., the file extension) in its
own array cell.  Choosing the best function for each job will hopefully
improve matters at least a bit.

David J.

--000000000000f8aebd05946b25da
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Tue, Oct 8, 2019 at 10:31 AM Mike Martin &lt;<a href=3D=
"mailto:[email protected]">[email protected]</a>&gt; wrote:</span><br=
></div></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr"><div>arrfile=3D(regexp_split_to_array(NEW.t=
agfile,&#39;/&#39;))[2:];<br></div></div></blockquote><div><br></div><div><=
div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif=
">string_to_array()?=C2=A0 You aren&#39;t providing a regexp so it seems wa=
steful to use the regexp engine to perform the task.</div></div><div class=
=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>=
			NEW.filearr :=3Darrfile[1:cardinality(arrfile)-1]||regexp_matches(arrfil=
e[cardinality(arrfile)],&#39;(.*)\.(.*)&#39;);</div></div></blockquote><div=
><br></div><div class=3D"gmail_default" style=3D"font-family:arial,helvetic=
a,sans-serif">regexp_match()?=C2=A0 You aren&#39;t returning a set of match=
es so use the scalar function.=C2=A0 Probably still need to deal with null =
properly.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex"><div dir=3D"ltr"><div></div><div>Which works perfectly, except perform=
ance is 50% slower on a dataset of around 20k</div><div><br></div><div>Is t=
here a better solution</div></div></blockquote><div><br></div><div class=3D=
"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">Probably n=
ot materially - it seems like you have to do it in two parts - one to break=
 apart the paths and then one to handle the fact that you want the part sub=
sequent to the final period (i.e., the file extension) in its own array cel=
l.=C2=A0 Choosing the best function for each job will hopefully improve mat=
ters at least a bit.</div><div class=3D"gmail_default" style=3D"font-family=
:arial,helvetica,sans-serif"><br></div><div class=3D"gmail_default" style=
=3D"font-family:arial,helvetica,sans-serif">David J.</div><div class=3D"gma=
il_default" style=3D"font-family:arial,helvetica,sans-serif"></div></div></=
div>

--000000000000f8aebd05946b25da--