Moving from Vicidial_list to vicidial_n tables

[Adrianyong]

How much RAM is on your DB server?

What RAID card are you using?

Do you really need all 19 million records in the vicidial_list table? Are they all active accounts?

Based on my experience with other predictive system such as interactive intelligence aka pureconnect

These system would never put the leads into a single table like vicidial_list. In each batch, a new table is created eg vicidial_list_aol_2022_01_spc

This allows easier managing and purging leads if required every three months or so. Let alone, myisam table locking issues to reporting.

I think a lot can be improved with this small change.

[Adrianyong]

One do not need to overwrite all existing logic, just add a qualifer to use this schema, and append alternate php or perl script eg

Vicidial_hopper.pl
*new* vicidial_hopper_udl.pl

Udt for user defined lists

[mflorell]

What you describe would affect dozens of scripts in hundreds of places in the code, it would not be a simple thing to change.

[Adrianyong]

Yes, it is not a simple thing. It can be done nonetheless and this change is quite important.

[Adrianyong]

Search "INTO vicidial_list" (93 hits in 42 files of 1980 searched)
Search "vicidial_list set" (196 hits in 85 files of 1980 searched)
Search "from vicidial_list" (782 hits in 142 files of 1980 searched)

Looking at the complexies, I would have done the following

1 ) Create rule to all new leads table to have lst_ prefix eg lst_<batch_name> *note the batch name will be list_id or list_name in vicidial_lists*
2 ) Add column list_id to vicidial_list_alt_phones (with hidden logic changes to retrieval)
3 ) Create a function to retrieve the new leads table and apply to top of all affected codes
4 ) Substitute all the queries with a parameters eg $vicidial_list
eg "INSERT INTO $vicidial_list

this is how i would have gone around it. of course testing would be a challenge

[martinch]

This is a pretty ambitious ask but it's a nice challenge. I may have a look into this.

You can also consider archiving leads off...that should keep the table nice and trim. That may be a feature in itself. List Options -> archive leads after X days.

Cheers,
Martin.

[Adrianyong]

Yeah great Martin, on while we are at it ... please convert it to INNODB not MyISAM. I can't tell you how worry every time. There is a power trip in my office or so.

[RBecker]

Yeah great Martin, on while we are at it ... please convert it to INNODB not MyISAM. I can't tell you how worry every time. There is a power trip in my office or so.

Matt F. has said himself that ViciDial uses the locking properties of MyISAM as a semaphore-type system to ensure that all processes execute in the proper order. While I don't agree with it and would much rather prefer to have actual transactionality in the system, I understand that that's a huge undertaking and not likely something the devs are going to accomplish any time soon (if at all).

[martinch]

I mean, the scope of work involved here...is as mflorell described, it's quite involved. So many lookups and updates to `vicidial_list`...it's thousands of blocks and yeah.

I was wondering if there would be a shortcut way this could achieved. I'm not a big SQL expert...but let's take this example;

We have 1 million records in `vicidial_list` and we use our lead_id and our list_id to point to our "specific" list table based on those values...all other values within `vicidial_list` would be NULL...would that reduce locking when selecting on the table?

Apologies if that was a newbie question...I'm curious about it though. My first thought was to create logic to refer to `vicidial_list_n` table but would require tonnes of change...we're talking potentially thousands of lines of change and yeah...it wouldn't be an easy task.

Thanks guys.

[mflorell]

One of the issues you would have to deal with in doing this is how to handle hopper runs where there are many active lists in a campaign and still allow sorting across all of them. Back in the beginning of VICIdial, it was very common for organizations to load new leads into new lists every day, and then work through all available lead data sorting by number of attempts and/or last call time across hundreds of active lists in a single campaign. This isn't a problem at all when using a single vicidial_list table, but when moving to separate tables for each list, this becomes a massive SQL mess with potentially hundreds of JOINs that would cause the database server to lock up and eventually die. You could of course limit or remove this kind of lead/list usage, but it would have to be addressed if you move forward with this as a project.

[martinch]

One of the issues you would have to deal with in doing this is how to handle hopper runs where there are many active lists in a campaign and still allow sorting across all of them. Back in the beginning of VICIdial, it was very common for organizations to load new leads into new lists every day, and then work through all available lead data sorting by number of attempts and/or last call time across hundreds of active lists in a single campaign. This isn't a problem at all when using a single vicidial_list table, but when moving to separate tables for each list, this becomes a massive SQL mess with potentially hundreds of JOINs that would cause the database server to lock up and eventually die. You could of course limit or remove this kind of lead/list usage, but it would have to be addressed if you move forward with this as a project.

Thanks Matt...well, my engineer tells me JOINs are not ideal and can be quite heavy on the the database engine...I'd probaby opt for straight up lookups where possible for speed...like is the ViCi style. Meat and potatoes kinda SQL. Hopper logic would need to be changed to account for it. I work with a ViCi instance that does exactly as you describe, dozens of lists and new ones added all the time...it can get tricky if we offload to x number of tables. It's an interesting one for sure and I love a good challenge.