Querys problems last too long


#1

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













#2

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.


#3

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 .


#4

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


#5

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.


#6

[
{
"#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”
}
]
}
]


#7

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.


#8

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


#9

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”
}
]
}
]


#10

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.


#11

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