1
\$\begingroup\$

I have a dataframe that have about 200 million rows. the example of dataframe is like this:

date         query
29-03-2019   SELECT * FROM table WHERE ..
30-03-2019   SELECT * FROM ... JOIN ... ON ...WHERE ..
....         ....
20-05-2019   SELECT ...

I have a function to get table(s) name, attribute(s) name from dataframe above and append to new dataframe.

import sqlparse
from sqlparse.tokens import Keyword, DML
def getTableName(sql):
    def getTableKey(parsed):
        findFrom = False
        wordKey = ['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN']
        for word in parsed.tokens:
            if word.is_group:
                for f in getTableKey(word):
                    yield f
            if findFrom:
                if isSelect(word):
                    for f in getTableKey(word):
                        yield f
                elif word.ttype is Keyword:
                    findFrom = False
                    StopIteration
                else:
                    yield word
            if word.ttype is Keyword and word.value.upper() in wordKey:
                findFrom = True
    tableName = []
    query = (sqlparse.parse(sql))
    for word in query:
        if word.get_type() != 'UNKNOWN':
            stream  = getTableKey(word)
            table   = set(list(getWord(stream)))
            for item in table:
                tabl = re.sub(r'^.+?(?<=[.])','',item)
                tableName.append(tabl)
    return tableName

and the function to get attribute is just like getTableName the different is the wordKey.

function to process dataframe is like this:

import pandas as pd
def getTableAttribute(dataFrame, queryCol, date):
    tableName       = []
    attributeName   = []
    df              = pd.DataFrame()
    for row in dataFrame[queryCol]:
        table       = getTableName(row)
        tableJoin   = getJoinTable(row)
        attribute   = getAttribute(row)
        #append into list
        tableName.append(table+tableJoin)
        attributeName.append(attribute)
    df = dataFrame[[date]].copy()
    df['tableName']      = tableName
    df['attributeName']  = attributeName
    print('Done')
    return df

The result of the function is like this:

date        tableName  attributeName
29-03-2019  tableN     attributeM
30-03-2019  tableA     attributeB
....        ...        ...
20-05-2019  tableF     attributeG 

But as this is my first try, I need an opinion about what I've tried, because my code runs slow with large file.

\$\endgroup\$
  • \$\begingroup\$ Are you sure memory optimization is what you're looking for? Reading your question sounds more like you want to optimize the runtime. Regardless, make sure to include all the necessary imports with your code. This allows reviewers to see which libraries you're using. Sidenote: dataframe sounds like pandas. There is a special tag pandas for this. \$\endgroup\$ – AlexV Jun 25 at 9:25
  • \$\begingroup\$ @AlexV no, sorry I clicked wrong tag. yes you're right that I need optimize runtime. I updated the code \$\endgroup\$ – elisa Jun 25 at 9:35
  • \$\begingroup\$ Is date the index of dataFrame? or is it just a column? \$\endgroup\$ – C.Nivs Jun 25 at 18:03
  • \$\begingroup\$ Also, is the function getTableName or getTableNameFrom? \$\endgroup\$ – C.Nivs Jun 25 at 19:05
  • \$\begingroup\$ @C.Nivs date is a column, and it's getTableName \$\endgroup\$ – elisa Jun 26 at 2:14
2
\$\begingroup\$

getTableKey

I'm not sure it's good style to define functions inside other functions unless you are implementing some sort of closure:

# this is much easier to read as a separate function
# and you don't incur the cost of defining it every time
# you call the encapsulating function
def getTableKey(parsed):
    findFrom = False
    wordKey = ['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN']
    for word in parsed.tokens:
        if word.is_group:
            for f in getTableKey(word):
                yield f
        if findFrom:
            if isSelect(word):
                for f in getTableKey(word):
                    yield f
            elif word.ttype is Keyword:
                findFrom = False
                StopIteration
            else:
                yield word
        if word.ttype is Keyword and word.value.upper() in wordKey:
            findFrom = True


def getTableName():
    tableName = []
    query = (sqlparse.parse(sql))
    for word in query:
        if word.get_type() != 'UNKNOWN':
            stream  = getTableKey(word)
            table   = set(list(getWord(stream)))
            for item in table:
                tabl = re.sub(r'^.+?(?<=[.])','',item)
                tableName.append(tabl)
    return tableName

