Increase Speed

Melanie

Board Regular
Joined
Sep 17, 2003
Messages
51
Does anyone have any suggestions on how to improve the speed at which my queries run?

I am working with a table that contains over 500,000 records. I am running queries off of queries from the original table. In the queries, I am selecting records, doing group bys and even a union query.

When I try and run the final query, which is based on all of the previous queries, it is taking forever. Is it the number of records that is the issue, the types of queries that I am running or simply my computer? I have an IBM ThinkPad.

Thanks in advance for your help.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Well,
is data originaly stored in Access or maybe SQL server?

if it's Access database, the way I speed this up is:
first run make-table query and save results as table and than run another query based on this table - but it might be real pain if data is live and changes a lot :(

Also check your original tables if fields that you search/link by are Indexed etc.

You might use Performance Analyser - sometimes it gives some good ideas

HTH
 
Upvote 0
Do the data types really make a difference in terms of speed?

For example, when I use the Performance Analyzer, it is recommending that I change several of the data types from text to long integer. I also want to make sure that I don't loss any of the data when I convert.

Thanks.
 
Upvote 0
Well before any changes, make a copy of your database.
I just know that Access probably needs less memory for Integers than for Texts, but I have never done that and am not sure if it will make your performance faster :(

you can try there:

www.utteraccess.com
www.dbforums.com

HTH
 
Upvote 0
Have you set Indexes on the data in your source table? Indexes will greatly improve the performance of queries; especially indexes on fields that get included in your Where Clauses. Also, if it is at all possible to rebuild the Union query so that it is a normal Select, than I'd advise you to do so. Be careful with conversion from Text to Long Integer, if you have numbers with decimals in your table, then those values will be replaced with Integers. To handle decimals, you'll need to declare your field as Single. Do you have subqueries? Subqueries are a big no no as the SQL code is not pre-compiled as the SQL in normal queries is.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top