When is a query too big?

Question

Hey guys,

I'm monitoring calls and putting them into a database. I'm putting caller, called, start, end in database. There are an average of 70-80 calls a day (none in the weekend), so it comes to 350-400 a week. The program will be used for a long time, so after a year there will be many items in the database.

A part of the program shows the calls in a graph (volume / day) and also in listbox (who called who). For that I'm using a typical "select * from table" to retrieve the info.

When will the query be so big that the user will experience performance loss?

Update:

I need all the info from the table, so a select * was best to use according to some people.

Each row in the database contains 1 int and 4 strings, simple data.

Answer 1

You didn't specify what your data store is, schema, indexes, etc. So there is very little information to go on.

But, as a general rule (tongue firmly in cheek):

When the users start complaining.

Answer 2

How long is a piece of string?

This depends on so many factors (including what you deem to be a 'performance loss') that it is impossible to give a firm answer here. You're going to have to test it yourself and see

Answer 3

Put a limit on it now.

Do you really need the data for all time? What's reasonable for the graph/list? 30 days? 60 days? User-selectable?

Though, 20000 calls a year is not a huge data volume.

Also, bad form to SELECT * -- you should always specify the column list you're selecting.

Answer 4

If you're only doing a simple select, then I'm guessing millions of records before someone will notice.

Of course, if you're db server is on a slow machine, that will also hinder it.

Are you doing any Where clauses or Grouping? Any Joins? If so, are those on Indexed columns?

The best answer would be: It Depends.

Answer 5

There should never be a performance problem during the life of the application, even if your volume increases. As long as:

• you have good indexes on the fields your queries use.
• you don't do something really silly in your queries.

Without good indexes, you will eventually have problems. A couple less important bits of advice:

• specify the columns you want, rather than using "Select *"
• make sure you're reporting query isn't being called a thousand times a second or something crazy like that.