直接从数据库获取发布和过期日期 - 请确认查询是否正确
我的网站有点大，使用 Umbraco API 循环遍历所有节点来获取发布和过期日期已经超时..所以我编写了以下查询来查找节点的过期和发布日期..
SELECT D.nodeId, D.releaseDate, D.expireDate
FROM dbo.cmsDocument D
WHERE D.newest = 1 AND
(
D.releaseDate IS NOT NULL
OR
D.expireDate IS NOT NULL
)
任何人都可以确认它是否对还是错...如果是错的，获取这些值的正确方法是什么..
谢谢
安兹
My website is a bit huge and looping through all the nodes using Umbraco APIs to get release and expire dates is timing out.. so I wrote the following query to find the expire and release dates of nodes..
SELECT D.nodeId, D.releaseDate, D.expireDate
FROM dbo.cmsDocument D
WHERE D.newest = 1 AND
(
D.releaseDate IS NOT NULL
OR
D.expireDate IS NOT NULL
)
can any one please confirm whether its right or wrong... If its wrong, what is the proper way to get these values..
Thanks
Anz
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我对 Umbraco 一无所知，但我确实知道并非所有 dbms 都会在计算
your_column_name IS NOT NULL等表达式时使用索引。如果您的目标 dbms 不将
IS NOT NULL视为可控制表达式，那么它不会使用索引。相反，它会进行全表扫描，这在大表上可能需要很长时间。如果需要很长时间，我认为超时仍然是可能的。也可能releaseDate 或expireDate 上没有索引。在一张大桌子上，这也会让你的速度减慢很多。
如果您想知道哪些文档在今天之前过期，也许您可以删除它们，我（也许天真地）希望这个标准 SQL 语句能够工作。 （但我也希望一些 Umbraco 内务处理模块可以使这变得不必要。）
I don't know anything about Umbraco, but I do know that not all dbms will use an index in evaluating expressions like
your_column_name IS NOT NULL.If your target dbms doesn't treat
IS NOT NULLas a sargable expression, then it won't use an index. Instead, it will do a full table scan, which can take a long time on a big table. And if it takes a long time, I suppose a timeout is still possible.It's also possible that there isn't an index on releaseDate or expireDate. On a big table, that will slow you down a lot, too.
If you want to know which documents expired before today, maybe so you can delete them, I'd (perhaps naively) expect this standard SQL statement to work. (But I'd also expect some Umbraco housekeeping module to make this unnecessary.)