利用查询提示优化SQL

9/1/2015来源:SQL技巧人气:2038

利用查询提示优化SQL

数据库环境:SQL SERVER 2005

我们来看一下SQL语句及对应的数据量

SELECT  COUNT(*)FROM    cinvout_02 aWHERE   ( a.dept_id IN ( SELECT RTRIM(dept_id)                         FROM   ctlm2320                         WHERE  user1_id = '000008'                                AND zone_id = '' )          OR a.dept_id = ''          OR ( a.dept_id IN ( SELECT    RTRIM(b.dept_id)                              FROM      ctlm2320 a ,                                        ctlm1003 b                              WHERE     a.zone_id = b.zone_id                                        AND a.user1_id = '000008'                                        AND a.zone_id <> '' ) )        )        AND a.out_time >= '2014-07-01'        AND a.out_time <= '2014-07-12'        AND a.sts_flag = 'S'---------------------数据量-------------------------       SELECT  COUNT(*)FROM    cinvout_02 aWHERE   a.out_time >= '2014-07-01'        AND a.out_time <= '2014-07-12'        AND a.sts_flag = 'S'--214555        SELECT  COUNT(*)FROM    ctlm2320WHERE   user1_id = '000008'        AND zone_id = ''--0        SELECT  COUNT(*)FROM    ctlm2320 a ,        ctlm1003 bWHERE   a.zone_id = b.zone_id        AND a.user1_id = '000008'        AND a.zone_id <> ''--3768

数据量不是很大,cinvout_02也走了索引查找,而且返回的数据才有214317行。按理说应该不会太慢,

可是,真实情况是,整条SQL要跑4分多钟才全部出结果。

光靠猜是得不出答案的,我们通过执行计划来分析为什么会出现这种情况。

