SQL server 2005 multicore problem with 'where column like' sql command

 Hi.

  We have two systems:
- new one dual procesor quad core 2,5 GHz W 2008 64, 24GB RAM  + SQL
2005
- 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.

  I search MS site for the problem, nothing. Some suggestions?

  Thanks

  Antonin
0
Motobit
4/29/2010 8:25:28 PM
sqlserver.server 1327 articles. 0 followers. Follow

4 Replies
727 Views

Similar Articles

[PageSpeed] 50

Motobit (info@pstruh.cz) writes:
>   We have two systems:
> - new one dual procesor quad core 2,5 GHz W 2008 64, 24GB RAM  + SQL
> 2005
> - 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.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
4/29/2010 9:58:55 PM
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 ;-)

S

Erland Sommarskog wrote:
> Motobit (info@pstruh.cz) writes:
>>   We have two systems:
>> - new one dual procesor quad core 2,5 GHz W 2008 64, 24GB RAM  + SQL
>> 2005
>> - 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.
> 
0
Simon
5/5/2010 7:28:11 PM
> 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.

  The data type of the column is text and collation is Czech_CI_AS.

  Main problem is not with speed (we can rewrite the query to freetext
search). The problem is that the 'like'  query has the same duration
on old server as on 4 times better new server - so it takes 4times
more processor time on the new server. The collation is the same on
both old and new servers.

  Antonin
0
Motobit
5/5/2010 8:56:21 PM
Antonin,

Are you using the same edition of SQL Server 2005 (for example both
"Standard")? Do you have the same patches / service packs?

Is the query plan the same?

Is column "text" of data type text? If so, are you using the same "text
in row" setting? You can check with:

  SELECT large_value_types_out_of_row FROM sys.tables WHERE
name='my_table'

Is column "text" indexed? If so, is the state of "String Index" the
same? You can check with:

  DBCC SHOW_STATISTICS ('my_table','index_of_text') WITH STAT_HEADER


-- 
Gert-Jan


Motobit wrote:
> 
>  Hi.
> 
>   We have two systems:
> - new one dual procesor quad core 2,5 GHz W 2008 64, 24GB RAM  + SQL
> 2005
> - 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.
> 
>   I search MS site for the problem, nothing. Some suggestions?
> 
>   Thanks
> 
>   Antonin
0
Gert
5/8/2010 6:34:45 PM
Reply:

Similar Artilces:

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

Problem with CFTPConnection
Hi I've an application that connects to an FTP Server and uploads a big directory (size over 500 MB). Now what happens is that after sometime the application stops uploading files and stays idle. The FTP server connection timeout value is set to 2 minutes. So the server seeing the connection idle for two seconds, disconnect the session and no more file is uploaded. On debugging I found out that after some time CFTPConnection.PutFile() method hangs on some random file and stays like that for 5 minutes (default send timeout value of CFTPSession). During this time the server connection timeo...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

Excel 2007 Run Remote/Server/Network/Shared Macro From Custom Butt
In Excel 2003 this was straightforward: add the add-in regarless of location and assign it to a button. I have tried several things in 2007 without success. I've followed the usual links (including to Rondebruin and the others!), local add-ins running remote add-ins, abused my keyboard, kicked some furniture, etc... Still I haven't found a solution. I'm sure the solution isn't as easy as in 2003 but if anyone knows it I would appreciate some advice/directions. Thanks, SQLServant ...

100% stacked column chart shows wrong information
I wonder if someone can help me. I have created a table that tracks the profitability of a particular project and I’m using the table as a source for a 100% Stacked Column graph. The source data for the graph is in three rows: • B21:M21 - contain the names of the months from Nov – Oct • B22:M22 - money taken in • B24:M24 - profit B23:M23 contains the money paid to supplier each month. I need this in the table but I don’t need to show it in the graph. I just want to show profit as a proportion of what we bill to customers on that project. My problem is that the graph seems to show in...

