Salesforce Too many soql 101

Question

1. 它在sandBox中工作得很好，但在生产中会导致太多的soql 101。我试着用2。具有内容分布的bulkified map与soql查询链接，但在map中总是阻塞bulk。有什么建议吗?

2. 因为我尝试了list customObject__c=[select…]内容分布:= ..[Id]但它也给了太多的soql 101

@RestResource (urlMapping = '/API/V1/注意/*)全局共享类API_Notice {

@HttpGet(UrlMapping='/API/V1/notice/all')
global static List<String> getNotice(){          

List<Object> senderJson = new List<Object>();  

for (Notice__c a: [SELECT Name, ClosingDate__c,Contents__c, NoticeTypes__c,createddate,OfficialSenders__c,id,(SELECT ContentDocumentId  FROM ContentDocumentLinks) FROM Notice__c]) {               
List<Object> multipleAcct = new List<Object>();                                       
NoticeWrapper nw = new NoticeWrapper();
Set<Id> contentsId = new Set<Id>();             
List<Object> urls = new List<Object>();
Set<Id> acctId = new Set<Id>();
for(ContentDocumentLink cdl: a.ContentDocumentLinks){
if(cdl.ContentDocumentId!=null){
contentsId.add(cdl.ContentDocumentId);
}
}
if(a.OfficialSenders__c != null){
acctId.add(a.OfficialSenders__c);
}

if(a.id!=null){
nw.noticeid = a.Id;               
}     
if(a.ClosingDate__c != null){   
nw.ClosingDate = a.ClosingDate__c;                  
}     
if(a.NoticeTypes__c != null) {
nw.NoticeTypes = a.NoticeTypes__c;                
}    
if(a.Contents__c !=null){
nw.Contents = a.Contents__c;            
}
if(a.Name !=null) {
nw.Name = a.Name;         
}
if(a.CreatedDate !=null){
nw.createddate = a.createddate; 
}  
if(!acctId.isEmpty()){
Map<Id,CampaignMember> camplinks = new Map<Id,CampaignMember>([
select accountid ,CampaignId from CampaignMember where CampaignId IN: acctId 
]);                
if(!camplinks.isEmpty()){
for(CampaignMember cm : camplinks.values()){
multipleAcct.add(cm.AccountId);
nw.accountId = multipleAcct;  
}
}
}
if(!contentsId.isEmpty() && contentsid!=null){
Map<Id,ContentDistribution> links = new Map<Id,ContentDistribution>([
select id , distributionPublicURL,ContentDocumentId from contentDistribution where ContentDocumentId IN: contentsId 
]); 
if(!links.isEmpty()){
for(contentDistribution cdb : links.values()){                          
urls.add(cdb.DistributionPublicUrl);
nw.DistributionPublicUrl = urls;
}
}              
}




senderJson.add(nw);    
}





List<String> sends = new List<String>();
for(Object json : senderJson){
sends.add(String.valueof(json));
}
return sends;    

}
@HttpPost
global static List<String> getOneNotice(String Id){

List<Object> urls = new List<Object>();
List<Object> senderJson = new List<Object>();
List<Object> multipleAcct = new List<Object>();
for (Notice__c a: [SELECT Name, ClosingDate__c,Contents__c,OfficialSenders__c, id,(SELECT ContentDocumentId FROM ContentDocumentLinks) FROM Notice__c where id=:id]) {
Set<Id> acctId = new Set<Id>();
Set<Id> contentsId = new Set<Id>();            
NoticeWrapper nw = new NoticeWrapper();  
nw.noticeid = a.Id;               
nw.ClosingDate = a.ClosingDate__c;                
nw.Contents = a.Contents__c;            
nw.Name = a.Name;


for(ContentDocumentLink cdl: a.ContentDocumentLinks){
if(cdl.ContentDocumentId!=null){
contentsId.add(cdl.ContentDocumentId);
}
}
if(a.OfficialSenders__c != null){
acctId.add(a.OfficialSenders__c);
}
if(!acctId.isEmpty()){
Map<Id,CampaignMember> camplinks = new Map<Id,CampaignMember>([
select accountid ,CampaignId from CampaignMember where CampaignId IN: acctId 
]);

if(!camplinks.isEmpty()){
for(CampaignMember cm : camplinks.values()){
multipleAcct.add(cm.AccountId);
nw.accountId = multipleAcct;  
}
}
}

if(!contentsId.isEmpty()){
Map<Id,ContentDistribution> links = new Map<Id,ContentDistribution>([
select id , distributionPublicURL,ContentDocumentId from contentDistribution where ContentDocumentId IN: contentsId 
]);


if(!links.isEmpty()){
for(contentDistribution cdb : links.values()){
urls.add(cdb.DistributionPublicUrl);
nw.DistributionPublicUrl = urls;
}
}
}
senderJson.add(nw);    
}                       
List<String> sends = new List<String>();

for(Object json : senderJson){
sends.add(String.valueof(json));
} 
return sends;    
}

}

