dotnetcore / dotnetcore/FreeSql
Potential performance bottleneck in AsTreeCte
- 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