ipIAaJoKH+5N357qI+L/ndJnenvHYvFpuTxV5LotUHnZ5jJtZQvphX3KcyBJVWhuG7wtckF24Uguj+SKkkMord5HO1vDo+FW/Ncpz2Q1xmnak1d/QSh0XcHOOjJOcbQ1d9PTTs9XptSYLRB5bD1NcZFn9B2RWVhqAIDmqFnkRZ1EPPYlxJ20q2PRn87TflaPGvVA4nUbpWKR1z37lL4UTKtC80xtYbOYfeSi13WY8WhnazjciS6RKD2eyWqM07TIi6m1IFQg1RXmg00lIk+/X8kUIo2JPM0ypXjyaJKnqccz8uqK/Q8AoH7qFnlRr5j2yKRPObJMr5ySjoX1TuLbo2bf0zQVT9d2zz5l1jGhCvloC4vFROvw6yqVZ0zckkVi6fDPMpk17LVN0rW4INLfErVoFR7kGvaLGk0sTcOUfYX8VU168sj6R7qW1+HSSyJoaRjPLakTdqA3g6FxOgCgbmoXedo6slO/Fy9UND6no8WlLoGtIouiKyN7VVOy7kfLM42j3XXn7VPSUjDj3R02bNldFdxi2nBGR67kuqIiN4uM6T1j+ZKPvdsm6dpVEAMDLq+NsxztZWtLEzOaH5GkIXVlucqUGrMN07UAgE2htH7fl+o/49G+WYJ1u9xybdYC+1Rcx7pN2yTdxhZEFUDkAQBqpdZ+n0zVVkgLRAyjkLnoxyVKvZ8W2AfawkHbJN3GFkQVQOQBAGoF/X4NwFwM1DEHbZN0G1sQVQCRBwCoFfT7NQBzMVDHHLRN0m1sQVRBKvKeTWc4cODAgQMHDhw4enPEIu+iYgaDAf6L/+K/+O8FAACAWlglDC4uLqpzGF5cXPzhZIUDBw4cg8FgpUN7ImfId3B4HjCXZJCMGmXG2RaspvAJKZaOeVZGOjiKHbWKvMFg0PjoggMHjsYP714e/f6anXvz2WjPAZHnACKvrwc8eThw4Kj7gCevrs69+Wy054DIcwCR19cDnjwcOHDUfcCTV1fn3nw22nNA5DmAyOvr4SnySvj2ucuT99HoyiDmlbs8JA3/aHTl6uie5UR6+r33Ahay//qA8/pYzIx57h9OVn94N0zCgrc+UpHHrwzC/Ynxz49GV2i0JNv2PCQn3g2vvDcxkrXaajAYXHlvYjOCNdtR/PT2J29dHQyYYdt6pDZM8m+GeB+Tt66mtrr3XjBIzT5+Raxy6p+2+ikWhKOArIezDtgqRhWFKDa6tS80KNeTN/l6MPip0YT8M/j6ZPWd1ep3w8HNcRoz+WcawTylPwfMJRnER5E4GIeDcOyM0VE6I/I2tfaWUu1Fkae2SitB5A1kT974FSVBphpdUTrJHEj8xpt77wVEsiSjtd/YKZ97N0yHf03AOUQekU08kzwP994LXrk7eetqIgGuju7dDa2SJdMI6p+2bH80unI1uEKzN1hD5JWrLdyppb9O3roavPXR6t57QWIoTbF5HvfeC668N0lOD65czaqHVORl1k935KyM5b2XHFdxxPRpYmWUeMmevMnXg8FgoDpu9Ptp5w5zMYP4KBKRySh+/IfIK2BSn7My0tnw2ltKtW/Ik+ep53zHm3j4t4wubpEnnmsEpgrMLvKujt56nThyXCIvdeMNXh/vx2dN3rpqyWe2EaIE7dmOs5f8eje88npYiROowOEWEET77r/OJR1RbN7H3TBRt+NXro72VQQ6rQqKPL0+5DFvfjderqvkyk9XPHnB10fhIByj32edO8zFDOKjSBzAk1fMpD5nZaSz4bW3lGpfucizePImb10d8OG5sMjLGIGyZkLFi7JT0hCXyLtnFQT6WRannSlifG9ZXd2W7ShCPDW82n89eOturmF7/Aqbo0xu9q3X9XDNQRu89RGduDTUz0ejK1fDV64mvkzp0lTGGZKOe/Lo5LjdK6Yp7D/8aHTl9bGWeCOePKNKuO6FzgXzqeTE5sqwsfq3G1mcj+6EJy/4+kT13ej3084d5mIG8VEkDiDyipnU56yMdDa89pZS7Ztbk6cGHk0WUCweETNaFSKPB6pEnCJPCTWnyLP5n6x+Ke2Wyexwpq1kDTp+herR7INqqfErg0QgfjS6MuCey/3XY+2eTKqm58YhXORl5MQh8vR5do9iTW8nnvZ95e4q8ufdo05QYzmdIaSyCsIR7nGbWfdCiiP2SuqZj4smDhHMLqdvJutpT99jUIEnb7L6zjh6vke/n3buMBcziI8icQCRV8ykPmdlpLPhtbeUat+UJ48cakTpgSdvkoiG0kWe65aT4TnTkxfNCCsPVuGZPlOccQ9WIjvckm4NkScrPIcvSk/zlbtKGEV/kAIq7skjOimnKhKK3nYvogE1Qam7aaM6kCnyxNJ01IH8RyWevKhnp0/5m93vw1ySQXwUiQOIvGIm9TkrI50Nr72lVPsGPXnqcC5jat2aPP0nSQHcey8wVrxVKvJU9jLW5N2LLnE1kF9bth25RF6Uh7uSMCok8sQ1eRYfnv42T0aa4SukvF55L560Fc7NNV1rW9iXdRhFb78XWeQZ4t7fyHWJvIo8eavVdyajnwrCm8k/H48C6UW8zej3YS7JID6KxAFEXjGT+pyVkc6G195Sqn0znjzygmQZnrxa367VV87Jg/r4Fe5JqnhNHl1nZn27Vs055n0zwD5dK8mC6Hs05A1WfbqWmiIqendOmHz8SL9H7TBStt7R+JXBQKuBA7JCdB2RV/jFC14lHPeim5TNtCq/XXJ6gelaQUO32pP3ndXqd8NB+vLdWC3QXn1nNb45CH9X7uX72O/DXJJBfBSJA4i8Yib1OSsjnQ2vvaVU+8Y8eWRduW0Zk/RBuGi4MqPZvnXHxkvLkec7eSuymtD6zRRjmLRM8k5YsoXfrtUXVJnZXtc3Y33xQrh9XROn5aWrwMFgMIi/6hIlbi0j9lU89ulBvkpP4bo79t4PcZuZltH0sVQ/xYKw1FKnhflHdox70b/XkxqQFpCaJQ+u8HMtRjZvkH5th9vEXJzq+17woDJP3mr1ncnop9IvLKwej5Tx6GcXaHmEvyuENN01l9S5w1zMID6KxAFEXjGT+pyVkc6G195Sqj12vGjmED6K5vhOHo4NOIp8J892lOF4K/0o2ZOHw9q5N5+N9hxliLze0hmRhyPnUavIa3xoaevh49vbtCP1F6oHrk2ySXl1wCXyGjNyyZ48HNbOvflstOeAyHMAkdfXA548HDhw1H3Ak1dX5958NtpzQOQ5gMjr6wFPHg4cOOo+4Mmrq3NvPhvtOSDyHEDk9fXgIu/ZdFbRAU8eDhw4ogOevLo69+az0Z4DIs8BRF5fDy7yqmYwGOC/+C/+i/9eAAAAqB5N5FU6XVvqgwcAoKuYvUGxEOAA5mKgjjkoq0laQop58kAJQOQBAOoGIq8GYC4G6pgDiLy+ApEHAKgbiLwagLkYqGMOIPL6CkQeAKBuIPJqAOZioI45gMjrK9ki72hnELPexmb+ZTYZBbaNYybpriWNbi5D8uGbjckoCEaT7HjjcBCO6X3SS+jh8U96YDCapNeajIIBuaoWbqRjZmMcxilmx/dB2xFoHIppyXHCcZrpfHUjvYxuB8Es45AlPhkFRlmINldXTK/mthQrCFbAUhnFUcxaREIECyS/2jOWc5smVSX8T5EoXeRtQM+QG5iLUYa26Pe2ZtWFlC7yelsQpZMh8k6HW4m2I382JPL0odkYktcnhw6j46pfLvwSj+9di0yuISbiGPijPlT9SsOdmYmzodrRuuOQ6ssTW6UJmsIziZOaOW3PtpYt1w1aR2gMi1nYXcb/9LE5j5PVAWkFEQQ0JwMi8nwKjmResEBWxiajIBxZCle88ZK61nJF3ib0DAWAuRjraQujg+oX3RF5PS+I0skxXXs63NoanjYn8irrC/NeoVhGvM5KhlCbhigg8oLRKDQ0YkZmkmyUJvL0ZK1/Gv/i92PTGH520W8nyywW6SenzfKVS+SNRmGS1jgMwlDKjKVipCGWElonn50ReZvQMxQB5mLAgeSgOyIvorcFUTr+Iq9pT56tbacOfzUKhmEQzSmNR0EyuUTCB+Z4SX/lk2XiCG+G8ulHOkFGfSpSeHTpcGwVVfYxXgxkqsUUMbIxDW1nF3njUDNUOBLNm9wXS9/ipWJxdHs7PXk2s7C4KsRmFjb36S+s1/LkjSbjWOWNw2A0lq6b6clztA5HxqJ/kRRG6fx40gBYsi0UeZvQM9js6ATmyjQItIUCIq+v+Iq80+HWYB0/XlUiLx3J479Uv0H/iMawpD9JY7JB1D3YG9dlPZk5y6jl3DE8D4yr8sjEqzSg0LVqUrdLOkRt8k5Mx7Q0FXlG/HEY9/7JGjXDvHaB4C/yJiNJJ+pLzOQJR6HguFbmZhHVVLbNzXElp8iL/zkOmfRM07fXxjVE3oQ6ASOxTotHTLMzIq9XPUMxYK5Mg0BbKCDy+oqXyFtf4RUQefE4nqz+H9ClSzSqrX+Rx9EkZuSqcJyrDeH2viPqiMT+iyZiS1w/1y7y1vPkRSmIxrHYXxB5QvxxqOSN27zsLvKIPJKi9WerWdyePNMs6U9Zg5MWaF4pp8iLqjz1q3ldl4SUIvKsQ3WOO/OkcpHXr56hGDBXpkGgLRQQeX0lW+SVovAKiDwp2GjEhfsmd4g5altJXoYVBI2uEiaJt0YO1++GJVhs4Nd/mowCy3ovjq/ImzjfEnCKvJxr8tLkZU+eVABCpqXb0cyifhsbt+NOm+c6r8hbTUZBEOjv3OQra0uJboLI24CeoRgwV6ZBoC0UEHl9JUvkHe2s++mU0kTeaiK8FDYRZhlsfRNxIyVKQZ8Z0EQWj61lg04fxuMkS8uWuOOiNlGlZ6aoyIuVkYfI81mTp3JlGoAXhJCuQ89q3j7zz9LerhXNon4MdPNn23ytFy+04bCooJct4M6nsSavmyJvA3qGQsBcmQaBtlBA5PWVDJGXfiRvMBgM1vLorS/yVrqHnvQd+qIO6wNooMWkqcWjeuQgUnLCOsVAHEm0l9KCaFZpH2QLTxIRlnxpvhetQPSv4lEzSUIh7dzFdExxZ12TR7M1DgeDIAy5ed0iz7SXM44pg73qhnYZpuMks4j/9rI5G8Hyizyfn8zM6GJabh2ZYtQm8nhz0KuE8CiQo9stV+St+t8zFAHmyjQItIUCIq+v+L9duy6liLzirN1j1kMl9144G/7tqC7zomWXi3d9S18AbqEnby060jMUAOZiNFbHukDXRB7wpY0irxI60ze1RMaMtR0vMqPXYd740b0N1smDvn6wdXeQs775VwknEHk1AHMxoC0cQOT1lY0ReQCA1tAikddfYC4G6pgDiLy+ApEHAKgbiLwagLkYqGMOIPL6Chd5z6azig6UGQAgAiKvBmAuBuqYA4i8vsJFHgAAAAAA6AeayMN0LQCgaszeoFgIcABzMS4KuZQ2hLKapCUEnrzGgMgDANQNRF4NwFwMiDwHEHl9BSIPAFA3EHk1AHMxIPIcQOT1FYg8AEDdQOTVAMzFgMhzAJHXV7JFntrZbM0tbCsqM9cn+8nGN3V8f3YclvCV2DJJNq0Sv8Nr7NYVb3VKQrT9u8zNviw7pBmm1r+rnB3f+9bIP1haWXumZXxXWd4hbV3SncHkTeTa8h3sOihf5G16YxfI6HK7cAvlUobI620j7ZrI62