frequently updated tables

Hi
I have a transaction table that is very frequently updated, so I have no
indexes on it. I also need to be able to run queries against this data on,
for example, a date range. I think the easiest solution would be to extract
data on a daily basis onto another table with indexes. Or should I be looking
at some sort of data warehousing/data cube approach? Any ideas/resources?
Thanks
if your just updating the table i dont see any reason why you couldnt
index that table and run data off of it. and if you would be able to
export it to a different table once a day i'm guess the data your
viewing isnt live data you need to see right away. i'd just index that
table and run queries off of it. just set all the tables your
selecting from 'WITH (NOLOCK)' then it wont matter if a record is
being access at that time. it doesnt sound like your worried about any
type of dirty reads. if it is being inserted into quite frequently you
might have to take another approach or run a job to reindex at night.
i'm not 100% sure of your exact situation. hope that helps.
|||We have been advised in the past that it wasn't a good idea to have indexes
on the transaction table that is being frequently written too i.e. less than
a second between inserts, but I'd still like to query the data. Without some
sort of index, the query ends up doing a full table scan on a massive table,
so I think I need some sort of extract to an indexed version to make these
queries manageable.
"GlennThomas5" wrote:

> if your just updating the table i dont see any reason why you couldnt
> index that table and run data off of it. and if you would be able to
> export it to a different table once a day i'm guess the data your
> viewing isnt live data you need to see right away. i'd just index that
> table and run queries off of it. just set all the tables your
> selecting from 'WITH (NOLOCK)' then it wont matter if a record is
> being access at that time. it doesnt sound like your worried about any
> type of dirty reads. if it is being inserted into quite frequently you
> might have to take another approach or run a job to reindex at night.
> i'm not 100% sure of your exact situation. hope that helps.
>
|||Who ever told you that certainly didn't know what they were talking about.
If you are going to update a row you need to know which row to update
otherwise you scan and most likely lock the entire table. If you need to
query it (which that is what tables are for) and don't want all the rows you
also need an index. The trick is to pick the right column(s) to index and
the correct index type. If you post the DDL for the table and a sample of
the queries you run against it we can help you decide on the proper
indexing.
Andrew J. Kelly SQL MVP
"Gary Homewood" <GaryHomewood@.discussions.microsoft.com> wrote in message
news:AE25E15B-FC19-4FDF-BEF1-DEAAA4671C5D@.microsoft.com...[vbcol=seagreen]
> We have been advised in the past that it wasn't a good idea to have
> indexes
> on the transaction table that is being frequently written too i.e. less
> than
> a second between inserts, but I'd still like to query the data. Without
> some
> sort of index, the query ends up doing a full table scan on a massive
> table,
> so I think I need some sort of extract to an indexed version to make these
> queries manageable.
>
> "GlennThomas5" wrote:
|||OK here's what I'm doing. My transaction table is written to potentially
several times a second; this is just an insert, not an update. I was under
the impression that if I had an index on such a large and rapidly expanding
table, there would be a performance hit when the index was recalculated as
rows are written to the table. I would subsequently like to retrieve results
from this table for a particular date range, in which case I do really need
an index on date to avoid doing a full table scan. Is there a way to get SQL
Server to maybe ignore the date index as rows are being inserted, and then
perhaps do a reindexing of the table once a day? Or do I need to extract data
to some sort of warehouse or data cube with a date index? In which case I'd
still have the same problem, because I'd need to extract entries from my very
large transaction table for a particular date.
Thanks for your replies so far. Hope I've phrased my problem a bit more
clearly this time.
"Andrew J. Kelly" wrote:

> Who ever told you that certainly didn't know what they were talking about.
> If you are going to update a row you need to know which row to update
> otherwise you scan and most likely lock the entire table. If you need to
> query it (which that is what tables are for) and don't want all the rows you
> also need an index. The trick is to pick the right column(s) to index and
> the correct index type. If you post the DDL for the table and a sample of
> the queries you run against it we can help you decide on the proper
> indexing.
> --
> Andrew J. Kelly SQL MVP
>
> "Gary Homewood" <GaryHomewood@.discussions.microsoft.com> wrote in message
> news:AE25E15B-FC19-4FDF-BEF1-DEAAA4671C5D@.microsoft.com...
>
>