On 2001-07-08 12:52:45 -0700, Tim Harsch wrote:
Yes,I realized,thanks. My post is to try to understand speed issues behind
an existence query. Once I understand that better... then the portability,
and how that will come into play in DBI, can come next. :-)
So, just to reiterate, what
-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: 08 July 2001 03:51
To: Steve Howard; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Checking for the existence of a certain row.
- Original Message -
From: Steve Howard [EMAIL PROTECTED]
To:
On 2001-07-07 18:16:26 -0500, Steven Lembark wrote:
- Steve Howard [EMAIL PROTECTED] on 07/07/01 17:54:18 -0500:
do a
SELECT COUNT(*) FROM tablename WHERE ...
A count is almost always faster than actually returning that row, and
requires even less network bandwidth to return the
On 2001-07-07 19:51:24 -0700, Michael A. Chase wrote:
- Original Message -
From: Steve Howard [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Saturday, July 07, 2001 19:47
Subject: RE: Checking for the existence of a certain row.
(I'm installing Oracle on
On Sun, Jul 08, 2001 at 12:52:45PM -0700, Tim Harsch wrote:
but, does this (Sybase specific) query:
select 1 where exists
( select * from some_table where some_column = 'some_value')
and/or this Oracle specific query:
select 1 from dual where exists
( select * from some_table where
-Original Message-
From: Ronald J Kimball [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 09, 2001 6:33 AM
To: Tim Harsch
Cc: M.W. Koskamp; Thomas A. Lowery; [EMAIL PROTECTED]
Subject: Re: Checking for the existence of a certain row.
On Sun, Jul 08, 2001 at 12:52:45PM -0700, Tim
Tim Harsch writes:
Don't think so, the database would clearly have to visit every row in order
to know there are none that match.
Wrong... at least for Sybase.
In a table with 4 million rows, doing a check on an indexed column:
select 1 from eC_user where exists (select * from
Yeah I thought about that too right at the end of my response. It sounds
like he didn't have an index.
-Original Message-
From: Michael Peppler [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 09, 2001 10:48 AM
To: Tim Harsch
Cc: Ronald J Kimball; M.W. Koskamp; Thomas A. Lowery;
I haven't seen anything on this thread related to DBI (but have
seen at least some misinformation about Oracle). Does
anyone know of some kind of 'any-db' sql list where this
would be appropriate? If so, I'd be happy to add my 2 cents
there.
-Chris
-Original Message-
From: Michael
-Original Message-
From: Tim Harsch [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 09, 2001 10:33 AM
To: Ronald J Kimball
Cc: M.W. Koskamp; Thomas A. Lowery; [EMAIL PROTECTED]
Subject: RE: Checking for the existence of a certain row.
-Original Message-
From:
On Mon, Jul 09, 2001 at 10:32:56AM -0700, Tim Harsch wrote:
Regarding Oracle, it stops after the first row that matches. I
tested this
on a table with 50 million rows, with a where clause that would select 20
million rows. The query returned in seconds. Here are the statistics:
True. That does sound like the best solution. But, how will the major
RDBMS' handle this? If they cache the first N, maybe up to available cache
memory, then the cache needs to be refilled again by other processes that
need it. Not wanting to get in to DB optimization techniques, but don't
On Sat, Jul 07, 2001 at 09:47:22PM -0500, Steve Howard wrote:
(I'm installing Oracle on Solaris right now. I'm anxious to get my hands on
it. I've only been off of mainframes for a couple of years, and haven't used
it, but hear some interesting things, like trim() in the where clause causes
- Original Message -
From: Steve Howard [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, July 08, 2001 4:47 AM
Subject: RE: Checking for the existence of a certain row.
OK, one more yes-no. I tried this for performance and as expected, it is
faster than count
my $statement = q{select 1 from x where exists xyz = ?};
Two things, this syntax does not with Oracle. Also, unless you've
selected the row(s) for update, than I believe it stops on the first
occurrence. The where clause, primary/unique indexes, would play a
part.
Tom
On Sat, Jul 07, 2001
- Original Message -
From: Steve Howard [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Saturday, July 07, 2001 19:47
Subject: RE: Checking for the existence of a certain row.
(I'm installing Oracle on Solaris right now. I'm anxious to get my hands
on
it. I've only
SYBASE::
select 1 where exists
( select * from some_table where some_column = 'some_value')
Anyway, it's obviously not as portable as any of the other methods, but
I'm
just trying to brainstorm the possibilities of the best methods for an
existence lookup.
Most portable is:
select
Yes,I realized,thanks. My post is to try to understand speed issues behind
an existence query. Once I understand that better... then the portability,
and how that will come into play in DBI, can come next. :-)
So, just to reiterate, what I'm after is this... if the following query
returns 1
James,
James Kufrovich wrote:
Hi.
I'd like to check for the existence of data in a row of a database
(MySQL), if given the value of a primary key. (Is there already a row in
the database that has this value as a key?) I don't care what data is in
the row, or if more than one
do a
SELECT COUNT(*) FROM tablename WHERE ...
A count is almost always faster than actually returning that row, and
requires even less network bandwidth to return the result. Of course, it a
result of 1 is returned - the row exists.
Steve H.
-Original Message-
From: Steven Lembark
- Steve Howard [EMAIL PROTECTED] on 07/07/01 17:54:18 -0500:
do a
SELECT COUNT(*) FROM tablename WHERE ...
A count is almost always faster than actually returning that row, and
requires even less network bandwidth to return the result. Of course, it a
result of 1 is returned - the row
What I do is:
my $statement = q{select 1 from x where xyz = ?};
Prepare:
my $exist_sth = $dbh-prepare( $statement );
Then call selectrow_array with a scalar:
$does_exists = $dbh-selectrow_array($statement, undef, $key_to_check );
Tom
On Fri, Jul 06, 2001
OK, one more yes-no. I tried this for performance and as expected, it is
faster than count on MS and Sybase (and count is very fast on MS). You might
see if there is a variation of it you can use with the Oracle Decode
functionI can't answer if there is or not, however in either case even
23 matches
Mail list logo
