SqlSever大数据分页

8/10/2015来源:ASP.NET技巧人气:5188

SqlSever大数据分页

在sql sever中大数据的分页一直是难以处理的一块,利用id自增列分页也存在不足之处。从一个相对全面的分页看,sql sever2005中新增的row_number()函数解决了这个问题。还是从一个实际项目开始介绍吧。中国铁建股份公司的项目表中数据很大,开发之初用的是GridView控件自带的分页,在运行一年以后,点击下一页终于是难以等待了,系统需要优化。对于分页的改进是业务的需要。于是,我采用了Row_number()函数分页。也算一解了燃眉之急。

说明:该篇文章只是一个案例的说明,更详细的理论请查看随笔《大数据分页实现与性能优化》

提高分页效率主要有:(1)充分利用外键,以提高表连接速度(2)尽可能使用自然链接,尽量避免使用外连接(3)尽可能将记录少的表放在连接的左边,以减少先处理的记录数(4)尽可能先选择,后连接,以减少处理的数据量(5)有可能的时候建立视图,充分使用数据库自身的优化功能

以下为分页查询的sql语句:

select * from(select t_gcxm_test.sgdw,t_gcxm_test.id,xmmc,t_gcxm_test.gclb as gclb,gchte,gchte - kl2 as xmsyjzl,kl2 as klsl,dwmc,lry ,Row_number() over(order by t_gcxm_test.id desc) as IDRank from t_dw,t_gcxm_test where t_dw.dwid=t_gcxm_test.sgdw and (sgdw like'gf12%' and ((sgdw <>'gf12' and fgcid is null)or(sgdw ='gf12' )) or lry = 'gf12gao'))as bwhere IDRank>=50023 and IDRank<50033

其中的IDRank的值可通过web网页,传递过来。把分页的sql语句写在存储过程中,可以充分展现分页的效果。

图一 单表分页耗时

