問題描述

這個(gè)問題是的后續(xù)上一個(gè)問題.為了給您提供上下文，我想總結(jié)一下上一個(gè)問題:在上一個(gè)問題中，我打算采用一種方法來執(zhí)行選擇而不將結(jié)果發(fā)送給客戶端.目標(biāo)是通過發(fā)送數(shù)百萬數(shù)據(jù)來衡量性能而不消耗大量資源.我只對(duì)執(zhí)行這些查詢所需的時(shí)間感興趣，而不對(duì)它們將結(jié)果發(fā)送到客戶端應(yīng)用程序的時(shí)間感興趣，因?yàn)槲掖蛩銉?yōu)化查詢，所以查詢的結(jié)果根本不會(huì)改變，但方法論會(huì)改變，我打算能夠比較這些方法.

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.

在我的另一個(gè)問題中，提出了幾個(gè)想法.一個(gè)想法是選擇記錄的計(jì)數(shù)并將其放入變量中.但是，這顯著改變了查詢計(jì)劃，結(jié)果在性能方面并不準(zhǔn)確.也提出了使用臨時(shí)表的想法，但是如果我們不知道什么查詢將是我們要測(cè)量的輸入并且還會(huì)引入很多白噪聲，那么創(chuàng)建一個(gè)臨時(shí)表并將其插入是很困難的，因此，即使想法很有創(chuàng)意，但對(duì)我的問題并不理想.最后，Vladimir Baranov 提出了一個(gè)想法，即創(chuàng)建與選擇將返回的列一樣多的變量.這是一個(gè)好主意，但我通過創(chuàng)建 nvarchar(max) 的單個(gè)變量并將我的所有列選擇到其中來進(jìn)一步完善它.這個(gè)想法很好，除了一些問題.我有大部分問題的解決方案，但我想分享它們，所以，我會(huì)描述它們，但不要誤解我，我有一個(gè)問題.

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.

如果我有一個(gè) @container 變量并且我在每個(gè)選擇中都做了一個(gè) @container = columnname，那么我就會(huì)遇到轉(zhuǎn)換問題.

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

我需要做一個(gè) @container = cast(columnname as nvarchar(max))

我需要轉(zhuǎn)換as something 到 @container = cast(<whatever> as nvarchar(max)) 對(duì)于選擇中的每一列，但不是子選擇，我需要有一個(gè)通用的解決方案處理case when 和括號(hào)，我不想在任何地方有 @container = 的任何實(shí)例，除了主要選擇的左側(cè).

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.

由于我對(duì)正則表達(dá)式一無所知，我可以通過迭代查詢字符串來解決這個(gè)問題，直到找到主查詢的 from 并且每次找到括號(hào)時(shí)，我什么都不做，直到找到括號(hào)關(guān)閉，找到應(yīng)該放置 @container = 和 as [customname] 的索引，并從右到左在查詢字符串中執(zhí)行所有操作.這將是一個(gè)又長(zhǎng)又不優(yōu)雅的代碼.

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] 結(jié)尾?

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

推薦答案

我會(huì)嘗試編寫一個(gè)單獨(dú)的 CLR 函數(shù)，該函數(shù)根據(jù)測(cè)量需要運(yùn)行盡可能多的查詢.它可能有一個(gè)參數(shù)，其中包含要運(yùn)行的查詢文本或要運(yùn)行的存儲(chǔ)過程的名稱.

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.

您對(duì)服務(wù)器有一個(gè)請(qǐng)求.一切都在服務(wù)器上本地完成.沒有網(wǎng)絡(luò)開銷.您可以通過對(duì)需要測(cè)量的每個(gè)查詢使用 ExecuteNonQuery 來丟棄 .NET CLR 代碼中的查詢結(jié)果，而不使用顯式臨時(shí)表.

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.

不要更改您正在衡量的查詢.優(yōu)化器很復(fù)雜，對(duì)查詢的更改可能會(huì)對(duì)性能產(chǎn)生各種影響.

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并讓服務(wù)器為你測(cè)量時(shí)間.獲取服務(wù)器要說的內(nèi)容，對(duì)其進(jìn)行解析并以適合您的格式將其發(fā)送回.

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.

我認(rèn)為，SET STATISTICS TIME ON/OFF 的結(jié)果是最可靠和準(zhǔn)確的，并且噪音最小.

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

這篇關(guān)于如何將我選擇的所有列放入虛擬變量中?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！