Querys problems last too long

Querys problems last too long.
Appreciate your help.

I have a cube that begins at fast but then I have more than 100 documents attached becomes slow:

  1. Query
  2. Poco
  3. secondary index
    4)App.Config****












  4. /*Query/

string strSql = “select messageId,uniqueId from archive where type = ‘MailSource’ and messageId = $messageId and uniqueId = $uniqueId LIMIT 1”;
bool result = false;
var queryRequest = new QueryRequest()
.Statement(strSql)
.AddNamedParameter("$messageId", messageId)
.AddNamedParameter("$uniqueId", uniqueId)
.AdHoc(false);

        using (Cluster cluster = new Cluster(couchbaseClients))
        {
            using (IBucket bucket = cluster.OpenBucket(Archive,ArchivePassword))
            {
                var query = bucket.Query<MailSource>(queryRequest);
                result = (query.Rows.Count > 0);
                   
            }
        }
        return result;
  1. /**Poco/
    public class MailSource:IDisposable
    {

     public MailSource()
     {
         FromAdrress = new Person();
         ToAddress = new MailAddressSource();
         ReplyToAddress = new MailAddressSource();
         CC = new MailAddressSource();
         Bcc = new MailAddressSource();
         AttachmentList = new List<FileAttach>();
         Folders = new List<string>();
         Viewers = new List<Viewers>();
         Headers = new Dictionary<string, string>();
     }
     public string Type { get { return "MailSource"; } }
     public Dictionary<string,string> Headers { get; set; }
     public string Id {get; set;}
     public string MessageId { get; set; }
     public string UniqueId { get; set; }
     public string Subject {get; set;}
     public Person FromAdrress {get;set;}
     public MailAddressSource ToAddress {get;  set;}
     public MailAddressSource ReplyToAddress {get; set;}
     public MailAddressSource CC { get;  set; }
     public MailAddressSource Bcc { get;  set; }
     public DateTime Date { get; set; }
     public string Body { get; set; }
     public string HtmlBody { get; set; }
     public bool IsBodyHtml { get; set; }
     public List<FileAttach> AttachmentList { get;  set; }
     public List<string> Folders { get;  set; }
     public DateTime MailArchiveDate { get; set; }
     public List<Viewers> Viewers { get; set; }
     public void Dispose()
     {
         GC.SuppressFinalize(this);
     }
    

    }

3-Secundary Index****
CREATE INDEX Index_messageId_uniqueId ON archive(messageId,uniqueId) WHERE (type = MailSource) USING GSI
4App.Config*******












Could you try an EXPLAIN on the query you’re trying to execute? That might give you an idea why it’s taking longer than you expect.

I’m looking if a document MailSource type there is the combination of MessageId and UniqueId should be unique anyway I limit the result to a document and then I check how many records I brought if the number of records is greater than zero I return true otherwise I return false .

Any query you run is taking more than 12 seconds to execute.

Thank you,

Below indexes and configuration of a single node cluster have now 1.3 GB of information in 15773 documents.

I’m using Centos 6.8

CREATE INDEX Index_LogProcess ON archive(taskName) WHERE (type = LogProcess) USING GSI
CREATE INDEX Index_address ON archive(address) WHERE (type = Person) USING GSI
CREATE INDEX Index_department ON archive(department) WHERE (type = Person) USING GSI
CREATE INDEX Index_messageId ON archive(messageId) WHERE (type = MailSource) USING GSI
CREATE INDEX Index_messageId_uniqueId ON archive(messageId,uniqueId) WHERE (type = MailSource) USING GSI
CREATE INDEX Index_uniqueId ON archive(uniqueId) WHERE (type = MailSource) USING GSI
CREATE INDEX Index_userName ON archive(userName) WHERE (type = Person) USING GSI
CREATE PRIMARY INDEX archive_primary_index ON archive USING GSI
CREATE INDEX Index_displayName ON archive(displayName) WHERE (type = Person) USING GSI
CREATE INDEX Index_Chunk_File ON archive(fileId) WHERE (type = ChunkFile) USING GSI
CREATE INDEX Index_Chunk_PositionId ON archive(positionId) WHERE (type = ChunkFile) USING GSI
CREATE INDEX Index_FileAttach_Name ON archive(fileName) WHERE (type = FileAttach) USING GSI
CREATE INDEX Index_FileAttach_Extension ON archive(extension) WHERE (type = FileAttach) USING GSI

Can you try: EXPLAIN SELECT id FROM archive WHERE type = ‘MailSource’ LIMIT 1;

That will show you which of the indexes you are using (if any). My guess is you’re expecting it to use ‘Index_messageId’ but maybe that is not the case.

[
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “archive_primary_index”,
“keyspace”: “archive”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “archive”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((archive.type) = “MailSource”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “(archive.id)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “1”
}
]
}
]

For this query should use the primary index , but for other queries should use secondary indexes , but the trouble is that no matter query run everything is slow.

Please post the EXPLAIN for each query. We can only troubleshoot each query individually.

There is something strange 'm specifying the index that the query should use but , still using the primary index and this and other queries that run take more than 20 seconds.

EXPLAIN SELECT id,messageId,attachmentList,bcc,body,cc,headers,htmlBody,isBodyHtml,mailArchiveDate, toAddress,type,uniqueId, viewers FROM archive USE INDEX (Index_messageId USING GSI) WHERE type = ‘MailSource’ and messageId =‘fd674eb8b8624855a428dd6c83ec3498@USQASWS0162.phx-dc.mysite.com’ LIMIT 1;


[
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “archive_primary_index”,
“keyspace”: “archive”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “archive”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “(((archive.type) = “MailSource”) and ((archive.messageId) = “\u003cfd674eb8b8624855a428dd6c83ec3498@USQASWS0162.phx-dc.mysite.com\u003e”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “(archive.id)”
},
{
“expr”: “(archive.messageId)”
},
{
“expr”: “(archive.attachmentList)”
},
{
“expr”: “(archive.bcc)”
},
{
“expr”: “(archive.body)”
},
{
“expr”: “(archive.cc)”
},
{
“expr”: “(archive.headers)”
},
{
“expr”: “(archive.htmlBody)”
},
{
“expr”: “(archive.isBodyHtml)”
},
{
“expr”: “(archive.mailArchiveDate)”
},
{
“expr”: “(archive.toAddress)”
},
{
“expr”: “(archive.type)”
},
{
“expr”: “(archive.uniqueId)”
},
{
“expr”: “(archive.viewers)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “1”
}
]
}
]

In your CREATE INDEX statement, you need to change

type = MailSource

to

type = ‘MailSource’

You need to drop all your indexes and re-create them with this correction.

Thank you,
Muchimas Gracias,
Tenia mas de una semana con este problema tu lo reolviste,

1 Like