Answer 1

循环的轮廓如下

for (Notice__c a: [SELECT Name, ClosingDate__c,Contents__c, NoticeTypes__c,createddate,OfficialSenders__c,id,(SELECT ContentDocumentId  FROM ContentDocumentLinks) FROM Notice__c]) {
// ...
if(!acctId.isEmpty()){
Map<Id,CampaignMember> camplinks = new Map<Id,CampaignMember>([
select accountid ,CampaignId from CampaignMember where CampaignId IN: acctId
]);
}
if(!contentsId.isEmpty() && contentsid!=null){
Map<Id,ContentDistribution> links = new Map<Id,ContentDistribution>([
select id , distributionPublicURL,ContentDocumentId from contentDistribution where ContentDocumentId IN: contentsId
]);
}
// ...
senderJson.add(nw);
}

所以在每个循环迭代中有2个查询，它将在50条Notice__c记录后爆炸。你可能可以稍微优化一下，但很快你就会遇到不同的限制，比如在单个事务中查询50K行。过去，你真的需要一些优化，也许考虑创建迷你JSON对象，因为人们编辑的数据和存储在Notice__c内的辅助文本字段?然后API可以简单地查询它们。

检查这样做是否会更好。

// 1. Query all notices.
// 2. Loop through them once to pull the Ids of related records we need to query.
// 3. Query the related stuff once and put it in a map we can easily access.
// 4. Then loop notices again, building the final JSON message.
// 1
List<Notice__c> notices = [SELECT Name, ClosingDate__c,Contents__c, NoticeTypes__c, createddate,
OfficialSenders__c, id,
(SELECT ContentDocumentId  FROM ContentDocumentLinks)
FROM Notice__c];
Map<Id, Campaign> campaigns = new Map<Id, Campaign>();
Map<Id, ContentDocument> contentdocs = new Map<Id, ContentDocument>();
// 2
for (Notice__c a: notices){
campaigns.put(a.OfficialSenders__c, null);
for(ContentDocumentLink cdl: a.ContentDocumentLinks){
contentdocs.put(cdl.ContentDocumentId, null);
}
}
campaigns.remove(null); // who needs ifs in the loop if you can just remove them once afterwards
contentdocs.remove(null);
// 3a - Campaigns
if(!campaigns.isEmpty()){
campaigns = new Map<Id, Campaign>([SELECT Id,
(SELECT AccountId FROM CampaignMembers WHERE AccountId != null)
FROM Campaign
WHERE Id IN :campaigns.keyset()]);
}
// 3b - download links
if(!contentdocs.isEmpty()){
contentdocs = new Map<Id, ContentDocument>([SELECT Id,
(SELECT distributionPublicURL FROM ContentDistributions WHERE distributionPublicURL != null)
FROM ContentDocument
WHERE Id IN :contentdocs.keyset()]);
}
// 4
List<Object> senderJson = new List<Object>();
for (Notice__c a: notices){
// bla bla, I'm skipping the ifs

Id campaignId = a.OfficialSenders__c;
// 4a
if(campaigns.containsKey(campaignId)){
Campaign c = campaigns.get(campaignId);
Set<Id> accountIds = new Set<Id>();
for(CampaignMember cm : c.CampaignMembers){
accountIds.add(cm.AccountId);
}
// nw.accountId = accountIds;
}

// 4b
if(!a.ContentDocumentLinks.isEmpty()){
Set<String> urls = new Set<String>();
for(ContentDocumentLink cdl: a.ContentDocumentLinks){
Id cdi = cdl.ContentDocumentId;
if(contentdocs.containsKey(cdi)){
for(ContentDistribution cd : contentdocs.get(cdi).ContentDistributions){
urls.add(cd.distributionPublicURL);
}
}
}
// nw.DistributionPublicUrl = urls;
}   
}