使用CURSOR是否会提高在SQL中使用PIVOT进行查询的性能/速度? [英] Will the use of CURSOR improve the performance / speed of querying using PIVOT in SQL?

问题描述

此处是SQL中DB的 EAV(实体-属性-值)模型的新手.

Newbie here on EAV (Entity-Attribute-Value) model of DB in SQL.

只是一个背景:我正在使用 SQL Server 2016 .使用EAV是工作中的一种要求，因此我正在学习一次做到这一点.

Just a background: I am using SQL Server 2016. The use of EAV is kind of a requirement at work so I am learning to do it one step at a time.

我最近学习了如何执行动态 PIVOT ，以在EAV表中返回具有 200+列的 800+行.

I recently learned how to do a dynamic PIVOT to return 800+ rows with 200+ columns in an EAV table.

在此处查看详细信息: 使用PIVOT将200+行转换为SQL Server中的列

See details here: Converting 200+ rows to column in SQL Server using PIVOT

成功返回我需要的数据，性能速度太慢-查询大约需要30分钟.顺便说一下，我使用的代码如下:

As successful it was to return the data I need, the performance speed was too slow - it took about 30mins to query. By the way, I am using the code as follows:

declare @pivot_col varchar(max);
declare @sql varchar(max);

select @pivot_col = STUFF( 
                             ( SELECT ',' + CAST([Col_Name] AS VARCHAR(max) ) AS [text()]  
                             FROM ( select distinct [Col_Name] from tbl_Values ) A 
                             ORDER BY [Col_Name] FOR XML PATH('')), 1, 1, NULL
                          );

set @sql = 'SELECT * 
            FROM ( SELECT [Row_ID], [Col_Name], [Col_Value] FROM tbl_Values ) AS a 
            PIVOT (
                    MAX([Col_Value])
                    FOR [Col_Name] in (' +  @pivot_col + ' )
                  ) AS p 
            ORDER BY [Row_ID]';

exec ( @sql );

我正在尝试将 CURSOR 与此并入，但进展不多.在我进一步研究之前，您能否提供有关性能/速度方面是否有所不同的信息?

I am trying to incorporate CURSOR with this but hasn't gone much far. Before I go more distance on research, can you provide input as to if it makes any difference with regards to performance / speed?

谢谢！

推荐答案

找到了解决PIVOT查询性能低下的方法:有人告诉我在表中的Row_ID列上创建聚簇索引.我在下面运行查询:

Found a solution to the poor performance of my PIVOT query: I was told to create a clustered index on the Row_ID column I have in my table. I ran the query below:

CREATE CLUSTERED INDEX IX_tbl_Values_Row_ID  
ON dbo.tbl_Values (Row_ID);   
GO  

我对我的问题的查询只花了30分钟，而现在它只运行了6秒钟！感谢@MohitShrivastava的提示！绝对有效.

And the query I have on my question which took 30 mins to load before had now run for just 6 seconds now! Thanks to @MohitShrivastava for the tip! Definitely worked.

在创建聚簇索引之前，我也提到了这一点:
https: //docs.microsoft.com/zh-CN/sql/relational-databases/indexes/create-clustered-indexes?view=sql-server-ver15

I also referred to this before creating the clustered index:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-clustered-indexes?view=sql-server-ver15

这篇关于使用CURSOR是否会提高在SQL中使用PIVOT进行查询的性能/速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！