如何将我选择的所有列放入虚拟变量中? [英] How to put all my selected columns into a dummy variable?

问题描述

这个问题是的后续上一个问题.为了给您提供上下文，我想总结一下上一个问题:在上一个问题中，我打算采用一种方法来执行选择而不将结果发送给客户端.目标是通过发送数百万数据来衡量性能而不消耗大量资源.我只对执行这些查询所需的时间感兴趣，而不对它们将结果发送到客户端应用程序的时间感兴趣，因为我打算优化查询，所以查询的结果根本不会改变，但方法论会改变，我打算能够比较这些方法.

This question is a follow-up to a previous question. To give you context here as well, I would like to summarize the previous question: in my previous question I intended to have a methodology to execute selections without sending their result to the client. The goal was to measure performance without eating up a lot of resources by sending millions of data. I am only interested in the time needed to execute those queries and not in the time they will send the results to the client app, since I intend to optimize queries, so the results of the queries will not change at all, but the methodology will change and I intend to be able to compare the methodologies.

在我的另一个问题中，提出了几个想法.一个想法是选择记录的计数并将其放入变量中.但是，这显着改变了查询计划，结果在性能方面并不准确.也提出了使用临时表的想法，但是如果我们不知道什么查询将是我们要测量的输入并且还会引入很多白噪声，那么创建一个临时表并将其插入是很困难的，因此，即使想法很有创意，但对我的问题并不理想.最后，Vladimir Baranov 提出了一个想法，即创建与选择将返回的列一样多的变量.这是一个好主意，但我通过创建 nvarchar(max) 的单个变量并将我的所有列选择到其中来进一步完善它.这个想法很好，除了一些问题.我有大部分问题的解决方案，但我想分享它们，所以，我会描述它们，但不要误解我，我有一个问题.

In my other question several ideas were presented. An idea was to select the count of the records and put it into a variable. However, that changed the query plan significantly and the results were not accurate in terms of performance. The idea of using a temporary table was presented as well, but creating a temporary table and inserting into it is difficult if we do not know what query will be our input to measure and also introduces a lot of white noise, so, even though the idea was creative, it was not ideal for my problem. Finally Vladimir Baranov came with an idea to create as many variables as many columns the selection will return. This was a great idea, but I refined it further, by creating a single variable of nvarchar(max) and selecting all my columns into it. The idea works great, except for a few problems. I have the solution for most of the problems, but I would like to share them, so, I will describe them regardless, but do not misunderstand me, I have a single question.

如果我有一个 @container 变量并且我在每个选择中都做了一个 @container = columnname，那么我就会遇到转换问题.

If I have a @container variable and I do a @container = columnname inside each selection, then I will have conversion problems.

我需要做一个 @container = cast(columnname as nvarchar(max))

我需要转换as something 到 @container = cast(<whatever> as nvarchar(max)) 对于选择中的每一列，但不是子选择，我需要有一个通用的解决方案处理case when 和括号，我不想在任何地方有 @container = 的任何实例，除了主要选择的左侧.

I will need to convert <whatever> as something into @container = cast(<whatever> as nvarchar(max)) for each columns in the selection, but not for subselections and I will need to have a general solution handling case when and parantheses, I do not want to have any instances of @container = anywhere, except to the left of the main selection.

由于我对正则表达式一无所知，我可以通过迭代查询字符串来解决这个问题，直到找到主查询的 from 并且每次找到括号时，我什么都不做，直到找到括号关闭，找到应该放置 @container = 和 as [customname] 的索引，并从右到左在查询字符串中执行所有操作.这将是一个又长又不优雅的代码.

Since I am clueless about regular expressions, I can solve this by iterating the query string until I find the from of the main query and each time I find a parantheses, I will do nothing until that parantheses is closed, find the indexes where @container = should be put and as [customname] should be taken out and from right to left do all that in the query string. This will be a long and unelegant code.

是否可以确保我的所有主要列都以 @container = 开头并以 as [Customname] 结尾?

Is it possible to make sure that all my main columns but nothing else start with @container = and ends without as [Customname]?

推荐答案

我会尝试编写一个单独的 CLR 函数，该函数根据测量需要运行尽可能多的查询.它可能有一个参数，其中包含要运行的查询文本或要运行的存储过程的名称.

I would try to write a single CLR function that runs as many queries as needed to measure. It may have a parameter with the text(s) of queries to run, or names of stored procedures to run.

您对服务器有一个请求.一切都在服务器上本地完成.没有网络开销.您可以通过对需要测量的每个查询使用 ExecuteNonQuery 来丢弃 .NET CLR 代码中的查询结果，而不使用显式临时表.

You have a single request to the server. Everything is done locally on the server. No network overhead. You discard query result in the .NET CLR code without using explicit temp tables by using ExecuteNonQuery for each query that you need to measure.

不要更改您正在衡量的查询.优化器很复杂，对查询的更改可能会对性能产生各种影响.

Don't change the query that you are measuring. Optimizer is complex, changes to the query may have various effects on the performance.

另外，使用SET STATISTICS TIME ON并让服务器为你测量时间.获取服务器要说的内容，对其进行解析并以适合您的格式将其发送回.

Also, use SET STATISTICS TIME ON and let the server measure the time for you. Fetch what the server has to say, parse it and send it back in the format that suits you.

我认为，SET STATISTICS TIME ON/OFF 的结果是最可靠和准确的，并且噪音最小.

I think, that results of SET STATISTICS TIME ON / OFF are the most reliable and accurate and have the least amount of noise.

这篇关于如何将我选择的所有列放入虚拟变量中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！