yield from syntax

Furthermore, instead of using for f in getTableKey(word): yield f, later versions of python3 introduced the yield from syntax:

def getTableKey(parsed):
    findFrom = False
    wordKey = ['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN']
    for word in parsed.tokens:
        if word.is_group:
            yield from getTableKey(word)

        # combine this, since it's exactly this combination that will yield
        # f, there's no elif or else
        if findFrom and isSelect(word):
            yield from getTableKey(word)
        # rest of func

This leverages less function calls and is faster:

import dis

def f():
    for i in range(10000):
        yield i

def g()
    yield from range(10000)

dis.dis(f)
2           0 SETUP_LOOP              22 (to 24)
              2 LOAD_GLOBAL              0 (range)
              4 LOAD_CONST               1 (10000)
              6 CALL_FUNCTION            1
              8 GET_ITER
        >>   10 FOR_ITER                10 (to 22)
             12 STORE_FAST               0 (i)

  3          14 LOAD_FAST                0 (i)
             16 YIELD_VALUE
             18 POP_TOP
             20 JUMP_ABSOLUTE           10
        >>   22 POP_BLOCK
        >>   24 LOAD_CONST               0 (None)
             26 RETURN_VALUE

dis.dis(g)
2           0 LOAD_GLOBAL              0 (range)
              2 LOAD_CONST               1 (10000)
              4 CALL_FUNCTION            1
              6 GET_YIELD_FROM_ITER
              8 LOAD_CONST               0 (None)
             10 YIELD_FROM
             12 POP_TOP
             14 LOAD_CONST               0 (None)
             16 RETURN_VALUE

To show the speed gain:

python -m timeit -s 'from somefile import f, g' 'list(f())'
1000 loops, best of 3: 507 usec per loop

python -m timeit -s 'from somefile import f, g' 'list(g())'
1000 loops, best of 3: 396 usec per loop

set vs list membership tests

Checking for membership in a list over and over is slow, worst case being O(N). To fix this, make word_list a set, which yields O(1) lookup:

python -m timeit -s "x = ['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN']" "'FULL JOIN' in x"
10000000 loops, best of 3: 0.0781 usec per loop

python -m timeit -s "x = set(['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN'])" "'FULL JOIN' in x"
10000000 loops, best of 3: 0.0246 usec per loop

So create the set like:

def getTableName(...):
   ~snip~
   wordKey = set(['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN'])

Though it might be even better to move this out of the getTableKey function entirely so you aren't paying for the re-construction of this set during every iteration:

# add a positional arg for it
def getTableKey(parsed, wordKey):
    # rest of func

And define it in getTableAttribute like:

def getTableAttribute(dataFrame, queryCol, date):
    wordKey = set(['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN'])
    ~snip~
    for row in dataFrame:
        table_name = getTableName(row, wordKey)

getTableName

There's no need to enclose sqlparse.parse in parens, as it will just default to whatever the enclosed value is:

x = (5)
x
5

You are losing speed calling set(list(iterable)), since set will consume any iterable, and it looks like getWord(stream) is an iterable already:

    table   = set(getWord(stream))

re.compile

If you are going to call a regex many times, it is better to compile it once, then call compiled.sub where compiled is the output of re.compile("<expression>"):

 python -m timeit -s 'import re; x = "abc123"' 'for i in range(100000): re.match("\w\d", x)'
10 loops, best of 3: 67.5 msec per loop

python -m timeit -s 'import re; x = "abc123"; y = re.compile("\w\d")' 'for i in range(100000): y.match(x)'
10 loops, best of 3: 28.1 msec per loop

To make this work, you might consider adding an arg in getTableName to allow for a compiled regex:

# somewhere in getTableAttribute.py file
import re
def getTableAttribute(dataFrame, queryCol, date):
    tableName       = []
    attributeName   = []
    table_re        = re.compile(r'^.+?(?<=[.])')
    df              = pd.DataFrame()
    for row in dataFrame[queryCol]:
        table       = getTableName(row, table_re)
        # rest of code
def getTableName(sql, re_expr):
    ...
    for item in table:
        tabl = re_expr.sub('', item)
        # rest of code
\$\endgroup\$
  • \$\begingroup\$ thank you for your review. that's really help \$\endgroup\$ – elisa Jun 26 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.