ROWS performance gets worse the larger the dataset
--------------------------------------------------
Key: CORE-6420
URL: http://tracker.firebirdsql.org/browse/CORE-6420
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0.6
Environment: Win 10 x64
Reporter: Rand Random
I have the need to display a large dataset in my client application, to not
load everything I have decided to work with a paged datagrid and only desire to
display 1000 entries of the dataset, but sadly I am facing an issue that the
last page of the 10million dataset IMHO performs poorly.
DataBase is nothing special
CREATE DATABASE 'localhost/3051:\rfa\BigData\BIGDATA.FDB"'
USER 'SYSDBA'
PAGE_SIZE = 4096
DEFAULT CHARACTER SET UTF8
COLLATION UTF8;
/* Structure for the `FOO` table : */
CREATE TABLE FOO (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
ITEM1 VARCHAR(255),
ITEM2 VARCHAR(255),
ITEM3 VARCHAR(255),
ITEM4 VARCHAR(255),
ITEM5 VARCHAR(255),
ITEM6 VARCHAR(255),
ITEM7 VARCHAR(255),
ITEM8 VARCHAR(255),
ITEM9 VARCHAR(255),
ITEM10 VARCHAR(255),
ITEM11 VARCHAR(255),
ITEM12 VARCHAR(255),
ITEM13 VARCHAR(255),
ITEM14 VARCHAR(255),
ITEM15 VARCHAR(255),
ITEM16 VARCHAR(255),
ITEM17 VARCHAR(255),
ITEM18 VARCHAR(255),
ITEM19 VARCHAR(255));
My connection string is also very simple
initial catalog=C:\rfa\BigData\bigData.fdb;data source=localhost;port
number=3051;user id=SYSDBA;password=masterkey
I am using the .Net Provider, to be precise I am using Entity Framework Core
Provider, to fill my datagrid, for the UI I am using this
<telerik:RadEntityFrameworkCoreDataSource Name="EfSource"
QueryName="Foos">
<telerik:RadEntityFrameworkCoreDataSource.DbContext>
<telerikWpfApp1:MyContext />
</telerik:RadEntityFrameworkCoreDataSource.DbContext>
</telerik:RadEntityFrameworkCoreDataSource>
<telerik:RadGridView Grid.Row="0"
ItemsSource="{Binding ElementName=EfPager,
Path=PagedSource}" />
<telerik:RadDataPager Grid.Row="1"
Name="EfPager"
Source="{Binding ElementName=EfSource,
Path=DataView}"
PageSize="1000" />
Protocolling the SQL queries the Entity Framwork Provider produces is active,
so I am seeing that when I want to look at the last page of my 10 million
dataset the following queries are getting produced
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand
(10ms) [Parameters=[@__p_0='9999000', @__p_1='1000'], CommandType='Text',
CommandTimeout='30']
SELECT COUNT(*)
FROM (
SELECT "f"."ID", "f"."ITEM1", "f"."ITEM10", "f"."ITEM11", "f"."ITEM12",
"f"."ITEM13", "f"."ITEM14", "f"."ITEM15", "f"."ITEM16", "f"."ITEM17",
"f"."ITEM18", "f"."ITEM19", "f"."ITEM2", "f"."ITEM3", "f"."ITEM4", "f"."ITEM5",
"f"."ITEM6", "f"."ITEM7", "f"."ITEM8", "f"."ITEM9"
FROM "FOO" AS "f"
ORDER BY "f"."ID"
ROWS (CAST(@__p_0 AS INTEGER) + 1) TO (CAST(@__p_0 AS INTEGER) +
CAST(@__p_1 AS INTEGER))
) AS "t"
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand
(6ms) [Parameters=[@__p_0='9999000', @__p_1='1000'], CommandType='Text',
CommandTimeout='30']
SELECT "f"."ID", "f"."ITEM1", "f"."ITEM10", "f"."ITEM11", "f"."ITEM12",
"f"."ITEM13", "f"."ITEM14", "f"."ITEM15", "f"."ITEM16", "f"."ITEM17",
"f"."ITEM18", "f"."ITEM19", "f"."ITEM2", "f"."ITEM3", "f"."ITEM4", "f"."ITEM5",
"f"."ITEM6", "f"."ITEM7", "f"."ITEM8", "f"."ITEM9"
FROM "FOO" AS "f"
ORDER BY "f"."ID"
ROWS (CAST(@__p_0 AS INTEGER) + 1) TO (CAST(@__p_0 AS INTEGER) + CAST(@__p_1 AS
INTEGER))
Entity Framework claimes the queries get executed in mere 10ms or 6ms but that
seems incorrect, since when I move the queries to flamerobin and exectue them
there I can see that the performance is as poor as in my application.
So when I run this query, cleaned and hard coded the parameters
SELECT COUNT(*)
FROM (
SELECT f.ID, f.ITEM1, f.ITEM10, f.ITEM11, f.ITEM12, f.ITEM13, f.ITEM14,
f.ITEM15, f.ITEM16, f.ITEM17, f.ITEM18, f.ITEM19, f.ITEM2, f.ITEM3, f.ITEM4,
f.ITEM5, f.ITEM6, f.ITEM7, f.ITEM8, f.ITEM9
FROM FOO AS f
ORDER BY f.ID
ROWS (9999000 + 1) TO (9999000 + 1000)
) AS t
flamerobin gives those statistics
Statement prepared (elapsed time: 0.001s).
Field #01: .COUNT Alias:COUNT Type:BIGINT
PLAN (T F ORDER PK_FOO)
Executing statement...
Statement executed (elapsed time: 0.000s).
22517204 fetches, 0 marks, 2520297 reads, 1 writes.
0 inserts, 0 updates, 0 deletes, 10000001 index, 0 seq.
Delta memory: 49609872 bytes.
Total execution time: 27.374s
Script execution finished.
The plan is rather simple
PLAN (T F ORDER PK_FOO)
Script execution finished.
I would appreciate any help. Thanks in advance.
If you need more information, I am more than willing to provide.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
