Poor performance of MTS Componant 
Author Message
 Poor performance of MTS Componant

I have converted my enterprise application to access a fox pro database over
MTS, instead of directly through ODBC.

When it accessed data through ODBC it was fast...

Now when its accessing it through the MTS componant it is very slow. A 2
second operation can take 10 - 20 seconds or more

The same thing happened running the same componant as a local COM object
(not over the network)

Below is the code for the componant.

Does anyone know how to increase performance when retrieving a recordset.

I am not making use of object context or anything as I have no experience
with that.....

Can someone suggest something

I am using VB5, Visual Fox Pro 5, ADO 1.2

I just set a recordset to the GetRecordset function in the componant to get
the recordsets or to make an update I use the DatabaseExecute function, as
VFP is not very supportive of updating remote recordsets....hmmm Actually it
has ZERO support for it (thats another story though).

Here is the class (componant)

'All Application Code Written, Developed,
'and Researched by Dan Collin Douglas, Year 2000

'This class module needs to be compiled seperatly into a DLL
'and put on MTS
Option Explicit

Public Sub DatabaseExecute(stExecute As String)
    Dim Ccn As New ADODB.Connection
    Ccn.Open "DSN=DataManager"
    Ccn.Execute stExecute
    Ccn.Close
    Set Ccn = Nothing
End Sub

Public Function GetRecordset(Optional stSource As String, Optional
cnActiveConnection As ADODB.Connection, Optional eCursorType As
CursorTypeEnum, Optional eLockType As LockTypeEnum, Optional lOptions As
Long) As ADODB.Recordset 'ByRef is default)
    Dim Ccn As New ADODB.Connection
    Dim Crs As New ADODB.Recordset

    Ccn.Open "DSN=DataManager"
    'Must be set to use client (for disconnecting the recordset)

    Crs.CursorLocation = adUseClient
    Crs.LockType = adLockOptimistic
    Crs.Open stSource, Ccn, eCursorType, , lOptions
    'Active Connection must be set to nothing
    'This tells ADO to ignore the original connection information for the
recordset
    '(for disconnecting the recordset)
    Crs.ActiveConnection = Nothing
    Set GetRecordset = Crs
    'Crs.Close
    'Ccn.Close
    'Set Crs = Nothing
    'Set Ccn = Nothing
End Function



Sun, 22 Jun 2003 04:53:41 GMT  
 Poor performance of MTS Componant
Not sure, as I've little MTS experience.  Some thoughts:
Step through the code and see what step seems to be taking a long time.  If it's
the connection.open, can you just open a connection object for your whole
project and use that throughout - I do that in Access and it works nicely, but
I'm guessing this is a perhaps a stateless project?

Alternatively, sometimes a connection string is faster than a DSN. Maybe try
that instead.  

Also, ADO 1.2?  That's quite outdated, I strongly suspect that ISN'T helping
either.  Is your MTS on an overworked machine, by chance?  

That could hurt I'd suppose, but I'm sure you've taken such factors out of the
loop.

Personally, I'd recommend SQL Server over FP anyday.  That's just me, though...
Depends on your needs.
hth
brett

Quote:

> I have converted my enterprise application to access a fox pro database over
> MTS, instead of directly through ODBC.

> When it accessed data through ODBC it was fast...

> Now when its accessing it through the MTS componant it is very slow. A 2
> second operation can take 10 - 20 seconds or more

> The same thing happened running the same componant as a local COM object
> (not over the network)

> Below is the code for the componant.

> Does anyone know how to increase performance when retrieving a recordset.

> I am not making use of object context or anything as I have no experience
> with that.....

> Can someone suggest something

> I am using VB5, Visual Fox Pro 5, ADO 1.2

> I just set a recordset to the GetRecordset function in the componant to get
> the recordsets or to make an update I use the DatabaseExecute function, as
> VFP is not very supportive of updating remote recordsets....hmmm Actually it
> has ZERO support for it (thats another story though).

> Here is the class (componant)

> 'All Application Code Written, Developed,
> 'and Researched by Dan Collin Douglas, Year 2000

