Solved

Do Functions perform better than dynamic (inline) SQL in PostgreSQL?

Posted on 2016-10-21
3
126 Views
Last Modified: 2016-10-21
I'm pretty new to PostgreSQL and have a basic design question I'd like some advice on.

Our company has 1000+ identical schemas defined in our PostgreSQL DB. We have separate Schemas (work places) defined for each of our clients. The overall DB Schema design is something which cannot be altered.

We have to create routines which will gather data from all the schemas and store it in DataTables in a separate (centralized) schema.

One approach would be to write a .Net based app using he npgsql interface. The application would iterate over all the schemas and run dynamic (inline) SQL to return the desired data.

Another approach would be to push all the data gathering routines into PostgreSQL functions. The functions would contain dynamic SQL, accept a schema as parameter, and then run that SQL against the target schema.

Is there any difference in the performance of the two approaches? I'm trying to figure out which approach will be best in terms of performance and maintainability.

Is there a better approach to take?

Thanks, JohnB
0
Comment
Question by:jxbma
  • 2
3 Comments
 
LVL 20

Accepted Solution

by:
Russ Suter earned 500 total points
ID: 41854249
Functions generally perform faster than inline or direct queries. However you have indicated that your functions will be running dynamic SQL so any possibility for enhanced performance through optimization is pretty much out the window.

As far as maintainability is concerned I'd lean toward just doing everything in .NET. It's easier to manage and comment and can easily be placed under source control. Also, it's frequently as fast, sometimes faster if bulk copy is an option, than running queries.
3
 
LVL 1

Author Closing Comment

by:jxbma
ID: 41854270
Thanks for the prompt response.
That's what I kind of figured.

I'm not sure of what the reasoning was behind creating separate schemas (partitioning/isolation of data)?
It certainly makes it more challenging.

JB
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41854281
I'm not sure of what the reasoning was behind creating separate schemas (partitioning/isolation of data)?
Yep, that's what comments and documentation are for. There may have been a perfectly good reason but you're left to guess what that might be. Maybe it was a valid reason at the time but the reason is no longer a consideration. Or just maybe that was how the designer decided to do it because that's his style.
1

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Javascript to set controls visibility 5 38
application restarter  - for an exe that hangs 9 28
VB.NET 2008 Publish Error 2 27
C# LINQ ForEach() question 6 28
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question