Click here to Skip to main content
14,668,708 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a SQL database with a large amount of data that is added to constantly during working hours. I need to be able to run large queries without restricting input.

What is the best way to do this?

Should I query overnight into another server and use the copy?
Posted
Updated 17-Feb-11 2:14am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Mirroring into another database is certainly an option, it depends how 'real time' you need your query data to be

SQL Server Replication[] is something that might help you here.

Transactional Replication[^] is near real-time ... otherwise, simply creating a nightly 'snapshot' of your data for query purposes might be acceptable

How many rows are we talking about?

If it's only a few million, well designed indexes + a data access plan should be able to handle the query load

e.g. Don't just let users link into your tables and perform any queries they want, force them to use a 'query' stored procedure that has been indexed tuned and the plans analysed.
   
Comments
milenalukic 17-Feb-11 8:54am
   
I have about 2.5 million records and need to reference additional tables. This is a dump from a legacy system with no unique columns. I'm currently inserting primary key columns when I can as they take ages so I stop the dump in the meantime. Plan on indexing main queried fields.

I have captured daily inserts through triggers to give information on current activity and am planning reports on historical data.


If I could join the two it will wgive me more flexibility.

Can you suggest the best way forard?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100