> 'This class module needs to be compiled seperatly into a DLL
> 'and put on MTS
> Option Explicit

> Public Sub DatabaseExecute(stExecute As String)
>     Dim Ccn As New ADODB.Connection
>     Ccn.Open "DSN=DataManager"
>     Ccn.Execute stExecute
>     Ccn.Close
>     Set Ccn = Nothing
> End Sub

> Public Function GetRecordset(Optional stSource As String, Optional
> cnActiveConnection As ADODB.Connection, Optional eCursorType As
> CursorTypeEnum, Optional eLockType As LockTypeEnum, Optional lOptions As
> Long) As ADODB.Recordset 'ByRef is default)
>     Dim Ccn As New ADODB.Connection
>     Dim Crs As New ADODB.Recordset

>     Ccn.Open "DSN=DataManager"
>     'Must be set to use client (for disconnecting the recordset)

>     Crs.CursorLocation = adUseClient
>     Crs.LockType = adLockOptimistic
>     Crs.Open stSource, Ccn, eCursorType, , lOptions
>     'Active Connection must be set to nothing
>     'This tells ADO to ignore the original connection information for the
> recordset
>     '(for disconnecting the recordset)
>     Crs.ActiveConnection = Nothing
>     Set GetRecordset = Crs
>     'Crs.Close
>     'Ccn.Close
>     'Set Crs = Nothing
>     'Set Ccn = Nothing
> End Function

--
Brett J. Valjalo
Database Programmer
Taos - The SysAdmin Company



Sun, 22 Jun 2003 10:56:16 GMT  
 Poor performance of MTS Componant
Oh . My mistake I meant ADO 2.1

I cant step through the code as its a compiled componant... I mean the same
code is quick running as part of a vb app, but is slow when even used within
a vb class (which is essentially what the componant is compiled from)

I prefer not to keep connections open, I think in most cases it is bad
design, I personally dont believe it to be an opening the connection problem
is the performance is directly related to the size of the recordset.

Maybe when i get some time ill look into it further


Quote:
> Not sure, as I've little MTS experience.  Some thoughts:
> Step through the code and see what step seems to be taking a long time.
If it's
> the connection.open, can you just open a connection object for your whole
> project and use that throughout - I do that in Access and it works nicely,
but
> I'm guessing this is a perhaps a stateless project?

> Alternatively, sometimes a connection string is faster than a DSN. Maybe
try
> that instead.

> Also, ADO 1.2?  That's quite outdated, I strongly suspect that ISN'T
helping
> either.  Is your MTS on an overworked machine, by chance?

> That could hurt I'd suppose, but I'm sure you've taken such factors out of
the
> loop.

> Personally, I'd recommend SQL Server over FP anyday.  That's just me,
though...
> Depends on your needs.
> hth
> brett


> > I have converted my enterprise application to access a fox pro database
over
> > MTS, instead of directly through ODBC.

> > When it accessed data through ODBC it was fast...

> > Now when its accessing it through the MTS componant it is very slow. A 2
> > second operation can take 10 - 20 seconds or more

> > The same thing happened running the same componant as a local COM object
> > (not over the network)

> > Below is the code for the componant.

> > Does anyone know how to increase performance when retrieving a
recordset.

> > I am not making use of object context or anything as I have no
experience
> > with that.....

> > Can someone suggest something

> > I am using VB5, Visual Fox Pro 5, ADO 1.2

> > I just set a recordset to the GetRecordset function in the componant to
get
> > the recordsets or to make an update I use the DatabaseExecute function,
as
> > VFP is not very supportive of updating remote recordsets....hmmm
Actually it
> > has ZERO support for it (thats another story though).

> > Here is the class (componant)

> > 'All Application Code Written, Developed,
> > 'and Researched by Dan Collin Douglas, Year 2000

> > 'This class module needs to be compiled seperatly into a DLL
> > 'and put on MTS
> > Option Explicit

