Created attachment 154973 [details]
stacktrace

> bolsover: Executing SQL that was expected to return ~1M very large number
> of results
Most of the heap is taken by byte[] instances, contained in com.microsoft.sqlserver.jdbc.TDSPacket objects.

Reassigning to db/Code. Please evaluate. Thank you.

Could you please add:

- structure of the queried tables
- the sql itself
- DB connection properties (jdbc URL, scrollable cursors activated)
- number of rows to be shown (the page size from netbeans display window)

The table structure and sql don't need to contain the "real" names if you need to keep the structure private.

Thank you!

I'm able to reproduce the problem with the following:

Product Version: NetBeans IDE 8.1 Beta (Build 201507302201)
Java: 1.8.0_51; Java HotSpot(TM) 64-Bit Server VM 25.51-b03
Runtime: Java(TM) SE Runtime Environment 1.8.0_51-b16
System: Windows 8 version 6.2 running on amd64; Cp1252; en_GB (nb)
Database Engine is SQLServer 2012 enterprise running on local machine.



/********************
SQL Query - BROKEN
*********************/
select * from inventory_trans;


/********************
SQL Query - OK
*********************/
select top 1000 * from inventory_trans;

/********************
Table create statement:
*********************/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[INVENTORY_TRANS](
	[ROWID] [int] IDENTITY(1,1) NOT NULL,
	[TRANSACTION_ID] [int] NOT NULL,
	[WORKORDER_TYPE] [char](1) NULL,
	[WORKORDER_BASE_ID] [varchar](30) NULL,
	[WORKORDER_LOT_ID] [varchar](3) NULL,
	[WORKORDER_SPLIT_ID] [varchar](3) NULL,
	[WORKORDER_SUB_ID] [varchar](3) NULL,
	[OPERATION_SEQ_NO] [smallint] NULL,
	[REQ_PIECE_NO] [smallint] NULL,
	[CUST_ORDER_ID] [varchar](15) NULL,
	[CUST_ORDER_LINE_NO] [smallint] NULL,
	[PURC_ORDER_ID] [varchar](15) NULL,
	[PURC_ORDER_LINE_NO] [smallint] NULL,
	[PART_ID] [varchar](30) NULL,
	[TYPE] [char](1) NOT NULL,
	[CLASS] [char](1) NOT NULL,
	[QTY] [decimal](14, 4) NOT NULL,
	[COSTED_QTY] [decimal](14, 4) NOT NULL CONSTRAINT [DF__INVENTORY__COSTE__46486B8E]  DEFAULT (0),
	[TRANSACTION_DATE] [datetime] NOT NULL,
	[WAREHOUSE_ID] [varchar](15) NULL,
	[LOCATION_ID] [varchar](15) NULL,
	[DESCRIPTION] [varchar](80) NULL,
	[USER_ID] [varchar](20) NULL,
	[POSTING_CANDIDATE] [char](1) NOT NULL CONSTRAINT [DF__INVENTORY__POSTI__473C8FC7]  DEFAULT (' '),
	[ADJ_REASON_ID] [varchar](15) NULL,
	[ACT_MATERIAL_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__INVENTORY__ACT_M__4830B400]  DEFAULT (0),
	[ACT_LABOR_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__INVENTORY__ACT_L__4924D839]  DEFAULT (0),
	[ACT_BURDEN_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__INVENTORY__ACT_B__4A18FC72]  DEFAULT (0),
	[ACT_SERVICE_COST] [decimal](15, 2) NOT NULL CONSTRAINT [DF__INVENTORY__ACT_S__4B0D20AB]  DEFAULT (0),
	[CREATE_DATE] [datetime] NOT NULL CONSTRAINT [DF__INVENTORY__CREAT__4C0144E4]  DEFAULT (getdate()),
	[ADD_BURDEN] [decimal](15, 2) NULL,
	[PIECE_COUNT] [decimal](14, 4) NULL,
	[LENGTH] [decimal](14, 4) NULL,
	[WIDTH] [decimal](14, 4) NULL,
	[HEIGHT] [decimal](14, 4) NULL,
	[DIMENSIONS_UM] [varchar](15) NULL,
	[TRANSFER_TRANS_ID] [int] NULL,
	[XFER_IN_TRANSIT] [char](1) NULL,
	[IBT_ID] [varchar](15) NULL,
	[IBT_LINE_NO] [smallint] NULL,
	[GL_ADJ_ACCT_ID] [varchar](30) NULL,
	[FROM_WBS_CODE] [varchar](30) NULL,
	[TO_WBS_CODE] [varchar](30) NULL,
	[WBS_CODE] [varchar](30) NULL,
	[DEPARTMENT_ID] [varchar](15) NULL,
	[COST_CATEGORY_ID] [varchar](15) NULL,
	[PROJ_REF_SEQ_NO] [smallint] NULL,
	[PROJ_REF_SUB_ID] [varchar](3) NULL,
	[REVISION_ID] [varchar](8) NULL,
	[STAGE_ID] [varchar](15) NULL,
	[ISSUE_REAS_ID] [varchar](15) NULL,
	[ACT_RATE_ID] [varchar](15) NULL,
	[ACT_PERIOD_ID] [varchar](15) NULL,
	[RETRO_RATE_ID] [varchar](15) NULL,
	[RETRO_PERIOD_ID] [varchar](15) NULL,
	[RETRO_RATE_CODE] [char](1) NULL,
	[LAST_RETRO_DATE] [datetime] NULL,
	[ORIG_TX_DATE] [datetime] NULL,
	[ORIG_TX_ID] [int] NULL,
	[AWS_COMMENT] [varchar](50) NULL,
 CONSTRAINT [PK_INVENTORY_TRANS] PRIMARY KEY CLUSTERED 
(
	[TRANSACTION_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/**********************
Table currently contains 3,805,423 records - sorry I can't provide that data!
**********************/

/**********************
Database connection - all default values
**********************/
<?xml version='1.0'?>
<!DOCTYPE connection PUBLIC '-//NetBeans//DTD Database Connection 1.2//EN' 'http://www.netbeans.org/dtds/connection-1_2.dtd'>
<connection>
  <driver-class value='com.microsoft.sqlserver.jdbc.SQLServerDriver'/>
  <driver-name value='Microsoft SQL Server 2012'/>
  <database-url value='jdbc:sqlserver://127.0.0.1:1433;databaseName=AWS653'/>
  <schema value='dbo'/>
  <user value='sa'/>
  <use-scrollable-cursors value='false'/>
</connection>

/**********************
Database driver
**********************/
<?xml version='1.0'?>
<!DOCTYPE driver PUBLIC '-//NetBeans//DTD JDBC Driver 1.1//EN' 'http://www.netbeans.org/dtds/jdbc-driver-1_1.dtd'>
<driver>
  <name value='SQL Server 2012'/>
  <display-name value='SQL Server 2012'/>
  <class value='com.microsoft.sqlserver.jdbc.SQLServerDriver'/>
  <urls>
    <url value='file:/D:/java/Libraries/Microsoft/sqljdbc_4.0/enu/sqljdbc4.jar'/>
  </urls>
</driver>

Thank you - that really helped! This is now reproducible.

Could you please check if your situation improves if you enable scrollable cursors? (Connection properties -> check "Use scrollable cursors")

I tested this with scrollable cursors enabled and the "simple" query works, while I managed to freeze my netbeans instance without scrollable cursors.

So while for you it might be the solution, other databases are a total fail with scrollable cursors. I'll have to think about this.

It's certainly different with scrollable cursors enabled - but not a solution.

I do now get some results (just 3929 rows)- but also an exception:

java.lang.OutOfMemoryError: GC overhead limit exceeded
	at java.lang.StringCoding$StringDecoder.decode(StringCoding.java:149)
	at java.lang.StringCoding.decode(StringCoding.java:193)
	at java.lang.String.<init>(String.java:414)
	at java.lang.String.<init>(String.java:479)
	at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:438)
	at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:2536)
	at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:193)
	at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:132)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2082)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2067)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getString(SQLServerResultSet.java:2392)
	at org.netbeans.modules.db.dataview.util.DBReadWriteHelper.readResultSet(DBReadWriteHelper.java:199)
	at org.netbeans.modules.db.dataview.output.SQLExecutionHelper.loadDataFrom(SQLExecutionHelper.java:788)
	at org.netbeans.modules.db.dataview.output.SQLExecutionHelper.access$800(SQLExecutionHelper.java:85)
	at org.netbeans.modules.db.dataview.output.SQLExecutionHelper$1Loader.run(SQLExecutionHelper.java:166)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
