Subscribe to this thread
Home - General / All posts - Performance with Oracle
rk
621 post(s)
#14-Aug-19 15:27

Hmm,

This is a remote database.

With M9 169.7

> !fullfetch

fullfetch on

> select * from [remoteoracle]::[OTHER.TABLE_2]

55.710 sec (10,491 records, 690.1 KB)

> select * from [remoteoracle]::[THIS.TABLE]

11.464 sec (2,022 records, 518.6 KB)

> select * from [remoteoracle]::[THIS.TABLE]

11.035 sec (2,022 records, 518.6 KB)

Oracle SQL Developer:

All rows fetched: 10491 in 2.893 seconds

All rows fetched: 2022 in 0.75 seconds

OTHER.TABLE_2 has no unique index, THIS.TABLE has unique index.

Dimitri


7,413 post(s)
#14-Aug-19 19:41

Three things I can think of that might make the comparison you cite an apples to oranges comparison...

First, are you running that query in Manifold native SQL or executing it within Oracle? Try it both ways and see the time (both without !fullfetch on). See the Example: Switching between Manifold and Native Query Engines topic.

Second idea: from the Log Window topic:

Caution:Enabling !fullfetchcan dramatically reduce performance. Do not use !fullfetch with large jobs.

Is there an equivalent directive you can issue to Oracle SQL Developer to reduce performance there, to make an apples to apples comparison?

Third idea: when you do SELECT * in Manifold, you are actually downloading 10,491 records or 2,022 records through the network, from your Oracle server into your desktop machine running Manifold.

I could be wrong about this, but to the best of my knowledge Oracle's SQL Developer isn't actually downloading 10,491 records or 2,022 records into your local desktop when it says "All rows fetched." It could be it is just leaving them up in the Oracle server, using the word "fetched" to mean "pulled out of the table, ready to go on the server."

I guess you might be able to test that by doing a SELECT in Oracle SQL Developer in a way that actually scrolls through a display of all 10,491 records or 2,022 records. I wouldn't be surprises if it took more than 0.75 seconds for Oracle SQL Developer to SELECT and to display, say, 2,022 records. Or, perhaps, to SELECT into a local CSV file.

Keep in mind that when Manifold is executing something within Oracle, it's using Oracle facilities, so the timings should be similar.

adamw


10,447 post(s)
#15-Aug-19 07:58

Dimitri is correct in that we are fetching Oracle data using Oracle facilities. But we might be using them differently from Oracle SQL Developer, so performance differences like this are always worth investigating.

First, I take it Oracle SQL Developer and Manifold 9 are using the exact same client libraries on the exact same machine, correct? If that's not the case, this should be done first.

More to the point, what are the field types? Are there any geometry fields or any binary fields? If there are some, how do the times change when you alter the SELECT to exclude them? (Does the performance difference grow or shrink?)

rk
621 post(s)
#15-Aug-19 12:49

d

I could be wrong about this, but to the best of my knowledge Oracle's SQL Developer isn't actually downloading 10,491 records or 2,022 records into your local desktop when it says "All rows fetched."

When it says "All rows fetched", it has downloaded them all. I issue 'select * from t'. It shows first 50 rows in grid view.

[Fetched 50 rows in 0.017 seconds]

(Quickly) In grid view I press Ctrl-End, it scrolls through all data. The table remains fully scrollable.

[All rows fetched: 10491 in 3.32seconds]

First, I take it Oracle SQL Developer and Manifold 9 are using the exact same client libraries on the exact same machine, correct? If that's not the case, this should be done first.

Oracle SQL Developer is Java application uses its own way, not oci.dll afaik.

I tried with Navicat Essentials for Oracle that uses the exact same c:\instantclient_19_3\oci.dll as Manifold does.

Issuing 'select * from t' brings all data from t into grid view, freely scrollable. Query time 1.6 sec for 10000 rows and 0.4 sec for 2022 rows.

There are no binary or geometry fields.

