Prev: Deploy a DDL trigger to prevent dropping tables for mulitple datab
Next: Deploy a DDL trigger to prevent dropping tables for mulitpledatab
From: Simon H on 5 May 2010 15:28
Good job on the book Erland - I have it sitting on my desk right now :-)
I didn't realise the royalties went to charity - even better job ;-)
Erland Sommarskog wrote:
> Motobit (info(a)pstruh.cz) writes:
>> We have two systems:
>> - new one dual procesor quad core 2,5 GHz W 2008 64, 24GB RAM + SQL
>> - old dual dual core 2Ghz 4GB RAM Win 2003 + SQL 2005
>> The new one has 3-4 times better performance, good working, almost
>> without problems. Except one simple command:
>> select keycolumn from table where text like '%keyword%'
>> from table with 1GB data, 100000 rows.
>> The query runs the same time - 40 seconds on both new and old
>> servers. And processor is 100% percent during the operation on both
>> old server (2 cores) and on new (8 cores) also. So the command takes 4
>> times more processor time on the new server and the running time is
>> the same.
> What collations and data types do you have? This operation is a lot
> faster with a binary collation, and also with an SQL collation if
> you use the varchar data type. Typicall the difference is in a factor
> of 7 to 10.
> With a non-binary Windows collation, SQL Server will have to apply the
> complex Unicode for many characters in the search string.
> Last year, I contributed to a book together with 52 other SQL Server MVPs.
> My chapter deals with this kind of search, and I suggest some methods to
> make them faster. Our royalties of this books goes to War Child
> International; we did not make this book to make us some money. More
> information here: http://www.sommarskog.se/yourownindex.html.