473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Slow query on date field via views

I have several views with unions, outer joints and calculations that later combine to find a "final"
report view.
This was to make things easier for the web-based report programmer to just pull the values from this
view.

If I query the view by the id/index/primary_key, the view runs fine (about 3secs)

e.g: Select *
from final_view
where Id_Index in (123, 456, 789)

However, when the view is queried by dates, it is slow.
More like impossible actually 'cos the temp table space gets filled up (32GB!) and oracle returns an
error about
being unable to extend.

E.g: Select *
from final_view
where Submission_Date=to_date('01-01-2005', 'dd-mm-yyyy')
I tried indexing the Sub_Date but it didn't help.

But if I run the query directly on the table in question (with the sub_date), the query works fine &
fast,
with or without the indexing.

Anyone got any pointers?

I'm using oracle 9i.
And the 3 big tables which the view taps on have about 100,000 records each.
Thanks

To e-mail, remove the obvious
May 10 '06 #1
2 11394
Sybrand Bakker <post...@sybrandb.demon.nl> wrote:
Did you get the explain plans and compare them?
Please get the explain plans and post them here. --
Sybrand Bakker, Senior Oracle DBA


Ok, after much fiddling around and running sqlanalyze I was able to optimize the query pretty well.
It told me to use a "union all" instead of "union". I hope I don't end up with dupe rows.
That did help pretty much.

However, it still took me 25 secs to run the below query:

Select w.*
from client.weekly_report_details w
where w.submission_date between to_date('17-Apr-2005', 'DD-MON-YYYY') and to_date('30-Apr-2005',
'DD-MON-YYYY')
and w.adv_id=42

If I use the index then it runs about 2 secs:

Select w.*
from client.weekly_report_details w
where w.submission_date between to_date('17-Apr-2005', 'DD-MON-YYYY') and to_date('30-Apr-2005',
'DD-MON-YYYY')
and w.policy_id in (63771, 63923, 63924)

I found more nasties in the jsp report the vendor wrote.
He was using to_char() on the date fields.

My plan table seems to be pretty big :)
I have no idea how to read it.

I hope a small binary does not irk people here

Thanks

To e-mail, remove the obvious
May 16 '06 #2
Sybrand Bakker <post...@sybrandb.demon.nl> wrote:
Did you get the explain plans and compare them?
Please get the explain plans and post them here. --
Sybrand Bakker, Senior Oracle DBA


Ok, after much fiddling around and running sqlanalyze I was able to optimize the query pretty well.
It told me to use a "union all" instead of "union". I hope I don't end up with dupe rows.
That did help pretty much.

However, it still took me 25 secs to run the below query:

Select w.*
from client.weekly_report_details w
where w.submission_date between to_date('17-Apr-2005', 'DD-MON-YYYY') and to_date('30-Apr-2005',
'DD-MON-YYYY')
and w.adv_id=42

If I use the index then it runs about 2 secs:

Select w.*
from client.weekly_report_details w
where w.submission_date between to_date('17-Apr-2005', 'DD-MON-YYYY') and to_date('30-Apr-2005',
'DD-MON-YYYY')
and w.policy_id in (63771, 63923, 63924)

I found more nasties in the jsp report the vendor wrote.
He was using to_char() on the date fields.

My plan table seems to be pretty big :)
I have no idea how to read it.

I hope a small binary does not irk people here

Thanks

To e-mail, remove the obvious
May 18 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: YoBro | last post by:
Hi, I have a database that stores in a field the number of views for a product. I want to write a query that gets the two highest views, so I can then output the result. I have this so far,...
6
by: Umar Farooq | last post by:
Hello all, Please bear with the long explanation of my scenario. As I'm relatively new to the query world, I like to write my queries using the visual toos such as the "View" option in SQL...
5
by: krystoffff | last post by:
I currently have PostgreSQL 7.1 installed on a server with about 700 Mb of RAM. I have many problems of speed with a database I created. For example, it took almost 12 sec to run the query...
15
by: Rolan | last post by:
There must be a way to enhance the performance of a query, or find a plausible workaround, but I seem to be hitting a wall. I have tried a few tweaks, however, there has been no improvement. ...
4
by: cefrancke | last post by:
Are there any ways to speed up a Cumulative sum in a query or perhaps another faster way to have a cumulative sum column (call a vba function?). For example, I want to sum up all values under...
1
by: jpatchak | last post by:
Hi Guys, Sorry if this is a really stupid question. I am trying to upsize my Access database to SQL server. When I used the Access upsizing wizard, some of my queries didn't get upsized so I am...
9
by: Emin | last post by:
Dear Experts, I have a fairly simple query in which adding a where clause slows things down by at least a factor of 100. The following is the slow version of the query ...
3
by: Antoni | last post by:
Hello, I have a table with more than 1,000,000 rows. mysqldescribe views_date; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key |...
2
by: AcCeSsDeNiEd | last post by:
I have several views with unions, outer joints and calculations that later combine to find a "final" report view. This was to make things easier for the web-based report programmer to just pull the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.