V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐学习书目
Learn Python the Hard Way
Python Sites
PyPI - Python Package Index
http://diveintopython.org/toc/index.html
Pocoo
值得关注的项目
PyPy
Celery
Jinja2
Read the Docs
gevent
pyenv
virtualenv
Stackless Python
Beautiful Soup
结巴中文分词
Green Unicorn
Sentry
Shovel
Pyflakes
pytest
Python 编程
pep8 Checker
Styles
PEP 8
Google Python Style Guide
Code Style from The Hitchhiker's Guide
zhangjiabin1010
V2EX  ›  Python

关于在 Python 里执行 Sql 速度慢的问题,求帮助

  •  
  •   zhangjiabin1010 · 2018-05-11 10:30:55 +08:00 · 6137 次点击
    这是一个创建于 2407 天前的主题,其中的信息可能已经有所发展或是发生改变。
    ps:刚才的主题没编辑完呢,就发出去了。重发一下。
    一条多表联合查询 Sql 语句,在 PL/SQL 里执行需要 100 秒,数据有 8 万条。在 Python 里用 CX_Oracle 里执行这条语句,需要很久。我都没等完过,最长的一次等了半小时。后来用 Pandas 连接数据库查询,速度同样很慢,后来我缩小了选择范围。数据量大概有 3000 条左右,执行时间对比如下:

    PL/SQL 15 秒
    Python cx_oracle 4 分钟 20 秒
    python pandas 5 分钟

    所以请教有过此问题经验的大佬给解答一下,是什么造成的差异。我该如何调整呢 感谢!!!



    PS:优化 SQL 语句我估计不太可行,语句是需求那边直接给的。如果要优化也是需求那边的问题。我的主要疑问是:
    #补充 SQL 语句如下:
    select d.cplyno, NVL(SUM(NVL(d.n_sum, 0)), 0)
    from (SELECT NVL(N_GET_PRM, 0) n_sum, a.c_ply_no cplyno
    FROM T_FIN_PLYEDR_COLDUE@linkzysyscard.regress.rdbms.dev.us.oracle.com A,
    [email protected] b
    WHERE a.c_cha_cde in ('55001247','55020299000760')
    and a.c_edr_no is null
    and a.c_ply_no = b.c_ply_no
    and b.cardno='0650mt'
    /* and a.c_prod_no like '0650mt'|| '%'*/
    and exists
    (SELECT C_DPT_CDE
    FROM t_department
    WHERE c_dpt_cde = a.c_dpt_cde
    and c_company_cde in
    (SELECT c_company_cde
    FROM t_department
    WHERE C_DPTACC_CDE = '55'))
    and b.t_udr_date BETWEEN
    to_date('2017-11-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
    to_date('2017-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    and b.c_b2b_udr_mrk <> 'X'
    and N_GOT_PRM = 0
    and T_CHARGE_TM is null
    union all
    SELECT NVL(N_GET_PRM, 0) n_sum, a.c_edr_no cplyno
    FROM T_FIN_PLYEDR_COLDUE@linkzysyscard.regress.rdbms.dev.us.oracle.com A,
    [email protected] b,
    [email protected] c
    WHERE a.c_cha_cde in ('55001247','55020299000760')
    and a.c_edr_no is not null
    and a.c_edr_no = b.c_edr_no
    and b.c_ply_no=c.c_ply_no
    /* and a.c_prod_no like '0650mt' || '%'*/
    and exists
    (SELECT C_DPT_CDE
    FROM t_department
    WHERE c_dpt_cde = a.c_dpt_cde
    and c_company_cde in
    (SELECT c_company_cde
    FROM t_department
    WHERE C_DPTACC_CDE = '55'))
    and b.t_udr_date BETWEEN
    to_date('2017-11-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
    to_date('2017-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    and b.c_b2b_udr_mrk <> 'X'
    and N_GOT_PRM = 0
    and c.cardno='0650mt'
    and T_CHARGE_TM is null) d
    group by d.cplyno
    order by d.cplyno;
    第 1 条附言  ·  2018-05-11 12:39:56 +08:00

    这是用Cprofile分析的代码执行情况,主要时间浪费在了执行语句 execute 上。

    image text

    第 2 条附言  ·  2018-05-11 15:29:26 +08:00

    Image text

    23 条回复    2018-06-01 10:08:41 +08:00
    est
        1
    est  
       2018-05-11 10:34:28 +08:00
    问题所在,多半是是一次性把数据载入内存的开销。
    myyou
        2
    myyou  
       2018-05-11 10:38:12 +08:00   ❤️ 1
    查询数据的游标 cursor 不要执行 cursor.fetchall()一次性取出数据,试试 for data in cursor 逐条去取数据,因为 cursor 是一个迭代器
    zhangjiabin1010
        3
    zhangjiabin1010  
    OP
       2018-05-11 10:41:23 +08:00
    @est 在 python 里 我测试: 只执行 sql 语句 curs.execute(sql) ,而不获取查询结果 速度同样很慢。请问下,只执行语句,不 fetchall,会占用很多内存吗?
    zhangjiabin1010
        4
    zhangjiabin1010  
    OP
       2018-05-11 10:43:08 +08:00
    @myyou 嗯,是的。我并没有 fetchall。只是 curs.execute(sql)。时间差异依然是那么大~
    zhangsen1992
        5
    zhangsen1992  
       2018-05-11 11:05:37 +08:00
    优化 sql 或者 strace -p 看卡在哪
    hcymk2
        6
    hcymk2  
       2018-05-11 11:10:13 +08:00
    有完整的 python 代码么?
    zhangjiabin1010
        7
    zhangjiabin1010  
    OP
       2018-05-11 11:21:16 +08:00
    @hcymk2 就是很简单的连数据库流程,
    conn = cx_Oracle.connect('xxxxx/[email protected]:xx/xxx')
    curs = conn.cursor()
    query_sql ="sql 语句"
    curs.execute(query_sql)
    #query_data = curs.fetchll()
    #print(query_data)
    curs.close()
    conn.close()
    est
        8
    est  
       2018-05-11 12:02:03 +08:00
    @zhangjiabin1010 就是 fetchall 卡住了。
    ioth
        9
    ioth  
       2018-05-11 12:07:31 +08:00
    不能在程序里面,多表联合查询用一个 sql 语句,能出来就奇怪了,要建立数据模型
    和 pl/sql 比较速度没有意义
    你用哪种语言都是这样结局,除非 foxpro 或者 powerbuilder 这样本身就是数据库为核心的开发系统
    可以什么都不管塞进去 sql
    zhangjiabin1010
        10
    zhangjiabin1010  
    OP
       2018-05-11 12:41:11 +08:00
    @est 您看下我补发的图片,从反映上看时间 应该还是浪费在了 execute 的执行上
    est
        11
    est  
       2018-05-11 12:57:05 +08:00
    @zhangjiabin1010 ok。那就不清楚了。
    zhangjiabin1010
        12
    zhangjiabin1010  
    OP
       2018-05-11 13:01:57 +08:00
    @ioth 不能用一个 sql 语句。是不能执行还是速度慢啊!我从测试上来看,数据量少的话,还是能获取执行结果的。再请问 如果不写在一个 sql 里的话。除了拆分 SQL 语句 还有什么好的方案可以实现吗?
    aliipay
        13
    aliipay  
       2018-05-11 13:05:51 +08:00
    看了这么长的 sql 语句,表示我不想说任何话
    lieh222
        14
    lieh222  
       2018-05-11 14:29:25 +08:00
    抓个包用 wireshark 看看吧,应该可以解析这个协议
    bxtx999
        15
    bxtx999  
       2018-05-11 14:42:14 +08:00
    1. python connector 的问题

    2. 游标(参考二楼)

    使用
    for row in cur:
    替代
    cur.fetchall()


    参考: https://stackoverflow.com/questions/26783735/python-5x-slower-than-perl-mysql-query
    zhangjiabin1010
        16
    zhangjiabin1010  
    OP
       2018-05-11 14:53:54 +08:00
    @bxtx999 和第二条应该没关系,我测试过第二种方案,效果一样。可能是 connector 的原因。但是我用 pandas 直接连接数据库执行语句,效果和用 cx_oracle 一样慢 。所以现在我也不是很清楚瓶颈卡在哪里了。您有什么好的提议吗?
    whx20202
        17
    whx20202  
       2018-05-11 14:59:20 +08:00
    你把 SQL 在数据库节点上执行,输出的 9W 行,直接重定向一下文件,看看用多久?
    现在你还没能够证明 SQL 本身很快啊
    whx20202
        18
    whx20202  
       2018-05-11 15:00:28 +08:00
    @whx20202 #17 原来这是新帖子 搞错了我再看下
    zhangjiabin1010
        19
    zhangjiabin1010  
    OP
       2018-05-11 15:20:53 +08:00
    @whx20202 感谢您的回复~刚才已经找到问题所在啦,原因是我错误的打乱了 sql 语句的格式造成的问题。他们给我 sql 语句之后,我用 双引号 "sql 语句" 存放,然后对缩进格式更改过。最后我直接使用 三引号代码块 """sql 语句""" 放 原格式语句 就好啦、
    zhangjiabin1010
        20
    zhangjiabin1010  
    OP
       2018-05-11 15:26:20 +08:00
    原因已经找到:我错误的打乱了 sql 语句的格式造成的问题。他们给我 sql 语句之后,我用 双引号 "sql 语句" 存放,然后对缩进格式更改过。最后我直接使用 三引号代码块 """sql 语句""" 放 原格式语句。可能是某些空格
    或者缩进单位影响了 sql 的执行效率。
    经验 :大段 SQL 语句要小心更改。最好直接使用三引号代码块

    结贴追加补图!
    sujin190
        21
    sujin190  
       2018-05-11 15:28:58 +08:00
    如果是 buffer cursor 的话,那么 execute 阶段就已经完成数据加载到内存解析完成了吧
    而且事实上 cx_oracle 是用 c 写的啊,和 python 语言性能并未有直接关系
    试过 pymysql 纯 python 写的从 mysql 取出百万条数据也不是很慢的吧
    zhangjiabin1010
        22
    zhangjiabin1010  
    OP
       2018-05-11 15:32:30 +08:00
    @sujin190 感谢回复哈。找到原因了,是我的问题。可以看我的最后回复。
    ioth
        23
    ioth  
       2018-06-01 10:08:41 +08:00
    @zhangjiabin1010 改 sql 要小心,确实。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2718 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 14:23 · PVG 22:23 · LAX 06:23 · JFK 09:23
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.