> > Public Sub DatabaseExecute(stExecute As String)
> >     Dim Ccn As New ADODB.Connection
> >     Ccn.Open "DSN=DataManager"
> >     Ccn.Execute stExecute
> >     Ccn.Close
> >     Set Ccn = Nothing
> > End Sub

> > Public Function GetRecordset(Optional stSource As String, Optional
> > cnActiveConnection As ADODB.Connection, Optional eCursorType As
> > CursorTypeEnum, Optional eLockType As LockTypeEnum, Optional lOptions As
> > Long) As ADODB.Recordset 'ByRef is default)
> >     Dim Ccn As New ADODB.Connection
> >     Dim Crs As New ADODB.Recordset

> >     Ccn.Open "DSN=DataManager"
> >     'Must be set to use client (for disconnecting the recordset)

> >     Crs.CursorLocation = adUseClient
> >     Crs.LockType = adLockOptimistic
> >     Crs.Open stSource, Ccn, eCursorType, , lOptions
> >     'Active Connection must be set to nothing
> >     'This tells ADO to ignore the original connection information for
the
> > recordset
> >     '(for disconnecting the recordset)
> >     Crs.ActiveConnection = Nothing
> >     Set GetRecordset = Crs
> >     'Crs.Close
> >     'Ccn.Close
> >     'Set Crs = Nothing
> >     'Set Ccn = Nothing
> > End Function

> --
> Brett J. Valjalo
> Database Programmer
> Taos - The SysAdmin Company




Sun, 22 Jun 2003 22:28:42 GMT  
 Poor performance of MTS Componant
Make sure you use BYVAL where BYREF is not needed. (BYREF is default and it
means 2 trips over the network)

Try to avoid passing objects, if you need to pass recordsets then pass
disconnected ones.

Stateless is the only way to go.

Your correct about keeping connection open, use connection pooling.

I've have used MTS (COM+) allot, with not much performance problems, its
very easy to get started with MTS but its worth while reading up on the do's
and don'ts, hunt around MSDN.

I could go on and on.

NOTE you can debug MTS

Also

Do you really need MTS for what you are doing???

Cheers

Steve


Quote:
> Oh . My mistake I meant ADO 2.1

> I cant step through the code as its a compiled componant... I mean the
same
> code is quick running as part of a vb app, but is slow when even used
within
> a vb class (which is essentially what the componant is compiled from)

> I prefer not to keep connections open, I think in most cases it is bad
> design, I personally dont believe it to be an opening the connection
problem
> is the performance is directly related to the size of the recordset.

> Maybe when i get some time ill look into it further


> > Not sure, as I've little MTS experience.  Some thoughts:
> > Step through the code and see what step seems to be taking a long time.
> If it's
> > the connection.open, can you just open a connection object for your
whole
> > project and use that throughout - I do that in Access and it works
nicely,
> but
> > I'm guessing this is a perhaps a stateless project?

> > Alternatively, sometimes a connection string is faster than a DSN. Maybe
> try
> > that instead.

> > Also, ADO 1.2?  That's quite outdated, I strongly suspect that ISN'T
> helping
> > either.  Is your MTS on an overworked machine, by chance?

> > That could hurt I'd suppose, but I'm sure you've taken such factors out
of
> the
> > loop.

> > Personally, I'd recommend SQL Server over FP anyday.  That's just me,
> though...
> > Depends on your needs.
> > hth
> > brett


> > > I have converted my enterprise application to access a fox pro
database
> over
> > > MTS, instead of directly through ODBC.

> > > When it accessed data through ODBC it was fast...

> > > Now when its accessing it through the MTS componant it is very slow. A
2
> > > second operation can take 10 - 20 seconds or more

> > > The same thing happened running the same componant as a local COM
object
> > > (not over the network)

> > > Below is the code for the componant.

> > > Does anyone know how to increase performance when retrieving a
> recordset.

> > > I am not making use of object context or anything as I have no
> experience
> > > with that.....

> > > Can someone suggest something

> > > I am using VB5, Visual Fox Pro 5, ADO 1.2

