Montag, 10. Oktober 2011

Zeichenketten mit PL/SQL zerlegen

Mit Bezug auf den vorherigen Post möchte ich heute eine Lösung mithilfe von PL/SQL aufzeigen und die beiden Möglichkeiten vergleichen; insbesondere hinsichtlich der Performance.
create or replace function split_string_plsql(
 p_list_in in varchar2,
 p_delimiter_in in varchar2
 )
return string_nt pipelined
is
 v_before_pattern integer;
 v_after_pattern integer;
 v_list varchar2(32767) := p_list_in;
begin
 loop
  v_before_pattern := regexp_instr(v_list, p_delimiter_in, 1, 1, 0, 'c');
  if (v_before_pattern = 0) then
   pipe row(v_list);
   exit;
  else
   pipe row(substr(v_list, 1, v_before_pattern - 1));
  end if;
  v_after_pattern := regexp_instr(v_list, p_delimiter_in, 1, 1, 1, 'c');
  v_list := substr(v_list, v_after_pattern);
  exit when v_list is null;
 end loop;
 return;
exception
 when others then
  return;
end split_string_plsql;
/
Auch damit kann man Zeichenketten zerlegen, wobei das Trennzeichen auch durch einen regulären Ausdruck angegeben werden kann.
select 
 column_value
from 
 table(split_string_plsql('a....b...c....d', '[.]+'));
COLUMN_VALUE
-------------
a
b
c
d       
Kommen wir nun zum Performance-Duell der beiden Varianten. Dafür steht eine Tabelle bereit, welche 100.000 Zeichenketten enthält, die jeweils durch ein Semikolon in fünf Felder unterteilt sind.
select * from strings_to_split;
STRING
------------------------------------------------------------------------------------
BnHZjfREKnUMdSbwOBflCRbqZISiKbLtraWEIuLpEKQCilFrRp;CZXLlbwZHuGcyCOqQlNnmTMwnlDhhryVH
hmwVoAdyOXKsTBhow;YrKdmqKbsSSvuPbtZTrPUPAkxfmNechcYrVkLSJfPnjvsaHwdk;iVxogWXxrQSsDRK
ofFhkZoHaKNqHgbPVjbQOywpNAdLMxabPAa;LgklKsjnwNxmTbriHCNABgisxWOjqLnBoRMaOSONbwiIosMS

acwjSJDzUBDWolfKMhtGwizwJpZvKMipMnuGKsRhbSpDzHHvqh;yIBOHkSVedWmpgoyczdxsoAiKvqGrzWYD
LbqZeIZjgeHOQRRDd;EAjqQJErVzkouioxcPLxYMgIEHERANYuGaqnhIDwpJTCIYeGWZ;NqejlJHoIQWcmca
acbqstRZeAhlKPbgSHexKEOPzEEkpYPkvez;QXYvDNXPtAkoGQvUnqqpagfbhShJZegBinXkgfurKoCVQAYX

ELbJayqFFdsDRcoHYmvqLxfsKqgDzNhmVBoHzYtcNYjLTzwaIL;qjVLHPBINaLXrbegLQAiklKeBexbTTCWn
VYoBXJBhLwlErEucT;hgMSqrOTmaiEfBvkMtynhtcwTodPZeyeIfltOCoLPkboCdwIfC;fmnEQBVqiQWNyuG
UxtDkwceZckXYFyqJBNiOgdWTJkPmGWFYiG;OslJbHXKdVALzjdFbeYcoGeTXCzjyKrlQqISRYXMkgpVDmpr

ztfMZluktZSOwtMIeQIBRLKNyrtUKogyddXQWLbCsjnTFwpTmL;nHDbrxlYhjabiWDpfSEnLWAvVXIzgkDhb
NPYwOBEAwmJZvZSmK;jApmGJeLaXioFJxpAelEugPrxkRZNIirzVWKqFYqweDHNymmoK;zUyCCgkGLURMCat
EzvOVgwIoLIeCeluQtMKkXkcieHSbRpjCDc;TTjxzLccEhSfXFgbxaKVndDhxymsrOlyWOjtcWoPSqPznLjP

