magento get sql profile

http://inchoo.net/ecommerce/magento/magento-debugging/extending-default-magento-profiler/

Here is a little tweak you can use to extend the information being displayed under Profiler.

Remember, besides enabling the Profiler from Magento Admin > System > Configuration, one should also uncomment the Varien_Profiler::enable(); line from the index.php file inside the Magento root folder.

We will need to change two files in order to extend default Profiler output.

First file in question is app/code/core/Mage/Core/Model/Resource.php. Look for methodgetConnection($name). Should be around line 69. Inside this method, you should be able to see the$conn = $typeInstance->getConnection($connConfig); around line 85. Since $conn is of typeVarien_Db_Adapter_Pdo_Mysql, we can write down the following right after the $conn definition: $conn->getProfiler()->setEnabled(true);. Your modified method should look something like:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

publicfunction getConnection($name)

{

    if(isset($this->_connections[$name])) {

        return$this->_connections[$name];

    }

    $connConfig= Mage::getConfig()->getResourceConnectionConfig($name);

    if(!$connConfig|| !$connConfig->is('active', 1)) {

        returnfalse;

    }

    $origName= $connConfig->getParent()->getName();

    if(isset($this->_connections[$origName])) {

        return$this->_connections[$origName];

    }

    $typeInstance= $this->getConnectionTypeInstance((string)$connConfig->type);

    $conn= $typeInstance->getConnection($connConfig);

    /** START Custom added, not part of the core MAGE */

    $conn->getProfiler()->setEnabled(true);

    /** END Custom added, not part of the core MAGE */

    $this->_connections[$name] = $conn;

    if($origName!==$name) {

        $this->_connections[$origName] = $conn;

    }

    return$conn;

}

Just by doing so, you should be able to see first results on any Magento page you refresh. Below is the sample screenshot showing result of this minor change.

Notice the part that is added to output.

Let’s continue extending the profiler. Open the lib/Varien/Profiler.php file. Be sure to create a backup of this file (or you can always download Magento from official site and restore file from there). Here you will find public static function getSqlProfiler($res) method. We will change this method in order to get more flexible and way more informative output then the one from step one. We need to change the behaviour offoreach loop inside the method to include extra stuff. Here is my final, modified, method (with a bit of styling to visually improve output)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

publicstatic function getSqlProfiler($res) {

    if(!$res){

        return'';

    }

    $out= '';

    $profiler= $res->getProfiler();

    if($profiler->getEnabled()) {

        $totalTime   = $profiler->getTotalElapsedSecs();

        $queryCount  = $profiler->getTotalNumQueries();

        $longestTime = 0;

        $longestQuery= null;

        $extra= '';

        $extra.=     '<style type="text/css">

                    .queryInfoDetails {

                        border-collapse:collapse;

                        border-top:solid 3px #E1E6FA;

                        font-family:"Lucida Sans Unicode","Lucida Grande",Sans-Serif;

                        font-size:12px;

                        text-align:left;

                        width:680px!important;

                        text-align:left;

                        margin:30px auto;

                    }

                    .queryInfoDetails th {

                        color:#003399;

                        font-size:14px;

                        font-weight:normal;

                        padding:5px;

                        border-top:1px solid #E8EDFF;

                    }

                    .queryInfoDetails tr:hover, .queryInfoDetails tr:hover td {

                        background:#EFF2FF none repeat scroll 0 0;

                        color:#333399;

                    }

                    .queryInfoDetails td {

                        border-top:1px solid #E8EDFF;

                        color:#666699;

                        padding:5px;

                    }

                </style>';

        $counter= 0;

        foreach($profiler->getQueryProfiles()as $query) {

        /** @var $query Zend_Db_Profiler_Query */

        $queryParams= $query->getQueryParams();

        $params= 'none';

        if(!empty($queryParams)) { $params = print_r($queryParams,1); }

        $queryType= (int)$query->getQueryType();

        switch($queryType) {

            case1:

                $queryType= 'CONNECT';

                break;

            case2:

                $queryType= 'QUERY';

                break;

            case4:

                $queryType= 'INSERT';

                break;

            case8:

                $queryType= 'UPDATE';

                break;

            case16:

                $queryType= 'DELETE';

                break;

            case32:

                $queryType= 'SELECT';

                break;

            case64:

                $queryType= 'TRANSACTION';

                break;

        }

        $extra.=     '<table class="queryInfoDetails"cellpadding="0"cellspacing="0">

                        <tr><th>Query no.</th><td>'.++$counter.'</td></tr>

                        <tr><th>Query type</th><td>'.$queryType.'</td></tr>

                        <tr><th>Query params</th><td>'.$params.'</td></tr>

                        <tr><th>Elapsed seconds</th><td>'.$query->getElapsedSecs().'</td></tr>

                        <tr><th>Raw query</th><td>'.wordwrap($query->getQuery()).'</td></tr>

                    </table>';

        if($query->getElapsedSecs() >$longestTime) {

            $longestTime = $query->getElapsedSecs();

            $longestQuery= $query->getQuery();

        }

        }

        $out.= 'Executed '. $queryCount. ' queries in '. $totalTime. ' seconds'. "<br>";

        $out.= 'Average query length: '. $totalTime/ $queryCount. ' seconds'. "<br>";

        $out.= 'Queries per second: '. $queryCount/ $totalTime. "<br>";

        $out.= 'Longest query length: '. $longestTime. "<br>";

        $out.= 'Longest query: <br>'. $longestQuery. "<hr>";

    }

    $out.= $extra;

    return$out;

}

And here is the screenshot of the final result.