> > > I just set a recordset to the GetRecordset function in the componant
to
> get
> > > the recordsets or to make an update I use the DatabaseExecute
function,
> as
> > > VFP is not very supportive of updating remote recordsets....hmmm
> Actually it
> > > has ZERO support for it (thats another story though).

> > > Here is the class (componant)

> > > 'All Application Code Written, Developed,
> > > 'and Researched by Dan Collin Douglas, Year 2000

> > > 'This class module needs to be compiled seperatly into a DLL
> > > 'and put on MTS
> > > Option Explicit

> > > Public Sub DatabaseExecute(stExecute As String)
> > >     Dim Ccn As New ADODB.Connection
> > >     Ccn.Open "DSN=DataManager"
> > >     Ccn.Execute stExecute
> > >     Ccn.Close
> > >     Set Ccn = Nothing
> > > End Sub

> > > Public Function GetRecordset(Optional stSource As String, Optional
> > > cnActiveConnection As ADODB.Connection, Optional eCursorType As
> > > CursorTypeEnum, Optional eLockType As LockTypeEnum, Optional lOptions
As
> > > Long) As ADODB.Recordset 'ByRef is default)
> > >     Dim Ccn As New ADODB.Connection
> > >     Dim Crs As New ADODB.Recordset

> > >     Ccn.Open "DSN=DataManager"
> > >     'Must be set to use client (for disconnecting the recordset)

> > >     Crs.CursorLocation = adUseClient
> > >     Crs.LockType = adLockOptimistic
> > >     Crs.Open stSource, Ccn, eCursorType, , lOptions
> > >     'Active Connection must be set to nothing
> > >     'This tells ADO to ignore the original connection information for
> the
> > > recordset
> > >     '(for disconnecting the recordset)
> > >     Crs.ActiveConnection = Nothing
> > >     Set GetRecordset = Crs
> > >     'Crs.Close
> > >     'Ccn.Close
> > >     'Set Crs = Nothing
> > >     'Set Ccn = Nothing
> > > End Function

> > --
> > Brett J. Valjalo
> > Database Programmer
> > Taos - The SysAdmin Company




Mon, 23 Jun 2003 00:49:25 GMT  
 Poor performance of MTS Componant

I am passing disconnected recordsets.. I believe.. (Did you see my source
code?)

I dont really need MTS per say. but a DCOM componants is definatly required
and through my experience its easier to implement a server componant
throught MTS rather than a DCOM componant standalone

I dont think its an MTS/Network issue as there was performance problems with
the same componants running locally.

Do you see anything in specefic as to the way I wrote my source code that
could be a problem?


Quote:
> Make sure you use BYVAL where BYREF is not needed. (BYREF is default and
it
> means 2 trips over the network)

> Try to avoid passing objects, if you need to pass recordsets then pass
> disconnected ones.

> Stateless is the only way to go.

> Your correct about keeping connection open, use connection pooling.

> I've have used MTS (COM+) allot, with not much performance problems, its
> very easy to get started with MTS but its worth while reading up on the
do's
> and don'ts, hunt around MSDN.

> I could go on and on.

> NOTE you can debug MTS

> Also

> Do you really need MTS for what you are doing???

> Cheers

> Steve


> > Oh . My mistake I meant ADO 2.1

> > I cant step through the code as its a compiled componant... I mean the
> same
> > code is quick running as part of a vb app, but is slow when even used
> within
> > a vb class (which is essentially what the componant is compiled from)

> > I prefer not to keep connections open, I think in most cases it is bad
> > design, I personally dont believe it to be an opening the connection
> problem
> > is the performance is directly related to the size of the recordset.

> > Maybe when i get some time ill look into it further


> > > Not sure, as I've little MTS experience.  Some thoughts:
> > > Step through the code and see what step seems to be taking a long
time.
> > If it's
> > > the connection.open, can you just open a connection object for your
> whole
> > > project and use that throughout - I do that in Access and it works
> nicely,
> > but
> > > I'm guessing this is a perhaps a stateless project?

> > > Alternatively, sometimes a connection string is faster than a DSN.
Maybe
> > try
> > > that instead.

