Select statetment takes forever to return data

Question

Post reply

Select statetment takes forever to return data

bladerunner148

SSCommitted

Points: 1824
More actions
April 13, 2009 at 8:31 am

#130558

There is a table called Plans where all the plans are stored for an organization. This table gets populated based on the web page in visual studio. Recently, we are having a problem. When I write a query SELECT * FROM Plans WHERE OrganizationId = 20, the query keeps running for a long time. It doesn't stop. Though there are no plans for this organization, it takes a long time to run. I then have to cancel the query and start it over..same result. This happens when a user gets an error when they fail to submit a plan. The reason they get an error is we have a PlanNameId as a Nullable field on Plans table. When they don't select plannames from the webform, right now it stores "-1" to the PlanNameId column, and this is where they get the error. But when I just write the above query to select plans, the query keeps running. What should I do to fix this? Is something going on the background of the sql server...I tried closing the sql server management studio and re open the studio..same result. This is going on with just this table. Others are fine.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Laerte Poltronieri Junior-367636 Hall of Fame Points: 3314 More actions · Answer 1

Are you try update statistics for this table ?

$hell your Experience !!![/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please post the table design, index definitions and the estimated execution plan (saved as a .sqlplan file, zipped and attached)

If there query blocked by some other connection?

Is that SELECT * really necessary? Do you need all the columns from this table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bladerunner148 SSCommitted Points: 1824 More actions · Answer 3

PlanIdintUnchecked

CampaignIdvarchar(50)

OrganizationIdint

CenterIdint

PlanNameIdint

Budgetmoney

MediaTypeIdint

SegmentIdint

yearint

SubmittedByvarchar(255)

CoopClaimbit

Commentstext

Activebit

IncludeinBlockingChartbit

IncludeinWorkBack bit

IncludeonGMCalbit

LastModifiedUservarchar(255)

LastModifiedDatedatetime

bladerunner148 SSCommitted Points: 1824 More actions · Answer 4

What I did was I closed the application, then I closed the sql server management studio..reopen the mgmt studio, then ran that query and this time it worked. Since I was debugging my app when that error took place, the select query was taking a long time to run. I closed the app, then tried to run this query and it worked this time. I think I need to pass the PlanNameId as Null when the user doesnot select a plan.

prem Mr or Mrs. 500 Points: 508 More actions · Answer 5

Hi ,

Please check if you are opening an explicit transaction (i.e Begin Tran ) from the application.

If for some reason before committing the transaction if there are errors encountered, how are you handling such exceptions. Are you rolling back your transaction?

Are you using Try/Catch blocks?

Thanks,

Prem