query against DNC list..

[paok1926]

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 ?

[williamconley]

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.

[paok1926]

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

[williamconley]

Excellent detailed postback.

[paok1926]

hello,

i change status to DNC, but the hopper still loads leads with DNC status !!!

[bourneshell]

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

[paok1926]

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 ?

[bourneshell]

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.

[paok1926]

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

[bourneshell]

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)

[williamconley]

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.