> > > Also, ADO 1.2?  That's quite outdated, I strongly suspect that ISN'T
> > helping
> > > either.  Is your MTS on an overworked machine, by chance?

> > > That could hurt I'd suppose, but I'm sure you've taken such factors
out
> of
> > the
> > > loop.

> > > Personally, I'd recommend SQL Server over FP anyday.  That's just me,
> > though...
> > > Depends on your needs.
> > > hth
> > > brett


> > > > I have converted my enterprise application to access a fox pro
> database
> > over
> > > > MTS, instead of directly through ODBC.

> > > > When it accessed data through ODBC it was fast...

> > > > Now when its accessing it through the MTS componant it is very slow.
A
> 2
> > > > second operation can take 10 - 20 seconds or more

> > > > The same thing happened running the same componant as a local COM
> object
> > > > (not over the network)

> > > > Below is the code for the componant.

> > > > Does anyone know how to increase performance when retrieving a
> > recordset.

> > > > I am not making use of object context or anything as I have no
> > experience
> > > > with that.....

> > > > Can someone suggest something

> > > > I am using VB5, Visual Fox Pro 5, ADO 1.2

> > > > I just set a recordset to the GetRecordset function in the componant
> to
> > get
> > > > the recordsets or to make an update I use the DatabaseExecute
> function,
> > as
> > > > VFP is not very supportive of updating remote recordsets....hmmm
> > Actually it
> > > > has ZERO support for it (thats another story though).

> > > > Here is the class (componant)

> > > > 'All Application Code Written, Developed,
> > > > 'and Researched by Dan Collin Douglas, Year 2000

> > > > 'This class module needs to be compiled seperatly into a DLL
> > > > 'and put on MTS
> > > > Option Explicit

> > > > Public Sub DatabaseExecute(stExecute As String)
> > > >     Dim Ccn As New ADODB.Connection
> > > >     Ccn.Open "DSN=DataManager"
> > > >     Ccn.Execute stExecute
> > > >     Ccn.Close
> > > >     Set Ccn = Nothing
> > > > End Sub

> > > > Public Function GetRecordset(Optional stSource As String, Optional
> > > > cnActiveConnection As ADODB.Connection, Optional eCursorType As
> > > > CursorTypeEnum, Optional eLockType As LockTypeEnum, Optional
lOptions
> As
> > > > Long) As ADODB.Recordset 'ByRef is default)
> > > >     Dim Ccn As New ADODB.Connection
> > > >     Dim Crs As New ADODB.Recordset

> > > >     Ccn.Open "DSN=DataManager"
> > > >     'Must be set to use client (for disconnecting the recordset)

> > > >     Crs.CursorLocation = adUseClient
> > > >     Crs.LockType = adLockOptimistic
> > > >     Crs.Open stSource, Ccn, eCursorType, , lOptions
> > > >     'Active Connection must be set to nothing
> > > >     'This tells ADO to ignore the original connection information
for
> > the
> > > > recordset
> > > >     '(for disconnecting the recordset)
> > > >     Crs.ActiveConnection = Nothing
> > > >     Set GetRecordset = Crs
> > > >     'Crs.Close
> > > >     'Ccn.Close
> > > >     'Set Crs = Nothing
> > > >     'Set Ccn = Nothing
> > > > End Function

> > > --
> > > Brett J. Valjalo
> > > Database Programmer
> > > Taos - The SysAdmin Company




Mon, 23 Jun 2003 01:04:42 GMT  
 Poor performance of MTS Componant
Have you tried debugging?

I've done the same kind of thing with no performance problems but not with
FOXPRO.



Quote:

> I am passing disconnected recordsets.. I believe.. (Did you see my source
> code?)

> I dont really need MTS per say. but a DCOM componants is definatly
required
> and through my experience its easier to implement a server componant
> throught MTS rather than a DCOM componant standalone

> I dont think its an MTS/Network issue as there was performance problems
with
> the same componants running locally.

> Do you see anything in specefic as to the way I wrote my source code that
> could be a problem?



