Low performance TkbmMWClientStoredProc with Sql Server

[Marco]

Good morning C4D,

We’re facing some performance issues with one of your components: TkbmMWClientStored

In fact, we use this component to call our SQL Stored Procedure and we see that it takes too much time to execute. So we check the event on the SQL database and we find something very strange.

When we “ExecProc”, the component seems to do more than just execute the stored procedure, for example, the list of statements of the call to “SP_LOAD_SHIPPING” :

exec SP_LOAD_SHIPPING default,default
exec [sys].sp_procedure_params_100_rowset N’SP_LOAD_SHIPPING’,1,NULL,>NULL
exec SP_LOAD_SHIPPING ‘test’,0
exec [sys].sp_procedure_params_100_rowset N’SP_LOAD_SHIPPING’,1,NULL,NULL
exec SP_LOAD_SHIPPING default,default
exec [sys].sp_procedure_params_100_rowset N’SP_LOAD_SHIPPING’,1,NULL,NULL
exec SP_LOAD_SHIPPING ‘test’,0
exec [sys].sp_procedure_params_100_rowset N’SP_LOAD_SHIPPING’,1,NULL,NULL
exec SP_LOAD_SHIPPING ‘test’,0

As you can see, 9 SQL calls have been done before executing the needed one. Do you know why ? And how to avoid these unneeded calls ?

Thanks

• This topic was modified 3 years ago by Marco.
• This topic was modified 3 years ago by Marco.

[Marco]

Sorry I forget to mention the version :

– kbmMW 4.11.0 Entreprise Edition

– kbmMemTable 7.23 Entreprise Edition

Thank you

[kimbomadsen]

Hi,

The reason is that kbmMW do not know the parameter definitions or the returned field definitions in advance, and have various ways to ask for that.

The default is to first ask for the parameter definitions from the database so you can fill them out.
That will result in the first call.
After that it will ask for the field definitions from the database as they can change based on your parameters. That will result in a 2nd call.
Then it will ask for the actual data from the database from the stored procedure. That is the 3rd call.

Then you may have a client side stored procedure component, which are doing exactly the same, which is then resulting in a flood of calls to the server.

To avoid that predefine parameters, and set AutoFieldDefsOnOpen to mwafoWithData or predefine fielddefs and set it to mwafoNever.

Do the same on the client side.

Further, if you do not predefine the fields/parameters on the client side and use mwafoNever, then you might want to enable cache on the connection pool for the client side, to handle some of the redundant calls to the server for definitions.

/Kim

[Marco]

Hello Kim,

 

Thanks for your help, I tried to put AutoFieldDefsOnOpen = mwafoNever on client and server side, the problem is that we don’t define fields on the server side (as you explained). This cause an exception on the client side (Parameter @TEST not found). To summarize what I’ve done :

Client side :  TkbmMWClientStoredProc.AutoFieldDefsOnOpen = mwafoNever (mwafoAlways before) -> Delete the first three calls to the server (no any more error)

Server side : TkbmMWADOXStoredProc.AutoFieldDefsOnOpen = mwafoNever (mwafoAlways before) -> Raise an exception (Parameters @TEST not found) on the client side.

 

When on the server side we had AutoFieldDefsOnOpen = mwafoAlways , I noticed that we had already this error on SQL Server side but not raised on the client side (so it was completely transparent for the user), now with mwafoNever , it’s not.

[kimbomadsen]

Try to set it to afoWithData.

Amd you may choose to do a FetchDefinitions for relevant server side StoredProc/Query components before first use. Then they will know what parameters are available.

/Kim

[Marco]

If I define  AutoFieldDefsOnOpen = mwafoWithData on client and server side, I have no issue BUT I have the same behavior (too much calls) as I said at the beginning of this post.

I put the code of my StoredProc, maybe I miss something :

Client side : 

object spLoadShipping: TkbmMWClientStoredProc
ClientAsTemplate = False
QueryService = ‘MYMSSQLQUERY’
QueryServiceVersion = ‘1.0’
CacheParams = ptUnknown
CacheFlags = []
SessionName = ‘MSSQL’
ConnectionPool = MyConnectionDM.MyToolClientConnectionPool
StoredProcName = ‘SP_LOAD_SHIPPING_TEST’
Overload = 0
Params = <
item
DataType = ftInteger
Name = ‘@RETURN_VALUE’
ParamType = ptResult
end
item
DataType = ftString
Name = ‘@TEST’
ParamType = ptInput
Value = ‘test’
end
item
DataType = ftInteger
Name = ‘@ERROR_ONLY’
ParamType = ptInput
Value = ‘0’
end>
TransportStreamFormat = MyConnectionDM.MyToolBinaryStreamFormat
FetchLargeFieldsOnDemand = False
LargeFieldSize = 256
FetchMaxRecords = 0
KeyFields = ‘SHP_DIEZ’
TableName = ‘SHIPPING’
OnResolveError = spLoadShippingResolveError
AutoResolveOnChange = False
AutoResolveOnClose = False
AttachedAutoRefresh = True
AttachMaxCount = 1
AutoFieldDefsOnOpen = mwafoNever
FieldDefs = <
item
Name = ‘SHP_DIEZ’
DataType = ftString
Size = 10
end
item
Name = ‘SHP_NUM_DIEZ’
DataType = ftInteger
end>
IndexDefs = <>
SortOptions = []
PersistentBackup = False
ProgressFlags = [mtpcLoad, mtpcSave, mtpcCopy]
LoadedCompletely = True
SavedCompletely = False
EnableVersioning = True
FilterOptions = []
Version = ‘7.23.00 Professional Edition’
LanguageID = 0
SortID = 0
SubLanguageID = 1
LocaleID = 1024
BeforePost = spLoadShippingBeforePost
OnCalcFields = spLoadShippingCalcFields
Left = 28
Top = 16
object spLoadShippingSHP_DIEZ: TStringField
FieldName = ‘SHP_DIEZ’
ProviderFlags = [pfInWhere]
Size = 10
end
object spLoadShippingSHP_NUM_DIEZ: TIntegerField
FieldName = ‘SHP_NUM_DIEZ’
ProviderFlags = []
end
end

Server Side : 

object MyMSSQLStoredProc: TkbmMWADOXStoredProc
SessionName = ‘MYMSSQL’
ConnectionPool = MyServerDataModule.MyMSSQLConnectionPool
CacheParams = ptUnknown
CacheFlags = []
AutoFieldDefsOnOpen = mwafoNever
Resolver = MyMSSQLResolver
KillConnectionOnException = True
DesignActivation = True
AttachMaxCount = 1
AttachedAutoRefresh = True
EnableVersioning = True
IndexDefs = <>
SortOptions = []
ProgressFlags = [mtpcLoad, mtpcSave, mtpcCopy]
FilterOptions = []
LanguageID = 0
SortID = 0
SubLanguageID = 1
LocaleID = 1024
Params = <>
LockType = ltUnspecified
end

 

Maybe it’s also because we don’t have the latest version of KbmMW ? I’m quite lost, if I try the test with an other component like the native ADOStoredProc, I have only one call on SQL Server database.

 

[Marco]

Or maybe you can give me documentation about it…

[kimbomadsen]

Hi,

Visit: https://blog.csdn.net/qq_18932003/article/details/103253217

You may need Google Translate as it is a Chinese article.

/Kim

 

 

[Author]

Posts