Export data, fast method

Yii 2.0 General Discussions
1

Hi!

I have a problem with export data from grid view to excel file.

My table has only 1500 records(join 4 big tables) and it during above 30seconds.

I’m trying ExportMenu and ExcelView widght but always time is >30sec.

This extension should provide export with filters values.

What should I do to get a positive result?

Thanks for help!

Daniel

2

Have you checked how much time sql query need to get data?

This is important to understand if problem is in sql query or exporting excel.

Tips: if you haven’t done, make foreign keys as INDEX in database table.

3

When I have dataProvider how can I display a SQL query?

I think problem is in SQL because gridView have a pagination and for each page make : select … join join join

It cost too much time.

How prepare dateProvider in ExportMenu to use filters and export full table?

4

I try optimize a query to database:

/* 1 query: 0,203 sec. (+ 0,672 sec. network) */




    $query = Model1::find();

    $query2 = Model2::find()->groupBy('ID');

    	$query3 = Model3::find()->groupBy('ID');

    	$query4 = Model4::find();

    	

    $query->leftJoin([

    			'model2' => $query2

    	], 'model2.ID = model1.ID');


    	$query->leftJoin([

    			'model3' => $query3

    	], 'model3.ID = model1.ID');

    	

    $query->leftJoin([

    			'model4' => $query4

    	], 'model4.ID = model1.ID');


    	

        $dataProviderExcel = new ActiveDataProvider([

            'query' => $query,

        ]);

I can’t find what cause a problem and I don’t known how optimize query.

How can I once SELECT all or SELECT using filters to xls.

dataProvider for each field make select many times it’s too many queries.

Should I turn off a pagination?

Thank for any help!