App hangs when SQL table open

G

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
 
M

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...?
 
G

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
 
G

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
 
M

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...!
 
G

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
 
M

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?
 
G

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
 
R

Ray Booysen

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

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.
 
M

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???
 
M

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.
 
G

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
 
M

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 :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,479
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top