Re: Suggestion to improve query performance of data validation in proc.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Suggestion to improve query performance of data validation in proc.

David G Johnston
You should read through the and the contained linked FAQ - note especially the concept and recommendation for “cross-posting”.

https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics

On Thursday, May 21, 2020, postgann2020 s <[hidden email]> wrote:

We have multiple long procs that are having 100s of data validations and currently we have written as below.

***********

if (SELECT 1  FROM SCHEMA.TABLE WHERE column=data AND column=data) then
statements
etc..

***********

Are there any other ways to validate the data, which will help us to improve the performance of the query?

I have no idea what your are trying to get at here.  You should try providing SQL that actually runs.  Though at first glance it seems quite probable your are doing useless work anyway.

David J.
Reply | Threaded
Open this post in threaded view
|

Re: Suggestion to improve query performance of data validation in proc.

postgann2020 s
Hi David,

Thanks for your feedback. 

We are using the below kind of validation throughout the proc in multiple locations and for validation we are using the below statements.

--check Data available or not for structure_id1
       IF EXISTS(SELECT 1  FROM schema.table_name WHERE column1=structure_id1)  THEN
     is_exists1 :=true;
END IF;


We are looking for a better query than "SELECT 1  FROM schema.table_name WHERE column1=structure_id1" this query for data validation.

Please suggest is there any other ways to validate this kind of queries which will improve the overall performance.

Regards,
Postgann.

On Fri, May 22, 2020 at 12:36 PM David G. Johnston <[hidden email]> wrote:
You should read through the and the contained linked FAQ - note especially the concept and recommendation for “cross-posting”.

https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics

On Thursday, May 21, 2020, postgann2020 s <[hidden email]> wrote:

We have multiple long procs that are having 100s of data validations and currently we have written as below.

***********

if (SELECT 1  FROM SCHEMA.TABLE WHERE column=data AND column=data) then
statements
etc..

***********

Are there any other ways to validate the data, which will help us to improve the performance of the query?

I have no idea what your are trying to get at here.  You should try providing SQL that actually runs.  Though at first glance it seems quite probable your are doing useless work anyway.

David J.
Reply | Threaded
Open this post in threaded view
|

Re: Suggestion to improve query performance of data validation in proc.

David G Johnston
On Friday, May 22, 2020, postgann2020 s <[hidden email]> wrote:
 

We are looking for a better query than "SELECT 1  FROM schema.table_name WHERE column1=structure_id1" this query for data validation.

 There is no more simple a query that involve records on a single,table.

Please suggest is there any other ways to validate this kind of queries which will improve the overall performance.

Abandon procedural logic and embrace the declarative set oriented nature of SQL.

David J.
Reply | Threaded
Open this post in threaded view
|

Re: Suggestion to improve query performance of data validation in proc.

Michael Lewis
On Fri, May 22, 2020 at 2:09 AM David G. Johnston <[hidden email]> wrote:
On Friday, May 22, 2020, postgann2020 s <[hidden email]> wrote:
 

We are looking for a better query than "SELECT 1  FROM schema.table_name WHERE column1=structure_id1" this query for data validation.

 If many rows match potentially, then wrapping the query with select exists(old_query) would allow the execution to bail asap.