Data Structure for Date Time Selection

Hi All,
We have a table which looks like the following:
ID (int)
Status (int)
Elgiblity (int)
EligibleTime (datetime)
Counter (int)
Flag (bit)
Typically our queries look like
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Since we're doing a lot of selects based on time + status codes... are
there any database structures suited for this?
Also, the values in this table are updated quite often... how do ensure
the indexes are update to date?
We load and use about 200K rows of data per day. We keep about 45 days
worth of data in the database. We would lilke to return the data as fast
as possible... does anyone have suggestions on how to store the data?
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.comLucas
Are you concerned about performance of the query? Do the users complain that
they have to wait for a request?
What are the inedexes defined on the table?
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
> Hi All,
> We have a table which looks like the following:
> ID (int)
> Status (int)
> Elgiblity (int)
> EligibleTime (datetime)
> Counter (int)
> Flag (bit)
> Typically our queries look like
> SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
> Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
> = 0 AND Counter < 2
> Since we're doing a lot of selects based on time + status codes... are
> there any database structures suited for this?
> Also, the values in this table are updated quite often... how do ensure
> the indexes are update to date?
> We load and use about 200K rows of data per day. We keep about 45 days
> worth of data in the database. We would lilke to return the data as fast
> as possible... does anyone have suggestions on how to store the data?
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Hi
to auto update statistics, use database option
AUTO_UPDATE_STATISTICS
>from bol
When set to ON, existing statistics are automatically updated when the
statistics become out-of-date because the data in the tables has changed.
When set to OFF, existing statistics are not automatically updated; instead,
statistics can be manually updated. For more information, see Statistical
Information.
By default, AUTO_UPDATE_STATISTICS is set to ON.
The status of this option can be determined by examining the
IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
To know when exactly statistics are updated
DBCC SHOW_STATISTICS
To updat statistics
UPDATE STATISTICS table | view
[
index
| ( statistics_name [ ,...n ] )
]
[ WITH
[
[ FULLSCAN ]
| SAMPLE number { PERCENT | ROWS } ]
| RESAMPLE
]
[ [ , ] [ ALL | COLUMNS | INDEX ]
[ [ , ] NORECOMPUTE ]
]
Regards
R.D
"Uri Dimant" wrote:

> Lucas
> Are you concerned about performance of the query? Do the users complain th
at
> they have to wait for a request?
> What are the inedexes defined on the table?
>
> "Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
> news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
>
>|||SORRY AT WRONG PLACE
"R.D" wrote:
> Hi
> to auto update statistics, use database option
> AUTO_UPDATE_STATISTICS
> When set to ON, existing statistics are automatically updated when the
> statistics become out-of-date because the data in the tables has changed.
> When set to OFF, existing statistics are not automatically updated; instea
d,
> statistics can be manually updated. For more information, see Statistical
> Information.
> By default, AUTO_UPDATE_STATISTICS is set to ON.
> The status of this option can be determined by examining the
> IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
> To know when exactly statistics are updated
> DBCC SHOW_STATISTICS
> To updat statistics
> UPDATE STATISTICS table | view
> [
> index
> | ( statistics_name [ ,...n ] )
> ]
> [ WITH
> [
> [ FULLSCAN ]
> | SAMPLE number { PERCENT | ROWS } ]
> | RESAMPLE
> ]
> [ [ , ] [ ALL | COLUMNS | INDEX ]
> [ [ , ] NORECOMPUTE ]
> ]
> Regards
> R.D
>
> "Uri Dimant" wrote:
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:

> Lucas
> Are you concerned about performance of the query? Do the users
> complain that they have to wait for a request?
> What are the inedexes defined on the table?
Hi,
It's an application that runs against the table... the application
places phone calls on behalf of records in the database - we need to put
out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
Right now there are a combination of factors:
1. Table Structure Could be Optimized
2. hardware could be better
As you see in my original post, the table is pretty much a flat file
(there are actually more columns - like 30+ but they're parameter
columns)... I'm just wondering if you guys have any pointers on how to
store datetime data in a way that is easily selectable.
Thanks!
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Lucas
can be bit specific
is it the problem of insertion or select statement to retrive it.
pl.Post script
Regards
R.D
"Lucas Tam" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:
>
> Hi,
> It's an application that runs against the table... the application
> places phone calls on behalf of records in the database - we need to put
> out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
> Right now there are a combination of factors:
> 1. Table Structure Could be Optimized
> 2. hardware could be better
> As you see in my original post, the table is pretty much a flat file
> (there are actually more columns - like 30+ but they're parameter
> columns)... I'm just wondering if you guys have any pointers on how to
> store datetime data in a way that is easily selectable.
> Thanks!
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
>|||examnotes <RD@.discussions.microsoft.com> wrote in
news:B0959AEA-AB2D-48A9-922D-5E0528AB5CE3@.microsoft.com:

> Lucas
> can be bit specific
> is it the problem of insertion or select statement to retrive it.
> pl.Post script
> Regards
> R.D
Problem with select statement:
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Something like that can take a while... and use a lot of resources.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com