sp_primary_keys_rowset loong duration

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

SQL Server Administration (2005)

sp_primary_keys_rowset loong duration

Author

Topic

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-19 : 08:44:23

Hi!

I'm using RDA (Remote Data Access) to pull 20 tables to my Pocket PC. It took quite a long time so I ran a trace to see what happened. Everything looks fine except for when it runs:

exec [mydb]..sp_primary_keys_rowset N'Person',NULL
The duration is: 18446744073!!!

A couple of more tables has this enomous duration others have about 5000 which seems more normal.

Any clues?

/Magnus

Jesus saves. But Gretzky slaps in the rebound.

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-19 : 08:56:39

I've enclosed the sql which runs before sp_primary_keys... I've tried to translate it to English. Anyone see anything strange?

SELECT	PersonID, LastName, FirstName, Mobile
FROM	Person
WHERE	PersonID IN (SELECT ContactPerson_PersonID FROM [Order] WHERE Orderbasket_OrderbasketID = 3 AND	Orderstatus_OrderstatusID BETWEEN 20 AND 50)
OR	PersonID IN (SELECT PlaceContact_PersonID FROM Place WHERE PlaceID IN (SELECT Place_PlaceID FROM [Order] WHERE Orderbasket_OrderbasketID = 270 AND Orderstatus_OrderstatusID BETWEEN 20 AND 50)) 
OR	PersonID IN (SELECT PlaceContact2_PersonID FROM Place WHERE PlaceID IN (SELECT Place_PlaceID FROM [Order] WHERE Orderbasket_OrderbasketID = 270 AND Orderstatus_OrderstatusID BETWEEN 20 AND 50))

Jesus saves. But Gretzky slaps in the rebound.

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-19 : 09:08:11

Found the answer! I needed to enclose the OR statements with two parentheses. WHERE PersonID (( and add )) at the end. Duration was now 314 :-)

Jesus saves. But Gretzky slaps in the rebound.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-19 : 09:14:01

You can also try this:

SELECT	PersonID, LastName, FirstName, Mobile
FROM	Person
WHERE	PersonID IN 
	(
	SELECT ContactPerson_PersonID FROM [Order] WHERE Orderbasket_OrderbasketID = 3 AND	Orderstatus_OrderstatusID BETWEEN 20 AND 50
	UNION ALL
	SELECT PlaceContact_PersonID FROM Place P JOIN [Order] O ON P.PlaceID = O.Place_PlaceID WHERE O.Orderbasket_OrderbasketID = 270 AND O.Orderstatus_OrderstatusID BETWEEN 20 AND 50
	UNION ALL
	SELECT PlaceContact2_PersonID FROM Place P JOIN [Order] O ON P.PlaceID = O.Place_PlaceID WHERE O.Orderbasket_OrderbasketID = 270 AND O.Orderstatus_OrderstatusID BETWEEN 20 AND 50
	)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources