Subscribe to this thread
Home - General / All posts - Performance with Oracle
rk
319 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


5,491 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


8,634 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
319 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


8,634 post(s)
#15-Aug-19 12:50

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

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

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

Dimitri


5,491 post(s)
#15-Aug-19 13:08

Interesting. How fast without !fullfetch?

rk
319 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


8,634 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


8,634 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
319 post(s)
#16-Aug-19 18:51

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

rk
319 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


8,634 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


8,634 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


5,491 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?

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