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.
-
We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.
Oracle DB question
- Thread starter Martin
- Start date
KLin
Lifer
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?
blackdogdeek
Lifer
can you post the query?
Suspicious-Teach8788
Lifer
Dont tell me you have multiple updates. For SQL, that slows things down... I dun use Oracle though
maximus maximus
Platinum Member
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).
maximus maximus
Platinum Member
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.
maximus maximus
Platinum Member
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.
maximus maximus
Platinum Member
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.
TRENDING THREADS
-
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: 25K
-
Discussion Intel current and future Lakes & Rapids thread- Started by TheF34RChannel
- Replies: 24K
-
-
