What is the fastest and most efficient way to check database for new entry?

Question

I currently have an application that, at any given time, will INSERT new data into my database. I also have a different python script that checks my database in an infinite loop for a new entry, and when it finds one, it selects it, and uses it, then waits again.

Im wondering if there is any way of doing this more efficiently and more accurately?

Thanks

I currently have a set up like this:

conn = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
                      'Server=REDACTED;'
                      'Database= REDACTED;'
                      'UID= REDACTED;'
                      'PWD= REDACTED;')

cursor = conn.cursor()

in a loop such as:

i = 1
while i < 2:
     #check database for new entry with select statement and compare old list with current list and see if there is a difference. If there is a difference, use that new key and process data.

It currently works fine, but I feel like it is doing a lot of work for nothing. For example, during the week it will only every really access the database 30-50 times a day, but on the weekend it will do it close to 100-200 times a day... the only thing is, there is no set number of times it will access it, or when.

Any help would be useful. Thanks

You can use a database trigger instead: https://support.pubnub.com/support/solutions/articles/14000043795-can-i-publish-a-message-via-a-database-trigger- — blhsing, Oct 18 '19 at 03:09

score 0 · Accepted Answer · answered Oct 18 '19 at 03:33

I’ve never had to do this before. But 3 ideas came to mind.

(1) if your script is writing to another database, then your alternative could be setting up a replication of your source database. The DBA of the original database can set that up for you.

(2) if you are willing to overhaul the original database, you could consider a real-time in-memory database (such as redis), depending on your use case that might help.

(3) it appears that sqlalchemy has a built in event listener. I use sqlalchemy in Python but never this particular feature.

https://docs.sqlalchemy.org/en/13/orm/session_events.html

What is the fastest and most efficient way to check database for new entry?

1 Answers1