Thursday, September 17, 2009

How to find informix cost factor

Cost factor of a table is used to measure the performance of the sql executions. When your table cost factor is high, it will decrease the sql exection . That means it will get some times to generate resultsets of the sql.
Here is the way to measure informix cost factor.
Example :-
set explain on;
select * from table
  • Use set explain on; before your sql stetement in telnet client in dbaccess mode in informix.
  • Then exit dbaccess mode and type "ls -ltrh" in your home directory.
  • This will list all the files. Go to end. You will find a file called as "sqexplain.out".
  • Vi the file. Then press "shift+g" to end of the file to see your table cost factor details.

Friday, September 11, 2009

sql for monthly,yearly report

select year(added_on),month(added_on),count(*) from table_name
group by 1,2
order by 2

This will produce a resultset for monthly yearly reports.

Ex :-

Year Month Records
------ ------ --------
2009 5 14416
2009 6 788
2009 7 15413
2009 8 16091

Wednesday, September 2, 2009

Mysql today in where clause

select * from table where added_on=date(now());

To use this sql you must have used "date" datatype for your table column.
select * from table where added_at=now();

This works fine for "time" columns too .