How long should a query that returns 5 million records take?

Question

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)?

Answer 1

I think you are asking the wrong question.

First of all - why do you need so many articles at one time on the local machine? What do you want to do with them? I'm asking because I think you want to transfer this of data to somewhere, so you should be measuring how long it takes to transfer the data.

Some advice:

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

UPDATE:

Because you are doing this for testing, I suggest that you

1. Remove * from your query - it takes SQL server some time to resolve this.
2. Put your data in temporary storage, try using VIEW or a temporary table for this.
3. Use plan caching on your server

to improve performance. But even if you're just testing, I still don't understand why you would need such tests if your application would never use such a query. Testing just for the sake of testing is a bad use of time