Create New tables for each list_id

[Noah]

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?

[mflorell]

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.

[Noah]

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?)

[mflorell]

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.

[Noah]

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...

[greg@byteworth.com]

The monster query could be avoided by simply creating a master list table.
That is how you handle it.
Say you have 700 list tables.
Each time you load new leads, the leads are loaded into the master list first, then generates the separated list for that newly entered list.
Inside the master list table, you maintain the list IDs for each record, this list_id, can be used to do a search in the smaller separated lists.
Were talking a difference of milliseconds compared to the current setup.

There are other benefits to using a master list, for example, the current setup has major drawbacks when dealing with mid sized databases. Maybe even small databases.
Any time the vicidial_list table grows beyond 1 million records things start to go awry and tweaks that help but doesn't really resolve the issues.
Possibly you can update the mater after hours with the lists results. That is not required but might be helpful component.

[cavagnaro]

Is there any doc that explains each part of the dialer? Why a manual call needs to be used against a DB? Making it optional makes more sense after call was established...Or idea is to "CRM" each call?

[williamconley]

Is there any doc that explains each part of the dialer? Why a manual call needs to be used against a DB? Making it optional makes more sense after call was established...Or idea is to "CRM" each call?

http://www.vicidial.org/store.php

The VICIDIAL MANAGER MANUAL

Free and paid versions both available. Read the ENTIRE free version for best results. Don't skip any of the exercises. Seriously.

This manual should be used as soon as installation has completed. Install using the PDF on Vicibox.com. Then switch to The vicidial manager's manual for everything else.

The paid version has more details, but the only "comprehensive" way to learn Vicidial is to read the paid version, plus the docs in /usr/src/astguiclient/trunk/docs plus read the options available from all the perl scripts in /usr/share/astguiclient by executing each one with "--help" after the filename, THEN read the tops of those same perl scripts (Many have comments on usage) and then the tops of the agi scripts (which change location based on your version, so "locate agi" may help) and of course read the tops of all the php files. It may take a little while to do all that. But even that doesn't actually generate "comprehensive" explanation of 'each part of the dialer' since the dialer also consists of Asterisk PBX, MySQL databasing and php/perl/ajax coding throughout. Oh, and some .css fun stuff, too.