| Tune This Simple SQL [message #258] |
Fri, 01 February 2002 06:59  |
Jay
Messages: 127
Registered: October 1999
|
Senior Member |
|
|
Hi, can anyone give some tips on tuning this SQL code to run a little faster?
select cycle_code, cycle_run_month, cycle_run_year, count(distinct(ban))
from us0801
where cycle_code = '8'
and cycle_run_month = '01'
and cycle_run_year = '2002'
group by cycle_code, cycle_run_month, cycle_run_year
order by cycle_code, cycle_run_month, cycle_run_year;
|
|
|
|
|
|
| Re: Tune This Simple SQL [message #269 is a reply to message #258] |
Sat, 02 February 2002 07:08  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
The are several ways to improve a perfomance of a query but for this more infos are needed.
How may rows does the table contain?
Is the table partitionned?
Are there any indexes?
Is the table frequently updated?
Have you analyzed the table (ANALYZE TABLE us0801 COMPUTE STATISTICS)?
|
|
|
|
| Re: Tune This Simple SQL [message #306 is a reply to message #258] |
Mon, 04 February 2002 10:52 |
Jay
Messages: 127
Registered: October 1999
|
Senior Member |
|
|
The only column being selected that is indexed is BAN. The table in question/example has a 1,943,792 row count. I don't think that the table is partitioned, is there an easy way for me to tell? The table has frequent inserts, not as may updates, but does have daily updates, it's a usage
table. The analyze command, what's the exact syntax of that so I can run it, I could get the sql below to run. Thanks, Jay
|
|
|
|
| Re: Tune This Simple SQL [message #318 is a reply to message #258] |
Tue, 05 February 2002 09:26 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
An easy way to (probably) improve your query is to create an index over the columns cycle_code, cycle_run_month and cycle_run_year.
CREATE INDEX i_us0801_y_m_c ON us0801(cycle_run_year, cycle_run_month, cycle_code);
Once the index is created execute:
ANALYZE TABLE us0801 COMPUTE STATISTICS;
HTH
Mike
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
