Devart Forums

motuzko

*without executing it*

motuzko

what's the oracle DB version you used?

motuzko

OracleLoader hangs posting lots (60 millions) of records with a batch size of 50000 in DIRECT mode.

The process hangs at random.

Table

Code: Select all

CREATE TABLE CRAW.LIMIT_USAGE_FACT
(
  SLICE_DATE    DATE                            NOT NULL,
  LIMIT_DEF_ID  NUMBER(9)                       NOT NULL,
  LIMIT_VER     NUMBER(9)                       NOT NULL,
  PL_PK         NUMBER(15)                      NOT NULL
)

Loading

Code: Select all

        String tmpSliceDateColumnName = 'SLICE_DATE';
        String tmpLimitDefIdColumnName = 'LIMIT_DEF_ID';
        String tmpLimitVerColumnName = 'LIMIT_VER';
        String tmpPlPkColumnName = 'PL_PK';

                #region Bulk store PlPks
                using (var tmpLoader = new OracleLoader(
                  $"CRAW.{tmpTempTableName}",
                  tmpOracleConnection))
                {
                  tmpLoader.CreateColumns();
                  tmpLoader.BatchSize = 50 * 1000;
                  tmpLoader.NotifyAfter = 50 * 1000;

                  Int32 tmpRowsPersisted = 0;

                  tmpLoader.RowsCopied += (inSender, inArgs) =>
                  {
                    tmpRowsPersisted += inArgs.RowsCopied;
                    tmpCurrentClassLogger.Debug($"Persist PlPks: {inArgs.RowsCopied:N0} rows copied ({tmpRowsPersisted:N0} so far)");
                  };

                  tmpLoader.Error += (inSender, inArgs) =>
                  {
                    if (Debugger.IsAttached)
                      Debugger.Break();

                    inArgs.Ignore = true;
                  };

                  foreach (OracleLoaderColumn tmpColumn in tmpLoader.Columns.Cast<OracleLoaderColumn>()
                    .ToList()
                    .Where(
                      inColumn => !new[]
                      {
                        tmpSliceDateColumnName,
                        tmpLimitDefIdColumnName,
                        tmpLimitVerColumnName,
                        tmpPlPkColumnName,
                      }.Contains(inColumn.Name)))
                    tmpLoader.Columns.Remove(tmpColumn);

                  tmpLoader.Open();
                  try
                  {
                    List<ProcessItem> tmpProcessItems;
                    lock (m_EnqueueLocker)
                    {
                      tmpProcessItems = m_ProcessItems.ToList();
                      m_ProcessItems.Clear();
                    }

                    List<LimitUsageFact> tmpLimitUsageFacts = tmpProcessItems.SelectMany(
                        inItem => inItem.PlPks,
                        (inItem, inPlPk) => new LimitUsageFact
                        {
                          SliceDate = SliceDate,
                          LimitDefId = inItem.LimitDefKey.LimitDefId,
                          LimitVer = inItem.LimitDefKey.LimitVer,
                          PlPk = inPlPk,
                        })
                      .ToList();

                    tmpCurrentClassLogger.Debug($"Persist PlPks: writing {tmpLimitUsageFacts.Count:N0} rows");

                    foreach (LimitUsageFact tmpLimitUsageFact in tmpLimitUsageFacts)
                    {
                      tmpLoader.SetValue(
                        tmpSliceDateColumnName,
                        tmpLimitUsageFact.SliceDate);
                      tmpLoader.SetValue(
                        tmpLimitDefIdColumnName,
                        tmpLimitUsageFact.LimitDefId);
                      tmpLoader.SetValue(
                        tmpLimitVerColumnName,
                        tmpLimitUsageFact.LimitVer);
                      tmpLoader.SetValue(
                        tmpPlPkColumnName,
                        tmpLimitUsageFact.PlPk);

                      tmpLoader.NextRow();
                    }
                  }
                  finally
                  {
                    tmpLoader.Close();
                    tmpCurrentClassLogger.Debug("Persist PlPks: done");
                  }
                }
                #endregion

