SQL Tuning

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
chou
Participant
Posts: 14
Joined: Wed Dec 10, 2003 12:19 pm

SQL Tuning

Post by chou »

Hi All,

What are the ways(steps) in tuning the SQL. could any one help me.

Thanx
vmcburney
Participant
Posts: 3593
Joined: Thu Jan 23, 2003 5:25 pm
Location: Australia, Melbourne
Contact:

Post by vmcburney »

It depends on what database you are using. SQL tuning is mainly done using the native database tools including database tracing, statistics and optimisation tools.

If you have a database DBA support available you may be able to use them. You can either copy and paste the SQL from DataStage plugins into a database tool for optimisation or turn on a database trace and run the datastage jobs. The trace will record the SQL generated by datastage and then there should be tools available to evaluate this SQL and recommend changes to the SQL or indexes on the database to improve performance.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

That said, most databases have an EXPLAIN command, so you can be advised of the execution plan. On this basis you might decide, for example, to add an index on a column that is constrained in your query. However, adding indexes slows down update times, so you can't just go adding indexes to your heart's content, particularly if you're smaller than your DBA!
Tuning SQL is something that takes a long time to learn, there are no quick answers of the kind you appear to be seeking.
The first question you need to ask yourself is what you mean by "tune". The second question is probably what you mean by "performance", which will have a different answer on an OLTP system and an OLAP system.
Then you need to learn what tools are available and which of these might be appropriate to your task. They you have to learn how to use the tools in a meaningful fashion. Only then are you even close to ready to undertake the task of tuning SQL.
That's one of the reasons there are consultants out there who undertake that kind of thing.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chou
Participant
Posts: 14
Joined: Wed Dec 10, 2003 12:19 pm

Post by chou »

Thanks Ray and Vincent
MukundShastri
Premium Member
Premium Member
Posts: 103
Joined: Tue Oct 14, 2003 4:07 am

Post by MukundShastri »

One more input :-
Using parallel hints in your query will increase speed of your query.
But it will not use any indexes created on the table. So typically u can use parallel hints if indexing is creating DB overheads.
Select /*+PARALLEL(a,10) */ X,Y,Z from TABLE a
where X = .....

Here 10 is the number of parallel processes that can be run for this query.
Before using this you need to ensure with your DBA what is the maximum parallelism applicable in your Database. The above is valid for ORACLE database!!


Thanks

Mukund Shastri
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Most databases have some way to force extra (parallel) tasks to be used in processing a query. The real skill is in being able to determine when extra tasks will be useful. If some other aspect of your query demands single-threading, you can request hundreds of extra tasks, but you won't get them. Further, the database engine is monitoring system load (and its own processes) and may limit your request to fewer than the requested number of processes.

Similarly, most databases have a threshhold that governs whether an index will be used (for example, at least 30% of rows must be omitted). You can re-write a query that processes the entire table (and therefore doesn't use the index) as the union of two queries each of which processes half of the rows in the table (and each of which does use an index).

I did a quick check and found no fewer than ten techniques for tuning Red Brick SQL queries. As I said earlier, it's a complex area.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
afreas
Participant
Posts: 19
Joined: Wed Nov 12, 2003 3:56 pm
Location: USA, Virginia

Post by afreas »

Tuning kind of falls in to multiple buckets.

Ways to write your SQL better (at a higher more DBMS independant level)

Ways to write your SQL better for your particular DBMS.

Ways to make your DBMS respond better to your query.

Ways to make your tables more suitable to your query.

There are varying degrees to how you accomplish these. These vary in the degree of dependancy. For example there are things that work very well in a non-partitioned UDB environment but very poorly in a UDB partitioned environment due to broadcasting issues. Another example is that you can set your database to use different optimization schemes in its config file (UDB). In other databases you can hint. But those catagories above can be used as a rough process.

Clean up your basic SQL

Make sure the database is doing what is good for it (explains and such)

Make sure the optimization levels are appropriate for your query load.

Make sure the database has enough information (indexes, runstats and such)

As always YMMV.
ART
Post Reply