> > Make sure you use BYVAL where BYREF is not needed. (BYREF is default and
> it
> > means 2 trips over the network)

> > Try to avoid passing objects, if you need to pass recordsets then pass
> > disconnected ones.

> > Stateless is the only way to go.

> > Your correct about keeping connection open, use connection pooling.

> > I've have used MTS (COM+) allot, with not much performance problems, its
> > very easy to get started with MTS but its worth while reading up on the
> do's
> > and don'ts, hunt around MSDN.

> > I could go on and on.

> > NOTE you can debug MTS

> > Also

> > Do you really need MTS for what you are doing???

> > Cheers

> > Steve


> > > Oh . My mistake I meant ADO 2.1

> > > I cant step through the code as its a compiled componant... I mean the
> > same
> > > code is quick running as part of a vb app, but is slow when even used
> > within
> > > a vb class (which is essentially what the componant is compiled from)

> > > I prefer not to keep connections open, I think in most cases it is bad
> > > design, I personally dont believe it to be an opening the connection
> > problem
> > > is the performance is directly related to the size of the recordset.

> > > Maybe when i get some time ill look into it further


> > > > Not sure, as I've little MTS experience.  Some thoughts:
> > > > Step through the code and see what step seems to be taking a long
> time.
> > > If it's
> > > > the connection.open, can you just open a connection object for your
> > whole
> > > > project and use that throughout - I do that in Access and it works
> > nicely,
> > > but
> > > > I'm guessing this is a perhaps a stateless project?

> > > > Alternatively, sometimes a connection string is faster than a DSN.
> Maybe
> > > try
> > > > that instead.

> > > > Also, ADO 1.2?  That's quite outdated, I strongly suspect that ISN'T
> > > helping
> > > > either.  Is your MTS on an overworked machine, by chance?

> > > > That could hurt I'd suppose, but I'm sure you've taken such factors
> out
> > of
> > > the
> > > > loop.

> > > > Personally, I'd recommend SQL Server over FP anyday.  That's just
me,
> > > though...
> > > > Depends on your needs.
> > > > hth
> > > > brett


> > > > > I have converted my enterprise application to access a fox pro
> > database
> > > over
> > > > > MTS, instead of directly through ODBC.

> > > > > When it accessed data through ODBC it was fast...

> > > > > Now when its accessing it through the MTS componant it is very
slow.
> A
> > 2
> > > > > second operation can take 10 - 20 seconds or more

> > > > > The same thing happened running the same componant as a local COM
> > object
> > > > > (not over the network)

> > > > > Below is the code for the componant.

> > > > > Does anyone know how to increase performance when retrieving a
> > > recordset.

> > > > > I am not making use of object context or anything as I have no
> > > experience
> > > > > with that.....

> > > > > Can someone suggest something

> > > > > I am using VB5, Visual Fox Pro 5, ADO 1.2

> > > > > I just set a recordset to the GetRecordset function in the
componant
> > to
> > > get
> > > > > the recordsets or to make an update I use the DatabaseExecute
> > function,
> > > as
> > > > > VFP is not very supportive of updating remote recordsets....hmmm
> > > Actually it
> > > > > has ZERO support for it (thats another story though).

> > > > > Here is the class (componant)

> > > > > 'All Application Code Written, Developed,
> > > > > 'and Researched by Dan Collin Douglas, Year 2000

> > > > > 'This class module needs to be compiled seperatly into a DLL
> > > > > 'and put on MTS
> > > > > Option Explicit

> > > > > Public Sub DatabaseExecute(stExecute As String)
> > > > >     Dim Ccn As New ADODB.Connection
> > > > >     Ccn.Open "DSN=DataManager"
> > > > >     Ccn.Execute stExecute
> > > > >     Ccn.Close
> > > > >     Set Ccn = Nothing
> > > > > End Sub

> > > > > Public Function GetRecordset(Optional stSource As String, Optional
> > > > > cnActiveConnection As ADODB.Connection, Optional eCursorType As
> > > > > CursorTypeEnum, Optional eLockType As LockTypeEnum, Optional
> lOptions
> > As
> > > > > Long) As ADODB.Recordset 'ByRef is default)
> > > > >     Dim Ccn As New ADODB.Connection
> > > > >     Dim Crs As New ADODB.Recordset

