This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

Bug 262457 - Request SQL profiler to roll up same queries with different bound values
Summary: Request SQL profiler to roll up same queries with different bound values
Status: NEW
Alias: None
Product: profiler
Classification: Unclassified
Component: Engine (show other bugs)
Version: 8.2
Hardware: PC Linux
: P3 normal with 1 vote (vote)
Assignee: Tomas Hurka
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-06-16 16:07 UTC by chunkyks
Modified: 2016-08-06 16:24 UTC (History)
0 users

See Also:
Issue Type: ENHANCEMENT
Exception Reporter:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description chunkyks 2016-06-16 16:07:43 UTC
Example here: https://imgur.com/3a8PkDj

I have a lot of code where I create a PreparedStatement from one query, then bind and step it many times, with differing parameter values. For example in the screenshot, all those lines are from the code below.

This happens whether I'm re-using the same prepared statement, or if I keep preparing fresh ones.

It would be nice if there was a way to say that this is ten thousand invocations of the SQL query "INSERT OR IGNORE INTO rtam_node(x,y,lat,lon) VALUES (?,?,?,?)". [like the "normal" call profiler]

I imagine that sometimes I would still like to split it so I can identify if a particular set of parameters is slow, but my general desired use would focus on the parameterised SQL, similar to the call profiler.

Extra aside: I think that what I'd also like is a checkbox alongside "Profile: All Queries/Defined Queries" in the settings. The checkbox would do something like "Just accumulate same-SQL queries, throw away bind information". Netbeans uses a *lot* of memory while I'm running the SQL Profiler, and can bog down a fair bit. This feature may make everything faster and use less memory?

Example code to generate the screenshot above, using sqlite jdbc 3.8.11.jar from here: https://bitbucket.org/xerial/sqlite-jdbc/downloads :

    public static void main(String[] args) throws SQLException {
        Connection dbConn = DriverManager.getConnection("jdbc:sqlite::memory:");
        
        dbConn.setAutoCommit(false);
        Statement stmt = dbConn.createStatement();
        
        Random rng = new Random(42l);
        stmt.execute(
            "CREATE TABLE IF NOT EXISTS rtam_node ("
                + " rtam_nodeid INTEGER PRIMARY KEY,"
                + " x INTEGER NOT NULL,"
                + " y INTEGER NOT NULL,"
                + " lat REAL NOT NULL,"
                + " lon REAL NOT NULL,"
                + " UNIQUE(x,y)"
                + ")"
            );
        dbConn.commit();
        
        try(PreparedStatement pstmt = dbConn.prepareStatement("INSERT OR IGNORE INTO rtam_node(x,y,lat,lon) VALUES (?,?,?,?)")) {
            for(int x = 0; x < 10; x++) {
                for(int y = 0; y< 10; y++) {
                    pstmt.setInt(1, x);
                    pstmt.setInt(2, y);
                    pstmt.setDouble(3, rng.nextDouble() * 180.0 - 90.0);
                    pstmt.setDouble(4, rng.nextDouble() * 360.0 - 180.0);
                    pstmt.executeUpdate();
                }
            }
        }
        dbConn.commit();
    }
Comment 1 chunkyks 2016-08-06 16:24:31 UTC
I wanted to add one note, here: this request is essentially "make the SQL profiler output be more equivalent to the other code profilers"

eg, the Java profiler doesn't provide a record of every function call, broken down by parameter values to the function; it just shows me "this function was executed a million times".

The SQL profiler has been immensely useful to me so far, identifying optimisation problems "in the small" in my code, eg pointing out some individually slow queries. But now I'm at the point where I need a broader view, identifying which queries are actually the impactful ones on my overall runtime, which the profiler right now doesn't cover.