FoxPro Views Causing Blocking?

I have a FoxPro 9 Application which primarily uses FoxPro views with a
SqlServer 2000 back end. During heavy loads (30 concurrent users).
I've randomly seen cases where blocking occurs on the database. At
times it will bring the application to a halt.
Have I reached a scaling point with views and should start looking
into converting to SPT?


Thanks in Advance
Vic

Dan
Fri Jun 22 11:04:50 CDT 2007

There is no functional difference between remote views and SPT. Either way,
you're sending commands to a server and getting back data. Views just add a
GUI and a little (workstation) overhead. So making that change would buy you
nothing.

If you need to scale in a c/s environment, you scale the database. You don't
change the clients.

Most likely, though, you have naughty views that are returning more rows
than they should. Tame those and your blocking issues will likely go away.

Dan

vdimenna@gmail.com wrote:
> I have a FoxPro 9 Application which primarily uses FoxPro views with a
> SqlServer 2000 back end. During heavy loads (30 concurrent users).
> I've randomly seen cases where blocking occurs on the database. At
> times it will bring the application to a halt.
> Have I reached a scaling point with views and should start looking
> into converting to SPT?
>
>
> Thanks in Advance
> Vic

swdev2
Fri Jun 22 13:56:21 CDT 2007

In addition to what Dan Said -

Rework your views so they only use ONE connection, a SHARED connection, per
NAMED connection in your DBC.

If each view gets its own connection, and you have 30 views load in a form
all at the same time,
then you have 30 connections back to the SQL Backend.

Also - do you need all of the views to load simultaneously?
Way back in '99, I helped formulate a technique using the 'noload' option on
parameterized remote views,
and the traffic stabilized even over a 64K ATM pipe back to the sql db.

FWIW - 30 users is not heavy.
400+ users is heavy.

Mondo Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

<vdimenna@gmail.com> wrote in message
news:1182526027.175169.295100@g4g2000hsf.googlegroups.com...
> I have a FoxPro 9 Application which primarily uses FoxPro views with a
> SqlServer 2000 back end. During heavy loads (30 concurrent users).
> I've randomly seen cases where blocking occurs on the database. At
> times it will bring the application to a halt.
> Have I reached a scaling point with views and should start looking
> into converting to SPT?
>
>
> Thanks in Advance
> Vic
>

vdimenna
Fri Jun 22 14:40:34 CDT 2007

On Jun 22, 2:56 pm, "swdev2" <wsand...@dotnetconversions.bob.com>
wrote:
> In addition to what Dan Said -
>
> Rework your views so they only use ONE connection, a SHARED connection, per
> NAMED connection in your DBC.
>
> If each view gets its own connection, and you have 30 views load in a form
> all at the same time,
> then you have 30 connections back to the SQL Backend.
>
> Also - do you need all of the views to load simultaneously?
> Way back in '99, I helped formulate a technique using the 'noload' option on
> parameterized remote views,
> and the traffic stabilized even over a 64K ATM pipe back to the sql db.
>
> FWIW - 30 users is not heavy.
> 400+ users is heavy.
>
> Mondo Regards [Bill]
> --
> ===================
> William Sanders / EFG VFP / mySql / MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.net www.viasqlserver.net
>
> <vdime...@gmail.com> wrote in message
>
> news:1182526027.175169.295100@g4g2000hsf.googlegroups.com...
>
>
>
> > I have a FoxPro 9 Application which primarily uses FoxPro views with a
> > SqlServer 2000 back end. During heavy loads (30 concurrent users).
> > I've randomly seen cases where blocking occurs on the database. At
> > times it will bring the application to a halt.
> > Have I reached a scaling point with views and should start looking
> > into converting to SPT?
>
> > Thanks in Advance
> > Vic- Hide quoted text -
>
> - Show quoted text -

We're using a shared connection in the the dbc as well as 'noload'
where applicable.
The one thing I find odd is when I do see blocking in the sql database
the transcount is 1 indicating the statement is in a transaction. I
know we're not setting transactions any where and originally thought
it was a result of the view doing something behind the scene.

