· 8 years ago · Apr 13, 2018, 05:40 AM
1IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters)
2BEGIN
3 UPDATE TABLE SET ColumnsX=ValuesX WHERE Where Columns=@Filters
4END
5
6IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters)
7BEGIN
8 UPDATE TABLE SET ColumnsX=ValuesX WHERE Columns=@Filters
9END
10
11SELECT whatever
12 FROM yourtable
13 WHERE EXISTS( SELECT 1/0
14 FROM someothertable
15 WHERE a_valid_clause )
16
17+-----------+----------+----------+--------+
18| # Columns | Avg Queries per sec | Winner |
19| |----------+----------| |
20| | SELECT * | SELECT 1 | |
21+-----------+----------+----------+--------+
22| 1 | 851.34 | 851.10 | * |
23| 2 | 847.39 | 848.31 | 1 |
24| 4 | 846.84 | 848.06 | 1 |
25| 8 | 844.51 | 844.63 | 1 |
26| 16 | 834.11 | 833.34 | * |
27| 32 | 806.81 | 805.79 | * |
28| 64 | 769.19 | 770.33 | 1 |
29| 128 | 703.19 | 704.29 | 1 |
30| 256 | 584.17 | 584.87 | 1 |
31| 512 | 444.64 | 444.20 | * |
32| 1024 | 260.51 | 259.76 | * |
33+-----------+----------+----------+--------+
34
35SET SHOWPLAN_TEXT ON;
36
37GO
38
39SELECT COUNT(1)
40FROM master..spt_values
41
42|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
43 |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
44 |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))
45
46DECLARE @V int
47
48WHILE (1=1)
49 SELECT @V=1 WHERE EXISTS (SELECT 1 FROM ##T) OPTION(RECOMPILE)
50
51sqlservr.exe!CMEDAccess::GetProxyBaseIntnl() - 0x1e2c79 bytes
52sqlservr.exe!CMEDProxyRelation::GetColumn() + 0x57 bytes
53sqlservr.exe!CAlgTableMetadata::LoadColumns() + 0x256 bytes
54sqlservr.exe!CAlgTableMetadata::Bind() + 0x15c bytes
55sqlservr.exe!CRelOp_Get::BindTree() + 0x98 bytes
56sqlservr.exe!COptExpr::BindTree() + 0x58 bytes
57sqlservr.exe!CRelOp_FromList::BindTree() + 0x5c bytes
58sqlservr.exe!COptExpr::BindTree() + 0x58 bytes
59sqlservr.exe!CRelOp_QuerySpec::BindTree() + 0xbe bytes
60sqlservr.exe!COptExpr::BindTree() + 0x58 bytes
61sqlservr.exe!CScaOp_Exists::BindScalarTree() + 0x72 bytes
62sqlservr.exe!CScaOpArg::BindTree() + 0x20 bytes
63sqlservr.exe!COptExpr::BindTree() + 0x58 bytes
64sqlservr.exe!CRelOp_Select::BindTree() + 0x52 bytes
65sqlservr.exe!COptExpr::BindTree() + 0x58 bytes
66sqlservr.exe!CRelOp_QuerySpec::BindTree() + 0xbe bytes
67sqlservr.exe!COptExpr::BindTree() + 0x58 bytes
68sqlservr.exe!CRelOp_SelectQuery::BindTree() + 0x48 bytes
69sqlservr.exe!COptExpr::BindTree() + 0x58 bytes
70sqlservr.exe!CRelOp_Query::FAlgebrizeQuery() + 0x1fa bytes
71sqlservr.exe!CProchdr::FNormQuery() + 0x31 bytes
72sqlservr.exe!CProchdr::FNormalizeStep() + 0x146 bytes
73sqlservr.exe!CSQLSource::FCompile() + 0x6e6 bytes
74sqlservr.exe!CSQLSource::FCompWrapper() + 0xab bytes
75sqlservr.exe!CSQLSource::Transform() + 0xdc52 bytes
76sqlservr.exe!CSQLSource::Execute() + 0x2c8 bytes
77sqlservr.exe!process_request() - 0x29e410 bytes
78sqlservr.exe!process_commands() + 0x150 bytes
79sqlservr.exe!SOS_Task::Param::Execute() + 0xda bytes
80sqlservr.exe!SOS_Scheduler::RunTask() + 0xb4 bytes
81sqlservr.exe!SOS_Scheduler::ProcessTasks() + 0x94 bytes
82sqlservr.exe!SchedulerManager::WorkerEntryPoint() + 0xe7 bytes
83sqlservr.exe!SystemThread::RunWorker() + 0x4c bytes
84sqlservr.exe!SystemThreadDispatcher::ProcessWorker() + 0x154 bytes
85sqlservr.exe!SchedulerManager::ThreadEntryPoint() + 0x137 bytes
86msvcr80.dll!_callthreadstartex() Line 348 + 0x6 bytes C
87msvcr80.dll!_threadstartex(void * ptd=0x0031d888) Line 326 + 0x5 bytes C
88kernel32.dll!_BaseThreadStart@8() + 0x37 bytes
89
90+-----------+-----------+-----------+--------+
91| # Columns | Avg Queries per sec | Winner |
92| |-----------+-----------| |
93| | SELECT * | SELECT 1 | |
94+-----------+-----------+-----------+--------+
95| 1 | 55698.26 | 56058.21 | 1 |
96| 2 | 55962.67 | 56082.81 | 1 |
97| 4 | 55972.91 | 56337.61 | 1 |
98| 8 | 56114.84 | 56217.14 | 1 |
99| 16 | 55905.96 | 56062.57 | 1 |
100| 32 | 56299.97 | 56441.43 | 1 |
101| 64 | 56337.83 | 56371.56 | 1 |
102| 128 | 55826.06 | 56004.81 | 1 |
103| 256 | 56080.07 | 55876.16 | * |
104| 512 | 55575.70 | 55801.11 | 1 |
105| 1024 | 55409.41 | 55171.57 | * |
106| TOTAL | 615183.69 | 616425.00 | |
107+-----------+-----------+-----------+--------+
108
109CREATE PROC #CompareStarVsConstant
110@NumberOfColumnsInTable INT = 1024,
111@BatchIterations INT = 10,
112@BatchTimeoutInSeconds INT = 60,
113@NumberOfStarQueries INT = 0 OUTPUT,
114@NumberOfConstantQueries INT = 0 OUTPUT
115AS
116IF(@NumberOfColumnsInTable NOT BETWEEN 1 AND 1024) OR
117 (@BatchIterations < 1) OR
118 (@BatchTimeoutInSeconds < 1)
119BEGIN
120RAISERROR('Invalid Params',16,1)
121RETURN
122END
123
124SET NOCOUNT ON;
125
126IF OBJECT_ID('tempdb..##T') IS NOT NULL
127 DROP TABLE ##T
128
129SELECT @NumberOfStarQueries = 0, @NumberOfConstantQueries=0
130
131DECLARE @table_create_sql NVARCHAR(MAX)
132
133SELECT @table_create_sql = isnull(@table_create_sql + ',','') + 'C' + LEFT(number,4) + ' INT'
134FROM master..spt_values
135WHERE type='P' AND
136 number BETWEEN 1 AND @NumberOfColumnsInTable
137
138SET @table_create_sql = 'CREATE TABLE ##T (' + @table_create_sql + ')'
139
140EXEC(@table_create_sql)
141
142
143DECLARE @BatchCounter INT = 1,
144 @CurrentBatchStarted DATETIME2,
145 @BatchTimeoutInMicroSeconds INT = @BatchTimeoutInSeconds * 1000000
146
147
148DECLARE @V int /*Holds results of execution to remove effect of results being sent back*/
149
150WHILE @BatchCounter <= @BatchIterations
151BEGIN
152
153SET @CurrentBatchStarted = SYSDATETIME()
154WHILE DATEDIFF(MICROSECOND,@CurrentBatchStarted,SYSDATETIME()) < @BatchTimeoutInMicroSeconds
155BEGIN
156SELECT @V=1 WHERE EXISTS (SELECT * FROM ##T) OPTION(RECOMPILE)
157SET @NumberOfStarQueries +=1
158END
159
160SET @CurrentBatchStarted = SYSDATETIME()
161WHILE DATEDIFF(MICROSECOND,@CurrentBatchStarted,SYSDATETIME()) < @BatchTimeoutInMicroSeconds
162BEGIN
163SELECT @V=1 WHERE EXISTS (SELECT 1 FROM ##T) OPTION(RECOMPILE)
164SET @NumberOfConstantQueries +=1
165END
166
167SET @BatchCounter +=1;
168END
169
170DROP TABLE ##T
171
172GO
173
174
175
176
177DECLARE @NumberOfStarQueries INT = 0,
178 @NumberOfConstantQueries INT = 0,
179 @BatchIterations INT = 10,
180 @BatchTimeoutInSeconds INT = 7
181
182DECLARE @TestSeconds int = 22*@BatchIterations*@BatchTimeoutInSeconds
183DECLARE @SecondsPerBatch float = @BatchIterations*@BatchTimeoutInSeconds
184
185
186RAISERROR('Beginning Test, Expected Completion in a little over %d seconds.',0,1,@TestSeconds) WITH NOWAIT
187
188DECLARE @NumberOfColumnsInTable INT = 1
189
190--Do 11 iterations checking effect of column counts from 1 to 1024
191WHILE (@NumberOfColumnsInTable < = 1024)
192 BEGIN
193 EXEC #CompareStarVsConstant
194 @NumberOfColumnsInTable,
195 @BatchIterations,
196 @BatchTimeoutInSeconds,
197 @NumberOfStarQueries OUTPUT,
198 @NumberOfConstantQueries OUTPUT
199
200 SELECT @NumberOfColumnsInTable AS [@NumberOfColumnsInTable],
201 @NumberOfStarQueries/@SecondsPerBatch AS [@NumberOfStarQueries per sec],
202 @NumberOfConstantQueries/@SecondsPerBatch AS [@NumberOfConstantQueries per sec]
203 RAISERROR('',0,1) WITH NOWAIT; /*Flush Buffer so see results sooner in SSMS*/
204 SET @NumberOfColumnsInTable += @NumberOfColumnsInTable
205 END
206
207
208
209DROP PROC #CompareStarVsConstant