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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

amigos

Active Member
Joined
Sep 23, 2003
Messages
407
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
 

Melanie

Board Regular
Joined
Sep 17, 2003
Messages
51
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.
 

amigos

Active Member
Joined
Sep 23, 2003
Messages
407
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
 

Dugantrain

Active Member
Joined
Feb 10, 2003
Messages
354
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

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
Top