We are currently in the process of evaluating (POC) bright house as a datawarehouse tool and hold data large amount of data.

The database currently has around 150gb dat size.  I have a stored procedure which requires a user to input few parameters and return the data from the various tables in the bright house database. The stored procedure was originally written in sybase. We are planning to move to use mysql environment and probably info bright for datawarehouse solution.

The problem i am having is that my stored procedure has local variables which i need to call to handle date input into specific format e.g.

Create procedure test
(  startDate varchar(14),
  endDate varchar(14),
UserId varchar(64),
corpId varchar(32),
group varchar(5),
subordinates varchar(5)

)
Begin
      declare starttime datetime;
      declare enddatetime datetime;
      declare Region varchar(32);
        set Region = ‘GLOBAL’;

set starttime = cast(concat(cast(startDate as char(10)), ’ 00:00:00’) as datetime);
set enddatetime = cast(concat(endDate char(10)), ’ 23:59:59’) as datetime);

CREATE temporary TABLE tt_exch
      AS
      select ExchangeCode,TSExchange,Region,
      CAST(SUBSTRING(TimeZone,1,3) AS DECIMAL(3,1)) ‘TimeZone’,
      TIMESTAMPADD(HOUR,-CAST(SUBSTRING(TimeZone,1,3) AS DECIMAL(3,1)),‘2008-01-01 00:00:00’) ‘StartTime’,
      TIMESTAMPADD(HOUR,-CAST(SUBSTRING(TimeZone,1,3) AS DECIMAL(3,1)),‘2008-02-02 00:00:00’) ‘EndTime’   
    from gblExchange
where Region = IFNULL(Region,Region);

so therefore i need to ensure that users only input date format as 20081001 and my stored proc needs to be able to handle and convert this with times to get accurate results…an i wish to use the starttime from exch table created etc….

The bottom line is how can use local variables in info bright to ensure i get best optimization.

Also there are few temporary tables in my stored procedure. Does info bright support this? If not, How can i get around? I also have few if statements to convert the users input parameter into other format e.g If group= ‘Y’ then group = ‘True’ e.t.c

kind regards

Naresh
TradingScreen

Hi !

About the expected performance: Infobright Optimizer does not support any functions (TIMESTAMPADD, CAST, SUBSTR, IFNULL), so the SELECT statement will be executed by mysql. Because of that do not expect a performance better than mysql.
Usually data reformatting is done on source files before loading. Then the full speed can obtained.

Cheers,
JanuszB

Hi JanuszB

This is bit confusing now.

I have a BRIGHTHOUS E 2 . 6 USER GUIDE which states that these functions are supported by info bright.

We are using enterprise edition.

Brighthouse 2.6
USER GUIDE

A. SUPPORTED FUNCTIONS AND OPERATORS
The following functions and operators are supported in Brighthouse. For more information,
see “Functions and Operators” in the MySQL 5.1 Reference Manual.

Comparison Functions and Operators
—————————————————————-
EQUAL =
NULL SAFE EQUAL <= >
NOT EQUAL <> , !=
LESS THAN OR EQUAL <=
LESS THAN <
GREATER THAN >
GREATER THAN OR EQUAL >=
IS
IS NOT
IS NULL
IS NOT NULL
BETWEEN … AND …
NOT BETWEEN … AND … (Limitations)
COALESCE
GREATEST
IN
NOT IN
ISNULL
INTERVAL
LEAST
Logical Operators
NOT, !
AND, &&
OR, | |
XOR

Control Flow Functions
—————————————
CASE
IF
IFNULL
NULLIF

String Functions
————————-
ASCII
BIN
BIT_LENGTH
CHAR
CHAR_LENGTH
CONCAT
CONCAT_WS
CONV
ELT
EXPORT_SET
FIELD
FIND_IN_SET
FORMAT
HEX
INSERT
INSTR
LCASE
LEFT
LENGTH
LOAD_FILE
LOCATE
LOWER
LPAD
LTRIM
MAKE_SET
MID
OCT
OCT_LENGTH
ORD
POSITION
QUOTE
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SOUNDEX
SOUNDS LIKE
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UNHEX
UPPER

