Query reaches 100% cpu - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query reaches 100% cpu

Author

Topic

sensy
Starting Member

7 Posts

Posted - 2010-11-23 : 07:12:27

Hi,

This is my first post on this forum.

I have a query that runned perfectly on a sqlserver2000. When we migrated to a 2003 (on new machines) it takes 100% of cpu and times out.
This is the failing query:

insert into [Customer_BaseDiscount]
(
[Customer_BaseDiscount].[Discount],
[Customer_BaseDiscount].[DiscountCategoryId],
[Customer_BaseDiscount].[ProductGroupId],
[Customer_BaseDiscount].[BranchId]
)
select [Discount],[DiscountCategoryId], [ProductGroupId], [BranchId] from [Customer_BaseDiscount_Import] where
(cast(DiscountCategoryId as nvarchar(6)) + '|' + cast(ProductGroupId as nvarchar(6)) + '|' + cast([BranchId] as nvarchar(2))
not in (select cast(DiscountCategoryId as nvarchar(6)) + '|' + cast(ProductGroupId as nvarchar(6)) + '|' + cast([BranchId] as nvarchar(2)) from [Customer_BaseDiscount]))

Is there a new locking mechanisme in sqlserver2003 why it goes in overdrive?

thnx in advance,

Sen

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-23 : 07:24:04

There is no SQL Server 2003...

No, you're never too old to Yak'n'Roll if you're too young to die.

sensy
Starting Member

7 Posts

Posted - 2010-11-23 : 07:31:04

Ups sqlserver 2005. Sorry

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-23 : 07:35:49

Use NOT EXISTS() instead of NOT IN().
That was a great help for me in the past...

But - is it required to concatenate all that columns to do a compare?
Isn't there a keyvalue that can be used?

No, you're never too old to Yak'n'Roll if you're too young to die.

sensy
Starting Member

7 Posts

Posted - 2010-11-23 : 07:53:23

I have 3 keys: DiscountCategoryId, ProductGroupId, BranchId. These 3 keys define the discount you'll get. Is there another way to handle 3 keys in an 'in clause'?
I don't see how you can rewrite my query without 'in clause'.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-23 : 07:55:14

If my head is in the right position this should do the same thing but without all the converting and stuff (no guarantees though!!):


insert into [Customer_BaseDiscount]
(
[Customer_BaseDiscount].[Discount],
[Customer_BaseDiscount].[DiscountCategoryId],
[Customer_BaseDiscount].[ProductGroupId],
[Customer_BaseDiscount].[BranchId]
) 
select [Discount],[DiscountCategoryId], [ProductGroupId], [BranchId] 
from [Customer_BaseDiscount_Import] a
	left outer join [Customer_BaseDiscount] b
		ON a.DiscountCategoryId = b.DiscountCategoryId
			AND a.ProductGroupId = b.ProductGroupId
			AND a.BranchId = b.BranchId
where b.DiscountCategoryId IS NULL

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-23 : 07:58:42

[code]
insert into [Customer_BaseDiscount]
(
[Customer_BaseDiscount].[Discount],
[Customer_BaseDiscount].[DiscountCategoryId],
[Customer_BaseDiscount].[ProductGroupId],
[Customer_BaseDiscount].[BranchId]
)
select
[Discount],
[DiscountCategoryId],
[ProductGroupId],
[BranchId]
from [Customer_BaseDiscount_Import] cbi
where NOT EXISTS(select *
from [Customer_BaseDiscount] cbd
where cbd.DiscountCategoryId = cbi.DiscountCategoryId
and cbd.ProductGroupId = cbi.ProductGroupId
and cbd.BranchId = cbi.BranchId)

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

sensy
Starting Member

7 Posts

Posted - 2010-11-23 : 07:59:56

Lumbago,

thnx dude... that query is indeed way more performant. How could I've missed that one :) I must have been sleeping.

sensy
Starting Member

7 Posts

Posted - 2010-11-23 : 08:19:01

When running the new query the cpu stays below 30%
I still find it bizar how sqlserver 2000 never had issues with that query, while 2005 does.
Anyhow thnx alot for the swift responses

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-23 : 08:43:32

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

sensy
Starting Member

7 Posts

Posted - 2010-11-24 : 04:22:31

Thank you webfred for that clarifying link! Was very helpfull.

We are still researching why these messed up queries runned within a timely manner on sqlserver 2000 and not in our 2005. Now we investigate if the problem lies in a sqlserver 2005 x86 running on a windows 2008 64bit. In any case I'll update my queries folling your advice :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-24 : 04:34:22

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

sensy
Starting Member

7 Posts

Posted - 2010-11-25 : 04:20:56

FYI

The problems we had were underlying caused by the fact we had a sqlserver x86 edition running on a 64bit OS. We moved to a sqlserver 2008 64 and all runned smoothly once again.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-25 : 04:33:33

Wise choice! x86 is for old people

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-25 : 08:39:29

quote:
Originally posted by Lumbago

Wise choice! x86 is for old people

thx

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-11-25 : 12:00:23

Did you rebuild indexes and update statistics after upgrading?

This link has steps for upgrade (its for SQl2008 but pretty much all applies to SQL2005 also)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources