try--SELECT
top 1
*
FROM
prodeuctTable
WHERE
(saleUserID <> '卖用户(我的卖号)')
AND
(status = 0)
and
not exists(select 1 from prodeuctTable b where b.buyUserID=a.buyUserID and b.buyDate>convert(varchar(10),GETDATE()-10,120))
ORDER BY
ID
top 1
*
FROM
prodeuctTable
WHERE
(saleUserID <> '卖用户(我的卖号)')
AND
(status = 0)
and
not exists(select 1 from prodeuctTable b where b.buyUserID=a.buyUserID and b.buyDate>convert(varchar(10),GETDATE()-10,120))
ORDER BY
ID
top 1
*
FROM
prodeuctTable a
WHERE
(a.saleUserID <> '卖用户(我的卖号)')
AND
(a.status = 0)
and
not exists(
select 1 from prodeuctTable b where b.buyUserID=a.buyUserID
and b.buyDate>convert(varchar(10),GETDATE()-10,120)
and b.saleUserID = '卖用户(我的卖号)')
ORDER BY
a.ID
FROM (
SELECT *
FROM prodeuctTable
WHERE (saleUserID <> '卖用户(我的卖号)')
AND (status = 0)) A
LEFT JOIN
(SELECT saleUserID
FROM prodeuctTable
WHERE buyUserID = '买用户(我的买号)'
and buyDate>DATEADD(day, -10,GETDATE())) B
ON A.saleUserID=B.saleUserID
WHERE B.saleUserID IS NULL
ORDER BY ID
SELECT top 1 *
FROM prodeuctTable a
WHERE
(saleUserID <> '卖用户(我的卖号)')
AND
(status = 0)
and
not exists(select 1 from prodeuctTable where buyUserID = '买用户(我的买号)' and saleUserID=a.saleUserID and DATEDIFF(d,buyDate, GETDATE())<10)
ORDER BY ID
如果还是慢,估计楼主并不是用到prodeuctTable中所有字段,改成这样看看是不是会快一些
SELECT top 1 saleUserID
FROM prodeuctTable a
WHERE
(saleUserID <> '卖用户(我的卖号)')
AND
(status = 0)
and
not exists(select 1 from prodeuctTable where buyUserID = '买用户(我的买号)' and saleUserID=a.saleUserID and DATEDIFF(d,buyDate, GETDATE())<10)
ORDER BY ID
感觉逻辑有问题,不知道是不是我理解的有问题,所以有几个疑问:
1,你要查的是产品记录还是用户记录,如红字部分。
2,prodeuctTable 是主表还是子表?
(1)如果是主表,那么是不是对于同一产品有多个买家就要产生多条相同的产品记录(只是买家不相同)
(2)如果是子表,那么saleUserID & status 就不应放在此表中
建议把表结构拿出来看下哈
WHERE (saleUserID <> '卖用户') AND (status = 0)
and not exists(select 1 from prodeuctTable where saleUserID=t.saleUserID and buyUserID = '卖用户' and buyDate<getdate()-10)
order by id
SELECT top 1 * FROM prodeuctTable t
WHERE (saleUserID <> '卖用户') AND (status = 0)
and not exists(select 1 from prodeuctTable where saleUserID=t.saleUserID and buyUserID = '卖用户' and buyDate>getdate()-10)
order by id另外,in,<>(相当于 not =), order by 子句,都可能导致索引无法被正确利用。
必要的话,强制使用某些索引。
right join
prodeuctTable b on b.saleUserID=t.saleUserID and b.buyUserID = '卖用户'
WHERE (t.saleUserID <> '卖用户') AND (t.status = 0) and datediff(d,t.buyDate,getdate())<10order by id
A.*
FROM
(
SELECT *
FROM prodeuctTable
WHERE
(saleUserID <> '卖用户(我的卖号)')
AND
(status = 0)
) A
LEFT JOIN
(
SELECT saleUserID
FROM prodeuctTable
WHERE
buyUserID = '买用户(我的买号)'
AND
buyDate>DATEADD(day, -10,GETDATE())
) B
ON
A.saleUserID=B.saleUserID
WHERE B.saleUserID IS NULL
ORDER BY ID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prodeuctTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[prodeuctTable]
GOCREATE TABLE [dbo].[prodeuctTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[buyUserID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[saleUserID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[productURL] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[isTS] [tinyint] NULL ,
[status] [tinyint] NULL ,
[inputIP] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[inputDate] [datetime] NULL ,
[buyDate] [datetime] NULL
) ON [PRIMARY]
GO
目前数据量也就是50W这样子..开始变得很慢的了.数据库CPU使用居高不下..郁闷ing...............
用的.NET 3.5 + Linq来写的..难道是我的Linq没使用好.目前正想把所有Linq的全部换了.把底层换了.麻烦啊
where status = 0 and saleUserID<>@userId and buyUserID<>@userId and buyDate>convert(varchar(10),GETDATE()-10,120)) saleUserID NOT IN (SELECT saleUserID FROM prodeuctTable b WHERE buyUserID = '买用户(我的买号)'
这就话改成 buyUserID<>@userId 这样就行了
如下:
卖家 <> (卖家=(买家=我)) <=> 买家<> 我然后对buyDate,saleUserID,buyUserID 建立索引,
另外,如果业务有控制不能买自己的商品的话,那么saleUserID<>@userId 条件都可以去掉,
所以,这个业务可以理解为:交易在10之内,不是我买的商品优化如下:SELECT * FROM prodeuctTable
where status = 0 and buyUserID<>@userId and buyDate>convert(varchar(10),GETDATE()-10,120)) 如果结果数据很多,建议分页