locked
Regarding Query expression RRS feed

  • Question

  • hi,

     I have a requirement like , i have to download recently uploaded records to an crm entity. for this we have written an custom aspx page. while entering records ( for example say 50, we are storing first record  time for all the 49 records in an  custom attribute) we are storing the creation time.

    While downloading we have to download all the 50 recent records.

    for this approach , we are facing one problem.

     

    Everyday they will upload so many records, in the query we are records based on sorted order of the custom attribute, but it will return all the records of the system. this will eat the system performance. so we want to fetch only recent records nto the whole records.

    how to achieve this

    can we use top keyword in query, if so, provide me an example

     

     

     

    Thursday, July 22, 2010 11:27 AM

Answers

  • Hi,

    The best way will be use "modifiedon", so that you can get the latest one only


    Mahain
    • Proposed as answer by HIMBAPModerator Thursday, July 22, 2010 11:35 AM
    • Marked as answer by Jim Glass Jr Thursday, July 22, 2010 2:59 PM
    Thursday, July 22, 2010 11:35 AM
    Moderator
  • you can apply modified date condition and then use order by clause

    SELECT "column_name"
    FROM "table_name"
    [WHERE "condition"]
    ORDER BY "column_name" [ASC, DESC]


    Mahain
    • Marked as answer by Jim Glass Jr Thursday, July 22, 2010 2:59 PM
    Thursday, July 22, 2010 12:26 PM
    Moderator
    • Marked as answer by Jim Glass Jr Thursday, July 22, 2010 3:00 PM
    Thursday, July 22, 2010 12:31 PM
    Moderator
  • Use the page and count attributes / PageInfo (in query expression) to set the number of retrieved records.

     

    Here is a fetch example:

     

    <fetch version=\"1.0\" output-format=\"xml-platform\" mapping=\"logical\" page=\"1\" count=\"50\" distinct=\"false\">

       <entity name=\"account\">

         <attribute name=\"name\"/>

         <order attribute=\"createdon\" descending=\"false\"/>

       </entity>

    </fetch>

     

    QueryExpression Example:

     

    QueryExpression query = new QueryExpression();

    //The query returns two pages and one account entity instance per page.

    query.PageInfo = new PagingInfo();

    query.PageInfo.Count = 50;

    query.PageInfo.PageNumber = 1;

     

    Cheers


    GI CRM Blog * GI Website
    • Marked as answer by Jim Glass Jr Thursday, July 22, 2010 3:00 PM
    Thursday, July 22, 2010 1:15 PM

All replies

  • First you need to keep the last retrieved record timestamp [in custom attribute]. And next time only retrieve those records which are created after the "RECORDED Timestamp" [in custom attribute] . this will help you to retrieve only updated records.

    Create you queries in such a way that only retrieve updated reocrds.


    Thanks, Ranjitsingh R | http://mscrm-developer.blogspot.com/ | MS CRM Consultant
    Thursday, July 22, 2010 11:34 AM
  • Hi,

    The best way will be use "modifiedon", so that you can get the latest one only


    Mahain
    • Proposed as answer by HIMBAPModerator Thursday, July 22, 2010 11:35 AM
    • Marked as answer by Jim Glass Jr Thursday, July 22, 2010 2:59 PM
    Thursday, July 22, 2010 11:35 AM
    Moderator
  • in this scenerio, how will get only the latest record, if u sort by ascending/descending it wil lreturn all the records.

    it will create performance issue

    Thursday, July 22, 2010 11:38 AM
  • you can apply modified date condition and then use order by clause

    SELECT "column_name"
    FROM "table_name"
    [WHERE "condition"]
    ORDER BY "column_name" [ASC, DESC]


    Mahain
    • Marked as answer by Jim Glass Jr Thursday, July 22, 2010 2:59 PM
    Thursday, July 22, 2010 12:26 PM
    Moderator
    • Marked as answer by Jim Glass Jr Thursday, July 22, 2010 3:00 PM
    Thursday, July 22, 2010 12:31 PM
    Moderator
  • If entity having 100-1000 records we can use this but if a entity is having more than 10000 records then it will be a problem in performance wise,

    Is there any way to select top records in crm ( i want only recent records not all the records)

    select [attributes] from [table] where [timestamp] = select top(1)[timestamp] from [table] order by [column] asc

    will the above query return recent records ? if so how to achieve this using queryexpression?

    Thursday, July 22, 2010 12:33 PM
  • Use the page and count attributes / PageInfo (in query expression) to set the number of retrieved records.

     

    Here is a fetch example:

     

    <fetch version=\"1.0\" output-format=\"xml-platform\" mapping=\"logical\" page=\"1\" count=\"50\" distinct=\"false\">

       <entity name=\"account\">

         <attribute name=\"name\"/>

         <order attribute=\"createdon\" descending=\"false\"/>

       </entity>

    </fetch>

     

    QueryExpression Example:

     

    QueryExpression query = new QueryExpression();

    //The query returns two pages and one account entity instance per page.

    query.PageInfo = new PagingInfo();

    query.PageInfo.Count = 50;

    query.PageInfo.PageNumber = 1;

     

    Cheers


    GI CRM Blog * GI Website
    • Marked as answer by Jim Glass Jr Thursday, July 22, 2010 3:00 PM
    Thursday, July 22, 2010 1:15 PM