SQL Server problems reading columns with a foreign key
- by illdev
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.
