Do Delete can work faster than search in sql in this case(special case)

Question

0.00/5 (No votes)

See more:

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.

Posted 6-Jan-16 19:38pm

Anant Beriwal

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2016-01-06T19:53:00

Solution 1

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

Posted 6-Jan-16 19:53pm

_Asif_

Updated 6-Jan-16 19:55pm

v2

Comments

Anant Beriwal 7-Jan-16 2:51am

Asif, here I have updations too on existing row, in any case , job is deleting old items from database. but job cant run every minute(It will be a very operation), so it is schedule for half hour. now the only problem is this half hour time stamp which can be left out of calculation via calculating records of last 24 hours. Can delete and then simply count will not be a good idea?

_Asif_ 7-Jan-16 3:09am

Are you purging records or archiving ? for every 30 mins

Anant Beriwal 7-Jan-16 3:10am

purging

_Asif_ 7-Jan-16 3:40am

I don't know your business case but usually Transactions do not get purge because of strict regulatory archiving policy. why don't you archive transactions for a day and get the count from archived table/repository.