0

TLDR for a reader with the same problem

If your external script is too slow, you may do something in your scritpt which can be implemented in TSQL, which is a big problem. If this is not the case use CLR or process your data in a external application where the functionality is present. Normally you should not rely on external scripts to achieve high performance. CLR is also more secure.

I am not allowed to do the above, because of workspace/interpersonal reasons.

Problem

I have to write a query which must rely on regular expressions and I am not allowed to use CLR or use anything outside the SQL Server environment. So I must use a R or Python external script. I have experience with Python. So I went with that.

I wrote the script it works but the input data is extremely large so the external script execution takes a long time. I already heavily optimized the script, but it is still slow. So I plan to parallelize it.

Python's multiprocessing module doesn't work inside the external script. So I got a strange idea:

What if I horizontally partition the temp table which holds the input data and execute the external script on the partitions using asynchronous procedure execution, then I just get back their union?

Or perhaps there is a better way to parallel execution of an exterenal script?

I work on SQL Server 2019 Development version we also have an enterprise version which I guess doesn't matter here.

Details

@J.D rightly asked for more details. I am only allowed to share a limited amount:

The temp table providing the input has different columns for the actual-, birth- and mother names for different people.

I also have a dataframe in the external script containing valid first names and associated genders parsed from a JSON which is passed as a nvarchar(max). This method described here.

My script extracts titles, surnames, given names and guess biological sex and martial status. The names are not English names.

I apply a function to the first axis of the input Pandas Dataframe which does the extraction.

I use only Pandas provided methods.

With a standard python environment I would use the np.array_split function to split the dataframe and use the multiprocessing module to apply extraction function on the splits in parallel processes, then concatenate the dataframes. As you can see this is a text processing problem and cannot be vectorized as mathematical one could be. So tools like Dask cannot be used here.

Why not CLR?

Using CLR would be a sane choice. So a colleague of mine and I also recommended to use CLR. Our expert said using CLR is very dangerous. Yes I know about the CLR strict security option, but our expert may not. So I am forced to use external script against my better judgement.

Why not use multi-threading

Python's Global Interpreter Lock only allows one thread to be executed by the interpreter. So multi-threading with python will not provide parallel computation, at least with the standard implementation. This is not a limitation for Jython or IronPython, but this is a different story.

10
  • 2
    R and Python are more outside SQL Server than CLR. They execute in another process, whereas CLR executes in-proc Jan 12, 2021 at 21:37
  • 1
    Using Python or R can be worst in terms of security rather than using CLR ! CLR at the minimal security level can do the job and the .net code is managed (eg controlled by the SQL Server) that cannot be for Python and R !
    – SQLpro
    Jan 13, 2021 at 13:26
  • 1
    atevm (and @SQLpro ): regarding "Our expert said using CLR is very dangerous.", your so called expert might know a lot of things, but they don't know what they are talking about regarding SQLCLR. Please see my answer to your related question, Is using CLR for regular expressions safer than using external scripts?, which adds to SQLPro's answer to that same question. Jan 19, 2021 at 5:40
  • 1
    @DaniellePaquette-Harvey Most likely because the character range wildcard of LIKE and PATINDEX is very much not Regular Expressions. The [...] syntax mimics a very small portion of what Regular Expressions can do. I would recommend not using the article that you linked to as it does a disservice to the community by increasing the amount of confusion folks have about this (and the part about case-sensitivity is, while partially correct, also partially misleading / incomplete). Jan 20, 2021 at 1:09
  • 1
    @DaniellePaquette-Harvey What Solomon Rutzky said is the exact reason.
    – atevm
    Jan 20, 2021 at 10:26

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.