There is an Oracle (I don?t know which version) database. One table has about 1.2m records, the other about 0.5m. When I run a query with an inner join and a COUNT built-in, it takes about 30-40 seconds for a result to be returned. Does that seem extremely slow to anyone else? for the record, I also don't know what hardware it runs on.
Oracle DB question
- Thread starter Martin
- Start date
KLin
Lifer
- Feb 29, 2000
- 30,351
- 671
- 126
http://forums.anandtech.com/categories.aspx?catid=33&flcache=4321916&entercat=y
What program are you using to build the query?
What program are you using to build the query?
Dont tell me you have multiple updates. For SQL, that slows things down... I dun use Oracle though
check the explain plan to see if you are missing any joins which results in a full-table scan. also check the join path to see if creating certain indexes may help
Check for the following
1) See the explain plan of the query. Try to reduce the number of full table scans.
2) Have a primary key or an index on the key columns.
In worst case, consider partitioning of the table (in case certain parameters in a query are going to remain constant. Like, the query is always run for a partiicular year etc. I hope you get the idea).
I can't post the query, as this is from work, nor can I do any modifications to the DB, as I don't own it and didn't design it.
Even the query I am using is a modified version of a stored procedure someone wrote. I think it may be the hardware its running on, though I don't have access to that, so I don't know.
Even the query I am using is a modified version of a stored procedure someone wrote. I think it may be the hardware its running on, though I don't have access to that, so I don't know.
Generally if its a hardware problem, you will see the problem in all your queries.
Is that so?
In case you are facing the problem only in this query, then it is definitely not a hardware issue.
Let me ask you this. Has the query been running fine till now and deteriorated all of a sudden?
Max.
You are saying that you are unable to change anything in the query nor make any changes anywhere... so that pretty much means that this query is going to run slowly all the time. With increased data volume the query performance is going to deteriorate each day.
To answer your original question, yes the query is slow by any standard.
Those times aren't unreasonable. Check to see if there is an index on the column you are trying to join on, most likely there isn't, which is what is causing the long run time. It might be doing a full table scan on both tables.
If you are sure there is a foreign key relationship on that column between the 2 tables, you can force it to use the rule based optimizer instead of the default cost-based optimizer that oracle now uses.
do:
select /*+ RULE */ blahblah
the /*+ RULE */ forces it to use rule based, which will use indexes if they exist. Note the + is right after the *. Letting it do a cost-based sometimes makes it use hash-joins between 2 tables, which often times makes it run longer.
You can see all this if you get an explain plan for the query.
If you are sure there is a foreign key relationship on that column between the 2 tables, you can force it to use the rule based optimizer instead of the default cost-based optimizer that oracle now uses.
do:
select /*+ RULE */ blahblah
the /*+ RULE */ forces it to use rule based, which will use indexes if they exist. Note the + is right after the *. Letting it do a cost-based sometimes makes it use hash-joins between 2 tables, which often times makes it run longer.
You can see all this if you get an explain plan for the query.
this has got all the signs of a full table scan
if you post the query, you'll likely get a lot more help -- or specifics on what to look at/ask-the-DBA about.
if you're worried about divulging secret table/field names, just re-write it for us saying something like: "select A,B,C from X,Y,Z where X.aa = Z.aa and..."
as a dba, i've seen queries that could be tweaked a tiny bit and performance goes from 2min to <2sec. The funny thing is, often people don't even ask if it can be sped up. And when it does get tweaked, they think something is wrong with it because all of the sudden it's so fast!
if you post the query, you'll likely get a lot more help -- or specifics on what to look at/ask-the-DBA about.
if you're worried about divulging secret table/field names, just re-write it for us saying something like: "select A,B,C from X,Y,Z where X.aa = Z.aa and..."
as a dba, i've seen queries that could be tweaked a tiny bit and performance goes from 2min to <2sec. The funny thing is, often people don't even ask if it can be sped up. And when it does get tweaked, they think something is wrong with it because all of the sudden it's so fast!
What do you mean a COUNT built in? Do you mean the query is only selecting a count of the records, or the count is involved somehow? If you are only returning a count, it seems awfully slow. If it's a web app and you are displaying 500 records on the screen, this is not all that out of line.
Just cause I'm bored, I did a count of the number of records on a table with 52m records and it took 3m22s to return on our development server. So counting tons of records is just slow in general.
Well, I guess it may be the counts. It takes 2s to count 0.5 Records and 6s to count 1.2m, with no joins or constraints...
TRENDING THREADS
-
News NVIDIA and Intel to Develop AI Infrastructure and Personal Computing Products- Started by poke01
- Replies: 281
-
Discussion Zen 5 Speculation (EPYC Turin and Strix Point/Granite Ridge - Ryzen 9000)- Started by DisEnchantment
- Replies: 25K
-
Discussion Intel Meteor, Arrow, Lunar & Panther Lakes + WCL Discussion Threads
- Started by Tigerick
- Replies: 22K
-
Discussion Intel current and future Lakes & Rapids thread- Started by TheF34RChannel
- Replies: 23K
Facebook
Twitter