Performance issue when calling Field.toString() [Was: Getting Table name from Field]

[Lukas Eder]

Hi Vishwanath,

Thank you very much for your message. I'm not sure if this is related to the original message, so I'll start a new discussion thread...

Indeed, generating any SQL content from a QueryPart does incur some overhead, but it should certainly not result in 30% of your query execution. However, I don't have the code of your DataAbstractLayer.ExecuteQuery method. Would you mind sharing that? Also, what jOOQ version are you using?

Cheers,

Lukas

2017-07-25 10:44 GMT+02:00 <vishwa...@gmail.com>:

Hi,

calling Field's tostring() function creates performance problems. Attached the call stack that has the split up as well. Can you give me suggestion ? 

Thanks,

Vishwanath

On Wednesday, 23 December 2015 13:56:40 UTC+5:30, Lukas Eder wrote:

Hi Lokesh,

Thanks for the details.

Unfortunately, not all databases / JDBC drivers report table (and schema) names with ResultSetMetaData (e.g. Oracle and SQL Server don't). What database are you using?

If those are present, then jOOQ will read those and associate them with the individual fields that are reported in the Result. The fields will then be of the form:

DSL.field(name(schemaName, tableName, columnName), dataType);

The table name can be extracted by calling toString() and then doing string manipulation. There is currently no other way.

BTW, if both tables A and B have one attribute with same name e.g.  "xxx" what would the response as map (attribute name to value) contain? 

jOOQ's Result will contain two Field references with the same column name "xxx". This is OK for jOOQ, because internally, all operations are column index based, not column name base. If you want to read the right column from a jOOQ Result, and there is no table name information available to disambiguate the names, you'll have to either:

- Access columns by index

- Explicitly alias such columns in your original query.

I hope this helps.

Let me know if you have any additional questions

Lukas

2015-12-23 4:45 GMT+01:00 <loke...@gmail.com>:

Hi Lukas,

Thanks for following up. The query that I am running looks like this:

select().from("A").join("B").on(some condition)

I tried response as ResultSet also. It is giving me empty table name for all the fields.

BTW, if both tables A and B have one attribute with same name e.g.  "xxx" what would the response as map (attribute name to value) contain? 

Thanks,

Lokesh

On Tuesday, 22 December 2015 01:08:25 UTC-8, Lukas Eder wrote:

Hi Lokesh,

This would depend on the concrete query that you're running.

- If you're selecting generated TableField instances, then TableField.getTable() can be used

- If you're selecting plain SQL Fields or aliased fields, or any kind of expression, you'll have to manage yourself.

What does the query you're running look like?

Cheers,

Lukas

2015-12-21 21:09 GMT+01:00 <loke...@gmail.com>:

Hi,

From a join query result record, how could I know which field belongs to which table? Or, how do I group the fields by table name? 

Thanks,

Lokesh

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hi Vishwanath

(I've noticed, this discussion accidentally went off-list. Putting the mailing list in CC again, redacting the content)

Indeed, the auto-generation of an alias for derived tables in SelectQueryImpl.asTable() should be faster. I thought there was a pending issue to fix this, but I cannot seem to find it. I've added an issue here:

https://github.com/jOOQ/jOOQ/issues/6714

The problem is, we need an alias for derived tables, and if you don't provide it explicitly, that's probably the best way to go right now (discussion will be in #6714)

You can always work around this problem by creating an explicit alias for your subquery, e.g. by calling Select.asTable("alias") or table(select).as("alias").

I hope this helps,

Lukas

2017-10-17 13:03 GMT+02:00 Vishwanath M:

Hi Lukas,

This way of accessing is name was really helpful . Now am facing issues in the below code 

Most of time is spent when i convert a select to table and the profiler shows it spends most of the time in org.jooq.impl.Tools.hash . May i know the reason ? 

Thanks,

Vishwanath.M

[Vishwanath M]

Thanks Lukas i will add alias and that should solve the problem.

One more issue which am noticing while profiling the code is the difference between the query execution time in DB and the actual time to fetch the data through jooq . 

Here is the sample 

This is the actual time taken to fetch the results from the DB is 40 ms (postgres)

But the profiler results from Jprofiler shows it took totally 80ms and below is the snapshot

Like you see above 79 secs is allocated to AbstractQuery.execute function , i cant understand why is it taking double the time . Can you help me ? 

Regards,

vishwanath.M