Send notifications to user when row with specific keywords is inserted

Question

I am using SQL Server over Azure and would like to give the ability to users to define keywords and, when an article where the title matches one of those keywords, the user would receive an alert.

Of course there can be 100.000 users each with 100 keywords defined.

To do such a query every time an article is inserted is obviously not feasible.

My idea is to create a job that would run every hour or so but, since for many reasons that also doesn't strike me as ideal, I was wondering if anyone would suggest a better option. Ideally using the azure infrastructure and not only a SQL based solution.

Answer 1

This is a question about how to query in SQL with large number of data.

According to your description, we can use database index to improve the performance of query.

We can create an index in the keyword field and use T-SQL as below:

Select count(1) From T Where Keyword = XXX

In this way, the database engine will use index instead of full table scan.

In Azure SQL Db, we can create index using T-SQL: CREATE INDEX (Transact-SQL)

We can also use SSMS to create index, more information about index in Azure SQL Db, we can refer to: Clustered and Nonclustered Indexes Described

Here are some optimization methods for database query for you, hope it will be help:

1. To optimize the query, avoid full table scanning as much as possible, and first consider indexing the columns involved in where and order by.


2. The null value judgment of the field in the where clause should be avoided as far as possible. Otherwise, it will cause the engine to abandon the index and scan the whole table, such as:

Select id from t where num is null

You can set a default value of 0 on num to ensure that there is no null value in the num column in the table.

Select id from t where num=0


3. try to avoid using "=" or "> operator in the where clause, otherwise the engine will discard the index and perform the full table scan.


4. Use or to join conditions in where clauses should be avoided as far as possible, otherwise it will cause the engine to abandon the use of the index and perform a full table scan, such as:

Select id from t where num=10 or num=20

It can be inquired like this:

Select id from t where num=10

Union all

Select id from t where num=20


5.in and not in should also be used with caution, otherwise the whole table will be scanned, such as:

Select id from t where num in (1,2,3)

For continuous values, use between instead of in:

Select id from t where num between 1 and 3

The query under 


6. will also result in full table scan:

Select id from t where name like'%abc%'

In order to improve the efficiency, the full text retrieval can be considered.

Answer 2

You could use Logic Apps for your use-case.

There is a SQL connector in the logic app where you can achieve your requirements easily.

I've done a sample below

Explaination

1. Creating a trigger for your SQL Table when any item got inserted into a particular table (Customer_Feedback)

2. Execute a Stored Procedure (action) and get back the result/output from that.The Stored Procedure can be a simple select statement with your requirements for searching a keyword. Please be sure to follow the Indexing as per Lee Liu Answer above

3. Adding a condition which will check the output of the stored procedure with the corresponding Keyword

4. If the Condition Satisfies then send mail to that user via send mail task

You can also modify this flow with your own creativity.