-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Run Faster Queries
- Thread starter Parra
- Start date
Corticus
Well-known Member
- Joined
- Apr 30, 2002
- Messages
- 1,579
Hi Parra,
One moethod would be to 'pre-filter' the data before it gets to the query.
Say you have to run a bunch of calculations is a query on your main table for a period of time. Instead of putting criteria in this query, make a new query without the calculations, and all the necessary fields, and put the date criteria there. You can optionaly make it a 'make table' query. Now have the calculation query look to the results of this filtered query, or the new made table. That way, you're not running calculations on records you're not interested in analyzing.
The idea is, give your slow query as small a dataset as possible to perform calculations on.
HTH,
One moethod would be to 'pre-filter' the data before it gets to the query.
Say you have to run a bunch of calculations is a query on your main table for a period of time. Instead of putting criteria in this query, make a new query without the calculations, and all the necessary fields, and put the date criteria there. You can optionaly make it a 'make table' query. Now have the calculation query look to the results of this filtered query, or the new made table. That way, you're not running calculations on records you're not interested in analyzing.
The idea is, give your slow query as small a dataset as possible to perform calculations on.
HTH,
Upvote
0
Corticus
Well-known Member
- Joined
- Apr 30, 2002
- Messages
- 1,579
indexing
You're looking at all the records in your main table at once?
That seems like a lot of data (multiple years) to have to look at at one time. What is the purpose of the query? Is it all calculations?
Does your SQL have a 'WHERE' in it?
Another option is indexing, but if it isn's a select query (with criteria) but just calculations, I don't think indexing will help.
The idea of indexing is, say you want to pull all of "John Qs" records. You make a customer table, and give "John Q" a number ID, such. Then, in your select quey, your criteria would look to filter by this ID, and not the actual name. You query numbers much faster than text strings.
We're actually dealing with a problem like this where I work. We have a large client database (~1 million) records, and the db was initially set up to do all client searches and joins on the client's social security number. Unfortunately, this has to be a text field, because some clients have letters in their social security number (I know their not supposed to, but because of privacy issues, our case managers have started using a generic client ID instead of a social, but it still goes in the social field). When searching by the socials, it is very slow. We're in the process of assigning the clients a number ID, and this will query much faster.
HTH,
You're looking at all the records in your main table at once?
That seems like a lot of data (multiple years) to have to look at at one time. What is the purpose of the query? Is it all calculations?
Does your SQL have a 'WHERE' in it?
Another option is indexing, but if it isn's a select query (with criteria) but just calculations, I don't think indexing will help.
The idea of indexing is, say you want to pull all of "John Qs" records. You make a customer table, and give "John Q" a number ID, such. Then, in your select quey, your criteria would look to filter by this ID, and not the actual name. You query numbers much faster than text strings.
We're actually dealing with a problem like this where I work. We have a large client database (~1 million) records, and the db was initially set up to do all client searches and joins on the client's social security number. Unfortunately, this has to be a text field, because some clients have letters in their social security number (I know their not supposed to, but because of privacy issues, our case managers have started using a generic client ID instead of a social, but it still goes in the social field). When searching by the socials, it is very slow. We're in the process of assigning the clients a number ID, and this will query much faster.
HTH,
Upvote
0
Similar threads
- Question
