SQL Server problems reading columns with a foreign key

Posted by illdev on Stack Overflow See other posts from Stack Overflow or by illdev
Published on 2010-05-05T15:34:30Z Indexed on 2010/05/05 16:28 UTC
Read the original article Hit count: 336

Filed under:
|
|
|

I have a weird situation, where simple queries seem to never finish

for instance

SELECT top 100 ArticleID FROM Article WHERE  ProductGroupID=379114

returns immediately

SELECT top 1000 ArticleID FROM Article WHERE  ProductGroupID=379114

never returns

SELECT ArticleID FROM Article WHERE  ProductGroupID=379114

never returns

SELECT top 1000 ArticleID FROM Article

returns immediately

By 'returning' I mean 'in query analyzer the green check mark appears and it says "Query executed successfully"'.

I sometimes get the rows painted to the grid in qa, but still the query goes on waiting for my client to time out - 'sometimes':

SELECT     
   ProductGroupID AS Product23_1_, 
   ArticleID AS ArticleID1_, 
   ArticleID AS ArticleID18_0_, 
   Inventory_Name AS Inventory3_18_0_, 
   Inventory_UnitOfMeasure AS Inventory4_18_0_, 
   BusinessKey AS Business5_18_0_, 
   Name AS Name18_0_, 
   ServesPeople AS ServesPe7_18_0_, 
   InStock AS InStock18_0_, 
   Description AS Descript9_18_0_, 
   Description2 AS Descrip10_18_0_, 
   TechnicalData AS Technic11_18_0_, 
   IsDiscontinued AS IsDisco12_18_0_, 
   Release AS Release18_0_, 
   Classifications AS Classif14_18_0_, 
   DistributorName AS Distrib15_18_0_, 
   DistributorProductCode AS Distrib16_18_0_, 
   Options AS Options18_0_, 
   IsPromoted AS IsPromoted18_0_, 
   IsBulkyFreight AS IsBulky19_18_0_, 
   IsBackOrderOnly AS IsBackO20_18_0_, 
   Price AS Price18_0_, 
   Weight AS Weight18_0_, 
   ProductGroupID AS Product23_18_0_, 
   ConversationID AS Convers24_18_0_, 
   DistributorID AS Distrib25_18_0_, 
   type AS Type18_0_
FROM         
   Article AS articles0_
WHERE     
   (IsDiscontinued = '0') AND (ProductGroupID = 379121)

shows this behavior.

I have no idea what is going on. Probably select is broken ;)

I got a foreign key on ProductGroups

ALTER TABLE [dbo].[Article] WITH CHECK ADD CONSTRAINT [FK_ProductGroup_Articles] 
FOREIGN KEY([ProductGroupID]) 
REFERENCES [dbo].[ProductGroup] ([ProductGroupID]) 
GO 
ALTER TABLE [dbo].[Article] CHECK CONSTRAINT [FK_ProductGroup_Articles] 

there are some 6000 rows and IsDiscontinued is a bit, not null, but leaving this condition out does not change the outcome.

Anyone can tell me how to handle such a situation? More info, anyone?

Additional Info: this does not seem to be restricted to this Foreign Key, but all/some referencing this entity.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server