Viewing 15 posts - 1 through 15 (of 55 total)
I actually pulled this query over a year ago that users ran several times. The idea is to build a list of commonly run queries that I gathered over...
June 13, 2011 at 8:14 am
Craig,
You're right and I don't see how I missed it:
I moved all the conditions in the having clause to a where clause except "Sum(([ALLOCATED_AMOUNT])."
completed in 17 seconds.
Not only does moving...
June 8, 2011 at 9:16 am
Let me continue later today.
June 8, 2011 at 8:32 am
I understand breaking down this query is a good idea and have told the user.
I have statistics with all the referenced columns as the leading column in the statistic definition.
It...
June 6, 2011 at 9:52 am
I don't see stats being outdated as the data has not changed for a year (it's 2009 data).
idx_MOD_CD_EXPEND_CODE_ID is defined as MODCD, EXPENDITURE_CODE_ID with include (TOTL).
The problem is that the...
June 2, 2011 at 7:26 am
The default plan with no hints and
plan with hash join hints attached.
June 1, 2011 at 1:28 pm
All,
Remember what the original issue was: I ran out of space on tempdb due to the query being too large - involving too many tables and rows.
This user has a...
May 31, 2011 at 7:32 am
table that is refreshed often is in the definition of the view prj_id_information_v.
table name is PMATT_PROGRAM_PROJECT.
Tuning Adviser in production db did not provide any recommendations.
I'll see what index improvements I...
May 25, 2011 at 1:47 pm
Thanks for the suggestions.
The first one using like 'xx%' instead of substring is something I understand and have known.
However, it did not help because there is no index that starts...
May 24, 2011 at 2:57 pm
Craig,
Thanks for some good ideas.
The #tmp table seems good. Create it from the table that is refreshed often and use that.
I know the idea of refreshing such a table...
May 24, 2011 at 8:26 am
first attachment does not appear to work.
I zipped sqlplan and attached it here.
May 24, 2011 at 6:47 am
The query does not run in prod as I only have 10 GB of tempdb space.
On dev/test we were able to get about 80 GB of tempdb on a temporary...
May 24, 2011 at 6:44 am
There are 5 or 6 tables in the query plus aggregation.
Most queries need this refresh table, so that's the main problem and why a delete/insert is needed vs. truncate/insert.
There are...
May 23, 2011 at 1:48 pm
I continually inform the users (there is one main user who runs these long queries) to break down the queries, but assume that some queries are just going to take...
May 23, 2011 at 11:33 am
The table is coming from another application on Oracle 10.2.
The rows can change and new rows can also be created. There's about 5,000 rows in this table.
We have a...
May 20, 2011 at 7:06 pm
Viewing 15 posts - 1 through 15 (of 55 total)