Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have some questions about stored procedures.

Using select query inside the stored procedure is efficent or using select query in front end. Which is take less time and give the result is fast or if have any other way to use select query in efficent manner? Please help.

EDIT

We are using postgresql-9.1. I want clarify my doubts because in my office we are using select query's (Getting Results from backend) in front end for reports. Now we want to know which is better and efficent way for using select query is front end or stored procedure (back end).

Thank you

share|improve this question

migrated from stackoverflow.com May 13 '13 at 7:32

This question came from our site for professional and enthusiast programmers.

1  
Nowhere near enough detail here. PostgreSQL version? (I'm sure I've reminded you to always include your Pg version before). Queries? Table sizes? Client/server connection info? –  Craig Ringer May 13 '13 at 5:07
    
@CraigRinger postgresql-9.1. I want clarify my doubts because in my office we are using select query's in front end for reports. Now we want to know which is better and efficent way for using select query is front end or stored procedure (back end) –  SATSON May 13 '13 at 5:15
3  
The query always runs in the server backend. If you need to (a) run the query and (b) send the results to the client, then all a stored proc gains you is a little bit of startup overhead. –  Craig Ringer May 13 '13 at 5:19
    
@CraigRinger this is true only from a performance point of view. Just for the sake of nitpicking :) –  dezso May 14 '13 at 9:23

1 Answer 1

up vote 0 down vote accepted

It depends a lot on what the data is, what you're doing with the data, what the bandwidth between client and server is, what stored procedure language you're using, etc.

Test it and see. You can:

  • Use the psql \timing command to get the final execution time including results transfer from the client perspective;

  • Use log_min_duration_statement = 0 in postgresql.conf and in your psql client run SET client_min_messages = INFO to see the messages or examine the logs. This will show you how long the statements took to run server-side.

  • Use explain and explain analyze to examine query plans and execution details

  • Use the auto_explain contrib module to collect detailed data, optoinally including execution times inside stored functions.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.