dotnetcore / dotnetcore/FreeSql

Potential performance bottleneck in AsTreeCte

Open
#1,491 1 comment 0 reactions 0 assignees View on GitHub
question
Dominant language
C#
Stars
4.4k
Forks
910
PR merge metrics
No merged PRs in 30d

Description

#### 问题描述及重现代码:
First of all, thank you very much for this excellent library!!

In this recursive query I am interested only in two columns:

```
fsql.Select()
.Where(a => a.ID == 23)
.AsTreeCte(up: true)
.ToList(p => new { p.ParentID, p.ID } );
```

This works as expected, but unfortunately inside the CTE in SQL all the columns are selected in both the anchor query and in the recursive query.

As you can see the 'Text', 'Due', etc. columns are selected unnecessarily, that can be a potential performance bottleneck when the table contains a lot of columns.

```
WITH [as_tree_cte]
as
(
SELECT 0 as cte_level, a.[ID], a.[ParentID], a.[PrevID], a.[Owner], a.[Text], a.[Style], a.[Due], a.[Created], a.[LastModification]
FROM [Task] a
WHERE (a.[ID] = 23)

union all

SELECT wct1.cte_level + 1 as cte_level, wct2.[ID], wct2.[ParentID], wct2.[PrevID], wct2.[Owner], wct2.[Text], wct2.[Style], wct2.[Due], wct2.[Created], wct2.[LastModification]
FROM [as_tree_cte] wct1
INNER JOIN [Task] wct2 ON wct2.[ParentID] = wct1.[ID]
)
SELECT a.[ParentID] as1, a.[ID] as2
FROM [as_tree_cte] a

```

#### 数据库版本
SQL Server 2019

#### 安装的Nuget包
3.2.693

#### .net framework/. net core? 及具体版本
.NET 6.0

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by tracing the AsTreeCte entry point from the supplied Select() example and reproduce the generated SQL on SQL Server 2019. Done means the recursive CTE selects only columns needed by the final ParentID/ID projection while preserving the expected tree results; add regression coverage if existing query tests cover this path.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
backend, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.