TroyK:Hi Tal;
Interesting experiment, but I think the effect you're seeing is more a function of the number of connectives you have in the predicate than the actual length of the query string in bytes.
Try rerunning your tests, but instead of increasing the query size by adding "OR"s, make your predicate like this:
SELECT c1
FROM t1000
WHERE c1 = 5555
OR
'<ABCD Short String>'
=
'<WXYZ Short String>'
Then, vary the length of your query by increasing the length of the two strings after the OR, keeping them distinct so that they don't inform the final result.
TroyK
Hello Troy,
First, Yes you are correct,
Second , as I wrote in my article, this is a true story – I found
myself in a design review meeting when the question popped up: "What
is the maximum text length of queries should we aim for?". You can assume
that in the practical world where an automatic DAL (Data Access Layer) writes
your query for you the lengthy query are results of a lot of conditions (poor design) in the criteria clause and not one that is
actually long.
Anyway, just to prove your point for you I wrote the following
script that created the test you asked for:
use
otal_long_text_queries
go
drop table t1001
go
create table t1001
(
c1 int not null,
c2 varchar(max)
)
go
alter table t1001 add constraint t1001_pk primary
key (c1)
go
set nocount on
declare @i as int
declare @txt as varchar(max)
declare @sql_stmt as varchar(max)
set @i = 0
set @txt = ''
while @i<1000
begin
set @i = @i + 1
set @txt = @txt + '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'
end
insert into t1001 (c1, c2) values (1, @txt)
set @sql_stmt = 'select c1 from t1001 where
c2 = '''+@txt+''''
execute
spWriteStringToFile @sql_stmt, 'c:\temp\', 'query.sql'
set nocount off
go
select len(c2)/1024 KB from t1001
go
Then I ran the script via:
sqlcmd -S <server> -i c:\temp\query.sql -o c:\temp\query.txt
The result was less than 1 second of running for more 2000KB query
file. So yes you are correct.
In case you are interested with actual results flies, drop me an
e-mail.
--Tal Olier (tal.olier@gmail.com).
