Mirriring Framework

[leandrosardi]

Develop mirroring tool.

Mirror the following tables:

• lnuseractivity

• movement

• boterrorlog

• buffer_lnsearchvariationblock_result (remove old results)

• search

• lnsearchvariationblock

• buffer_incrawl_lnsearchvariation_result

• result

• profilejob

• profile

• company

• lnchat

• drl_minutely_stats

[leandrosardi]

Analysis

Use this query to get the number of rows by table.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY SUM(sPTN.Rows) DESC
GO

List of Active Clients

To decide if we can delete the data of a client or not.

create view v_prospecting_and_3_month_active_clients
as
    select distinct u.id_client as id
    from [login] l with (nolock)
    join [user] u with (nolock) on u.id=l.id_user
    where l.create_time > dateadd(mm, -3, getdate())
    union 
    select id from v_flywheel_clients

Requirement

hosthistory

Once SHM product is online, aged records should be moved to an history table. But never being truncated.

truncate table [hosthistory];

browseractivity

This table has been discontinued.

truncate table [browseractivity];

boterrorlog

truncate table boterrorlog;

lnuseractivity

Cookies warming, and dispatcher.recover.message requires a full hostory of the lnuseractivity. So, these 2 processes must be adapted if we are truncating this table

truncate table lnuseractivity;

movement

Removing records of not active clients from the table movement is not a solution, because more than 75% of the records there are belonging active clients.

select count(*) from movement with (nolock)
-- 3.779.921

select count(distinct m.id) 
from movement m with (nolock)
join v_flywheel_clients v with (nolock) on v.id=m.id_client
-- 2.195.601

Solution is to ARCHIVE old records, and replace them by a initial status record

result

select count(*) from result with (nolock)
-- all: 16.844.474

select count(r.id) 
from result r with (nolock)
join search s with (nolock) on s.id=r.id_search
join [user] u with (nolock) on u.id=s.id_user
join v_flywheel_clients v on v.id=u.id_client
-- active clients: 5.432.891

select count(r.id) 
from result r with (nolock)
join search s with (nolock) on s.id=r.id_search
join [user] u with (nolock) on u.id=s.id_user
join v_prospecting_and_active_clients v on v.id=u.id_client
-- active clients: 7.979.583

lnsearchvariationblock

select count(*) from lnsearchvariationblock with (nolock) 
-- all: 7.293.146

select count(b.id) 
from lnsearchvariationblock b with (nolock) 
join search s with (nolock) on s.id=b.id_search
join [user] u with (nolock) on u.id=s.id_user
join v_flywheel_clients v on v.id=u.id_client
-- active clients: 1.053.313

select count(b.id) 
from lnsearchvariationblock b with (nolock) 
join search s with (nolock) on (s.id=b.id_search and s.delete_time is null)
join [user] u with (nolock) on u.id=s.id_user
join v_flywheel_clients v on v.id=u.id_client
-- active clients and active searches: 106.516

lnchat

select count(*) from lnchat with (nolock)
-- all: 8.741.692

select count(h.id) 
from lnchat h with (nolock)
join result r with (nolock) on r.id=h.id_result
join search s with (nolock) on s.id=r.id_search
join [user] u with (nolock) on u.id=s.id_user
join v_prospecting_and_active_clients v with (nolock) on v.id=u.id_client
-- active clients: 5.590.493

[leandrosardi]

Remove FKs

-- this is to handle the request to delete data of a client
alter table client add remove_data_start_time datetime null;
go
alter table client add remove_data_end_time datetime null;
go
alter table client add remove_data_result bit null;
go
alter table client add remove_data_error_description text null;
go

-- this to to delete the search blocks, with no delete the records in the results table
alter table lnsearchvariationblock DROP CONSTRAINT FK__lnsearchv__id_ln__2665925D;
go
alter table lnsearchvariationblock_firstname DROP CONSTRAINT FK__lnsearchv__id_ln__2A3F5813;
go
alter table lnsearchvariationblock_keyword DROP CONSTRAINT FK__lnsearchv__id_ln__0A2D1CC9;
go
alter table lnsearchvariationblockparent DROP CONSTRAINT FK__lnsearchv__id_ln__34B3B1B4;
go
alter table lnsearchvariationblockparent DROP CONSTRAINT FK__lnsearchv__id_ln__33BF8D7B;
go
alter table buffer_incrawl_lnsearchvariation_result drop constraint FK__buffer_in__id_ln__2E06B425;
go
alter table result drop constraint FK__lisearchr__id_ln__2D128FEC;
go

alter table drl_minutely_stats drop constraint FK__drl_minut__id_re__31CC6BBE;
go

Remove Indexes

drop index lnsearchvariationblock.IX_lnsearchvariationblock__id_lnsearch;
--drop index lnsearchvariationblock.IX_lnsearchvariationblock__id_search;
drop index lnsearchvariationblock.IX_lnsearchvariationblock__id_search__incrawl_reservation_id__urgency__id;
drop index lnsearchvariationblock.IX_lnsearchvariationblock__id_search__incrawl_reservation_id__incrawl_end_time__incrawl_reservation_times;
drop index lnsearchvariationblock.IX_lnsearchvariationblock__id_search__incrawl_reservation_id__incrawl_end_time__incrawl_reservation_times__urgency;
--drop index lnsearchvariationblock.IX_lnsearchvariationblock__id_search__id_lnlocation__id_lnindustry;
drop index lnsearchvariationblock.IX_lnsearchvariationblock__results__ttipiskka_reservation_times__ttipiskka_end_time__id_lnlocation__id_lnindustry;
drop index lnsearchvariationblock.IX__lnsearchvariationblock__id_lnindustry__id;
--drop index lnsearchvariationblock.IX_lnsearchvariationblock__incrawl_reservation_id__incrawl_end_time__incrawl_reservation_times;
drop index lnsearchvariationblock.IX_lnsearchvariationblock__id_lnsearchvariationblock_parent;
drop index lnsearchvariationblock.IX_lnsearchvariationblock__stat_expected_results__stat_last_update_time;
drop index lnsearchvariationblock.IX_lnsearchvariationblock__stat_last_update_time;
drop index lnsearchvariationblock.IX_lnsearchvariationblock__id_search__incrawl_reservation_id__incrawl_end_time__incrawl_reservation_times__results;

To reset the process

update client set remove_data_start_time=null where remove_data_start_time is not null;
update client set remove_data_end_time=null where remove_data_end_time is not null;

Brute force

Use this in case you can't delete records from lnsearchvariationblock because of query timeout. Such a query timeout happens when the database is overloaded.

truncate table lnsearchvariationblock;
truncate table lnsearchvariationblock_firstname;
truncate table lnsearchvariationblock_keyword;
truncate table lnsearchvariationblockparent;