When creating a view is it possible to use the SQL Server nolock
hint?

swdev2
Fri Jun 22 19:59:32 CDT 2007

How many of your views are set to 'updateable' ?
How many do you NEED to be 'updateable' ?
I'd start there - try to refactor that one clicky checkbox on each view, but
make sure its one (or more) that
you DO NOT need to update.

I've studied sql server logs till I'm purple in the face (yes, waaaaaaaaay
past blue)
and while you can't pass a 'nolock' optimizer hint into the obfuscated spt
code used at run time,
you CAN do a general spt with those other options (and the option stays set
per connection, goes away when the connection is lost) with THE EXACT SAME
sql connection prior to a view being instantiated.

HTH - regards [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

<vdimenna@gmail.com> wrote in message
news:1182541234.402553.8940@m37g2000prh.googlegroups.com...
> On Jun 22, 2:56 pm, "swdev2" <wsand...@dotnetconversions.bob.com>
> wrote:
> > In addition to what Dan Said -
> >
> > Rework your views so they only use ONE connection, a SHARED connection,
per
> > NAMED connection in your DBC.
> >
> > If each view gets its own connection, and you have 30 views load in a
form
> > all at the same time,
> > then you have 30 connections back to the SQL Backend.
> >
> > Also - do you need all of the views to load simultaneously?
> > Way back in '99, I helped formulate a technique using the 'noload'
option on
> > parameterized remote views,
> > and the traffic stabilized even over a 64K ATM pipe back to the sql db.
> >
> > FWIW - 30 users is not heavy.
> > 400+ users is heavy.
> >
> > Mondo Regards [Bill]
> > --
> > ===================
> > William Sanders / EFG VFP / mySql /
MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.net www.viasqlserver.net
> >
> > <vdime...@gmail.com> wrote in message
> >
> > news:1182526027.175169.295100@g4g2000hsf.googlegroups.com...
> >
> >
> >
> > > I have a FoxPro 9 Application which primarily uses FoxPro views with a
> > > SqlServer 2000 back end. During heavy loads (30 concurrent users).
> > > I've randomly seen cases where blocking occurs on the database. At
> > > times it will bring the application to a halt.
> > > Have I reached a scaling point with views and should start looking
> > > into converting to SPT?
> >
> > > Thanks in Advance
> > > Vic- Hide quoted text -
> >
> > - Show quoted text -
>
> We're using a shared connection in the the dbc as well as 'noload'
> where applicable.
> The one thing I find odd is when I do see blocking in the sql database
> the transcount is 1 indicating the statement is in a transaction. I
> know we're not setting transactions any where and originally thought
> it was a result of the view doing something behind the scene.
>
> When creating a view is it possible to use the SQL Server nolock
> hint?
>

swdev2
Sun Jun 24 23:25:38 CDT 2007

Take a look here -
http://support.microsoft.com/kb/198606/en-us?spid=7992&sid=200

And review the sql server settings - I'm betting there's a sql server
setting that needs to be tweaked a bit.

Lemme know how it turns out.

Regards [Bill]

--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

<vdimenna@gmail.com> wrote in message
news:1182541234.402553.8940@m37g2000prh.googlegroups.com...
> On Jun 22, 2:56 pm, "swdev2" <wsand...@dotnetconversions.bob.com>
> wrote:
> > In addition to what Dan Said -
> >
> > Rework your views so they only use ONE connection, a SHARED connection,
per
> > NAMED connection in your DBC.
> >
> > If each view gets its own connection, and you have 30 views load in a
form
> > all at the same time,
> > then you have 30 connections back to the SQL Backend.
> >
> > Also - do you need all of the views to load simultaneously?
> > Way back in '99, I helped formulate a technique using the 'noload'
option on
> > parameterized remote views,
> > and the traffic stabilized even over a 64K ATM pipe back to the sql db.
> >
> > FWIW - 30 users is not heavy.
> > 400+ users is heavy.
> >
> > Mondo Regards [Bill]
> > --
> > ===================
> > William Sanders / EFG VFP / mySql /
MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.net www.viasqlserver.net
> >
> > <vdime...@gmail.com> wrote in message
> >
> > news:1182526027.175169.295100@g4g2000hsf.googlegroups.com...
> >
> >
> >
> > > I have a FoxPro 9 Application which primarily uses FoxPro views with a
> > > SqlServer 2000 back end. During heavy loads (30 concurrent users).
> > > I've randomly seen cases where blocking occurs on the database. At
> > > times it will bring the application to a halt.
> > > Have I reached a scaling point with views and should start looking
> > > into converting to SPT?
> >
> > > Thanks in Advance
> > > Vic- Hide quoted text -
> >
> > - Show quoted text -
>
> We're using a shared connection in the the dbc as well as 'noload'
> where applicable.
> The one thing I find odd is when I do see blocking in the sql database
> the transcount is 1 indicating the statement is in a transaction. I
> know we're not setting transactions any where and originally thought
> it was a result of the view doing something behind the scene.
>
> When creating a view is it possible to use the SQL Server nolock
> hint?
>

vdimenna
Mon Jun 25 13:31:34 CDT 2007

On Jun 25, 12:25 am, "swdev2" <wsand...@dotnetconversions.bob.com>
wrote:
> Take a look here -http://support.microsoft.com/kb/198606/en-us?spid=7992&sid=200
>
> And review the sql server settings - I'm betting there's a sql server
> setting that needs to be tweaked a bit.
>
> Lemme know how it turns out.
>
> Regards [Bill]
>
> --
> ===================
> William Sanders / EFG VFP / mySql / MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.net www.viasqlserver.net
>
> <vdime...@gmail.com> wrote in message
>
> news:1182541234.402553.8940@m37g2000prh.googlegroups.com...
>
>
>
> > On Jun 22, 2:56 pm, "swdev2" <wsand...@dotnetconversions.bob.com>
> > wrote:
> > > In addition to what Dan Said -
>
> > > Rework your views so they only use ONE connection, a SHARED connection,
> per
> > > NAMED connection in your DBC.
>
> > > If each view gets its own connection, and you have 30 views load in a
> form
> > > all at the same time,
> > > then you have 30 connections back to the SQL Backend.
>
> > > Also - do you need all of the views to load simultaneously?
> > > Way back in '99, I helped formulate a technique using the 'noload'
> option on
> > > parameterized remote views,
> > > and the traffic stabilized even over a 64K ATM pipe back to the sql db.
>
> > > FWIW - 30 users is not heavy.
> > > 400+ users is heavy.
>
> > > Mondo Regards [Bill]
> > > --
> > > ===================
> > > William Sanders / EFG VFP / mySql /
>
> MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.netwww.viasqlserver.net
>
>
>
>
>
> > > <vdime...@gmail.com> wrote in message
>
> > >news:1182526027.175169.295100@g4g2000hsf.googlegroups.com...
>
> > > > I have a FoxPro 9 Application which primarily uses FoxPro views with a
> > > > SqlServer 2000 back end. During heavy loads (30 concurrent users).
> > > > I've randomly seen cases where blocking occurs on the database. At
> > > > times it will bring the application to a halt.
> > > > Have I reached a scaling point with views and should start looking
> > > > into converting to SPT?
>
> > > > Thanks in Advance
> > > > Vic- Hide quoted text -
>
> > > - Show quoted text -
>
> > We're using a shared connection in the the dbc as well as 'noload'
> > where applicable.
> > The one thing I find odd is when I do see blocking in the sql database
> > the transcount is 1 indicating the statement is in a transaction. I
> > know we're not setting transactions any where and originally thought
> > it was a result of the view doing something behind the scene.
>
> > When creating a view is it possible to use the SQL Server nolock
> > hint?- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

We can have anywhere between 50-100 views (most are updateable) open
per session at any given time.
I couldn't find any particular SQL server setting to be a fit for my
condition. The ansi null option under ODBC config is something I will
look into.