Create New tables for each list_id

Discussions about new features or changes in existing features

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Create New tables for each list_id

Postby Noah » Fri Oct 13, 2017 10:34 am

Hi All Vici guru's

Searched the forum...ever been any feature request to create and maintain lists in a different tables for each list instead of the single table vicidial_list?
The code implications are large for lots of production files, I get that. However this 1 structural change could ultimately change the productivity and query efficiency by a leaps and bounds. Expanding the capacity for existing DB servers to 10s of millions of records for dialing.

What would be involved for production? Could this be achieved with a sed find and replace?
Lists of files to consider changes to.

grep -ilr 'insert into vicidial_list\|update vicidial_list\|from vicidial_list' /srv/www/htdocs/*

/srv/www/htdocs/agc/vdc_script_display.php
/srv/www/htdocs/agc/manager_send.php
/srv/www/htdocs/agc/vdc_script_dispo_example.php
/srv/www/htdocs/agc/vicidial_bak_orig.php
/srv/www/htdocs/agc/vicidial.php
/srv/www/htdocs/agc/vdc_form_display.php
/srv/www/htdocs/agc/vicidial_nodiv.php
/srv/www/htdocs/agc/vdc_script_notes.php
/srv/www/htdocs/agc/functions.php
/srv/www/htdocs/agc/dispo_move_list.php
/srv/www/htdocs/agc/api.php
/srv/www/htdocs/agc/audit_comments.php
/srv/www/htdocs/agc/vicidialshiftplanner.php
/srv/www/htdocs/agc/deactivate_lead.php
/srv/www/htdocs/agc/vdc_db_query.php
/srv/www/htdocs/agc/vicidial_applied.php

/srv/www/htdocs/vicidial/lead_tools_advanced.php
/srv/www/htdocs/vicidial/remote_dispo.php
/srv/www/htdocs/vicidial/reset_campaign_lists.php
/srv/www/htdocs/vicidial/user_stats.php
/srv/www/htdocs/vicidial/list_split.php
/srv/www/htdocs/vicidial/count_functions.inc
/srv/www/htdocs/vicidial/admin.php
/srv/www/htdocs/vicidial/admin_modify_lead.php
/srv/www/htdocs/vicidial/spreadsheet_sales_viewer.pl
/srv/www/htdocs/vicidial/call_report_export_carrier.php
/srv/www/htdocs/vicidial/closer-fronter_popup2.php
/srv/www/htdocs/vicidial/admin_NANPA_updater.php
/srv/www/htdocs/vicidial/AST_VDADstats.php
/srv/www/htdocs/vicidial/lead_report_export.php
/srv/www/htdocs/vicidial/AST_hangup_cause_report.php
/srv/www/htdocs/vicidial/AST_dialer_inventory_report.php
/srv/www/htdocs/vicidial/AST_LIST_UPDATEstats.php
/srv/www/htdocs/vicidial/listloader_stmts.txt
/srv/www/htdocs/vicidial/non_agent_api.php
/srv/www/htdocs/vicidial/closer_dispo.php
/srv/www/htdocs/vicidial/callbacks_bulk_move.php
/srv/www/htdocs/vicidial/AST_LISTS_pass_report.php
/srv/www/htdocs/vicidial/closer-fronter_popup.php
/srv/www/htdocs/vicidial/called_counts_multilist_report.php
/srv/www/htdocs/vicidial/AST_LISTS_stats.php
/srv/www/htdocs/vicidial/listloader_rowdisplay.pl
/srv/www/htdocs/vicidial/closer_popup.php
/srv/www/htdocs/vicidial/AST_LISTS_campaign_stats.php
/srv/www/htdocs/vicidial/call_report_export.php
/srv/www/htdocs/vicidial/vicidial_sales_viewer.php
/srv/www/htdocs/vicidial/user_status.php
/srv/www/htdocs/vicidial/admin_search_lead.php
/srv/www/htdocs/vicidial/AST_campaign_status_list_report.php
/srv/www/htdocs/vicidial/list_download.php
/srv/www/htdocs/vicidial/qc_modify_lead.php
/srv/www/htdocs/vicidial/lead_tools.php
/srv/www/htdocs/vicidial/leadloader_template_display.php
/srv/www/htdocs/vicidial/AST_admin_template_maker.php
/srv/www/htdocs/vicidial/user_territories.php
/srv/www/htdocs/vicidial/admin_listloader_fourth_gen.php
/srv/www/htdocs/vicidial/AST_carrier_log_report.php
/srv/www/htdocs/vicidial/admin_listloader_third_gen.php
/srv/www/htdocs/vicidial/admin_lists_custom.php
/srv/www/htdocs/vicidial/AST_url_log_report.php
/srv/www/htdocs/vicidial/admin_campaign_multi_alt.php

grep -ilr 'insert into vicidial_list\|update vicidial_list\|from vicidial_list' /usr/share/astguiclient/*

./ADMIN_adjust_GMTnow_on_leads.pl
./ADMIN_keepalive_ALL.pl
./AST_CRON_purge_recordings.pl
./AST_DB_action.pl
./AST_DB_dead_cb_purge.pl
./AST_DB_lead_status_change.pl
./AST_DB_territory_populate.pl
./AST_DB_tz_divide.pl
./AST_VDauto_dial.pl
./AST_VDauto_dial_FILL.pl
./AST_VDhopper.pl
./AST_VDlist_summary_export.pl
./AST_VDlist_summary_export_UK.pl
./AST_VDremote_agents.pl
./AST_VDsales_export.pl
./AST_VDsales_export_SFTP.pl
./AST_VDsales_export_SFTPSSH2.pl
./AST_VDweekly_agent_statuses_report.pl
./AST_call_log_export.pl
./AST_dialer_inventory_snapshot.pl
./AST_inbound_email_parser.pl
./AST_recordings_export.pl
./AST_send_URL.pl
./AST_sourceID_summary_export.pl
./FastAGI_log.pl
./QC_gather_records.pl
./VICIDIAL_DEDUPE_leads.pl
./VICIDIAL_IN_new_leads_file.pl
./VICIDIAL_UPDATE_leads_status_file.pl
./VICIDIAL_fix_list_statuses.pl
./VICIDIAL_fix_status_mismatch.pl
./VICIDIAL_last_local_call_time_UPDATE.pl
./VICIDIAL_rebuild_list_statuses.pl
./Vtiger_OUT_sync2VICIDIAL_file.pl
./libs/vicidial.pm
./nanpa_type_filter.pl
./nanpa_type_preload.pl


What would be involved for reporting?
Select Unions or trigger updates to the original table?
If trigger updates to a reporting table vicidial_list - no changes to reporting?
MyCallCloud.com - Cool Vici Customizations - Hosted - Configured - Supported
P: 888-663-0760
E: sales@mycallcloud.com
Noah
 
Posts: 81
Joined: Tue Feb 08, 2011 7:14 pm

Re: Create New tables for each list_id

Postby mflorell » Fri Oct 13, 2017 11:49 am

It would be a massive undertaking, and if you wanted to maintain the same full feature set, it would also actually slow down the database. Every time you had more than one list active in a campaign, you would have to create a temp table every time you ran the hopper, to dump all dialable leads into to do the lead sort(lead order) that was selected. Also, using UNIONs and TRIGGERs and SUBSELECTS greatly slow down the database and reduce the capacity of your system.

It's not a horrible idea, just one that would have side effects that would hinder the goals of having a faster system with more lead capacity. It's actually the strategy we used when creating custom list fields, although with that the main default lead data fields still all exists in one table, the extra fields are created in a list-specific table allowing for more flexibility and speed when storing that extra lead data.
mflorell
Site Admin
 
Posts: 16336
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Create New tables for each list_id

Postby Noah » Fri Oct 13, 2017 1:22 pm

mflorell wrote:It would be a massive undertaking, and if you wanted to maintain the same full feature set, it would also actually slow down the database. Every time you had more than one list active in a campaign, you would have to create a temp table every time you ran the hopper, to dump all dialable leads into to do the lead sort(lead order) that was selected. Also, using UNIONs and TRIGGERs and SUBSELECTS greatly slow down the database and reduce the capacity of your system.

It's not a horrible idea, just one that would have side effects that would hinder the goals of having a faster system with more lead capacity. It's actually the strategy we used when creating custom list fields, although with that the main default lead data fields still all exists in one table, the extra fields are created in a list-specific table allowing for more flexibility and speed when storing that extra lead data.


It's interesting the thought of the hopper process being the biggest challenge.
The hopper could be split into a preload and then a "hopper_load" process, which the hopper kinda does already. Hopper_preload data for each ($list_id[]) {select bla limit $hopper/count($list) into temp or other}. That select into a temp table or preload_table would be very quick, since the lists are now chopped down to 10k or 20k or even 200k records.
Then process the rest of the hopper stuff on that temp table or pre_load table to the hopper table in the appropriate sort order.

I would have thought the other side of the data processing load would have been the biggest challenge....the call connect and dispo processes, which would in turn would be greatly made more efficient, since now the table locks are related only to a particular list_id vs the list table for updates and inserts.

Loading records during production would also be a big benefit, as to not lock the vicidial_list table during load.
(Bill - Poundteam, Kumba, Gardo...thoughts?)
MyCallCloud.com - Cool Vici Customizations - Hosted - Configured - Supported
P: 888-663-0760
E: sales@mycallcloud.com
Noah
 
Posts: 81
Joined: Tue Feb 08, 2011 7:14 pm

Re: Create New tables for each list_id

Postby mflorell » Fri Oct 13, 2017 2:25 pm

On the inbound side, or an agent placing a manual dial call, think about the monster JOIN query that would be needed to search for a phone number in all of those separate list tables.

There are many challenges like these that would be needed to be sorted through before anything like this could be attempted, and there is no way we would embark on a project as big as this as unfunded either. We would have to have a client pay for it, and it would most likely be 200+ hours to make it happen.
mflorell
Site Admin
 
Posts: 16336
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Create New tables for each list_id

Postby Noah » Fri Oct 13, 2017 4:18 pm

mflorell wrote:On the inbound side, or an agent placing a manual dial call, think about the monster JOIN query that would be needed to search for a phone number in all of those separate list tables.

There are many challenges like these that would be needed to be sorted through before anything like this could be attempted, and there is no way we would embark on a project as big as this as unfunded either. We would have to have a client pay for it, and it would most likely be 200+ hours to make it happen.


Good point on inbound. Lot's of moving parts around the vicidial_list table.
Using add or replace mysql merge views, possibly, to manage the all the joins for each list_id instead of raw queries, could work?
The view (merge algorithm) is then lists assignments per campaign view to join on campaign id...Hey that could really minimize changes in hopper script...and inbound lookups
Well lots to mull over...
MyCallCloud.com - Cool Vici Customizations - Hosted - Configured - Supported
P: 888-663-0760
E: sales@mycallcloud.com
Noah
 
Posts: 81
Joined: Tue Feb 08, 2011 7:14 pm


Return to Features

Who is online

Users browsing this forum: No registered users and 5 guests