<?xml version='1.0' encoding='UTF-8'?><rss xmlns:atom="http://www.w3.org/2005/Atom" xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/" xmlns:blogger="http://schemas.google.com/blogger/2008" xmlns:georss="http://www.georss.org/georss" xmlns:gd="http://schemas.google.com/g/2005" xmlns:thr="http://purl.org/syndication/thread/1.0" version="2.0"><channel><atom:id>tag:blogger.com,1999:blog-4983091686029411693</atom:id><lastBuildDate>Wed, 11 Sep 2024 22:49:32 +0000</lastBuildDate><title>Share the experience</title><description></description><link>http://spinifex3.blogspot.com/</link><managingEditor>noreply@blogger.com (Anonymous)</managingEditor><generator>Blogger</generator><openSearch:totalResults>2</openSearch:totalResults><openSearch:startIndex>1</openSearch:startIndex><openSearch:itemsPerPage>25</openSearch:itemsPerPage><item><guid isPermaLink="false">tag:blogger.com,1999:blog-4983091686029411693.post-4594549386780791896</guid><pubDate>Mon, 05 Nov 2012 13:27:00 +0000</pubDate><atom:updated>2012-11-05T05:38:54.272-08:00</atom:updated><title>[WPF bug] Сочетание свойств DataGrid EnableColumnVirtualization=”True” и AutoGenerateColumns=”False” приводит к зависанию.</title><description>&lt;div style=&quot;text-align: left&quot; dir=&quot;ltr&quot; trbidi=&quot;on&quot;&gt; &lt;p&gt;Достался мне как-то баг в &lt;span style=&quot;mso-ansi-language: en-us&quot; lang=&quot;EN-US&quot;&gt;пользовательском интерфейсе&lt;/span&gt;, исправить который я намеревался минут за 15. Но не тут то было… &lt;span style=&quot;mso-spacerun: yes&quot;&gt;&amp;nbsp;&lt;/span&gt;Описать баг очень просто: есть окно со списком (&lt;span style=&quot;mso-ansi-language: en-us&quot; lang=&quot;EN-US&quot;&gt;DataGrid&lt;/span&gt;) при горизонтальном скроллировании приложение зависает. &lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;br&gt; &lt;p&gt;Приложение грузит процессор? – это хорошо, есть повод запустить профайлер и посмотреть, что именно происходит в момент скроллинга. &lt;span style=&quot;mso-spacerun: yes&quot;&gt;&amp;nbsp;&lt;/span&gt;Неожиданно &lt;span style=&quot;mso-spacerun: yes&quot;&gt;&amp;nbsp;&lt;/span&gt;профайлер указал на сам &lt;span style=&quot;mso-ansi-language: en-us&quot; lang=&quot;EN-US&quot;&gt;DataGrid&lt;/span&gt;&lt;span lang=&quot;EN-US&quot;&gt; &lt;/span&gt;и осталось только выяснить какие условия заставляют &lt;span style=&quot;mso-ansi-language: en-us&quot; lang=&quot;EN-US&quot;&gt;WPF&lt;/span&gt;&lt;span lang=&quot;EN-US&quot;&gt; &lt;/span&gt;&lt;span style=&quot;mso-ansi-language: en-us&quot; lang=&quot;EN-US&quot;&gt;DataGrid&lt;/span&gt;&lt;span lang=&quot;EN-US&quot;&gt; &lt;/span&gt;так «виснуть». После продолжительного отбрасывания и комментирования лишнего кода, не влияющего на исследуемое поведение, остался необходимый минимум: &lt;span style=&quot;mso-spacerun: yes&quot;&gt;&amp;nbsp;&lt;/span&gt;&lt;/p&gt;&lt;span style=&quot;font-family: consolas; color: blue; font-size: x-small&quot;&gt;&lt;span style=&quot;font-family: consolas; color: blue; font-size: x-small&quot;&gt;&lt;span style=&quot;font-family: consolas; color: blue; font-size: x-small&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt; &lt;p&gt;&amp;lt;&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;font color=&quot;#a31515&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#a31515&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#a31515&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;DataGrid&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;font color=&quot;#ff0000&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#ff0000&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#ff0000&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt; EnableColumnVirtualization&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;=&quot;True&quot;&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;font color=&quot;#ff0000&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#ff0000&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#ff0000&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt; AutoGenerateColumns&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;=&quot;False&quot;&amp;gt; &lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&amp;lt;/&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;font color=&quot;#a31515&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#a31515&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#a31515&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;DataGrid&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#0000ff&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&amp;gt;&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;font color=&quot;#a31515&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#a31515&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt;&lt;font color=&quot;#a31515&quot; size=&quot;2&quot; face=&quot;Consolas&quot;&gt; &lt;/p&gt;&lt;/font&gt;&lt;/font&gt;&lt;/font&gt;&lt;/span&gt;&lt;/span&gt;&lt;/span&gt; &lt;div style=&quot;margin: 0cm 0cm 10pt&quot; class=&quot;MsoNormal&quot;&gt;&lt;span style=&quot;font-family: calibri; color: black; font-size: small&quot;&gt;&lt;/span&gt;&lt;/div&gt; &lt;p&gt;Оказалось, что я не один столкнулся с таким поведением и в Microsoft connect заведен баг: &lt;a href=&quot;http://connect.microsoft.com/VisualStudio/feedback/details/742728/wpf-datagrid-hangs-while-scrolling-horizontally&quot;&gt;&lt;u&gt;http://connect.microsoft.com/VisualStudio/feedback/details/742728/wpf-datagrid-hangs-while-scrolling-horizontally&lt;/u&gt;&lt;/a&gt;, который впрочем, сейчас считается низкоприоритетным ... &lt;/p&gt; &lt;p&gt;В ссылке выше есть минимальный проект на Visual Basic, воспроизводящий баг, а также workaround который в моей ситуации и на моей машине никак не помог. В качестве совета могу сказать, что если у вас используется виртуализация столбцов подумайте, так ли она вам необходима.&lt;/p&gt;&lt;/div&gt;  </description><link>http://spinifex3.blogspot.com/2012/11/wpf-bug-datagrid-enablecolumnvirtualiza.html</link><author>noreply@blogger.com (Anonymous)</author><thr:total>0</thr:total></item><item><guid isPermaLink="false">tag:blogger.com,1999:blog-4983091686029411693.post-861761913460113959</guid><pubDate>Tue, 09 Oct 2012 03:21:00 +0000</pubDate><atom:updated>2012-10-10T22:24:11.857-07:00</atom:updated><title>Производительность запросов с табличными переменными</title><description>&lt;div dir=&quot;ltr&quot; style=&quot;text-align: left;&quot; trbidi=&quot;on&quot;&gt;
&lt;div style=&quot;text-align: left;&quot;&gt;
&lt;a href=&quot;http://www.blogger.com/blogger.g?blogID=4983091686029411693&quot; imageanchor=&quot;1&quot; style=&quot;clear: right; float: right; margin-bottom: 1em; margin-left: 1em;&quot;&gt;&lt;/a&gt;&lt;/div&gt;
&lt;div style=&quot;text-align: left;&quot;&gt;
Табличные переменные хранятся в оперативной памяти SQL Server, и только большие объемы данных могут вынудить сервер задействовать жесткий диск посредством tempdb.&amp;nbsp; Известно, что оперативная память это быстро и хорошо. Но не все так просто, иногда можно получить очень медленный запрос в неожиданном месте… В каких случаях? Рассмотрим это более предметно, с использованием примера. &lt;/div&gt;
&lt;div style=&quot;text-align: left;&quot;&gt;
&lt;br /&gt;
&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;br /&gt;
Во-первых, нам понадобится постоянная таблица, содержащая большое количество строк:
&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;set statistics &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;profile &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;off&lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;go &lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;use &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;tempdb&lt;/span&gt;&lt;/code&gt;&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;&amp;nbsp;&lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;go&lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;if &lt;/span&gt;&lt;span style=&quot;color: magenta;&quot;&gt;object_id &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;(&lt;/span&gt;&lt;span style=&quot;color: red;&quot;&gt;&#39;PermanentTable&#39;&lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;) &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;is &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;not null&lt;br /&gt;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;drop table &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;PermanentTable&lt;/span&gt;&lt;/code&gt;&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;&amp;nbsp;&lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;go&lt;/span&gt;&lt;/code&gt;&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt; &lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;create table &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;PermanentTable &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;(&lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;id &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;int&lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;, &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;name &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;nvarchar&lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;(&lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;1000&lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;))&lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;go&lt;/span&gt;&lt;/code&gt;&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: green;&quot;&gt; &lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: green;&quot;&gt;--вставка 100,000 строк во временную таблицу&lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;div class=&quot;separator&quot; style=&quot;clear: both; text-align: center;&quot;&gt;
&lt;a href=&quot;http://www.blogger.com/blogger.g?blogID=4983091686029411693&quot; imageanchor=&quot;1&quot; style=&quot;margin-left: 1em; margin-right: 1em;&quot;&gt;&lt;/a&gt;&lt;/div&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;set nocount on&lt;br /&gt;begin tran&lt;br /&gt;&amp;nbsp;&amp;nbsp; declare &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;int&lt;br /&gt;&amp;nbsp;&amp;nbsp; set &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;= &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;0&lt;br /&gt;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;while &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;&amp;lt; &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;100000&lt;br /&gt;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;begin&lt;br /&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; insert into &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;PermanentTable &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;values &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;(&lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i&lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;, &lt;/span&gt;&lt;span style=&quot;color: red;&quot;&gt;&#39;something&#39;&lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;)&lt;br /&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;set &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;= &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;+ &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;1&lt;br /&gt;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;end&lt;br /&gt;commit tran&lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;go&lt;/span&gt;&lt;/code&gt;&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt; &lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;update statistics &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;PermanentTable &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;with &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;fullscan&lt;/span&gt;&lt;/code&gt;&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;&amp;nbsp;&lt;/span&gt;&lt;/code&gt;&lt;br /&gt;
&lt;code style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;go&lt;/span&gt;&lt;/code&gt;&lt;/div&gt;
&lt;div style=&quot;text-align: left;&quot;&gt;
Во-вторых, создадим табличную переменную и заполним ее тестовыми данными:&lt;br /&gt;
&lt;div style=&quot;text-align: left;&quot;&gt;
&lt;br /&gt;
&lt;pre style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;set nocount on&lt;/span&gt;&lt;/pre&gt;
&lt;pre style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;go &lt;/span&gt;&lt;/pre&gt;
&lt;pre style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;declare &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@VariableTable &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;table &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;(&lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;id &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;int&lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;)&lt;/span&gt;&lt;/pre&gt;
&lt;pre style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: grey;&quot;&gt;&amp;nbsp;&lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;begin tran
&amp;nbsp;&amp;nbsp; declare &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;int
&amp;nbsp;&amp;nbsp; set &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;= &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;0
&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;while &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;&amp;lt; &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;100
&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;begin
&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; insert into &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@VariableTable &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;values &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;(&lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i&lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;)
&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;set &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;= &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@i &lt;/span&gt;&lt;span style=&quot;color: grey;&quot;&gt;+ &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;1
&amp;nbsp;&amp;nbsp; &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;end
commit tran&lt;/span&gt;&lt;/pre&gt;
&lt;br /&gt;
Теперь сделаем обычных join между таблицей и табличной переменной:&lt;br /&gt;
&lt;br /&gt;
&lt;pre style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;set statistics &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;profile &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;on
set statistics &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;time &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;on
&amp;nbsp;&amp;nbsp; select &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;pt.id &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;from &lt;/span&gt;&lt;span style=&quot;color: #434343;&quot;&gt;@VariableTable &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;vt
&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;inner join &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;PermanentTable pt &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;on &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;pt.id&lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;=&lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;vt.id&lt;/span&gt;&lt;/pre&gt;
&lt;pre style=&quot;font-size: 12px;&quot;&gt;&lt;span style=&quot;color: blue;&quot;&gt;set statistics &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;profile &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;off
set statistics &lt;/span&gt;&lt;span style=&quot;color: black;&quot;&gt;time &lt;/span&gt;&lt;span style=&quot;color: blue;&quot;&gt;off
&lt;/span&gt;&lt;/pre&gt;
&lt;br /&gt;
В итоге этот запрос выполнялся около 3-х с половиной секунд на моей машине:
&lt;br /&gt;
&lt;br /&gt;
&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9-3GRn_pXFJIxeWFKn1uN35aSh8UPVRNH29R43dDnFSzpiBBoBq8rPVf-74aTSfzKVnEHkkMXPKXTCNce52A2rU6dFUMf4VMDSB38doOZxkw7aevPg1g6k6y754agWJ5f-k5vGYGcrgY/s1600/nestedLoopTime.png&quot;&gt;&lt;img border=&quot;0&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9-3GRn_pXFJIxeWFKn1uN35aSh8UPVRNH29R43dDnFSzpiBBoBq8rPVf-74aTSfzKVnEHkkMXPKXTCNce52A2rU6dFUMf4VMDSB38doOZxkw7aevPg1g6k6y754agWJ5f-k5vGYGcrgY/s1600/nestedLoopTime.png&quot; /&gt;&lt;/a&gt;&lt;br /&gt;
Посмотрим на план запроса, который даст нам все объяснения:
&lt;br /&gt;
&lt;br /&gt;
&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJR8gh0RWnrHAiEmxAqdyTqS2_Qr3PjMGdDXxvWOU1EG_qCbjTsVVVbWINhKz4bHMYm1RIfzIx6d4KXY-JEL106T7y9F8yOzHNtAU7mimz-aJp26hGf6qzlOVHP2CLks_cNaJXaZWC5ws/s1600/nestedLoog.png&quot;&gt;&lt;img border=&quot;0&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJR8gh0RWnrHAiEmxAqdyTqS2_Qr3PjMGdDXxvWOU1EG_qCbjTsVVVbWINhKz4bHMYm1RIfzIx6d4KXY-JEL106T7y9F8yOzHNtAU7mimz-aJp26hGf6qzlOVHP2CLks_cNaJXaZWC5ws/s1600/nestedLoog.png&quot; /&gt;&amp;nbsp;&lt;/a&gt;
&lt;br /&gt;
&lt;br /&gt;
Из плана видно, что Sql Server предположил, что в табличной переменной находится одна запись. Однако в действительности там находится 100 записей, т.е. при осуществлении join сервер в цикле 100 раз пробежал по таблице PermanentTable, что и послужило причиной его выполнения за 3,5 секунды.&lt;br /&gt;
&lt;br /&gt;
Избежать этого можно добавив либо хинты (option (recompile) или option (hash join)) к запросу, либо используя временную таблицу вместо табличной переменной. Ниже показаны план и время выполнения запроса с хином option (recompile).&lt;br /&gt;
&lt;br /&gt;
&lt;a href=&quot;http://www.blogger.com/blogger.g?blogID=4983091686029411693&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;/a&gt;&lt;br /&gt;
&lt;div class=&quot;separator&quot; style=&quot;clear: both; text-align: center;&quot;&gt;
&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzr7lWuBZmELfPmIyoWLAKH6oqiSiFJZexWDDQjCqe5Ky93ofgg-io7R8XevQ2O7WdTR-Ieq6oWUXtJ1-kKPHWa2zGT6d6GPALQwhHpE1FiDvlt7GGO_lWlqEsrIvoqIijBCoCY8kJG7Y/s1600/Normal.png&quot; imageanchor=&quot;1&quot; style=&quot;clear: left; float: left; margin-bottom: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzr7lWuBZmELfPmIyoWLAKH6oqiSiFJZexWDDQjCqe5Ky93ofgg-io7R8XevQ2O7WdTR-Ieq6oWUXtJ1-kKPHWa2zGT6d6GPALQwhHpE1FiDvlt7GGO_lWlqEsrIvoqIijBCoCY8kJG7Y/s1600/Normal.png&quot; /&gt;&lt;/a&gt;&lt;/div&gt;
&lt;br /&gt;
&lt;br /&gt;
&lt;a href=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9EOFXTbJdWX2SL1rS3H_ymAXbI8N0WQErTCyaRaDtCZ68A8S1cnA5vSuEnM7VArBLqGujjMhDgUxIvIyJiG5-7wgYg9YHM_ug_HmDouDXAiFX9Df3sRR8ZwvlwDdZ37dS_afK4CLh_xw/s1600/NormalTime.png&quot; imageanchor=&quot;1&quot; style=&quot;margin-left: 1em; margin-right: 1em;&quot;&gt;&lt;img border=&quot;0&quot; src=&quot;https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9EOFXTbJdWX2SL1rS3H_ymAXbI8N0WQErTCyaRaDtCZ68A8S1cnA5vSuEnM7VArBLqGujjMhDgUxIvIyJiG5-7wgYg9YHM_ug_HmDouDXAiFX9Df3sRR8ZwvlwDdZ37dS_afK4CLh_xw/s1600/NormalTime.png&quot; /&gt;&lt;/a&gt; &lt;br /&gt;
В любом случае, имея дело с табличными переменными нужно быть начеку.&lt;br /&gt;
&lt;br /&gt;&lt;/div&gt;
&lt;/div&gt;
&lt;/div&gt;
</description><link>http://spinifex3.blogspot.com/2012/10/blog-post.html</link><author>noreply@blogger.com (Anonymous)</author><media:thumbnail xmlns:media="http://search.yahoo.com/mrss/" url="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9-3GRn_pXFJIxeWFKn1uN35aSh8UPVRNH29R43dDnFSzpiBBoBq8rPVf-74aTSfzKVnEHkkMXPKXTCNce52A2rU6dFUMf4VMDSB38doOZxkw7aevPg1g6k6y754agWJ5f-k5vGYGcrgY/s72-c/nestedLoopTime.png" height="72" width="72"/><thr:total>0</thr:total></item></channel></rss>