CREATE TABLE T2022 

   ( F_ID NUMBER PRIMARY KEY , 

 ID NUMBER NOT NULL

 x VARCHAR2(20 CHAR), 

 x VARCHAR2(30 CHARNOT NULL

 CHAR(1 BYTENOT NULL

 x VARCHAR2(30 CHAR), 

 x NUMBER(4,0), 

 DATE

 x NUMBER(4,0), 

 DATE

 x NUMBER, 

 x NUMBER, 

 x NUMBER, 

 x NUMBER, 

 x VARCHAR2(100 CHAR), 

 CHAR(1 BYTE), 

 CHAR(1 BYTE), 

 CHAR(1 BYTE), 

 x NUMBER(3,0), 

 x NUMBER DEFAULT 0, 

 x NUMBER DEFAULT 0, 

 CHAR(1 BYTE), 

 CHAR(1 BYTE), 

 CHAR(1 BYTE), 

 CHAR(1 BYTE), 

 CHAR(1 BYTE), 

 CHAR(1 BYTE), 

 CHAR(1 BYTE), 

 CHAR(1 BYTE), 

 CHAR(1 CHAR), 

 CHAR(1 CHAR), 

 CHAR(1 CHAR)

)

; 

  

  

CREATE TABLE T10491

(

  ID NUMBER(10, 0) NOT NULL 

, x NUMBER(10, 0) 

, x VARCHAR2(80 CHAR

, x DATE 

, x VARCHAR2(4 CHAR

, x VARCHAR2(1 CHAR

)

  

adamw


10,447 post(s)
#15-Aug-19 12:50

OK, thanks, we'll do some tests then.

rk
621 post(s)
#15-Aug-19 13:03

Manifold 8 imports those 10K rows in ~3 secs.

Dimitri


7,413 post(s)
#15-Aug-19 13:08

Interesting. How fast without !fullfetch?

rk
621 post(s)
#15-Aug-19 15:47

Without !fullfetch the first rows start to appear after 1/10th of a second, both !native and !manifold. But then they keep coming very slowly.

I did some tests with M8, Navicat, SQL Developer and M9.

Table: 150,000 rows, 20 columns, including area geom with 163 coords in average. Full import into M8 took about 2min.

Selecting 4 numeric columns from that table took:


Manifold 8.0.30 8.1-8.9sec -- (linking a query in db Console, close/open project, select * from [linked-table], no refresh on start))

Navicat 12.1 16-18sec

SQL Developer 18.4 37-40sec (Ctrl+End)

M9 -- rows kept coming, cancelled.


Restricting that query on non-unique indexed column (precalculated area < X) leaving just 7553 rows.


Manifold 8.0.30 0.45-0.52sec -- (linking a query in db Console, close/open project, select * from [linked-table], no refresh on start)

Navicat 12.1 0.85-1.1sec

SQL Developer 18.4 1.8-1.9sec (Ctrl+End)

M9 -- !fullfetch Oracle !native 38.984 sec (7,553 records, 162.3 KB)


Manifold (8) rocks!

adamw


10,447 post(s)
#16-Aug-19 12:45

This is very helpful, thanks.

We are looking into it. (So far we were not able to reproduce the problem, 9 performs much faster in our tests than in yours, but we just started and have a lot of things to try.)

adamw


10,447 post(s)
#16-Aug-19 15:09

A follow up.

We have not been able to reproduce it despite trying a number of things.

9 performs slightly slower than SQL Developer in our tests, but the difference is not big, tests where 9 is looking the worst are:

Test 1. 150,000 records, multiple fields, selecting a single numeric field. SQL Developer: ~3 sec. 9: ~4 sec. The difference is the overhead in 9 for the background thread that provides the ability to cancel the query, and for similar things.

Test 2. 150,000 records, multiple fields, selecting ~15 fields including geometry. SQL Developer: ~10 sec. 9: ~14 sec. The difference is as above plus parsing geometry values, which SQL Developer does not do and 9 does.

Also, the difference between the SELECT executed by the Manifold query engine (!manifold) and the SELECT executed by the Oracle query engine (!native) from inside 9 is pretty much non-existent.

We have been using the exact same client as you do: 19.3, the latest.

What is the version of the database? (Which version of Oracle is it?) Anything special in the configuration? Do the times reproduce on a different machine?

PS: Yes, we see that the numbers for 8 are great. No idea why. The code isn't the same, but the differences are in favor of 9, not 8, and that's confirmed by tests, too. Save for things like background threads again, which can sometimes skew the numbers to make 8 faster, but not by much. In short, something is wrong.

rk
621 post(s)
#16-Aug-19 18:51

Thank you. I will try on different machine with viewer.

rk
621 post(s)
#19-Aug-19 08:18

The dbms is on client site. I use my corporate laptop to connect over VPN. I do not see antivirus kicking in, but something is clearly wrong with my setup that seems to affect (only) M9 (latest portable and same with installed Viewer 169.0)

On client side, I asked them try with latest viewer 169.7 and they got

0.257 sec (2,022 records, 518.6 KB)

whereas I got

13.137 sec (2,022 records, 518.6 KB)

With other apps it is still under a second on my machine too.

adamw


10,447 post(s)
#19-Aug-19 08:52

Thanks, this helps.

This seems to be related to the configuration of the connection. Dramatic slowdowns on default settings like that are actually pretty common because there are tons of things involved and it only takes one thing for which the default does a bad job to bottleneck everything. What is likely to help in this case is increasing the batch size for queries, to fetch 2,022 records in fewer chunks than they are being fetched now. Normally, this is configured in the layer below one at which we operate - eg, in this case, in TNSNAMES.ORA on the client, and you might need to configure an allowance for the batch size to go higher than it is now on the server. But there might be calls we might make from our side as well - we'll check and if there's anything worth configuring from the OCI side, we'll expose that as a connection string option.

PS: One other option you might consider is to try switching the way you connect hoping that a different way would provide work better without additional configuration - eg, you can try switching to the ODBC driver. But that's a worse option than getting the OCI connection working well.

adamw


10,447 post(s)
#19-Aug-19 12:52

A follow up - we think we found what we can do safely for OCI without compromising the performance of queries on connections that don't have high latency. Let's revisit this whole issue after our next build. Given the numbers you report for 8, you probably won't have to change anything in your configuration to make numbers for 9 much better than they currently are for your connection.

Dimitri


7,413 post(s)
#15-Aug-19 13:25

When it says "All rows fetched", it has downloaded them all.

Forgive me for doubting, but a quick web search indicates the above is not so, at least not by default. Instead, there are pages likethis one that report:

SQL Developer doesn’t show you all records, all at once. Instead, it brings the records down in ‘chunks,’ or as-needed

I'm not an Oracle guy, but for years I've heard they really do fetch only those chunks that are needed. This also is something that came up when people were getting worried that Manifold pulled only the first 50,000 records from big tables for an initial display, so I did some research to see what other DBMS packages did by default, discovering Oracle typically pulled only 50 to 200 records, etc.

So if you look at a table and jump to the End, it only sends you the records that are needed to show the end with an SQL array fetch size typically between only 50 and 200 records. If this is done cleverly (and Oracle does it very cleverly) it can give you the impression it has downloaded all the records.

There are also pages, like this one on stackexchange, that explicitly state SQL Developer does not actually download all the records in the results table. As that link notes, there are hacks around it, but just running a SELECT * FROM... isn't one of those hacks. I'm interested in the details, to remove any apples to oranges issues.

What is the SQL array fetch size set in your instance of SQL Developer?

Also, could you answer my question if, within the query you launch in the command window, you are running a query using the Manifold engine? or running a query using the Oracle engine?

rk
621 post(s)
#16-Sep-19 10:26

It is fine now :-)

I'm on different location and different network (wireless, no VPN) but anyway.

fullfetch on

select * from  [remoteoracle]::[TABLE]

  0.234 - 0.312 sec (2,025 records, 519.5 KB) # SQL: manifold

  0.284 - 0.350 sec (2,025 records, 519.5 KB) # SQL: oracle

  0.429 - 0.630 sec                           # Oracle SQL Developer 

select * from [remoteoracle]::[TABLE_2]

  0.648 - 0.882 sec (10,491 records, 690.1 KB) # SQL: manifold

  0.815 - 0.818 sec (10,491 records, 690.1 KB) # SQL: oracle

  1.917 - 2.850 sec                            # Oracle SQL Developer 

select a_number from [remoteoracle]::[TABLE150K]

  13.123 sec (149,927 records, 1.1 MB) # SQL: manifold 

  5.998 sec (149,927 records, 1.1 MB)  # SQL: oracle

  13.109 sec                           # Oracle SQL Developer

 

select a, b, c, d from [remoteoracle]::[TABLE150K] where [AREA] < 4500

  8.277 sec (7,552 records, 162.2 KB) # SQL: manifold    (fullfetch effect?)

  0.413 sec (7,552 records, 162.2 KB) # SQL: oracle

  0.755 sec                           # Oracle SQL Developer

select * from TABLE150K

  -                                       # SQL: manifold 

  602.771 sec (149,927 records, 393.8 MB) # SQL: oracle

  620.676 sec                             # Manifold 8  (was ~120 sec last time on wired connection)

  -                                       # Oracle SQL Developer 

select * from TABLE150K where AREA < 4500

  cancelled, too long                # SQL: manifold   (fullfetch effect?)

  13.275 sec (7,552 records, 9.0 MB) # SQL: oracle

  12.900 sec                         # Oracle SQL Developer 

adamw


10,447 post(s)
#16-Sep-19 17:21

The numbers seem fine, indeed.

The 8.277 sec for SQL: Manifold vs 0.413 sec for SQL: Oracle and 0.755 sec for Oracle SQL Developer in test 4 is because SELECT ... WHERE area<4500 does not use indexes and so SQL: Oracle and Oracle SQL Developer run WHERE on the server but SQL: Manifold runs it on the client, retrieving the entire table. This is how it should be, WHERE id=500 on an indexed field would have went to the server in SQL: Manifold as well, and if you want tests like WHERE area<4500 to be performed on the server, too, you can put them onto the server using EXECUTE.

Same for the last test - it would probably have completed in something like 600 sec give or take, since the table is the same as in the previous test.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.