Caused: java.lang.RuntimeException
	at org.netbeans.modules.db.dataview.output.SQLExecutionHelper.initialDataLoad(SQLExecutionHelper.java:283)
	at org.netbeans.modules.db.dataview.output.DataView.create(DataView.java:118)
	at org.netbeans.modules.db.dataview.api.DataView.create(DataView.java:74)
[catch] at org.netbeans.modules.db.sql.execute.SQLExecuteHelper.execute(SQLExecuteHelper.java:116)
	at org.netbeans.modules.db.sql.loader.SQLEditorSupport$SQLExecutor.run(SQLEditorSupport.java:618)
	at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1443)
	at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:68)
	at org.openide.util.lookup.Lookups.executeWith(Lookups.java:303)
	at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2058)



Changing the netbeans.conf settings solves the problem - I previously had the install defaults of:

netbeans_default_options="-J-client -J-Xss2m -J-Xms32m -J-Dnetbeans.logger.console=true -J-ea -J-Dapple.laf.useScreenMenuBar=true -J-Dapple.awt.graphics.UseQuartz=true -J-Dsun.java2d.noddraw=true -J-Dsun.java2d.dpiaware=true -J-Dsun.zip.disableMemoryMapping=true -J-Dnetbeans.extbrowser.manual_chrome_plugin_install=yes"