Monitor Server in 2003?
Hi all, After installing Exchange 2003 SP1, I'm having some issues monitoring exchange server via scripting. 1. I can no longer get any instances from "Win32_PerfRawData_ESE_DatabaseInstances" via scripting. 2. The property "LogGenerationCheckPointDepth" does not even appear as a property of "Win32_PerfRawData_ESE_DatabaseInstances". (I checked this with WMI CIM studio) I can still get the LogGenerationCheckPointDepth from performance monitor. The following code verifies what I've seen in WMI CIM studio: set RawProc = GetObject("winmgmts:\\r...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

CRM Outlook Client Configuration Wizard Problem
CRM 4.0 - Microsoft Outlook Client Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client ins...

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Outlook Mail via a PROXY SERVER?
If I am using a shared Internet service on a second computer using a PROXY server on the host (first) computer. I can access the net using the PROXY settings on the Internet explorer. However, how do I download mail with OUTLOOK 2000? The proxy server is CCPROXY from YoungZSoft (this runs on XP) and it has the MAIL Settings enabled but the difficulty is what to do on the Second computer? Thanks, -- John jrg_REM_SPAM@bigpond.net.au That would be determined by the proxy manufacturer - check with their support. JM7 wrote: > If I am using a shared Internet service on a second computer ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

Sorting on Column headers
I have a spreedsheet that someone else created and I need to take the Column headers, which are in date form, and rearrange them in chronological order. Can this be done without cutting and pasting? I really need to save time with this project if possible. Thanks!:) --- Message posted from http://www.ExcelForum.com/ > I have a spreedsheet that someone else created and I need to take the > Column headers, which are in date form, and rearrange them in > chronological order. Can this be done without cutting and pasting? I > really need to save time with this project if possib...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Multivalue with Null value SSRS 2005
I have a query to populate a multivalue parameter: SELECT distinct cast(AGRPYear.value as varchar(4)) + AGRPMonth.value 'ReportDate' FROM TPROJECT AS TPROJECT One of the values that is returned from this query is NULL. However, when I run the report, the NULL value does not show in the dropdown. I've also tried adding "select NULL as 'ReportDate' union" to the above query and the null value still doesn't show. As a result some of the records in my database have a null value for this field, they will never show up on my report. Any id...

SQL Server Connection Failed
Hi everybody, this is my scenario: 1. Windows Server 2008 2. WSS 3.0 + MOSS 2007 3. SQL Server 2008. i'm having this problem: "Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: 72.55.191.137]". in this momment i can't see any application from sharepoint. somebody knows how can i do? i don't change the sa password. i don't change anything in the sharepoint configuration. since two days ago i'm having this problem. pls, is very urgent, because i have to deliver this server the next monday to the...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

Like a pivot table
Hello every body I'm first time requesting in this group, so I opologize in advance for any mistakes or something annoying I repeat what I have sent before 10 min because I see it unclear when it goes to news group If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is exist name age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I wa...

Changing ip address of exchange server #2
Hi, I have a back-end server and a smtp server in DMZ. I want to change ip address of back-end server. are there any issues? all incoming and outgoing emails are going via smart host. Hi, No issues at all as long as you remember to change all the references to this server in your firewall, SMTP scanner etc. Leif "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:FE5927A1-D20D-4C6B-991F-2E1EFD19434D@microsoft.com... > Hi, > > I have a back-end server and a smtp server in DMZ. > > I want to change ip address of back-end server. are ...

Backing up databases on remote SQL
Hi, I recently followed one of the MS articles on how to move GP 9 sql database to a different server. Everything went well except when trying to run the backup option from the GP9 server. I currently have a server with GP9 installed and another server with SQL and the GP databases. When I try to run the backup from the backup option on the GP server it says " You must be on the server machine to perform this task". presume this is because the SQL is no longer on that server. The SQL server has a backup routine of its own, but I would like our finance users to have the ability...