Hi,

I'm in a situation where I've found that one user is causing problems
for other users on the same database.

The one particular user is running some queries that are taking some
time to chew through the data. (Minutes). During this time, it's causing
other users to work quite sluggish, and in some cases time out.

While I understand that if there's a lot of data processing to be done,
it's going to demand more from the disk / server - I was wondering, is
there a way that I can handle this, to give priority to other users or
to lessen the impact on other users when an individual performs a
demanding task?

Cheers

Adam.

Adam,

Not knowing the details it's hard to say, but the obvious first thought is
to try to optimize those queries, by checking the execution plans and see if
they can be optimized, adding indexes that might speed up things or even
finding different approaches to achieve the same results faster.
Other things to check are to make sure you keep all transactions as short as
possible in case they are using transactions.
Another possible way might be to extract the data to process from the
database to temporary tables, possibly moving them to the local computer and
process them locally, if that makes sense in your case.

--
Fernando Dias
[Team Elevate]

Fernando

>Another possible way might be to extract the data to process from the
>database to temporary tables, possibly moving them to the local computer and
>process them locally, if that makes sense in your case.

That one's a pretty good suggestion. I was thinking along the lines of seeing if any of the queries could be run as batch jobs overnight and saving the results for the next day.

There's always shooting the user though

Roy Lambert [Team Elevate]

Adam,

In addition to what Fernando said is the slowdown due to table locking
or work being done in dbsrvr and data transfer over network  ?

You could try running another dbsrvr instance just for that user and see
if it makes a difference (dbsrvr needs to run on a anotherp port and
user app has to be able to specify the new port).

Raul

On 10/23/2012 4:48 AM, Fernando Dias wrote:
> Adam,
>
> Not knowing the details it's hard to say, but the obvious first thought
> is to try to optimize those queries, by checking the execution plans and
> see if they can be optimized, adding indexes that might speed up things
> or even finding different approaches to achieve the same results faster.
> Other things to check are to make sure you keep all transactions as
> short as possible in case they are using transactions.
> Another possible way might be to extract the data to process from the
> database to temporary tables, possibly moving them to the local computer
> and process them locally, if that makes sense in your case.

Raul

>You could try running another dbsrvr instance just for that user and see
>if it makes a difference (dbsrvr needs to run on a anotherp port and
>user app has to be able to specify the new port).

With the tables being accessed only by one server then oplocks (I think it is) will be at their best. Access via another instance and you'll get the dreaded Windows multiuser access slowdown.

If the tables are being accessed by multiple servers or via f/s then that wouldn't be a problem.

Roy Lambert [Team Elevate]

Roy,

But this is multiple servers - i was suggesting running multiple dbsrvr
instances (against the same local database(s)). It's the clientserver NG
so i assumed c/s in the OP.

I agree that f/s over network would be slow (oplocks are SMB issue) but
local multiple dbsrvr instances has worked well for us.

Raul

On 10/23/2012 2:02 PM, Roy Lambert wrote:
> With the tables being accessed only by one server then oplocks (I think it is) will be at their best. Access via another instance and you'll get the dreaded Windows multiuser access slowdown.
>
> If the tables are being accessed by multiple servers or via f/s then that wouldn't be a problem.
>
> Roy Lambert [Team Elevate]

Hi Guys,

Thanks for the suggestions. I've done pretty much all that I can
regarding optimisation at this stage. Some of this stuff is just hard
slog to process the data I'm afraid.

Good idea moving it to a local temporary table, but unfortunately the
processes are data processing, as opposed to just query results /
reporting so this won't work for us.

Having a second DBSRVR instance is a good idea. I hadn't thought of
that. I'm guessing that the single DBSRVR doesn't handle this sort of
stuff as well, and having a second instance gets around the first one
bogging down?

Cheers

Adam.

Raul


Must be me - I've always read users as users not network connected users.

Roy Lambert

Adam

>Having a second DBSRVR instance is a good idea. I hadn't thought of
>that. I'm guessing that the single DBSRVR doesn't handle this sort of
>stuff as well, and having a second instance gets around the first one
>bogging down?

I'll be interested to see what your results are. If the two servers are running on the same machine they'll still be sharing the same resources. What is different is that Windows/you can set the priorities for each process (I think) so you might be able to "improve" matters that way.

Roy Lambert [Team Elevate]

Me too.

I have this behavior in one of my customers.

Eduardo