Prev: Help
Next: Need Ideas for SQL Query
From: todtown on 21 Jul 2008 15:56
I have a query that needs to return only if there are records that
match the criteria. My current query is something like this:

SELECT MAX(RECORD_DATE),RECORD_NAME
FROM TABLE_NAME
WHERE RECORD_DATE >= '7/21/2008' /*Today's Date*/
GROUP BY RECORD_NAME

The problem is that there can be thousands of records. The query can
often time out. And all I need to know is if there are any records. If
there is even one that's all I need to know.

Could somebody help me with this one?

tod

From: Hugo Kornelis on 21 Jul 2008 16:15
On Mon, 21 Jul 2008 12:56:34 -0700 (PDT), todtown wrote:

>I have a query that needs to return only if there are records that
>match the criteria. My current query is something like this:
>
>SELECT MAX(RECORD_DATE),RECORD_NAME
>FROM TABLE_NAME
>WHERE RECORD_DATE >= '7/21/2008' /*Today's Date*/
>GROUP BY RECORD_NAME
>
>The problem is that there can be thousands of records. The query can
>often time out. And all I need to know is if there are any records. If
>there is even one that's all I need to know.
>
>Could somebody help me with this one?

Hi tod,

If you only need to know if rows match the filter, then you don't need
to determine the maximum record_date to achieve that. Instead, use an
EXISTS query so that SQL Server knows it can stop after finding the
first match.

IF EXISTS
(SELECT *
FROM YourTable
WHERE YourDate >= '20080721' -- Use unambiguous date format
)
PRINT 'It exists!';
ELSE
PRINT 'None found..';

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: --CELKO-- on 21 Jul 2008 16:18
>> I have a query that needs to return only if there are records [sic: rows are not records] that match the criteria. And all I need to know is if there are any records [sic]. If here is even one that's all I need to know. <<

SELECT DISTINCT CURRENT_TIMESTAMP, 'Yes, we will have work!'
FROM Dummy -- any table
WHERE EXISTS
(SELECT *
FROM Foobar
WHERE record_date >= CURRENT_TIMESTAMP);

Your approach was to count the rows as if they were records in a
sequence. They are elements in a set; look at the set as a whole, not
as separate records. Use the right words and the proper mindset
follows.
From: Roy Harvey (SQL Server MVP) on 21 Jul 2008 18:04
On Mon, 21 Jul 2008 22:15:28 +0200, Hugo Kornelis
<hugo(a)perFact.REMOVETHIS.info.INVALID> wrote:

>Hi tod,
>
>If you only need to know if rows match the filter, then you don't need
>to determine the maximum record_date to achieve that. Instead, use an
>EXISTS query so that SQL Server knows it can stop after finding the
>first match.
>
>IF EXISTS
> (SELECT *
> FROM YourTable
> WHERE YourDate >= '20080721' -- Use unambiguous date format
> )
> PRINT 'It exists!';
>ELSE
> PRINT 'None found..';

Or if you would prefer the answer in a SELECT result instead of PRINT
commands:

SELECT CASE WHEN EXISTS
(SELECT * FROM YourTable
WHERE YourDate >= '20080721')
THEN 'Yes'
ELSE 'No'
END as TestResult

Of course instead of 'Yes' and 'No' and pair of values of compatible
type can be used, including 1 and 0.

Roy Harvey
Beacon Falls, CT
From: Denisio on 22 Jul 2008 01:50
Information is not full. Do you have indexes? How many records in table? What
is table structure? Do you have primary key on table? Which columns used as
primary key? What is server version? I think what you can create index with
next statement:

create index IX_1 on TABLE_NAME (RECORD_DATE, RECORD_NAME)
// if you have SQL 2000/2005/2008

or

create index IX_1 on TABLE_NAME (RECORD_DATE) include (RECORD_NAME)
// if you have SQL 2005/2008.

Also, I preferred create primary key on each table in database. Please check
existence of primary key in you table.

Sorry for bad English.
 | 
Pages: 1
Prev: Help
Next: Need Ideas for SQL Query