iptYyzv45z45CTnuslx3MVNOmPh7DiO49hOiHFIYrnFNIk3NkYGm4CxsemEEAbRGQQCmx4BkrZAQoAaUI96aUtb0nz1q6q51qy5frNq1mr2Xqv29w0+VtWcs2bNWf387VpLY0/tPGWGzafFXaLBnsy2pbaNm2R6vGimx1BIbXshItbVua541yDtejXXtq+NWl/mQtuevG2OWvtn0+HbVljPfP95+7WO7vHYqo9jAmvpHzuJ/rFl1cqqdc6tq+u625g14h5j1iau22vMs/uMee7ASfP8oRnz4uFps+nwIbP58H6z5fAryeces/nQbrMp8cWD4p5kem/igbTcpsOHzaYjR83mI9Nmy9HjZvPRmWT6hNl09GTqi0dOmRcOn0rqNmbjwcQDxmzYb8z6ZJ3PJut+5pXMdYlTYtKmkC8ndfzb5deaF5Pl1yXzms8kvv+DEyYWKWvb8KynrOfaO35htibt9vNEWfall15KlelQuv1cn+SF3JSv66VkXVr++mRb1fWC7/woXe+WLVs6lHTJ15ar6+akzRf9+7Vm+2FjhjKYIbZPav1Ewhiz7aht3yZZuBG4Fm4Gmto2Q0Sso3ddUa5F2nVrLk3bNTTXQNuWXNu+vK1z7XBtK6xvf44nW0fhWFXXh6jpHzuJ7nGlHl9Wrb7hUA1caObBDAlkrE0Gyc8kg9ENB4x5/tBJs+nIjNl8dNpsnT5kXjq232w79op5+dhu8/L0LvPS0V1m65GdifL5itl6dF9S7qB5KS17xLx0/Kh5+fixxOPJ9EziiST9hNl67KTZcvRkUq/UnwU2JKjxXLJON6AhwYyqgMZLEcEMUQbqr7zySpRS1gYz/IBGTDBj06ZNqTLt6qbbTzVIkVsZzBCdAEKVNpDx/PPPT9Tm+xHQGIlghjjsJ/FomGxDZds20bibgqu2vRAR66hcW7xrkXa9mmuztmr9mQtte3Klfbla22fbtC1Dtb0wXrvvsv2o7d9YW8elW6e6TkRN79hxjyn12HLV6hsu1SCG1XkrQwIHz8hbGclg9IUjp8yWaQlCTJttM4fNjhMHzK6Tr5idJ/eYHTN7zPbju822Y7vMtmn53Gu2Hd+feNBsT8puP3HU7Dh5zOw8NZN4wuxw3H7yZFLfSfPy8ZPmpTSwccpsOiLBk/ZbGvJmiAQS0kCGq7TRsU4wY+vWrVFKWTeY4QY0YoIZGzduLFXKtIIGyTIho4IZohJM0JR1rl+/vqVtg5auLV/H0QlmiCNyEg+32bbUtnGTLNwQXAs3BF9teyEi1tW7tjjXIO16Nde2r41aX+ZC2568bY5a+2fb4dteWM98/3n7ta6t49LW11JbJ6Kvc8y4x5OvW66lVt9wqQYyRC+Y8WwyiN54KBlMHz1lth4/YV4+cdzsOHXU7DKHzB6zP3Gv2W1eSQMbEtTYMbM3cZ/ZPnPQbD9x2Ow4OW12njqelD+RlDuVlDcFk9UleSYpc8psP3nKbDtuzNZpWV9nQCN9MyNvlxbQsMGMF5LyMq8FMsQvfGkyHaTHKGX9YIY1Jpjx7LPPlpqWycurwYnc6GCGqAQUfG3gwmrfwAil9+LIBDPE9kmtnzgYY7Ydte3bJAs3At/CDcFX22aIiHX0rivKdUi7bs2labuG5hpo25Jr25e3dRgcru2F9e39eGodl+6xyjGB0ebHi3sMuceV1eYV1OobXiuDGckgdPO0MS+dOGm2n5oxu8wx84o5YvaZQ4kHk+kDRgIbu07tMztP7jc7Th4yO04cNdtPHEumTyTlT6VBi6Q6k4xtU/clJlWnnzKfrC4ts+OUMdtmjNl6LA9oJOuWN0PWJ4XlKy+Fr5rYz1wJZnzrqhvNc3uOp0ELm+4HMzTltzRSkzpjlaBJWTDjfC8woCll3GXU4ERirWCGNdlew+Cm/SfNt668fjSCGWL7xNZPGIwx247a9m2ShZuBa+GGoKltM0TEOnrXFe86pF2z5tr29VHrz2xr25Jr25ertX82Ha5thfXN95+3X+vaOiZtfS21dSL6eseNe0y5+uVStfqGXxvMWJMMhuU3MySYIQGFTceMefnkKbMjfcPiuNlnjpmd5qg5kAY1Dps95pDZlaTsPHXI7Dh51Gw7cdy8PHMi+TTJMlmgIqmuFcQ4kJiMy1NlWtKScW8a1JDyLyfLtQIaR4zZIAGDpJB87UV+lFQCGenveiTaoIYEMy69drmZevlAGmywwYzaQY1k2RglmHFNSTCjW7XARFfBDNEJKsyFG5L9tWHHIXPZtbeMTjBDHOWTeHhMtqGybZtox01BLNwQNLVthogYq3Jd8a5D2vVqrs3aqvVnLrTtybbXsG27tC1Dtb2wnvn+8/ZrHd1jslUfxwTW0jt2/OPK6pdL1eobcpVgRvpmRhrMMGanOWleMSfMfnPcHE4DGtNmnzlq9pgjSZ58reSIeXlm2mw5NpP+ayXpck5AIxnftoIZh3LdoIYEO5LVZwGNU8ZsnUkG8tNZQGO9DORLAhqbkwpuvv8pc/svnjTPJ2Uk4NB1QENMli/TBjO2JO2SeS0w0a1+UKLrYIboBBdm0+eSfbAp2Sf3Pb7O3Przp0YsmCGO6kk8VGbbUtvGTbJwM3At3BB8te2FiFhH77riXYO069Vc2742av2ZbW1bcm37crX2z7bDtb2wvv05ltrHgVVbF6Jmfsy4x5A9pnxtfkGtziF2tzGrk4F8K5iRDEY3HjFm8/EsuLDTnDKvmJPmgDlhDpsZc8gcM/vMtNltjpjtpw6bl2cOm81Hj5rnDx0z6/fNpF8ReXHamK0niwENCVwkY9uCNrBhAxo7E7clbj2RDIqPZe3YkBSSNklAww1miDKAfnbbQTN5xfXm5hWPmNWbXzEvJmUl6OAqv3VRpQQPUpPly5RghgzS7bwEVAalBDN2JOvS8qJMNu5sKQGetVteMXc9+JS5+KobzIYdB9NtPzLBDLF9UisnCtYw25baNm6KhRuBa+FmEFLbZoiIsSrXlfwapF2vhsH29VHrz2xr2+Jo25i3dy4drm2F9bT7Lt+Pzn6tq12+fTxYtfUi+ubHi38s+RaOLatW3xBbEszYdkp+rLMdzDiSBzP2mqPpWxkvzxw0m48eNM8dOGTW7Tlint5+1KzZdTL7qspRY7Ymy2/PAxUSsJAARlJ1QUlLxsHp2xvJ6tMfB00DGieNedELaPhvZ6xN2r5u9wnz/CvHzW0PrzPfX3Jn+oOgvfhN67/Xc7KPyo9njqJX3HinufORdWbT3uPmuVdOpMGmkQpmiO0TWzlZMNJ8G3rbtokWbgiurRuCprbNEBHr6lxXvGuQdr2aa9vXRq0vc6FtT962XK3ts+3wbSusZ+/Hk12+fSxYtfUhaubHjz2G3GPKt3CMWbU6h9CIYMZeJ5hxMP8x0O2nDprN0/vMxgP7zNTufebJl/eZhzftN49uPZqcg8Y8IwENeUPDZG9cyNsZ8ibG0dwkK1WmbUAjWXUa0JDyLyduPmHStzykPfKVk3X52xmtgEbSdlG+WiFvB8hvaMT4coTb5tjt/TTZwLPp+t3HW189GeFgxgidxENpti21bdwkCzcC19YxFFLbZoiIdfSuK8q1SLtuzaXDdf2z7cmV9uVqbZ9t07YM1fbCevbneLJ1FI5VdX2Ivs4x4x5D7nFldcu21OocQmu+mSG/mbFb3so4sd+8cHiPefaV3eapl3eZh1/cae57Zqe5/7n95pEtM+appO5nDhrzgnxdxWRvZ0jAQt7GsMGMY/mnDWgk4+40oCFl5SsqEgjZPJO95SFvZ6zbr7+d0TJP8383Q1P77Qzfbv7FE1H7TYxu7fg9jG7Ngwuz7djOw6fMqLkL+6q2jZuk1mdExGFQu2bNpVobh1Wt/bOt1i4cPbV9G6tWHyI6Hsk9esrsTnxl+pTZe/yU2X/ilDl06pQ5nHj01EkznXj81InkcyZJO2YOnDhq9k4fTuo4ZLbvP2i2vnLAvLjzgNm067B5ae9xs/1gVucrSV0H8nqmE4/nzihK+rFEKSflZf0HTiZtmcnatCdpm7QxNWlzP9wTa7LOOr6CqWOf+LtzDCIiIiIiIuIg/Ku/z/zrfzjH/M3nzzF/+4VzzGfPOcf8XeLfn/OP5h8SP3/OF5LPs83nzvkH87dn/5351FmfNZ/47GfMx//mb81H/+rT5k8+/jfmTyc+Yz76ybPMmZ8+x0x87hzzSanvH7O6zkr8h8TPJ54dUPKlnKxXlvnbxL9J2vKppE2fPKvdTq0POHyOHTp0yFgPHz48Lzxy5Mi88OjRo/PG6enpeeGxY8fmhcePH58XzszMICIiIiJiFxLMaLDaoL+pagP/JqoN/JuoNvBvotpFGRERERERqyWY0WC1QX9T1Qb+TVQb+DdRbeDfRLWLMiIiIiIiVkswo8Fqg/6mqg38m6g28G+i2sC/iWoXZURERERErJZgRoPVBv1NVRv4N1Ft4N9EtYF/E9UuyoiIiIiIWC3BjAarDfqbqjbwb6LawL+JagP/JqpdlBERERERsVqCGQ1WG/Q3VW3g30S1gX8T1Qb+TVS7KCMiIiIiYrUEMxqsNuhvqtrAv4lqA/8mqg38m6h2UUZERERExGqdYMaNZuHYQrM0HfAvTabHzFjAhUuTMksXmtPPe7ojUJD69Hnm9NMXmoWn52W1MnNieb/aJtvBBgVWJ31Ry1hPN+etLgYRhsPV5vxk+59+/hp18C/etFDrT9Gy5efaNeef3mqfO+Bfe8Hp5sxlxSDA9PRac0GyPbQ+Fj3dXLDWX3YuXWbOHDvTLMvntYF/lcvOlD7pecOqNvBvotpFGRERERERq/XezJDBvgQ0ks/TzzNPpwGAp815p9sgx2GzNBkA2wCFTKsBDQlmLFyaTMuyyWD/6Xbe0+edbsZaef5Asr0eMVh/j7qD/tVJe04/b3UhraAEM5L2qnmJSxd2BjOkTumjtL/Yv9w8r3S9dV26UF+X4sKbsmCABDPstOpNCzuCGRJAGFt4UxYIST478k4/36xR0s6X/vY9MLLGnJ8cmzcl024AIBzMaAcFYpW6xs5c5qRJcMHZnnnesjOT/l2w1inXo8vOLOwz65nLvPWffoFZmwYAOoM1Zy7LAgNlwQzbP7u8Xabl2gvM6RJMSaZtHwv5A1Ib+DdR7aKMiIiIiIjVBr5mkgUzlsqg3Bkc2UF4+22Lp815C89LdMpopsGLpHz6xoYbJPHe3MgH5IX6vWBIP3SDAFHBDL8/Bb1ghpRP+rjareNIHiQqpK3O+tavtzrSN2Wyfthgih302wCEne46mLHm/KRvebAiySsGLiSwkG2P89fYtGwdWR2SX8zrxeJbJQsLQYryYIZ8Km9fyKBdAgNlaXmAoVB3kpYFMQL19qz/ZkY+nwz4pZ/t4ELevzwYkG2DbDoYzLD9S+ergxlZmdl5y0Mb+DdR7aKMiIiIiIjV5sGMp8w33pQNDLNAQtybGWHd5dt2BEL8YIYoAQ/3DQ0JcNhgSJ90gwD9fjMj62OxjB7MSMz7VkjrwTSIIYP7NJiSfc3EBivSgIbkOW9T1A1mFMpLYKMQuLgp7ePCQp1eAEMCIN7bHD2bB1iWyRsGaWCj0yz4kA/206BANnBvvUlhB+x5wMAqbyK0AxfZGxGdQRJHCXYU3uLoRe8NjFQJKuTBjLTNbmAh65NbvuMtjtT2Mln/isuXBzMS8z4WygxAbeDfRLWLMiIiIiIiVlt4M0O+AtIOZmS/eVEcCOWD1aVZIELS9MCGFszw37IIBDP89MLbHP3RDwD0782M0NsWgWCG+hZHd0oQRW+fYh5QqBfM8N+syN7EaC2fByrct0CygEf2NZDWvPc1lN5st8ENBJS/mdFOk8F8tk06Axkdb1rIIN5/c8PXvumg5XVtsd3umxnFAEReLg8GhN7MaE/b/mXp0cEM20e3zADUBv5NVLsoIyIiIiJitSXBDD2A4L5dYcunv4PhDJZPP+88Zfl8QN+ajwxmdCzXu24QoL9vZgSCFrXTu7P9VkgWVOn4SocEE7p+MyN786IVmEh0AxetutyARcebGJ119GK6/qSfsj43gBAbzGj9LoUagMiDBnY+JpjhL9OD0gf3nLKefsEF6TrObAVi3GBGsWw7XQtguEERffm2bjl/ucGoDfybqHZRRkRERETEasuDGd6PSsqPcWrBjCxIIEEIG3Rwp3M73rAoC2b4b3C4873rBgD6+mZG8E2LUNCiv7+bYYMZra+bFFxobuolmKG9VdFKc9/aaE9L/e7XVDrf7uhBCZRIv2T9Mu18vSMmmJEGC/LgRBY48IIQ/lsWUcEMGyzQ8rpRAgdOfUkbbDCjM5iQ9y+fz7aBn+5Md7xhIXkRb2ak5dw3OgajNvBvotpFGRERERERq1WCGU+b8xYmA3z5nQrn9yokLxTMWHqeBCmKAYz0jYVCACIf0LfmA8EMWWch6OEv17tuAKBpb2acngw0ZZ+l03kgoRVU6PObGa00L9CR1asFLvr3ZsZNC4vrdYMA5cEMCRCMdfy2RRbQcAMR/lsWXmBB1V+mB9MgghuM0m0HH9yghRvMSNrUClq4ZfK25uXjgxn+coNRG/g3Ue2ijIiIiIiI1RaCGTIAlgHdQglmyD+JGvNmRjKozAIexWCGDX6038iQgbsb4FCCGen6vCCI/4OgfdANAISDGfLWRLvvlaZvZOR9rPObGVp6l2b7pj0vQQVpW+Htip6CGZ1vVaR1JMdAu1yivCmRpC3U3uToUzAjtZtgxrJlJcGGtWZtIZhRDF50BjwS07clyn9ItHu94Ei+Ljvg7/wBzzPNBWkb7XGZtPUCdxk/mCF9cZePCGZ0BDcGozbwb6LaRRkREREREat1ghlPJYN3CSQ4QQfnzQyrH8xov0WRBSeyQVQyQLdBCPnM63CXLZbPLbyRkau0oVfdAEDlmxmJra9tpAELSXMCHUnb3LJ+QCEzEMzI+1ZI68F03efJds/aZgMV2W9LJIN+JZjR2vYB3SCFGvzIv+5RTJfA