SQLite: Some More SQL Tuning

In SQLite: Join And Group vs Group By And Join, I mentioned about the need to rewrite the SQL to make it perform better. In this post, I am going to discuss a few more that I did.

Note that when doing performance tuning, rewriting of the SQL to make it perform is one of the ways while the other way is to create the appropriate indexes if applicable. Restructuring the query is needed from smaller databases to large and commercial databases. However, the big guys like Oracle are capable of handling a lot more scenarios. Here are two of my cases that I had to rewrite

1. co-related sub-query:

select … from p where … abc in (select xyz from ch where p.l = ch.m);

is changed to

select … from p,ch where … p.l = ch.m and abc = xyz;

2. transitive filter:

select … from p where … abc = (select id from c where n = ?) and xyz in (select lid from ch where id = abc);

Here, the index is on xyz. So, changing it to

select … from p,ch where … abc = lid and id = (select id from c where n = ?) and xyz = lid;

worked because the plan would then use the index on id and index on xyz.

SQL tuning is all about understanding the access paths and helping the databases a bit in case they are not smart enough to figure out things themselves.

Advertisements

Leave a comment

Filed under performance tuning, SQL Tuning, SQLITE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s