App hangs when SQL table open

[Guest]

Hello all,

We have a strange problem with our app. If the database table to which the
app is writing is open, the app hangs and just takes ages.

Why would this be so?

Thanks,

Jon

 

[Ray Booysen]

Because your connection is waiting for the lock on the table to go away.

 

[Guest]

Ray,

Thanks for the response. Can you help with how to overcome it?

Jon

 

[Mark Rae]

Thanks for the response. Can you help with how to overcome it?

Something somewhere is opening a table lock which is not being closed.

How are you interfacing with ADO.NET?

What other systems might be causing the problem? E.g. is your DBA opening
the table in design mode through Enterprise Manager...?

 

[Guest]

Hi Mark.

Yep, were interfacing with ADO.net using the MS Data Application Blocks.

Nope, no other Enterprise manager windows are open. But this is the problem,
we need to report on the system via EM, but soon as we do that, the app hangs
- which is how we found the problem.

It's a logging database with is causing the problems. The funny thing is, if
we open any table in the main database (where all other data is saved, users
etc) we can still use the app with no hangs.

Any help would be great.

Thanks,

Jon

 

[Guest]

Hell all again.

I think I've managed to identify what the problem is. Having cleared out all
30,000 rows, the app now does NOT hand when the table is open. Therefore, I
beleve it to be down to the volume of data.

Based on this, what can be done to speed things up? Indexs?

Thanks,

Jon

 

[Mark Rae]

Hell all again.

It can't be that bad surely... ;-)

I think I've managed to identify what the problem is. Having cleared out
all
30,000 rows, the app now does NOT hand when the table is open. Therefore,
I
beleve it to be down to the volume of data.

Based on this, what can be done to speed things up? Indexs?

I think you maybe need to take a step back and examine your design. 30,000
rows is nothing to SQL Server...

HOWEVER, what are you *actually* doing with Enterprise Manager? You're
surely not trying to open the table AND bring all 30,000 rows into the local
machine's memory...? If anything's going to shag your performance, that
will...!

 

[Guest]

lol, well spotted Mark - no, thankfully things aren't that bad.

Yes, we are doing exactly that. Using EM to perform queries to get an idea
of what is in the log table. However, when we do this, the app just dies.

How else could we report on the table?

Thanks.

Jon

 

[Mark Rae]

Yes, we are doing exactly that. Using EM to perform queries to get an idea
of what is in the log table. However, when we do this, the app just dies.

Blimey! Just when you think you've heard it all...

How else could we report on the table?

Just about any way you can think of which doesn't actually involve opening
the table and reading its entire contents row by row!

Pop a query window, write a stored procedure, pretty much anything but what
you're doing...

What do you *actually* need to know?

 

[Guest]

Hi Mark.

So purely by having the table open in EM, will cause ASP.net (our app) on
our web server to hang / perform very slowly?

We're reporting on pages vited, by whom, times etc.

Thanks for your help so far, really appreciate it!

Jon

 

[Ray Booysen]

Why use EM?? It keeps the actual table open on the queries. Use the
Query Analyzer which pulls the data locally.

 

[Ray Booysen]

As far as I know, using EM opens a read/write lock which means that no
other queries can use that data you've pulled which will give you the
symptoms you're seeing.

 

[Mark Rae]

So purely by having the table open in EM, will cause ASP.net (our app) on
our web server to hang / perform very slowly?

It will set an exclusive lock on your table - UNDER NO CIRCUMSTANCES use EM
to open a table on a production server!!!!!

We're reporting on pages vited, by whom, times etc.

So why on earth are you using Enterprise Manager for this???

 

[Mark Rae]

Why use EM??

Inexperience, I guess...?

It keeps the actual table open on the queries.
Correct.

Use the Query Analyzer which pulls the data locally.

Yep, or anything else which doesn't lock the table!

 

[Mark Rae]

As far as I know, using EM opens a read/write lock which means that no
other queries can use that data you've pulled which will give you the
symptoms you're seeing.

Correct. EM should NEVER EVER be used to open a table on a production
server.

 

[Guest]

Mark,

You've been a great help, thank you very much. I can go back to the business
with this and that they must use Query Analyser.

We've using EM as a temp measure until our BO universe is set up - not ideal
but has served it's purpose.

Thanks again for all your help.

Jon

 

[Mark Rae]

You've been a great help, thank you very much. I can go back to the
business
with this and that they must use Query Analyser.

It's not so much that they *must* use Query Analyser - it's that they *MUST
NOT* use Enterprise Manager