查询:10w数据信息的查询
举例:
SELECT TOP 10 * FROM rlsc_users //查询时间(0.117s)
SELECT TOP 10 * FROM RLSC_Users as ru ORDER BY CreateTime //查询时间(0.253s)
SELECT TOP 10 * FROM rlsc_users as ru ORDER BY CreateTime //查询时间(0.355s)
前2句sql,区别RLSC_Users的大小写的关系,RLSC_Users为数据库名字,虽然不区分大小写,但是速度还是区别,网络接口不稳定,服务器请求多......等原因?
SELECT TOP 100 * FROM RLSC_Users as ru ORDER BY CreateTime //查询时间(0.783s)
SELECT TOP 1000 * FROM RLSC_Users as ru ORDER BY CreateTime //查询时间(34.591s)
SELECT TOP 10000 * FROM RLSC_Users as ru ORDER BY CreateTime //查询时间(.......死了.......)
以上数据都是10倍的递增,但是查询时间明显从100以上开始,就出现类似几次幂的狂增模式
SELECT TOP 10 ru.Nation,ru.City,ru.ComputerGrade,ru.IdCard FROM RLSC_Users as ru ORDER BY CreateTime //查询时间(0.084s)
SELECT TOP 100 ru.Nation,ru.City,ru.ComputerGrade,ru.IdCard FROM RLSC_Users as ru ORDER BY CreateTime //查询时间(0.158)
从以上数据分析我们查询时具体到需要的字段,也是sql优化的必要项 节省3倍以上的时间
存储过程的比较
但语句执行 存储过程是要慢于直接的语句查询的?在单语句的时候没有比较的必要性,比较结果差别不大!
一些常用的sql语句
(1)添加一个Id自增的不存在的字段
SELECT row_number()over(order by trainCompanyId) as Id,cc.trainCompanyId as Name from CYPX_CmpUser as cc GROUP BY cc.trainCompanyId
(2)删除 一个表中所有重复的数据字段
delete from CYPX_User
where idCard in (select idCard from CYPX_User group by idCard having count(idCard) > 1)
and DOrder not in (select min(DOrder) from CYPX_User group by idCard having count(idCard)>1)
(3)修改外键表中所有的UserId字段,以身份证号连接,使其与User表相对应
UPDATE CYPX_CmpUser set CYPX_CmpUser.UserId=(select CYPX_User.DOrder from CYPX_User
where CYPX_User.idcard=CYPX_CmpUser.idcard) where CYPX_CmpUser.idcard in (select CYPX_User.idcard from CYPX_User)
(4)按照月份 进行 分组,统计一年每个月的Count()数据 substring Convert的使用
select substring(Convert(varchar(10),CYTime,120),6,2) as name,count(*) as id
from CYPX_EntityPay where YEAR(CYTime)=@year group by substring(Convert(varchar(10),CYTime,120),6,2)
(5)2张数据表的对比,一张作为数据源表去更新比外一张表 使用MERGE INTO