Query limit

10 posts, 1 answers
  1. SI
    SI avatar
    9 posts
    Member since:
    Aug 2014

    Posted 08 Aug 2015 Link to this post

    Is ​this query (image link below) returning all rows? Where's limit keyword?

    Query Image

  2. Simeon Simeonov
    Admin
    Simeon Simeonov avatar
    24 posts

    Posted 12 Aug 2015 Link to this post

    Hello SI,

    Based on the information you have provided we cannot properly analyze your case. Could you provide us with additional information:
    - Are you using the Contemporary API or the Classic API?
    - Are you using RLINQ or fluent mapping?
    - What is the object between "var query =" and ".Where(p =>" ? What is the object on which the Where method is executed? Could you provide us with the code for constructing that object? If you cannot provide us with your code, could you provide us with code demonstrating the issue using the Northwind database?
    - What is the type of the "query" object and is the "Comand Text" property displayed, a property of that object?

    Thank you in advance for your collaboration.

    Regards,
    Simeon Simeonov
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
  4. SI
    SI avatar
    9 posts
    Member since:
    Aug 2014

    Posted 17 Aug 2015 in reply to Simeon Simeonov Link to this post

    - Contemporary 

    - I believe that is fluent mapping:

    var produtoMapping = new MappingConfiguration<Produto>();
    produtoMapping.MapType(produto => new
    {

    ....

     
    - What is the object between "var query =" and ".Where(p =>" ?....

    public List<Produto> GetByDescricao(string descricao)
    {
        var query = Produtos.Where(p => p.Descricao.StartsWith(descricao)).Take(10);
        return query.ToList();
    }
     
     private IQueryable<Produto> Produtos
    {
        get
        {
            return _context.GetAll<Produto>();
        }
    }

    - I think that the code above answer the last question question.

     

  5. Simeon Simeonov
    Admin
    Simeon Simeonov avatar
    24 posts

    Posted 20 Aug 2015 Link to this post

    Hi SI,

    Thank you for the provided additional information.

    I have implemented your exact case and executed it. Here is the code I used:
    var query = context.GetAll<Product>().Where(p => p.Name.StartsWith(name)).Take(10);

    Could you tell me how you have displayed this "CommandText" property?

    When I want to check what SQL query will be created from that specific code I execute this:
    query.ToString();

    The end result was:
    "SELECT  TOP(??T) a.[Products_id] AS COL1, a.[CategoryId] AS COL2, a.[CreatedBy] AS COL3, a.[CreatedDate] AS COL4, a.[Discontinued] AS COL5, a.[Id] AS COL6, a.[nme] AS COL7, a.[Price] AS COL8 FROM [Products] a WHERE a.[nme] LIKE ? + '%' ESCAPE '\\' "

    As you may see in the generated SQL query, there is a TOP parameter. Also the query, when executed, actually retrieved the first 10 records.

    When you execute the query on your side, is an actual paging enforced? Are the first 10 records retrieved or all of them? 

    Thank you in advance. 

    Regards,
    Simeon Simeonov
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  6. SI
    SI avatar
    9 posts
    Member since:
    Aug 2014

    Posted 20 Aug 2015 in reply to Simeon Simeonov Link to this post

    1 - Intellitrace

    Image

    2 - Just first 10, my doubt its about this query.

     

     

  7. Simeon Simeonov
    Admin
    Simeon Simeonov avatar
    24 posts

    Posted 25 Aug 2015 Link to this post

    Hi SI,

    Thank you for the provided information. After investigating the problem with the rest of the team we have the suspicion that maybe we are not correctly detecting the version of your backend.

    Could you provide us with details about the database you are using?

    If you are using MSSQL could you execute the following query against your database and provide us with the resulting string:

    select @@version

    Thank you in advance,
    Regards,
    Simeon Simeonov
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  8. SI
    SI avatar
    9 posts
    Member since:
    Aug 2014

    Posted 25 Aug 2015 in reply to Simeon Simeonov Link to this post

    SELECT SERVERPROPERTY('productversion')
    8.00.2039  

    SERVERPROPERTY ('productlevel')
    SP4

     SERVERPROPERTY ('edition')

    Developer Edition

    select @@version
    Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1) 

  9. Answer
    Simeon Simeonov
    Admin
    Simeon Simeonov avatar
    24 posts

    Posted 28 Aug 2015 Link to this post

    Hello SI,

    Thank you for your answer. From this additional information I now see you are using SQL SERVER 2000, which does not support the TOP clause. As you can check on this link, the TOP clause is supported from version 2005 and above. For this reason the generated SQL statement you see and the one that actually is executed on the SQL server has not TOP clause and OpenAccess performs the actual paging in memory. This is not ideal from performance point of view and you should keep that in mind.

    Regards,
    Simeon Simeonov
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  10. SI
    SI avatar
    9 posts
    Member since:
    Aug 2014

    Posted 28 Aug 2015 in reply to Simeon Simeonov Link to this post

    It's because brackets? 

    select top (1) * from product (not working)

    Could be transformed in:

    select top  1  * from product (working)

  11. SI
    SI avatar
    9 posts
    Member since:
    Aug 2014

    Posted 28 Aug 2015 in reply to SI Link to this post

    Sorry, now I understand.

     

    Thanks.

Back to Top
DevCraft banner