String Comparison Functions
—————————————————
LIKE (The escape character is not supported.)
NOT LIKE (The escape character is not supported.)
REGEXP
NOT REGEXP
STRCMP

Numeric Functions
—————————
Arithmetic Operators
Addition ( + )
Subtraction ( - )
Multiplication ( * )
Division ( / )

Mathematical Functions
—————————————-
ABS, ACOS, FORMAT, LOG … etc.

Date and Time Functions
—————————————-
ADDDATE
ADDTIME
CONVERT
CURDATE
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
FROM_UNIXTIME
GET_FORMAT
HOUR
LAST_DAY
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIMEDIFF
TIMESTAMP
TIMESTAMPADD
TIMESTAMPDIFF
TIME_FORMAT
TIME_TO_SEC
TO_DAYS
UNIX_TIMESTAMP
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK

Other Functions
————————-
Bit Functions
Encryption and Compression Functions
Information Functions
Miscellaneous Functions

Functions and Modifiers to Use with Group By Clauses
—————————————————————————————-
Group By Aggregate Functions
AVG
BIT_OR
BIT_AND
BIT_XOR
COUNT
GROUP_CONCAT
MIN
MAX
STD, STDDEV
SUM
VAR_POP
VAR_SAMP
VARIANCE

Group By Modifiers
—————————-
ROLLUP

Hi Naresh,

I think that there is some confusion here.

Infobright supports the MySQL Ansi-92 standard, and the list below describes this.  However, certain functions are supported by the Infobright optimizer and others are ‘flipped over’ to the MySQL optimizer where performance is reduced.

My team is looking at your stored procedure and will post some suggestions shortly.

Regards,

Hi John

Thanks for quick reply.

It will be, also, nice to have some tips on how to handle temporary tables and local variables in stored procedure to ensure best optimization is achieved as most of our stored procedures use these features and we need to find ways to transfer this for our webreporting to work efficiently.

Thank you in advance.

regards

Naresh

Hi Naresh,

Sorry for taking so long to post a reply – we wanted to go through your posting in detail.

We’ve tried to break out the questions that you had as follows:
1)    Does Infobright support stored procedures?
Yes. You need to set the delimiter character to define the procedure, then change the delimiter back when you are done. See the example below in #2.
2)    Does Infobright support the use of local variables within stored procedures?
Yes. You can declare variables and use them just as in Sybase. You can use MySQL-supported functions in expressions to set the value of local variables.
Here’s an example that takes a date formatted as a VARCHAR (‘YYYYMMDD’) and converts it to a string (‘YYYY-MM-DD 23:59:59’) that can be loaded into a DATETIME column in Infobright.
drop table if exists testdt;
create table testdt (
  x datetime
) engine=brighthouse;
drop procedure if exists t;
delimiter |
create procedure t(
—startdate format ‘20010101’
  startdate varchar(8)
) begin
  declare startdate_f varchar(19);
 
—convert from ‘yyyymmdd’ to ‘yyyy-mm-dd 23:59:59’ format
  set startdate_f = concat(
    substring(@startdate, 1, 4), ‘-’,
    substring(@startdate, 5, 2), ‘-’,
    substring(@startdate, 7, 2),
    ’ 23:59:59’);
   
—insert it to Infobright
—(conversion to datetime happens on parsing the value)
  insert into testdt (x) values (startdate_f);
 
  select * from testdt;
end |
delimiter ;
call t(‘20010101’);

3)    Are temporary tables available in Infobright?
Yes, they are available in Infobright Enterprise Edition (IEE), not the Community Edition.  You can use the following syntax:
create temporary table mytemptable engine=brighthouse as select ………….;

When installed, Infobright sets the default table engine to Brighthouse.  However, we strongly recommend that you explicitly define the engine in your create table statement.