LWgGKQSVpqaOabvBYAbUrPQaVbomxyaxOXVvPojYkDkPqNrozP;gafSCgxUsHPtTENvKpPfaJDMkhdIOVqUM
dRdeMsNDehCdxzvEF;sRxDbQIJaQCopFlgWxQGJOUwhDHDHonWZLYSlVmrugAiToLDAt;DJJZCaVEGjFvgAc
bGbaXYQeRodCgAixLMQOpxvPfBqQSrWHHJu;MkUeKsmMFCISPSzTldvrGIRrqQnmYxXPQUNHDzcTsGNeyBJC

OOPamDAHRipKbVWWaSScmdjIWFJbunDBBnXKTRceqsJEpOcnxQ;dwffwkUYBgqEJSWNUvnzAQKbgcsLwTWmY
ZSzmVMZqKWlxNFezc;OdEHsJZYrsEqLaGJEvnimLFJkxdQGgEGpAbOwxxJsqLXzwTbgk;wsqFmybhYypdegs
gEHXETxmyGmIZkQiZIwlrEQizNlbcqlXQeX;BmYSjCeyeWPJHgvbVjwbgSsygJiJPlJJMIjScmvQNUapgJMw

...
Nun sollen die Zeichenketten in jeweils fünf Zeichenketten zerlegt werden, wodurch eine Tabelle mit 500.000 Zeilen entsteht. Beginnen wir mit der Java-Variante aus dem vorherigen Post:
create table sub_strings as 
 select 
  t.* 
 from 
  strings_to_split s, 
  table(split_string(s.string, ';')) t;
Tabelle wurde erstellt.

Abgelaufen: 00:04:37.35
select count(*) from sub_strings;
COUNT(*)
----------
    500000
Jetzt zum Vergleich die PL/SQL-Variante:
create table sub_strings_plsql as 
 select 
  t.* 
 from 
  strings_to_split s, 
  table(split_string_plsql(s.string, ';')) t;
Tabelle wurde erstellt.

Abgelaufen: 00:00:19.81
select count(*) from sub_strings;
COUNT(*)
----------
    500000
Man sieht deutlich, dass die PL/SQL-Variante um ein Vielfaches schneller ist; mehr als 14 mal so schnell.

An dieser Stelle gilt der Dank Carsten Czarski, der mich darauf aufmerksam gemacht hat, wie viel Zeit für das Context-Switching zwischen Java und SQL verloren geht.

1 Kommentar:

  1. nur als Randnotiz: bei Tom Kyte liest man ziemlich regelmäßig den Hinweis, dass man alles, was man mit SQL machen kann, auch mit SQL machen sollte, und wenn's mit SQL nicht funtioniert, dann mit PL/SQL - und erst wenn's auch damit nicht funktioniert kommen Java und andere Sprachen in Betracht. Auf der Suche nach einem entsprechenden Zitat bin ich bei AskTom dann auf einen Thread gestossen, in dem der Herr Kyte sich explizit zum Thema String-Tokenzizer äußert (http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2189860818012#17088075063394):

    "Now, if you had a couple documents AND you needed to parse them THEN java would be a good idea.

    If you have a couple of simple strings AND they are columns in a table AND you needed to extract data from them THEN substr/instr et.al. are a good idea

    So, it is not that java is evil, or to be avoided - just that the heinous overheads of calling plsql or java (specially the latter) from SQL far outweighs the keystrokes you may or may not save."

    Im gegebenen Beispiel würd ich wahrscheinlich auch zunächst an SUBSTR und INSTR denken; aber interessant sind die Möglichkeiten von piplined functions zur Ergänzung zusätzlicher Möglichkeiten neben den Built-In-Funktionen natürlich allemal.

    Viele Grüße

    Martin

    AntwortenLöschen