Win a copy of Microservices in Action this week in the Web Services forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

PostgreSQL "OR" update to "UNION ALL"  RSS feed

 
Ranch Hand
Posts: 1215
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I have a postgressql query using "OR" and I would like to write it again but by using "UNION ALL" because of the performance.

The query I have is like this one:



Then I wrote this one:



Particularly the way I wrote about the parameters is not working and as an output we get no rows.



Any idea of how should this parameters should be writen please?

Regards, Isaac
 
Sheriff
Posts: 5469
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By "parameters is not working" do you mean that you have Java code using a prepared statement?  If so, can you post the prepared statement code and the SQL query string?
 
Isaac Ferguson
Ranch Hand
Posts: 1215
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I cant at the moment but what i mean is that the way i am writing the quotation marks '?'

It takes it as a literal. I nee use another syntax.

Any idea, please?
 
Bartender
Posts: 19996
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would be surprised if UNION ALL gives better performance than a simple "OR" query, since it's effectively 2 queries and a merge, but some EXPLAIN analysis could confirm.

The '?' on what was supposed to be raw SQL threw me too. But I think I recall that using ? with LIKE-style operations gave me problems. Like whether to put the '%' on the SQL or in the value to be substituted.
 
Knute Snortum
Sheriff
Posts: 5469
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Isaac Ferguson wrote:I cant at the moment but what i mean is that the way i am writing the quotation marks '?'

It takes it as a literal. I nee use another syntax.

Any idea, please?


I don't think the query string needs single quotes around the ? if you are using setString() with a PreparedStatement.
 
Tim Holloway
Bartender
Posts: 19996
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:
I don't think the query string needs single quotes around the ? if you are using setString() with a PreparedStatement.


Now that you mention it, I don't either.
 
Isaac Ferguson
Ranch Hand
Posts: 1215
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
On the previous messages I have used mock values instead if ?, and it is working with a correspondant PreparedStatement.

On the another hand, I would like to select only once the appointmentid with news.


The desired output is appointmentid3,appointmentid4, appointmentid2. But I get for rows like this;


The are grouped by appointmentid but appointmentid3 should be jumped.



Any idea, please?
 
Isaac Ferguson
Ranch Hand
Posts: 1215
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The thing is that also when the query one provides de same result that the query two, only one value should be provided.
 
Isaac Ferguson
Ranch Hand
Posts: 1215
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For example, particularly in this case speakerid5 and lisenerid5 are grouped by appointmentid which it is also 5 in my mock database. So the undesired result I am gettting is two rows (one comin from each select) instad of just the newest one.



 
Isaac Ferguson
Ranch Hand
Posts: 1215
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This another query which in theory avoid duplicates but when I try it I get no rows at all



In the table news  there are several rows, for example row1->speakerid1->lisenerid1

A colleague commented me that this should work... for me it makes no so much sense...but I find it interesting...

Any comment, please?
 
Knute Snortum
Sheriff
Posts: 5469
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Isaac Ferguson wrote:
The desired output is appointmentid3,appointmentid4, appointmentid2. But I get for rows like this;


The are grouped by appointmentid but appointmentid3 should be jumped.



Any idea, please?


I don't think that UNION ALL is helping here.  I believe it just returns the output of the two queries "stuck" together.  Since both queries have appointmentid, why not a simple JOIN?
 
Knute Snortum
Sheriff
Posts: 5469
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Isaac Ferguson wrote:This another query which in theory avoid duplicates but when I try it I get no rows at all



In the table news  there are several rows, for example row1->speakerid1->lisenerid1

A colleague commented me that this should work... for me it makes no so much sense...but I find it interesting...

Any comment, please?


I'm not sure about that WHERE clause.  What is it trying to do?  It would make more sense like this:
 
Isaac Ferguson
Ranch Hand
Posts: 1215
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The performance with JOIN is worst, I search for the best possible performance.
 
Knute Snortum
Sheriff
Posts: 5469
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Even with indexing?
 
Isaac Ferguson
Ranch Hand
Posts: 1215
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By using EXPLAIN ANALYZE the best option is AND even over Union All
 
Isaac Ferguson
Ranch Hand
Posts: 1215
3
Java Netbeans IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Finally the best performance was with AND
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!