Keeping scrollable cursors enabled, I changed this to:

netbeans_default_options="-J-server -J-Xss2m -J-Xms512m -J-Xmx4096m  -J-XX:PermSize=128m -J-XX:CompileThreshold=50 -J-Dsun.java2d.noddraw=true -J-Dsun.java2d.dpiaware=true -J-Dsun.zip.disableMemoryMapping=true -J-XX:+UseParallelGC -J-XX:+CMSClassUnloadingEnabled -J-XX:+CMSPermGenSweepingEnabled"

I now get results - 4 pages with a page size of 1,000,000.

Disabling scrollable cursors with the 'new' .conf settings, I get same error as before.

Changed -J-Xmx4096m to -J-Xmx8192m, disabled scrollable cursors - query returns results nicely - 4 pages of 1,000,000 rows. But if I ask for all 3,800,000 rows (not something I ever actually need to do) the problem returns.

*** Bug 253665 has been marked as a duplicate of this bug. ***

Created attachment 155305 [details]
Demo Data Generator for problem database

I can reproduce the problem outside netbeans:

1. Create a sample database
2. create a sample table (see comment #4) where bolsover kindly provided a sample
3. Run the attached sample to create demo data
4. To demonstrate the supposed cause:

        try (Connection con = DriverManager.getConnection("jdbc:sqlserver://192.168.56.102:1433;databaseName=AdventureWorksLT2008R2", "sa", "Pass@word1");
                Statement s = con.createStatement();) {
            s.execute("SELECT * FROM INVENTORY_TRANS");
            try (ResultSet rs = s.getResultSet()) {
                con.getMetaData().getPrimaryKeys("AdventureWorksLT2008R2", "dbo", "INVENTORY_TRANS");
                System.out.println("FETCHING");
                int i = 0;
                while (rs.next()) {
                    i++;
                    if (i % 1000000 == 999999) {
                        System.out.println(i);
                    }
                }
            }
        }

I was able to reproduce this with 2.5 Million rows and a Heap of 2GB (smaller heap reproduces this faster).

My asumption about what happens inside the jdbc driver:

Normally the sql driver streams the resultset to the client, and if the client "normally" works through the resultset, everything is fine. The big difference is the call to DatabaseMetaData#getPrimaryKeys in the sample, _after_ the resultset was opened. So now the server has a stream of data waiting to be received by the client. If the connection is not multiplexed to allow multiple command sequences to run in parallel, the datastream has to be consumed first. So the driver drains the data stream and buffers it, then it runs the metadata query and returns the data. Iterating over the resultset is now simply draining the buffer.

Apart from the sequence above in an sample heap dump I could see, that most of the heap (in that case it was 704MB of 725MB) was occupied by TDSPackets referencing each other. As this is not the case when the metadata call is commented out, I take this as support for my hypothesis.

As oracle also shows problems when metadata is queried (see https://netbeans.org/bugzilla/show_bug.cgi?id=179959#c4). The assessment from Jaroslav looks sane and just show a different approach to the problem (oracle seems to just drops the biggest chunk of the data on the server side).

I'll have to take another look at the Fetching process, but I think reversing loading data and fetching the metadata afterwards might be a workable approach.

Following the conclusions I implemented the change:

http://hg.netbeans.org/core-main/rev/0f53fbe28f35

Comment:

"Seperate extracting metadata for resultset from adding database metadata

There are multiple problems reported when reading database metadata while the resultset is open.

These problems manifest in invalidated (pointbase), partly invalidated (oracle) resultsets or out-of-memory errors because the whole resultset is buffered (mssql).

This change moves fetching database metadata after reading the resultset."


To be clear: This only fixes the problem when fetching the resultset. If you try to display large pages this will still fail.

The paging is in place to have a limit number of rows active, that occupy memory. If you try to buffer million of rows you will still see head-overflows. I'd strongly advise against page sizes larger than a thousand entries.

In the next few days a nightly build should be created and when that happens a message is appended here. Please test that nightly build and if you find the issue fixed, please change the status to verfied.

Looks as if I made a mistake when pushing the change, causing the availability of a nightly build not be advertised (the change has hit main-golden). So I checked the current nightly:

http://bits.netbeans.org/download/trunk/nightly/latest/

and this contains the change. It would be nice if you could give it a spin.

Product Version: NetBeans IDE Dev (Build 201508230002)
Java: 1.8.0_60; Java HotSpot(TM) 64-Bit Server VM 25.60-b23
Runtime: Java(TM) SE Runtime Environment 1.8.0_60-b27
System: Windows 8.1 version 6.3 running on amd64; Cp1252; en_GB (nb)
System has 16Gb ram

Default installation configuration.
netbeans_default_options="-J-client -J-Xss2m -J-Xms32m -J-Dnetbeans.logger.console=true -J-ea -J-Dapple.laf.useScreenMenuBar=true -J-Dapple.awt.graphics.UseQuartz=true -J-Dsun.java2d.noddraw=true -J-Dsun.java2d.dpiaware=true -J-Dsun.zip.disableMemoryMapping=true -J-Dplugin.manager.check.updates=false -J-Dnetbeans.extbrowser.manual_chrome_plugin_install=yes"

Database connection as before configured to use scrollable cursors.

Same query and database.

IDE becomes unresponsive for over 15 minutes - no results returned yet.

Windows task manager indicates IDE is using 89% CPU and 1,421Mb memory - forced NB to stop.

Edited default conf:

WAS: netbeans_default_options="-J-client -J-Xss2m -J-Xms32m -J-Dnetbeans.logger.console=true -J-ea -J-Dapple.laf.useScreenMenuBar=true -J-Dapple.awt.graphics.UseQuartz=true -J-Dsun.java2d.noddraw=true -J-Dsun.java2d.dpiaware=true -J-Dsun.zip.disableMemoryMapping=true -J-Dplugin.manager.check.updates=false -J-Dnetbeans.extbrowser.manual_chrome_plugin_install=yes"

NOW: netbeans_default_options="-J-server -J-Xss2m -J-Xms512m -J-Xmx4096m  -J-XX:PermSize=128m -J-XX:CompileThreshold=50 -J-Dsun.java2d.noddraw=true -J-Dsun.java2d.dpiaware=true -J-Dsun.zip.disableMemoryMapping=true -J-XX:+UseParallelGC -J-XX:+CMSClassUnloadingEnabled -J-XX:+CMSPermGenSweepingEnabled"

1,000,000 rows returned in about 1 minute.


So - whilst I do not see any errors, the IDE is unuseable unless the netbeans.conf file is edited as above.

I think some better guidance on how/when to edit the netbeans.conf file is needed!!

David

Created attachment 155591 [details]
demo

Hello bolsover, the difference may be that I used a much smaller page size than you. I limited my testing to pages size up to 100.000 rows. Please note, that that is the upper limit, so a reasonable page size is, depending on the row size, about 1000 rows per page.

There probably won't be a support for bigger page sizes.

The attached screenshot shows "the more than worst-case" scenario. In the background you can see, that the heap is filled, but still usable.

Yes- I understand that page size is at issue here and that I can set this AFTER retrieving some results. What I don't understand why my system defaults to 1M rows; is there a parameter I should be setting or is this NB default?

I see your problem - netbeans currently saves the last used row count. So to fix this - please run:

select TOP 1 * from INVENTORY_TRANS;

This will result in a resultset with just one row - now change the page size to 100 or something similar and hit the reload button left of the paging buttons.

Now you can remove the "TOP 1" part and this query should use the just entered page size.

Before you say it: yes this is not optimal and I'm already thinking about an alternative approach.