View Issue Details

Jump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0005875OXID ERP InterfaceOXID ERP Interface - subpublic2014-09-10 13:412015-03-17 14:58
Reportermichael_keiluweit Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Product Version2.13.0 
Target Version2.14.0Fixed in Version2.14.0 
Summary0005875: Low perfomance when using view tables (OXERPGetArticles)
DescriptionWhen the table oxarticles has more than ~5.000 records, the query from the ERP function OXERTPGetArticles needs some time to be completed.
Steps To ReproduceCreate more than 5.000 articles.
Do the call OXERPGetArticles (0, 100).
Wait.
TagsArticle, ERP, Performance, SOAP

Relationships

related to 0005867 resolvedSven Brunk Wrong variable naming for $iShopId 
related to 0005987 resolvedjurate.baseviciene Too many tables in a join in get methods 

Activities

michael_keiluweit

2014-09-10 13:52

administrator   ~0010139

Last edited: 2014-09-10 14:09

 Perfomace increased by adapting two Methods:


oxERPType_Article::getSQL()

    public function getSQL( $sWhere, $iLanguage = 0, $iShopId = 1)
    {
        if ( !$this->_aFieldList) {
            return;
        }

        $sSQL = 'select ';
        $blSep = false;

        foreach ( $this->_aFieldList as $sField) {
            if ( $blSep) {
                $sSQL .= ',';
            }

            $sSQL .= $this->_getSqlFieldName($sField, $iLanguage, $iShopId);
            $blSep = true;
        }

        $oCompat = oxNew( "OXERPCompatibility");
        if ($oCompat->isShopEE()) {
            if ($this->_blRestrictedByShopId) {
                if ( strstr( $sWhere, 'where')) {
                    $sWhere .= ' and ';
                } else {
                    $sWhere .= ' where ';
                }

                $sWhere .= '`a`.`oxshopid` = \''.$iShopId.'\'';
            }
        }

        $sSQL .= ' from '.$this->getTableName($iShopId, $iLanguage) . ' AS `a` ';
        if ($oCompat->isShopEE() && oxConfig::getInstance()->getConfigParam( 'blMallCustomPrice' )) {
            $sSQL .= ' LEFT JOIN oxfield2shop AS `f2s` ON `f2s`.`oxartid` = `a`.`oxid`';
            $sSQL .= " AND `f2s`.`oxshopid` = '" . $iShopId . "'";
        }
        $sSQL .= ' LEFT JOIN oxartextends AS `ax` ON `ax`.`oxid` = `a`.`oxid`';
        $sSQL .= ' ' . $sWhere;


        return $sSQL;
    }


oxERPType_Article::_getSqlFieldName()

    protected function _getSqlFieldName($sField, $iLanguage = 0, $iShopID = 1)
    {
        $oCompat = oxNew("OXERPCompatibility");
        if ($oCompat->isShopPE()) {
            switch ($sField) {
                case 'OXORDERINFO':
                    return "'' as $sField";
                case 'OXPIXIEXPORTED':
                    return "'0000-00-00 00:00:00' as $sField";
                case 'OXPIXIEXPORT':
                    return "'0' as $sField";
                case 'OXVPE':
                    return "'1' as $sField";
            }
        }

        switch ($sField) {
            // oxlongdesc is valid in all versions
            case 'OXLONGDESC':
            case 'OXLONGDESC_1':
            case 'OXLONGDESC_2':
            case 'OXLONGDESC_3':
                // take from oxartextends
                return '`ax`.'.$sField;
        }

        if ('1' == oxERPBase::getUsedDbFieldsVersion()) {
            switch ($sField) {
                case 'OXAKTION':
                case 'OXSEOID':
                case 'OXSEOID_1':
                case 'OXSEOID_2':
                case 'OXSEOID_3':
                    return "'' as $sField";
                case 'OXACTIV':
                    return "OXACTIVE as OXACTIV";
                case 'OXACTIVFROM':
                    return "OXACTIVEFROM as OXACTIVFROM";
                case 'OXACTIVTO':
                    return "OXACTIVETO as OXACTIVTO";
            }
        }

        if ($oCompat->isArticleRemindActiveFieldFixed()) {
            switch ($sField) {
                 case 'OXREMINDACTIV':
                    return "OXREMINDACTIVE as OXREMINDACTIV";
            }
        }

        if ($oCompat->areArticleZoomPicsRemoved()) {
            if (preg_match('/oxzoom[0-9]+/i', $sField)) {
                return "'' as $sField";
            }
        }

        if ($oCompat->isPicsGeneratedFieldRemoved()) {
            if ( 'OXPICSGENERATED' == $sField ) {
                return "'' as $sField";
            }
        }

        if ($oCompat->isShopEE() && oxConfig::getInstance()->getConfigParam( 'blMallCustomPrice' ) ) {
            $aField2ShopFields = $this->_getMultishopArticleFields();
            if ( in_array( $sField, $aField2ShopFields ) ){
                return " IFNULL(`f2s`.`$sField`, `a`.`$sField`) as `$sField`";
            }
        }

        if ($oCompat->isShopPE() && in_array($sField, array('OXSHOPID', 'OXSHOPINCL', 'OXSHOPEXCL'))) {
            $sField = parent::_getSqlFieldName($sField, $iLanguage, $iShopID);
        } else {
            $sField = '`a`.'. parent::_getSqlFieldName($sField, $iLanguage, $iShopID);
        }

        return $sField;
    }