Strange SQL Query behaviour 1

[ColinM]

I have the following query:

use northwind
select left(shipname, (select max(ProductID) from [order details] where OrderID = O.OrderID)) as test
FROM Orders O

but DO NOT RUN on anything but a test server as it will max out the processing power, and I cant even seem to cancel the query. I have to end the sqlservr process and restart the service.

I also cannot get an estimated execution plan.
I ran something similar to this overnight and it was still using all process power over 16 hours later.

however if I take out the left function:
select O.OrderID, (select max(ProductID) from [order details] where OrderID = O.OrderID) as test
FROM Orders O

the query runs instantly.

Whats going on?????
Anybody know a way round this problem?

 

[ColinM]

Hmmm, if I use this:

use northwind
select O.OrderID, substring(shipname,1,(select max(ProductID) from [order details] where OrderID = O.OrderID)) as test
FROM Orders O

it works fine.
Is this a bug with the left function?

p.s. Does anybody know if it is possible to limit a query so as not to use all the available processing power.
As during intensive query, nobody else can logon to SQL Server, it just times out.

 

[foxdev]

It's not a bug in the LEFT function, but rather I don't think you are using it as intended.

Your statement:

[tt]select left(shipname, (select max(ProductID) from [order details] where OrderID = O.OrderID)) [/tt]

Is effectively saying: take the left 'X' characters of ShipName, with 'X' being the maximum ProductID where blah blah blah. It makes no sense (you wouldn't take the leftmost 391 characters of ShipName because 391 was the highest ProductID, would you?)

You probably meant something like:

[tt]select left(shipname, 1), max(ProductID) from [order details] where OrderID = O.OrderID)) [/tt]


Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[ColinM]

That northwind example probably wasn't a good example to use.

What I am trying to do is use a table to lookup up a value on where to split a varchar field.
Heres a better one (hopefully!)

USE TEST
CREATE TABLE SplitTbl
(
ID INT IDENTITY(1,1),
SPLIT INT
)

CREATE TABLE WordTbl
(
Words VARCHAR(20),
SplitID INT
)

INSERT SplitTbl VALUES (5)
INSERT SplitTbl VALUES (6)
INSERT SplitTbl VALUES (7)

INSERT WordTbl VALUES ("To be or not to be",1)
INSERT WordTbl VALUES ("That is the question",2)
INSERT WordTbl VALUES ("he proclaimed",3)

SELECT LEFT(Words, (SELECT Split FROM SplitTbl WHERE ID = W.SplitID))
FROM WordTbl W

I still get the same effect of maxing out the processing power to the extent that sqlserver won't even allow anyone to logon.

 

[tcorrigan]

As a suggestion try this:

Select
left(words, split)
from
WordTbl W join SplitTbl S on W.SplitID = S.ID

I haven't really tried this but I have come across situations where SQL Server can't handle a query written as a correlated subquery but can do it when written as a join.

 

[ColinM]

Yeah, that works too. Not sure why it doesn't like the subquery with the left function, but works okay with the substring function.

Hey, but at least it works!