> > > > >     Ccn.Open "DSN=DataManager"
> > > > >     'Must be set to use client (for disconnecting the recordset)

> > > > >     Crs.CursorLocation = adUseClient
> > > > >     Crs.LockType = adLockOptimistic
> > > > >     Crs.Open stSource, Ccn, eCursorType, , lOptions
> > > > >     'Active Connection must be set to nothing
> > > > >     'This tells ADO to ignore the original connection information
> for
> > > the
> > > > > recordset
> > > > >     '(for disconnecting the recordset)
> > > > >     Crs.ActiveConnection = Nothing
> > > > >     Set GetRecordset = Crs
> > > > >     'Crs.Close
> > > > >     'Ccn.Close
> > > > >     'Set Crs = Nothing
> > > > >     'Set Ccn = Nothing
> > > > > End Function

> > > > --
> > > > Brett J. Valjalo
> > > > Database Programmer
> > > > Taos - The SysAdmin Company




Mon, 23 Jun 2003 01:15:18 GMT  
 Poor performance of MTS Componant
It could be a Fox Pro issue, FoxPro wasnt designed for this purpose.


Quote:
> Have you tried debugging?

> I've done the same kind of thing with no performance problems but not with
> FOXPRO.



> > I am passing disconnected recordsets.. I believe.. (Did you see my
source
> > code?)

> > I dont really need MTS per say. but a DCOM componants is definatly
> required
> > and through my experience its easier to implement a server componant
> > throught MTS rather than a DCOM componant standalone

> > I dont think its an MTS/Network issue as there was performance problems
> with
> > the same componants running locally.

> > Do you see anything in specefic as to the way I wrote my source code
that
> > could be a problem?



> > > Make sure you use BYVAL where BYREF is not needed. (BYREF is default
and
> > it
> > > means 2 trips over the network)

> > > Try to avoid passing objects, if you need to pass recordsets then pass
> > > disconnected ones.

> > > Stateless is the only way to go.

> > > Your correct about keeping connection open, use connection pooling.

> > > I've have used MTS (COM+) allot, with not much performance problems,
its
> > > very easy to get started with MTS but its worth while reading up on
the
> > do's
> > > and don'ts, hunt around MSDN.

> > > I could go on and on.

> > > NOTE you can debug MTS

> > > Also

> > > Do you really need MTS for what you are doing???

> > > Cheers

> > > Steve


> > > > Oh . My mistake I meant ADO 2.1

> > > > I cant step through the code as its a compiled componant... I mean
the
> > > same
> > > > code is quick running as part of a vb app, but is slow when even
used
> > > within
> > > > a vb class (which is essentially what the componant is compiled
from)

> > > > I prefer not to keep connections open, I think in most cases it is
bad
> > > > design, I personally dont believe it to be an opening the connection
> > > problem
> > > > is the performance is directly related to the size of the recordset.

> > > > Maybe when i get some time ill look into it further


> > > > > Not sure, as I've little MTS experience.  Some thoughts:
> > > > > Step through the code and see what step seems to be taking a long
> > time.
> > > > If it's
> > > > > the connection.open, can you just open a connection object for
your
> > > whole
> > > > > project and use that throughout - I do that in Access and it works
> > > nicely,
> > > > but
> > > > > I'm guessing this is a perhaps a stateless project?

> > > > > Alternatively, sometimes a connection string is faster than a DSN.
> > Maybe
> > > > try
> > > > > that instead.

> > > > > Also, ADO 1.2?  That's quite outdated, I strongly suspect that
ISN'T
> > > > helping
> > > > > either.  Is your MTS on an overworked machine, by chance?

> > > > > That could hurt I'd suppose, but I'm sure you've taken such
factors
> > out
> > > of
> > > > the
> > > > > loop.

