query against DNC list..

All installation and configuration problems and questions

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

query against DNC list..

Postby paok1926 » Wed May 22, 2019 6:07 pm

Hello all,

i have a table called dnc_list where i keep the dnc list
When i create a new list, i want to check it against this list and change the status of the leads appropriate..
i run this mysql query...

Code: Select all
UPDATE vicidial_list c1
INNER JOIN dnc_list c2 ON c1.phone_number = c2.dnc_number
SET c1.STATUS = 'DNC'
WHERE
c1.list_id = 50082


The dnc list has 8.5 mil records, and the specific list 10.000 records
This query runs the last 8500", its 2AM and i want to go to sleep :)

can someone suggest a fastest way to do this ?
Vicibox from ViciBox_v8_1.x86_64-8.1.2.iso | Asterisk 11.25.3-vici
VERSION: 2.14-695a - BUILD: 181116-1133
No Extra Software and Hardware | VM with 8 cores & 16Gb Ram
~8000-9000 calls per day
paok1926
 
Posts: 40
Joined: Mon Jan 07, 2019 6:45 pm

Re: query against DNC list..

Postby williamconley » Wed May 22, 2019 6:14 pm

Don't load the entire NDNC into a single table. Load each area code into its own table. Then run each of those tables against your vicidial_list table. It would SEEM to take more time that way, but it actually takes MUCH less. Try it on a single area code as a test.

Also be sure your indexes match all your search terms. Missing indexes can multiply your "where" and "join" time by an order of magnitude.

Remember: When you join, it joins first and then runs the "where" clause. So it has to join ALL the records in Vicidial List to ALL the records in NDNC before it can even begin to run the where. Checking one list and checking all lists is not really very different.

If you do it one area code at a time, the hit is dramatically reduced. Still takes a long time, but hours not days any more! lol (and each individual area code is not long at all, so you can see progress).

If you limit the searches to area codes that actually exist in this list, you can reduce the time even farther.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: query against DNC list..

Postby paok1926 » Thu May 23, 2019 5:00 am

As i had my tables, the update query was running for more than 10000 secs...
I added these primary keys and indexes, after your recommendation, and the query runs at 0.13secs

Code: Select all
 PRIMARY KEY (`dnc_id`, `dnc_number`) USING BTREE,
  INDEX `numbers`(`dnc_number`) USING BTREE


thank you very-very much !!!!
Vicibox from ViciBox_v8_1.x86_64-8.1.2.iso | Asterisk 11.25.3-vici
VERSION: 2.14-695a - BUILD: 181116-1133
No Extra Software and Hardware | VM with 8 cores & 16Gb Ram
~8000-9000 calls per day
paok1926
 
Posts: 40
Joined: Mon Jan 07, 2019 6:45 pm

Re: query against DNC list..

Postby williamconley » Thu May 23, 2019 4:44 pm

Excellent detailed postback. 8-)
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: query against DNC list..

Postby paok1926 » Wed Jun 19, 2019 6:32 am

hello,

i change status to DNC, but the hopper still loads leads with DNC status !!!
Vicibox from ViciBox_v8_1.x86_64-8.1.2.iso | Asterisk 11.25.3-vici
VERSION: 2.14-695a - BUILD: 181116-1133
No Extra Software and Hardware | VM with 8 cores & 16Gb Ram
~8000-9000 calls per day
paok1926
 
Posts: 40
Joined: Mon Jan 07, 2019 6:45 pm

Re: query against DNC list..

Postby bourneshell » Wed Jun 19, 2019 6:39 am

The hopper actually validates the phone number if they exist in both the vicidial_dnc and vicidial_campaign_dnc tables, considering that you have the DNC settings on your campaign setup.

I would recommend to also copy your custom dnc_list table to vicidial_dnc and / or vicidial_campaign_dnc
https://www.dialer.host/
bourneshell
 
Posts: 29
Joined: Mon May 14, 2018 12:55 pm

Re: query against DNC list..

Postby paok1926 » Wed Jun 19, 2019 8:08 am

My dnc list has 10.000.000 records...

everyone said that it will hang the system, that's why i choose to check and set the lead status to DNC.
hopper does not check the status of the lead ?
Vicibox from ViciBox_v8_1.x86_64-8.1.2.iso | Asterisk 11.25.3-vici
VERSION: 2.14-695a - BUILD: 181116-1133
No Extra Software and Hardware | VM with 8 cores & 16Gb Ram
~8000-9000 calls per day
paok1926
 
Posts: 40
Joined: Mon Jan 07, 2019 6:45 pm

Re: query against DNC list..

Postby bourneshell » Wed Jun 19, 2019 8:14 am

It would depend on what you have set as dialable statuses on your campaign settings. If DNC is not on that list then yes your leads should not be loaded on the hopper.

Unless you have it set on your lead recycling. Kindly check both settings and see maybe you have DNC on those.
https://www.dialer.host/
bourneshell
 
Posts: 29
Joined: Mon May 14, 2018 12:55 pm

Re: query against DNC list..

Postby paok1926 » Wed Jun 19, 2019 8:24 am

i have DNC as dial status in every campaign.
Should i remove it ?
Vicibox from ViciBox_v8_1.x86_64-8.1.2.iso | Asterisk 11.25.3-vici
VERSION: 2.14-695a - BUILD: 181116-1133
No Extra Software and Hardware | VM with 8 cores & 16Gb Ram
~8000-9000 calls per day
paok1926
 
Posts: 40
Joined: Mon Jan 07, 2019 6:45 pm

Re: query against DNC list..

Postby bourneshell » Wed Jun 19, 2019 8:26 am

There is the culprit. Please remove that. Only put Dial Statuses on the Campaign settings that you want to call again and remove the one's that should no longer be called. e.g. Not Interested (NI)
https://www.dialer.host/
bourneshell
 
Posts: 29
Joined: Mon May 14, 2018 12:55 pm

Re: query against DNC list..

Postby williamconley » Wed Jun 19, 2019 11:14 am

paok1926 wrote:i have DNC as dial status in every campaign.
Should i remove it ?

NEVER put DNC as a dial status in any campaign ever.

Dial Status gives permission to dial. Many people think they need to put the "dispositions for the end of the call" in this "Dial Status" list, but that's a completely different function. Statuses to display at the end of the call are in "System Settings => System Statuses". There is a vertical column for "Selectable" that is used to decide which ones appear in the "Select" list at the end of the call.

Or if you want to ADD one for just one campaign (but not shown on others), you use Detail View Top Menu -> Statuses.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)


Return to Support

Who is online

Users browsing this forum: No registered users and 78 guests