Thread: What is the quickest query in the database?
What is the quickest and least intrusive query in the database that will always succeed? select current_user; -- OR -- select datname from pg_database where datname = 'some_database'; Or would it be something else? TIA. __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
Hi P G, On Thu, 20 Feb 2003 09:52:07 -0800 (PST) P G <pg_dba@yahoo.com> wrote: > What is the quickest and least intrusive query in the > database that will always succeed? select 1; :o) Regards Tino > select current_user; > > -- OR -- > > select datname from pg_database where datname = > 'some_database'; > > Or would it be something else? > > TIA. > > __________________________________________________ > Do you Yahoo!? > Yahoo! Tax Center - forms, calculators, tips, more > http://taxes.yahoo.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Thursday 20 Feb 2003 5:52 pm, P G wrote: > What is the quickest and least intrusive query in the > database that will always succeed? > > select current_user; > > -- OR -- > > select datname from pg_database where datname = > 'some_database'; > > Or would it be something else? Probably something like SELECT 1; -- Richard Huxton
Might select NULL; be faster, since the number doesn't have to be parsed, and null is probably a keyword? Jon On Thu, 20 Feb 2003, Tino Wildenhain wrote: > Hi P G, > > On Thu, 20 Feb 2003 09:52:07 -0800 (PST) > P G <pg_dba@yahoo.com> wrote: > > > What is the quickest and least intrusive query in the > > database that will always succeed? > > select 1; > > :o) > > Regards > Tino > > > > select current_user; > > > > -- OR -- > > > > select datname from pg_database where datname = > > 'some_database'; > > > > Or would it be something else? > > > > TIA. > > > > __________________________________________________ > > Do you Yahoo!? > > Yahoo! Tax Center - forms, calculators, tips, more > > http://taxes.yahoo.com/ > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Tino Wildenhain wrote: > > Hi P G, > > On Thu, 20 Feb 2003 09:52:07 -0800 (PST) > P G <pg_dba@yahoo.com> wrote: > > > What is the quickest and least intrusive query in the > > database that will always succeed? > > select 1; Not if you're currently in transaction aborted state. An empty query is. In psql this can be done with just a semicolon. It will not affect the current transaction state, it will return PGRES_EMPTY_QUERY (IIRC) or bail out because of a lost connection. And it doesn't even need to parse a single keyword. Jan > > :o) > > Regards > Tino > > > select current_user; > > > > -- OR -- > > > > select datname from pg_database where datname = > > 'some_database'; > > > > Or would it be something else? > > > > TIA. > > > > __________________________________________________ > > Do you Yahoo!? > > Yahoo! Tax Center - forms, calculators, tips, more > > http://taxes.yahoo.com/ > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
It's academic. Set timing on in psql and run a bunch of tests. The variation from test to test is large enough to mask any difference between select 0 or select '' or select null. It would, however, be nice to know the reason for this query. I suspect it is to "ping" the server and making sure it is up by connecting and running a simple query with a known result. (Many colos have all sorts of setups for monitoring web servers but fewer for other services - setting up a "test" page on the web server that makes a request from the app server which, in turn, does a simple db query, all of which ultimately returns a standard string, say "system up", to the http request works quite well to set off alarm bells at the colo or even to allow a load balancer to take action). But I am surmising. What is the real reason for the query? Cheers, Steve On Thursday 20 February 2003 11:52 am, Jonathan Bartlett wrote: > Might select NULL; be faster, since the number doesn't have to be parsed, > and null is probably a keyword? > > Jon > > On Thu, 20 Feb 2003, Tino Wildenhain wrote: > > Hi P G, > > > > On Thu, 20 Feb 2003 09:52:07 -0800 (PST) > > > > P G <pg_dba@yahoo.com> wrote: > > > What is the quickest and least intrusive query in the > > > database that will always succeed? > > > > select 1; > > > > :o) > > > > Regards > > Tino > > > > > select current_user; > > > > > > -- OR -- > > > > > > select datname from pg_database where datname = > > > 'some_database'; > > > > > > Or would it be something else? > > > > > > TIA. > > > > > > __________________________________________________ > > > Do you Yahoo!? > > > Yahoo! Tax Center - forms, calculators, tips, more > > > http://taxes.yahoo.com/ > > > > > > ---------------------------(end of > > > broadcast)--------------------------- TIP 3: if posting/reading through > > > Usenet, please send an appropriate subscribe-nomail command to > > > majordomo@postgresql.org so that your message can get through to the > > > mailing list cleanly > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
You are correct Steve Crawford. The query is used to remotely test the connection to the db using JDBC. I think, though, Jan Wieck provides the most valuable solution so far. Any more thoughts, anyone? TIA. --- Steve Crawford <scrawford@pinpointresearch.com> wrote: > It's academic. Set timing on in psql and run a bunch > of tests. The variation > from test to test is large enough to mask any > difference between select 0 or > select '' or select null. > > It would, however, be nice to know the reason for > this query. I suspect it is > to "ping" the server and making sure it is up by > connecting and running a > simple query with a known result. (Many colos have > all sorts of setups for > monitoring web servers but fewer for other services > - setting up a "test" > page on the web server that makes a request from the > app server which, in > turn, does a simple db query, all of which > ultimately returns a standard > string, say "system up", to the http request works > quite well to set off > alarm bells at the colo or even to allow a load > balancer to take action). > > But I am surmising. What is the real reason for the > query? > > Cheers, > Steve > > > On Thursday 20 February 2003 11:52 am, Jonathan > Bartlett wrote: > > Might select NULL; be faster, since the number > doesn't have to be parsed, > > and null is probably a keyword? > > > > Jon > > > > On Thu, 20 Feb 2003, Tino Wildenhain wrote: > > > Hi P G, > > > > > > On Thu, 20 Feb 2003 09:52:07 -0800 (PST) > > > > > > P G <pg_dba@yahoo.com> wrote: > > > > What is the quickest and least intrusive query > in the > > > > database that will always succeed? > > > > > > select 1; > > > > > > :o) > > > > > > Regards > > > Tino > > > > > > > select current_user; > > > > > > > > -- OR -- > > > > > > > > select datname from pg_database where datname > = > > > > 'some_database'; > > > > > > > > Or would it be something else? > > > > > > > > TIA. > > > > > > > > > __________________________________________________ > > > > Do you Yahoo!? > > > > Yahoo! Tax Center - forms, calculators, tips, > more > > > > http://taxes.yahoo.com/ > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- TIP 3: > if posting/reading through > > > > Usenet, please send an appropriate > subscribe-nomail command to > > > > majordomo@postgresql.org so that your message > can get through to the > > > > mailing list cleanly > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please > send an appropriate > > subscribe-nomail command to > majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
But, unless there is a timeout on that query, wont the system being down really hang you up, until your script reaches it's default timeout? We had this problem with PHP and Perl in the past. Is there a way to get around this in Java? - Ericson Smith eric@did-it.com On Thu, 2003-02-20 at 15:32, P G wrote: > You are correct Steve Crawford. The query is used to > remotely test the connection to the db using JDBC. I > think, though, Jan Wieck provides the most valuable > solution so far. Any more thoughts, anyone? > > TIA. > > --- Steve Crawford <scrawford@pinpointresearch.com> > wrote: > > It's academic. Set timing on in psql and run a bunch > > of tests. The variation > > from test to test is large enough to mask any > > difference between select 0 or > > select '' or select null. > > > > It would, however, be nice to know the reason for > > this query. I suspect it is > > to "ping" the server and making sure it is up by > > connecting and running a > > simple query with a known result. (Many colos have > > all sorts of setups for > > monitoring web servers but fewer for other services > > - setting up a "test" > > page on the web server that makes a request from the > > app server which, in > > turn, does a simple db query, all of which > > ultimately returns a standard > > string, say "system up", to the http request works > > quite well to set off > > alarm bells at the colo or even to allow a load > > balancer to take action). > > > > But I am surmising. What is the real reason for the > > query? > > > > Cheers, > > Steve > > > > > > On Thursday 20 February 2003 11:52 am, Jonathan > > Bartlett wrote: > > > Might select NULL; be faster, since the number > > doesn't have to be parsed, > > > and null is probably a keyword? > > > > > > Jon > > > > > > On Thu, 20 Feb 2003, Tino Wildenhain wrote: > > > > Hi P G, > > > > > > > > On Thu, 20 Feb 2003 09:52:07 -0800 (PST) > > > > > > > > P G <pg_dba@yahoo.com> wrote: > > > > > What is the quickest and least intrusive query > > in the > > > > > database that will always succeed? > > > > > > > > select 1; > > > > > > > > :o) > > > > > > > > Regards > > > > Tino > > > > > > > > > select current_user; > > > > > > > > > > -- OR -- > > > > > > > > > > select datname from pg_database where datname > > = > > > > > 'some_database'; > > > > > > > > > > Or would it be something else? > > > > > > > > > > TIA. > > > > > > > > > > > > __________________________________________________ > > > > > Do you Yahoo!? > > > > > Yahoo! Tax Center - forms, calculators, tips, > > more > > > > > http://taxes.yahoo.com/ > > > > > > > > > > ---------------------------(end of > > > > > broadcast)--------------------------- TIP 3: > > if posting/reading through > > > > > Usenet, please send an appropriate > > subscribe-nomail command to > > > > > majordomo@postgresql.org so that your message > > can get through to the > > > > > mailing list cleanly > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > TIP 3: if posting/reading through Usenet, please > > send an appropriate > > > subscribe-nomail command to > > majordomo@postgresql.org so that your > > > message can get through to the mailing list > cleanly > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Tax Center - forms, calculators, tips, more > http://taxes.yahoo.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Ericson Smith <eric@did-it.com>
It sounds like the caller is simply trying to see if the engine is up and operational. If that is the case, then just connect, disconnect. Jonathan Bartlett wrote: >Might select NULL; be faster, since the number doesn't have to be parsed, >and null is probably a keyword? > >Jon > >On Thu, 20 Feb 2003, Tino Wildenhain wrote: > > > >>Hi P G, >> >>On Thu, 20 Feb 2003 09:52:07 -0800 (PST) >>P G <pg_dba@yahoo.com> wrote: >> >> >> >>>What is the quickest and least intrusive query in the >>>database that will always succeed? >>> >>> >>select 1; >> >>:o) >> >>Regards >>Tino >> >> >> >> >>>select current_user; >>> >>>-- OR -- >>> >>>select datname from pg_database where datname = >>>'some_database'; >>> >>>Or would it be something else? >>> >>>TIA. >>> >>>__________________________________________________ >>>Do you Yahoo!? >>>Yahoo! Tax Center - forms, calculators, tips, more >>>http://taxes.yahoo.com/ >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 3: if posting/reading through Usenet, please send an appropriate >>>subscribe-nomail command to majordomo@postgresql.org so that your >>>message can get through to the mailing list cleanly >>> >>> >>> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/users-lounge/docs/faq.html >> >> >> > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly > >
That question sounds like one more of design or architecture. Timing out the connection is one solution. Another immediate idea comes to mind where you can use a seperate thread or fork another process allowing the rest of the application to continue if needed. Thanks. --- Ericson Smith <eric@did-it.com> wrote: > But, unless there is a timeout on that query, wont > the system being down > really hang you up, until your script reaches it's > default timeout? > > We had this problem with PHP and Perl in the past. > Is there a way to get > around this in Java? > > - Ericson Smith > eric@did-it.com > > On Thu, 2003-02-20 at 15:32, P G wrote: > > You are correct Steve Crawford. The query is used > to > > remotely test the connection to the db using JDBC. > I > > think, though, Jan Wieck provides the most > valuable > > solution so far. Any more thoughts, anyone? > > > > TIA. > > > > --- Steve Crawford > <scrawford@pinpointresearch.com> > > wrote: > > > It's academic. Set timing on in psql and run a > bunch > > > of tests. The variation > > > from test to test is large enough to mask any > > > difference between select 0 or > > > select '' or select null. > > > > > > It would, however, be nice to know the reason > for > > > this query. I suspect it is > > > to "ping" the server and making sure it is up by > > > connecting and running a > > > simple query with a known result. (Many colos > have > > > all sorts of setups for > > > monitoring web servers but fewer for other > services > > > - setting up a "test" > > > page on the web server that makes a request from > the > > > app server which, in > > > turn, does a simple db query, all of which > > > ultimately returns a standard > > > string, say "system up", to the http request > works > > > quite well to set off > > > alarm bells at the colo or even to allow a load > > > balancer to take action). > > > > > > But I am surmising. What is the real reason for > the > > > query? > > > > > > Cheers, > > > Steve > > > > > > > > > On Thursday 20 February 2003 11:52 am, Jonathan > > > Bartlett wrote: > > > > Might select NULL; be faster, since the number > > > doesn't have to be parsed, > > > > and null is probably a keyword? > > > > > > > > Jon > > > > > > > > On Thu, 20 Feb 2003, Tino Wildenhain wrote: > > > > > Hi P G, > > > > > > > > > > On Thu, 20 Feb 2003 09:52:07 -0800 (PST) > > > > > > > > > > P G <pg_dba@yahoo.com> wrote: > > > > > > What is the quickest and least intrusive > query > > > in the > > > > > > database that will always succeed? > > > > > > > > > > select 1; > > > > > > > > > > :o) > > > > > > > > > > Regards > > > > > Tino > > > > > > > > > > > select current_user; > > > > > > > > > > > > -- OR -- > > > > > > > > > > > > select datname from pg_database where > datname > > > = > > > > > > 'some_database'; > > > > > > > > > > > > Or would it be something else? > > > > > > > > > > > > TIA. > > > > > > > > > > > > > > > > __________________________________________________ > > > > > > Do you Yahoo!? > > > > > > Yahoo! Tax Center - forms, calculators, > tips, > > > more > > > > > > http://taxes.yahoo.com/ > > > > > > > > > > > > ---------------------------(end of > > > > > > broadcast)--------------------------- TIP > 3: > > > if posting/reading through > > > > > > Usenet, please send an appropriate > > > subscribe-nomail command to > > > > > > majordomo@postgresql.org so that your > message > > > can get through to the > > > > > > mailing list cleanly > > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > > > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > > TIP 3: if posting/reading through Usenet, > please > > > send an appropriate > > > > subscribe-nomail command to > > > majordomo@postgresql.org so that your > > > > message can get through to the mailing list > > cleanly > > > > > > __________________________________________________ > > Do you Yahoo!? > > Yahoo! Tax Center - forms, calculators, tips, more > > http://taxes.yahoo.com/ > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > -- > Ericson Smith <eric@did-it.com> > __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/