sql语句的优化？

2012-08-17 05:12:28 142 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Sql语句的优化以及Sql语句优化工具这周在做项目的时候，发现有些Sql语句写的复杂，一个复杂的Sql语句不仅性能比较低下，当涉及到重构的时候也是不容易理解和读懂的，并且一个复杂的sql语句会显著增加程序的耦合度，所以Sql语句的优化就显的很必要了。 Sql语句复杂化的原因首先列举一下Sql语句复杂化的原因: 将业务逻辑由Sql语句实现数据库表的设计不符合第三范式 Sql语句中含有大量冗余的不必要逻辑没有很好的利用视图如何优化Sql语句呢不要超过5个表以上的连接超过5张表以上的连接会大大增加sql引擎的消耗。考虑使用临阅读详情

select top 42 * from View_jydhwxx where fgsbm in (101) and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  and jhrq  >= '2012-08-10 16:49:06' and jhrq  <= '2012-08-17 16:49:06' and jydbh not in (select top 0 jydbh from View_jydhwxx where fgsbm in (101) and jhrq  >= '2012-08-10 16:49:06' and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  and jhrq  <= '2012-08-17 16:49:06' order by jydbh desc) order by jydbh desc

以上这句语句是没有问题的，查询的速度是2秒钟
但是如果将 select top 0 jydbh 改为42的倍数即 42 84 。。。的话速度就很慢提示时间超时
select top "+start+" jydbh
start 是接收翻页的参数第一页是0 第二页42 第三页84 类推.....

怎么将上面的语句改为翻页的时候速度快点

...全文

计算机保研/考研面试题——计算机组成原理篇本文总结了985和华五学校习计算机保研/考研常问面试时的计算机组成原理题，欢迎点赞、收藏、评论、关注阅读详情

jiery666

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

jiery666 2012-08-17

打赏
举报

select top 42 * from View_jydhwxx where fgsbm in (101) and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  

and jhrq  >= '2012-08-10 16:49:06' and jhrq  <= '2012-08-17 16:49:06' and jydbh not in (select top 42 jydbh from View_jydhwxx 

where fgsbm in (101) and jhrq  >= '2012-08-10 16:49:06' and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032) 

 and jhrq  <= '2012-08-17 16:49:06' order by jydbh desc) order by jydbh desc

将以上语句用not exists 替换怎么做？

以学习为目的 2012-08-17

打赏
举报

支持小F姐，用ROW_NUMBER实现

jiery666 2012-08-17

打赏
举报

public DataSet GetRegionsInfo(int start, int limit,string jdsj1,string jdsj2,string fgsbm,string zxmc)

        {

            DataSet ds_zx = sqlhelper.GetDataSet("select FDz_id from bm_zxxxmx where Fzx_id in (" + zxmc + ")");

            int size = ds_zx.Tables[0].Rows.Count;

            string result = null;

            for (int i = 0; i < size; i++)

            {

                result += ds_zx.Tables[0].Rows[i]["FDz_id"].ToString() + ",";

            }

            result = result.TrimEnd(',');

            try

            {

                string sql = "select top " + limit + " * from View_jydhwxx where fgsbm in (" + fgsbm + ")  and jhrq  >= '" + jdsj1 + "' and jhrq  <= '" + jdsj2 + "' and jydbh not in(select top " + start + " jydbh from View_jydhwxx where fgsbm in (" + fgsbm + ") and jhrq  >= '" + jdsj1 + "' and jhrq  <= '" + jdsj2 + "' order by jydbh desc) order by jydbh desc";

                ds = sqlhelper.GetDataSet(sql);

                return ds;

            }

            catch (Exception)

            {



                throw;

            }

        }

以上是extjs中分页并可翻页的后台sql语句
start limit 参数必须用上啊翻页的时候 start 接收参数是变动值

问苍天人生几何 2012-08-17

打赏
举报

试试

select top 42 * from View_jydhwxx 

where fgsbm=101 

and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  

and jhrq  between '2012-08-10 16:49:06' and '2012-08-17 16:49:06' 

and not exists (select 1 from View_jydhwxx t

				  where t.fgsbm=101

				  and t.jhrq  between '2012-08-10 16:49:06' and '2012-08-17 16:49:06' 

				  and t.dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  

				  and t.jydbh=jydbh) 

order by jydbh desc

--小F-- 2012-08-17

打赏
举报

用ROW_NUMBER来分页吧。

sql语句的优化？

相关推荐