4)    what is the deal with functions?
You can use functions like CONCAT(), SUBSTRING(), etc.  in queries, but they are not currently implemented in the Infobright optimizer. This means that the query execution will use the MySQL optimizer, and consequently, performance will be much slower than for Infobright-supported functions. However, if your table is very small (few thousand rows) then the performance should not be an issue.

Note it is fine to use functions in places like the SET statement above, because that isn’t touching any Infobright tables.
We do support simple arithmetic for numeric types (for example, select a + b ...)

If you are using a lot of functions (especially on large-volume tables), then consider using an ETL approach to precompute function values and add them as new columns in your table. Because Infobright is column-based and uses compression, the cost in additional storage by this approach is often not much of an issue.
We expect to have support for some functions by Q4 of 2008. In the meantime, you can also think about changing your design to avoid the need to use functions in queries as much as possible.
Geoffrey

Another point: As far as I can tell, your where clause

  where Region = IFNULL(Region,Region);

always gives the same results as:

  where Region IS NOT NULL;

except the latter is fully supported by the Infobright optimizer. What was the reason for using IFNULL?

Geoffrey

Hi Geoffrey

Thanks for the good response.

Regarding your question about Region in the where clause:

Create procedure test
( startDate varchar(14),
endDate varchar(14),
UserId varchar(64),
corpId varchar(32),
group varchar(5),
subordinates varchar(5)

)
Begin
declare starttime datetime;
declare enddatetime datetime;
declare Region varchar(32);
set Region = ‘GLOBAL’;

set starttime = cast(concat(cast(startDate as char(10)), ‘ 00:00:00’) as datetime);
set enddatetime = cast(concat(endDate char(10)), ‘ 23:59:59’) as datetime);

if(Region = ‘GLOBAL’) then
    SET Region = NULL;
  END IF; 

CREATE temporary TABLE tt_exch
AS
select ExchangeCode,TSExchange,Region,
CAST(SUBSTRING(TimeZone,1,3) AS DECIMAL(3,1)) ‘TimeZone’,
TIMESTAMPADD(HOUR,-CAST(SUBSTRING(TimeZone,1,3) AS DECIMAL(3,1)),’2008-01-01 00:00:00’) ‘StartTime’,
TIMESTAMPADD(HOUR,-CAST(SUBSTRING(TimeZone,1,3) AS DECIMAL(3,1)),’2008-02-02 00:00:00’) ‘EndTime’
from gblExchange
where Region = IFNULL(Region,Region);

I have attached the code which i forgot to attach earlier to explain why i had to use IFNULL.. I suppose this is a wrong usage of IF function/statement in mysql.

Basically, Region is a variable that will prompt the users to select with default value being ‘GLOBAL’ which in the database table is a column called Region in the table gblExchange. This value is null for GLOBAL otherwise there are other values e.g. AMERICA, ASIA etc populated and thats why its first calling the default value as GLOBAL otherwise selecting from the list of Region values from the temporary table…Hope this makes sense.

Thanks for the reply to my earlier queries regarding local variables and temporary tables.

I now understand that info bright, although, supports few functions the original problem can be eliminated using the ETL to ensure computed columns are populated in a table for info bright optimization otherwise default to mysql optimizer.

Also, finally. do you know when you anticipate to have DML Update command working in bright house? This can be a very useful feature overall.

Naresh

Hi Nash!

Sorry for my so short reply, but I was sure there will be a follow up . John and Geoffrey know more about this stuff.

using the ETL to ensure computed columns are populated in a table

Yes, that is the clue. As already stated, IB columnwise and compressed storage makes it possible and still efficient to store even redundant data, so you can add columns holding properly formatted data.

Update - it is working in IEE.  Maybe your manual is not up to date. However, IB is targeted to data warehousing, so update is not designed to work as efficiently as in OLTP databases.

Oh, IB = Infobright, the current name for Brighthouse.

Cheers,
JanuszB