motuzko

would it be possible to implement this functionality in the future?

motuzko

2 Scenarios:

1) IQueryable<T> to SQL + parameter values
2) a way to get a SQL for Min/Max(IQueryable<T> => int)

motuzko

In LinqConnect, is there a way to obtain a SQL with all the parameter values without executing it?

motuzko

Shalex wrote:Your model is huge, the queries are too complicated. We have analysed your project: there are no particular bottlenecks in our code which lead to the performance loses. This is a resource demanding task which requires redesigning LinqConnect ORM framework.

Sorry for resurrecting and old beast, but you said "Your model is huge, the queries are too complicated". How can I make the model simpler? What is the problem? Number of tables? Number of relationships between them? Number of fields?

motuzko

the installation went fine after uninstalling the build tools.
I am not eager to pursue this one, although I hope there must be a way to detect tools' presence.

motuzko

on other hand, postsharp installation refused to install; claimed build tools were unavailable. uninstalled the separate instance of build tools, postsharp installation went through.

motuzko

Yes, other vsix packages install fine.

motuzko

Installing 9.4.314 13-Jul-17 to VS 2017
Got 2 errors while installing the extensions.

Code: Select all

7/14/2017 12:52:07 PM - Microsoft VSIX Installer
7/14/2017 12:52:07 PM - -------------------------------------------
7/14/2017 12:52:07 PM - vsixinstaller.exe version:
7/14/2017 12:52:07 PM - 15.0.26430.15 built by: D15SVC
7/14/2017 12:52:07 PM - -------------------------------------------
7/14/2017 12:52:07 PM - Command line parameters:
7/14/2017 12:52:07 PM - C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\VSIXInstaller.exe,/v:15.0,/s:Enterprise,C:\Program Files (x86)\Devart\dotConnect\Oracle\Devart.Data.Oracle.VSProPackage.vsix
7/14/2017 12:52:07 PM - -------------------------------------------
7/14/2017 12:52:07 PM - Microsoft VSIX Installer
7/14/2017 12:52:07 PM - -------------------------------------------
7/14/2017 12:52:07 PM - Failed to initialize settings for Isolated Shell C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\ssms.exe: Unable to find an entry point named '?' in DLL 'PkgdefMgmt.dll'.
7/14/2017 12:52:07 PM - Initializing Install...
7/14/2017 12:52:07 PM - Extension Details...
7/14/2017 12:52:07 PM - 	Identifier         : Devart.Data.Oracle.VSProPackage.b897bbb8-3325-484d-9d86-d9a3a74205b0
7/14/2017 12:52:07 PM - 	Name               : Devart.Data.Oracle.VSProPackage
7/14/2017 12:52:07 PM - 	Author             : Devart
7/14/2017 12:52:07 PM - 	Version            : 1.0
7/14/2017 12:52:07 PM - 	Description        : Empty VSIX Project.
7/14/2017 12:52:07 PM - 	Locale             : en-US
7/14/2017 12:52:07 PM - 	MoreInfoURL        : 
7/14/2017 12:52:07 PM - 	InstalledByMSI     : False
7/14/2017 12:52:07 PM - 	SupportedFrameworkVersionRange : [4.5,)
7/14/2017 12:52:07 PM - 
7/14/2017 12:52:07 PM - 	SignatureState     : Unsigned
7/14/2017 12:52:07 PM - 	Supported Products : 
7/14/2017 12:52:07 PM - 		Microsoft.VisualStudio.Community
7/14/2017 12:52:07 PM - 			Version : [15.0]
7/14/2017 12:52:07 PM - 
7/14/2017 12:52:07 PM - 	References         : 
7/14/2017 12:52:07 PM - 		-------------------------------------------------------
7/14/2017 12:52:07 PM - 		Identifier   : Devart.Data.VSPackage.91b71a62-55a1-4e54-8bef-63f710c5e2a7
7/14/2017 12:52:07 PM - 		Name         : Devart.Data.VSPackage
7/14/2017 12:52:07 PM - 		Version      : [1.0,2.0)
7/14/2017 12:52:07 PM - 		MoreInfoURL  : 
7/14/2017 12:52:07 PM - 		Nested       : Yes
7/14/2017 12:52:07 PM - 
7/14/2017 12:52:07 PM - 		-------------------------------------------------------
7/14/2017 12:52:07 PM - 		Identifier   : Devart.Data.Oracle.VSStdPackage.7afb613f-415a-47cb-97a6-3a1e00b563a9
7/14/2017 12:52:07 PM - 		Name         : Devart.Data.Oracle.VSStdPackage
7/14/2017 12:52:07 PM - 		Version      : [1.0,2.0)
7/14/2017 12:52:07 PM - 		MoreInfoURL  : 
7/14/2017 12:52:07 PM - 		Nested       : Yes
7/14/2017 12:52:07 PM - 
7/14/2017 12:52:07 PM - 	Prerequisites      : 
7/14/2017 12:52:07 PM - 		-------------------------------------------------------
7/14/2017 12:52:07 PM - 		Identifier   : Microsoft.VisualStudio.Component.CoreEditor
7/14/2017 12:52:07 PM - 		Name         : Visual Studio core editor
7/14/2017 12:52:07 PM - 		Version      : [15.0,16.0)
7/14/2017 12:52:07 PM - 
7/14/2017 12:52:07 PM - Signature Details...
7/14/2017 12:52:07 PM - 	Extension is not signed.
7/14/2017 12:52:07 PM - 
7/14/2017 12:52:07 PM - System.IO.FileNotFoundException: C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\
   at System.Diagnostics.FileVersionInfo.GetVersionInfo(String fileName)
   at VSIXInstaller.SupportedVSSKU.get_VersionWithBuildRevisionInfo()
   at VSIXInstaller.App.<>c__DisplayClass45_0.<GetInstallableData>b__0(SupportedVSSKU sku)
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   at VSIXInstaller.App.GetInstallableData(String vsixPath, Boolean isRepairSupported, IEnumerable`1& skuData)
   at VSIXInstaller.App.Initialize(Boolean isRepairSupported)
   at VSIXInstaller.App.Initialize()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()

motuzko

Can you please guide me how to address it?
Should I change the way I query?
If I switch from LinqConnect to EF, would it perform better?
In other words, are there any ways to improve performance?

motuzko

Pinturiccio wrote:Thank you for the provided information. We are now working on creating a test environment with a user, who has PASSWORD_VERSIONS equal to 10G only, in order to reproduce and fix the issue. We will post here when we get any results.

Is there an ETA?

motuzko

Pinturiccio wrote:Thank you for the provided information. We are now working on creating a test environment with a user, who has PASSWORD_VERSIONS equal to 10G only, in order to reproduce and fix the issue. We will post here when we get any results.

Thank you so much!

motuzko

Thanks again, Edward, but I was addressing devArt.

Devart Forums

Search found 54 matches

Re: Obtaining a SQL/parameter values without executing it

Re: OracleLoader hangs posting lots (60 millions) of records with a batch size of 50000 in DIRECT mode

OracleLoader hangs posting lots (60 millions) of records with a batch size of 50000 in DIRECT mode

Re: Obtaining a SQL/parameter values without executing it

Re: Obtaining a SQL/parameter values without executing it

Obtaining a SQL/parameter values without executing it

Re: Performance issues with complicated SQLs

Re: Installation errors (VS 2017)

Re: Installation errors (VS 2017)

Re: Installation errors (VS 2017)

Installation errors (VS 2017)

Re: Performance issues with complicated SQLs

Re: ORA-01017: invalid username/password; logon denied

Re: ORA-01017: invalid username/password; logon denied

Re: ORA-01017: invalid username/password; logon denied