Page MenuHomePhabricator

Slow searches: Transaction by ID # using % and also Contribs by email address
Closed, ResolvedPublic

Description

Both of these kinds of searches are taking long enough, 2+ minutes in many cases, that I'm working around them and it's making some tasks like refunds unworkably difficult. If there is a better way to search for this data, please let me know.

Details

Related Gerrit Patches:
wikimedia/fundraising/crm : masterUpdate civicrm submodule
wikimedia/fundraising/crm/civicrm : masterFix slow email search
wikimedia/fundraising/crm/civicrm : masterFix slow search on transaction ID (remove wildcard)

Event Timeline

MBeat33 created this task.Nov 2 2015, 10:15 PM
MBeat33 raised the priority of this task from to High.
MBeat33 updated the task description. (Show Details)
MBeat33 added subscribers: Aklapper, atgo, awight and 3 others.

I'll take a look at the gateway one now

This appears to be working for the speed issue - but I'm going to have to get my head straight about which branch I'm submitting to!

https://github.com/eileenmcnaughton/civicrm-core/commit/e1e9400e370e08596469127db9c9a7445c3b7006

CiviCRM issue https://issues.civicrm.org/jira/browse/CRM-17492

I'll be able to discuss the issue with Dave G (CiviCRM core team tomorrow)

@MBeat33
You should be able to search for txn IDs using the "Gateway Transaction ID" field. For example,
Find ContributionsGateway Transaction ID4495023666

I have a patch for this part - just trying to get it pushed up

trxn_id (main Transaction ID) search is also crazily slow. Maybe it's surrounding the trxn_id in wildcards?

awight added a subscriber: LeanneS.Nov 4 2015, 12:06 AM

awight - yes it IS. - sorry I have a patch to try for it... just getting it to you

@awight thank you very much for the "Gateway Transaction ID" tip - it's working quickly

Change 251005 had a related patch set uploaded (by Eileen):
Fix slow search on transaction ID (remove wildcard)

https://gerrit.wikimedia.org/r/251005

Change 251005 merged by jenkins-bot:
Fix slow search on transaction ID (remove wildcard)

https://gerrit.wikimedia.org/r/251005

awight moved this task from Backlog to Important on the Wikimedia-Fundraising-CiviCRM board.
awight set Security to None.
awight moved this task from Backlog to Done on the Fundraising Sprint William Shatner board.

@mbeat can you confirm this is OK to close

@Eileenmcnaughton, the search by a full email address + Display Results as Contribs is still taking 3+ minutes to process, unfortunately. If that can be improved it would be really helpful for the fraud research, where we need to see if previous donors have switched methods before manually settling edge cases.

Change 252463 had a related patch set uploaded (by Eileen):
Fix slow search on transaction ID (remove wildcard)

https://gerrit.wikimedia.org/r/252463

Searches are still slow enough that they're affecting workflow.

Find Contacts Advanced Search > Email address (full, no wildcards) > Display Results as Contacts is consistently taking longer than 40 seconds, experienced by multiple agents. We're doing bulk work with donors, so if this search can be quickened it would be really helpful.

Thanks Michael - that is slightly different to the one above - (which is contribution search) - but I think your description is pretty clear so I should be able to get a fix on it tomorrow.

Change 252463 merged by Eileen:
Fix slow search on transaction ID (remove wildcard)

https://gerrit.wikimedia.org/r/252463

Change 253171 had a related patch set uploaded (by Eileen):
Fix slow email search

https://gerrit.wikimedia.org/r/253171

In the code there was a clear intent to permit strings like "my@email.com" - ie. with the quotes in the query it denotes exact match. I unbroke it but am tempted to remove it unless we think it might be of some use - if not in this than in some other field. I have reservations about keeping for code for functionality so hidden no-one noticed it didn't work....

A further thought - the query doesn't actually search by email - it searches by name - because of the search options WMF has chosen - I feel like the request is to add in an email field as well as the name field

Change 253171 merged by jenkins-bot:
Fix slow email search

https://gerrit.wikimedia.org/r/253171

Eileenmcnaughton added a comment.EditedNov 18 2015, 3:33 AM

@MBeat33 I'm moving this to pending deployment - but I feel like it may not be a complete fix from your point of view. The contribution search has a search-by-name - this only finds email matches if there is no name (& hence their email is used as their display name).

On most CiviCRM sites it is a a search by email or name field but this option has been deselected for performance in the WMF config and the fix alters the label to reflect this.

However, I feel like what you actually want is more like the advanced search screen -ie. the ability to search by one or the other - if that is the case I think it makes sense to open a separate phab for that

Thank you, @Eileenmcnaughton. It is the advanced search that we use most commonly, so a new task for that would make sense. We're still seeing slow results - I just tested and got:

Find Contacts Advanced Search > Email address (full, no wildcards or " ") > Display Results as Contacts - 45 seconds
Find Contacts Advanced Search > Email address (full, no wildcards or " ") > Display Results as Contributions - 4 min, 21 sec

Is there anything from this task that I could add to the new one?

I think the code changes I've made are actually still pending deployment....

I'm gonna deploy these now!

Change 254083 had a related patch set uploaded (by Awight):
Update civicrm submodule

https://gerrit.wikimedia.org/r/254083

Change 254083 merged by jenkins-bot:
Update civicrm submodule

https://gerrit.wikimedia.org/r/254083

@MBeat33
Give it another shot! Looks like @Eileenmcnaughton fixed it :D

@awight @Eileenmcnaughton fantastic, thank you so much!

awight closed this task as Resolved.Nov 19 2015, 6:53 PM

Sounds like it's done.

Change 285780 had a related patch set uploaded (by Eileen):
Fix slow search on transaction ID (remove wildcard)

https://gerrit.wikimedia.org/r/285780

Change 285789 had a related patch set uploaded (by Eileen):
Fix slow email search

https://gerrit.wikimedia.org/r/285789

Change 285780 merged by Eileen:
Fix slow search on transaction ID (remove wildcard)

https://gerrit.wikimedia.org/r/285780

Change 285789 merged by Eileen:
Fix slow email search

https://gerrit.wikimedia.org/r/285789

mmodell removed a subscriber: awight.Jun 22 2017, 9:51 PM