Query limit

[SI]

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

Query Image

[Simeon Simeonov]

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

 

[SI]

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

 

[Simeon Simeonov]

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

 

[SI]

1 - Intellitrace

Image​

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

 

 

[Simeon Simeonov]

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

 

[SI]

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) 

[Simeon Simeonov]

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

 

[SI]

It's because brackets? 

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

Could be transformed in:

select top  1  * from product (working)

[SI]

Sorry, now I understand.

 

Thanks.