在这种情况下，删除是否比在sql中搜索更快（特殊情况） [英] Do Delete can work faster than search in sql in this case(special case)

问题描述

我有一个包含数百万行的数据库。每行包含一个事务。

我必须搜索过去24小时内添加/更新的行数。表总是包含修改/添加的时间戳。



现在超过1天的数据无论如何都没有用。如果搜索过去24小时的记录。我有2种方式



1.我可以写一个搜索查询，返回当前时间和24小时之前的行数。



2.我删除超过24小时的所有行，只返回no。通过Count方法的行（它可能会在给定时间删除几千个）。



哪一个会更好。请提供一个深入了解。



注意 - 我在的地方工作（30分）反正删除记录每隔几分钟。

解决方案

我不得不说，你的目的删除不正确的选项，删除是一项昂贵的活动，因为它有这样做的背后这么多的任务，场景，你可能不知道（例如指数修正，锁定，记录等）。



在您的情况下，超过您的时间跨度字段的索引将显着提高搜索性能。这应该是获取最近24小时内添加的行数的正确方法。



最后在您的问题中删除将永远不会比搜索操作更快

I have a database of millions of rows. each rows contain one transaction.
I have to search how many rows are added/updated in last 24 hours. Table always contains timestamps of modification/addition.

Now data beyond 1 days is not useful anyway. If a search is made for records of last 24 hour. I have 2 ways

1. I can write a search query, returning count of rows between current time and 24 hour ago.

2. I delete all rows beyond 24 hours, and just return the no. of rows via Count method (It may delete some few thousand at a given time).

Which one will be better. Please provide a insight.

Note- I have a job in place deletes records every few minutes (30 min) anyways.

解决方案

I have to say that for your purpose deletion is not the right option, Deletion is a costly activity as it has to do so many tasks behind the scene which you probably not aware of (for example index correction, locking, logging, etc).

in your case an index over your timespan field will increase search performance significantly. This should be the correct way of getting count of rows added in last 24 hours.

Finally in your problem deletion will never be faster then search operation

这篇关于在这种情况下，删除是否比在sql中搜索更快（特殊情况）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！