Performance improvement tips - block unwanted search enginee bots

It is really important to block bots from some countries if you are not shipping there. For example, after blocking all traffic from China and Russia(Andrei never mind :) ) and Germany on an eCommerce application which only ship into USA, I noticed a significant performance improvement. You block/challenge countries using different website security service tools like incapsula or cloudflare etc. Also, you can block IP from web.config easily.


Fig 1: Look at AWS cloud watch. CPU usages, web traffic significantly reduced.


Fig 2: Server response time incredibly improved - Newrelic.


You can write a simple trigger to catch unwanted bots ip and block it.

Create table to track all search engine ips

CREATE TABLE [dbo].[x_SearchEngine](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [LastIpAddress] [nvarchar](50) NULL,
  [CreatedOnUtc] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Create trigger to insert ips into previously created table
CREATE TRIGGER [dbo].[x_searchengine]
   ON [dbo].[Customer]
   AFTER UPDATE
AS 
BEGIN

  SET NOCOUNT ON;

   IF EXISTS(SELECT 1 FROM inserted where Id = 42693958)
   BEGIN
   insert into x_SearchEngine
   (LastIPAddress, CreatedOnUtc)
   select LastIpAddress, GETUTCDATE() from inserted where id = 42693958
   END

END

Wait few hours and run following query and find bots who is killing your server
select lastipaddress, count(*)
from x_searchengine
group by lastipaddress 
order by count(*) desc

Find country from ip using iplocation.net
and see if the country/ip is relevant for your business otherwise block it.


You can also visit my Quora post for more details

Hi Shahdat,

Thanks a lot! Very good contribution! Maybe, we (or somebody else from our community) can implement it as a plugin.

Here is a work item

RE: "... where Id = 42693958"

Is that a "typo"?

New York wrote:

Yes, it should be where Id = 2

We can find search engine user id using following query

SELECT Id FROM Customer WHERE email = 'builtin@search_engine_record.com'

Does this still apply when you are using a proxi like Cloud Flare?

Tecnofin wrote:

Yes, Cloud Flare does not filter anything unless you tell it do something. In Cloud Flare, you can challenge or apply captcha on specific countries, you can block ips.

shahdat45ict wrote:
Thank you Shahdat