> > > > > Personally, I'd recommend SQL Server over FP anyday.  That's just
> me,
> > > > though...
> > > > > Depends on your needs.
> > > > > hth
> > > > > brett


> > > > > > I have converted my enterprise application to access a fox pro
> > > database
> > > > over
> > > > > > MTS, instead of directly through ODBC.

> > > > > > When it accessed data through ODBC it was fast...

> > > > > > Now when its accessing it through the MTS componant it is very
> slow.
> > A
> > > 2
> > > > > > second operation can take 10 - 20 seconds or more

> > > > > > The same thing happened running the same componant as a local
COM
> > > object
> > > > > > (not over the network)

> > > > > > Below is the code for the componant.

> > > > > > Does anyone know how to increase performance when retrieving a
> > > > recordset.

> > > > > > I am not making use of object context or anything as I have no
> > > > experience
> > > > > > with that.....

> > > > > > Can someone suggest something

> > > > > > I am using VB5, Visual Fox Pro 5, ADO 1.2

> > > > > > I just set a recordset to the GetRecordset function in the
> componant
> > > to
> > > > get
> > > > > > the recordsets or to make an update I use the DatabaseExecute
> > > function,
> > > > as
> > > > > > VFP is not very supportive of updating remote recordsets....hmmm
> > > > Actually it
> > > > > > has ZERO support for it (thats another story though).

> > > > > > Here is the class (componant)

> > > > > > 'All Application Code Written, Developed,
> > > > > > 'and Researched by Dan Collin Douglas, Year 2000

> > > > > > 'This class module needs to be compiled seperatly into a DLL
> > > > > > 'and put on MTS
> > > > > > Option Explicit

> > > > > > Public Sub DatabaseExecute(stExecute As String)
> > > > > >     Dim Ccn As New ADODB.Connection
> > > > > >     Ccn.Open "DSN=DataManager"
> > > > > >     Ccn.Execute stExecute
> > > > > >     Ccn.Close
> > > > > >     Set Ccn = Nothing
> > > > > > End Sub

> > > > > > Public Function GetRecordset(Optional stSource As String,
Optional
> > > > > > cnActiveConnection As ADODB.Connection, Optional eCursorType As
> > > > > > CursorTypeEnum, Optional eLockType As LockTypeEnum, Optional
> > lOptions
> > > As
> > > > > > Long) As ADODB.Recordset 'ByRef is default)
> > > > > >     Dim Ccn As New ADODB.Connection
> > > > > >     Dim Crs As New ADODB.Recordset

> > > > > >     Ccn.Open "DSN=DataManager"
> > > > > >     'Must be set to use client (for disconnecting the recordset)

> > > > > >     Crs.CursorLocation = adUseClient
> > > > > >     Crs.LockType = adLockOptimistic
> > > > > >     Crs.Open stSource, Ccn, eCursorType, , lOptions
> > > > > >     'Active Connection must be set to nothing
> > > > > >     'This tells ADO to ignore the original connection
information
> > for
> > > > the
> > > > > > recordset
> > > > > >     '(for disconnecting the recordset)
> > > > > >     Crs.ActiveConnection = Nothing
> > > > > >     Set GetRecordset = Crs
> > > > > >     'Crs.Close
> > > > > >     'Ccn.Close
> > > > > >     'Set Crs = Nothing
> > > > > >     'Set Ccn = Nothing
> > > > > > End Function

> > > > > --
> > > > > Brett J. Valjalo
> > > > > Database Programmer
> > > > > Taos - The SysAdmin Company




Mon, 23 Jun 2003 02:03:40 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Poor performance of MTS Componant

2. (ADO + Oracle)*MTS = Poor Performance

3. Using New to create instances of a class in MTS componant

4. Upgrade a componant on MTS?

5. A MTS componant client

6. Comments on my MTS componant

7. Permission Denied MTS Componant

8. Compiling VB (w/ MTS componant) and running it on another machine

9. Permission Denied MTS Componant

10. Using New to create instances of a class in MTS componant

11. Upgrade a componant on MTS?

12. A MTS componant client

 

 
Powered by phpBB® Forum Software