0

I have a console application in task scheduler that runs every 1 minute for 12 hours. It performs the following steps:

  1. Starts and connect to a mailbox using POP3 and gets list of all emails.

  2. For every new email, it queries SQL DB to check if email id exists in table.

  3. If email id exists in table, it performs it actions and move to next email id in queue.

  4. This action continues for every 1 minute for 12 hours.

My questions are:

  1. For step no 2 - is there any alternative instead of hitting db every 1 minute for every new email id. I thought of caching the table data and then check in cache for matching records, but the table contains 10k rows which I believe is heavy for cache.

  2. Is there any other way to keep a watch on my inbox, if a new email appears I can trigger the action instead of console application in task scheduler that runs every 1 minute.

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
shravan bardwa
  • 27
  • 2
  • 10
  • a) https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency b) yeah if you have something like IMAP, exchange etc. that does push email – ta.speot.is Aug 31 '17 at 04:53
  • a) It will detect changes in sql, but I have email id which I need to check if it exits in table. (The reverse) – shravan bardwa Aug 31 '17 at 04:57
  • My mistake. 10K records is not a lot for a cache, but if you are still averse to that you can consider using a table valued parameter to pass *all* the Ids in at once, and do whatever you need to do then. https://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure – ta.speot.is Aug 31 '17 at 05:01
  • a) what are you actually doing here? Are you running a bunch of individual select queries? Do you have a performance issue? 10k rows isn't much. – Nick.McDermaid Aug 31 '17 at 05:11
  • Hmm..table valued parameter will do, but what about every 1 minute instance ? There is new instance every 1 minute. – shravan bardwa Aug 31 '17 at 05:13
  • I have mentioned step wise in my question, let me know if you need further clarifications- @Nick.McDermaid – shravan bardwa Aug 31 '17 at 05:15
  • You already have a collection of caches here (disk, SQL Server etc.). Don't add another manual one. In SQL you can take steps to ensure his table is pinned in memory anyway. But you only need to address this if you have a performance issue. If you don't have a performance issue, don't complicate it – Nick.McDermaid Aug 31 '17 at 05:49
  • Ok I got it. For question 2, please give your suggestions – shravan bardwa Aug 31 '17 at 15:27
  • For 2, that depends heavily on what kind of mailbox. You say you connect with POP3.... what are you connecting to? Office 65? gmail? You'd need to add some code to the actual email server. – Nick.McDermaid Sep 01 '17 at 03:19
  • Here's one example I found for office 365. https://flow.microsoft.com/en-us/documentation/email-triggers/ I found this this in 5 seconds on google. – Nick.McDermaid Sep 01 '17 at 03:22
  • I am using IBM lotus note mailbox and want to start a console application when a new email appears in my inbox. – shravan bardwa Sep 01 '17 at 04:34
  • Guess what I did... go on guess.... I googled and found this.... https://www.ibm.com/support/knowledgecenter/en/SSVRGU_8.5.3/com.ibm.designer.domino.main.doc/H_TRIGGERING_AN_AGENT_ON_AN_EVENT_STEPS.html There's absolutely no reason you couldn't do the same. You need to open up lotus notes and dig around in this area and see if you can set up a trigger based off a new email. Then ask a specific question – Nick.McDermaid Sep 01 '17 at 06:56

0 Answers0