Btukfyl

I realise the answer should probably be 'as little time as possible' but I'm trying to learn how to optimise databases and I have no idea what an acceptable time is for my hardware.

For a start I'm using my local machine with a copy of sql server 2008 express. I have a dual-core processor, 2GB ram and a 64bit OS (if that makes a difference). I'm only using a simple table with about 6 varchar fields.

At first I queried the data without any indexing. This took a ridiculously long amount of time so I cancelled and added a clustered index (using the PK) to the table. This cut the time down to 1 minute 14 sec. I have no idea if this is the best I can get or whether I'm still able to cut this down even further?

Am I limited by my hardware or is there anything else I can do to my table/database/queries to get results faster?

FYI I'm only using a standard SELECT * FROM to retrieve my results.

Thanks!

EDIT: Just to clarify, I'm only doing this for testing purposes. I don't NEED to pull out all the data, I'm just using that as a consistent test to see if I can cut down the query times.

I suppose what I'm asking is: Is there anything I can do to speed up the performance of my queries other than a) upgrading hardware and b) adding indexes (assuming the schema is already good)?

I think you are asking the wrong question.

First of all - why do you need so many articles at one time at the local machine? What do you want to do with them?

Why I'm asking? I think this amount of data will be transfered to somewhere. And only at this time you should measure the time of transfering the data.

And even in this situation I want to make advice to you:

Your applications should not select 5 million records at the time. Try to split your query, and get data partially.

UPDATE:

As you say are doing this for testing, I suggest you to:

1. Remove * from your query - SQL server spends some time to resolve this.


2. Try to put your data to some temp data storage. Try to use VIEW or temp table for this.


3. Try to use some cache plan on your server
   

But I still don't understand - why do you need such tests if your application would not ever use such query? Testing only for testing is bad time spending.

Look at the query execution plan. If your query is doing a table scan, it will obviously take a long time. The query execution plan can help you decide what kind of indexing you would need on the table. Also, creating table partitions can help sometimes in cases where the data is partitioned by a condition (usually date and time).

The best optimized way depends on the indexing strategy you choose. As many of the above answers, i too would say partitioning the table would help sometimes. And its not the best practise to query all the billion record in a single time frame. Will give you much bettered resuld if you could try to query partially with the iterations. you may check this link to clear the doubts on the minimum requirements for the Sql server 2008 Minimum H/W and S/W Requirements for Sql server 2008

When fecthing 5 million rows you are almost 100% going spool to tempdb. you should try to optimize your temp Db by adding additional files. if you have multiple drives on seperate disks you should split the table data into different ndf files located on seperate disks. parititioning wont help when querying all the data on the disk
U can also use a query hint to force parrallelism MAXDOP this will increase the CPU utilization. Ensure that the columns contain few nulls as possible and rebuild ur indexes and stats

I did 5.5 million in 20 seconds. That's taking over 100k schedules with different frequencies and forecasting them for the next 25 years. Just max scenario testing, but proves the speed you can achieve in a scheduling system as an example.