Query for Candidate takes 4+ minutes

Tagged:

Currently contains 7 posts

Avatar

March 31, 2021 14:18

eknowlton

I have a request to https://www.pcrecruiter.net/rest/api/candidates and I’m searching for a specific email…

Since we do not know which field the email resides, we want to look at least in…

- EmailAddress
- Email_Home
- Email_Work

https://www.pcrecruiter.net/rest/api/candidates?Query=EmailAddress+co+test%40test.com+or+Email_Work+co+test%40test.com+or+Email_Home+co+test%40test.com

(session ID omitted)

Other custom fields we would like to consider…

- Email_Homepage
- Email_Child
- Email_GMAIL
- Email_Other
- Email_Spouse

Currently the above request takes on the high end of 4 minutes* to execute.

We have a few ideas such as querying one field at a time, starting with `EmailAddress` to search without having the search take all 4+ minutes*, but seems rather silly.

We were wondering if there is something we can do to optimize this query, or perhaps maybe, since were using a “temporary” database during our data migration, these fields or custom fields are just not currently indexed?

Any ideas would be appreciated. Thanks!

Avatar

March 31, 2021 15:13

Jarrid-PCRecruiter

I would check the primary “EmailAddress” first and if a result isn’t found then make a second call to check the alternate email address fields. The alternate fields are technically stored with all of our other custom fields so it takes longer for lookup requests and typically you’re going to find the address by just checking the primary email address field. Also, just “contains” instead of “equals” will do a wildcard search which will increase lookup times. Hope this helps!

Avatar

March 31, 2021 15:45

eknowlton

Yes, that was what I meant by searching one field at a time ( stargting with EmailAddress.)

I don’t believe “and typically you’re going to find the address by just checking the primary email address field” is really true.

Also, the query is already using “Contains”, so thanks.

We will go ahead and search one by one and cache the results to avoid hitting the API as much as possible. Thanks!

Avatar

March 31, 2021 16:08

Jarrid-PCRecruiter

No problem, but what I meant is that contains is a wildcard search, it’s basically:
SELECT candidate_id FROM candidates WHERE email_address LIKEtest@test.com
Where as equals would be:
SELECT candidate_id FROM candidates WHERE email_address = ‘test@test.com’
It makes the search go much faster if you’re doing an exact match instead of telling SQL to do a wildcard search. All of the fields are indexed so it really shouldn’t take 4+ minutes. If you want to email me (jarrid at mainsequence dot net) your database ID I can take a look at the database itself to make sure it’s not missing any indexes.

Avatar

March 31, 2021 16:09

Jarrid-PCRecruiter

The forum removed my percent signs but it should have been…
contains is a wildcard search, it’s basically:
SELECT candidate_id FROM candidates WHERE email_address LIKE ‘PERCENTSIGNtest@test.comPERCENTSIGN’

Avatar

March 31, 2021 17:05

eknowlton

I understand. I guess I was hoping to be more inclusive with using the contains, but it really doesn’t make much sense.

I’m going to switch to use equals instead of contains. I didn’t realize what you meant in your first post: using contains could slow down the search.

I’ve switched to using equals, and it seems searching `EmailAddress`, `Email_Work`, and `Email_Home` returns in ~15 seconds, which is great.

Thanks for your help!

Avatar

March 31, 2021 17:11

Jarrid-PCRecruiter

Glad I could help! Please let us know if you have any other questions.

Reply to thread