DSXchange

Hi All,

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

Thanx

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.

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.

Thanks Ray and Vincent

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

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.

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