Infobright.org Forums Copyright (c) 2013 ExpressionEngine tag:infobright.org,2013:06:19 query taking 3-4 minutes in Enterprsie Edition ( 95 million data ) tag:infobright.org,2013:Forums/register/viewthread/.3410 2013-05-25T02:46:13Z kannan Hi All

  I am using Infobright Enterprise Edition for a while ,and it is performing good ,there is table called WORLDBASE which is having 95 million data,all searches are happening in this WORLDBASE table only (which is taking max of 50 secs),there is new requirement to implement radius search in application in the table WORLDBASE which is having 95 million data ...

Implemented the Radius search using latitude ,langitude in WORLDBASE Table ,Result output is fine but the performance is too slow ,it is taking 3-4 minutes for the Radius search ,Below mentioning all the details ,please suggest me how to improve the performance .

WORLDBASE Table :

CREATE TABLE `WORLDBASE` (
  `COMPANYNAME` varchar(120) DEFAULT NULL,
  `INTELLECTID` int(20) DEFAULT NULL,
  `DUNSNO` int(9) DEFAULT NULL,
  `COMPANYTYPECODE` tinyint(1) DEFAULT NULL,
  `LOCATIONTYPECODE` tinyint(1) DEFAULT NULL,
  `PHYSICALCOUNTRYCODE` int(4) DEFAULT NULL,
  `PHYSICALCOUNTYCODE` int(4) DEFAULT NULL,
  `PHYSICALSTATECODE` int(4) DEFAULT NULL,
  `PHYSICALCITYDIGIT` int(6) DEFAULT NULL,
  `WORLDREGIONCODE` tinyint(1) DEFAULT NULL,
  `USMETROPOLITANAREACODE` int(4) DEFAULT NULL,
  `SIC1DIGIT` int(1) DEFAULT NULL,
  `SIC2DIGIT` int(2) DEFAULT NULL,
  `SIC3DIGIT` int(3) DEFAULT NULL,
  `SIC4DIGIT` int(4) DEFAULT NULL,
  `SIC5DIGIT` int(5) DEFAULT NULL,
  `SIC6DIGIT` int(6) DEFAULT NULL,
  `SIC7DIGIT` int(7) DEFAULT NULL,
  `SIC8DIGIT` int(8) DEFAULT NULL,
  `NAICCODE1DIGIT` int(1) DEFAULT NULL,
  `NAICCODE2DIGIT` int(2) DEFAULT NULL,
  `NAICCODE3DIGIT` int(3) DEFAULT NULL,
  `NAICCODE4DIGIT` int(4) DEFAULT NULL,
  `NAICCODE5DIGIT` int(5) DEFAULT NULL,
  `NAICCODE6DIGIT` int(6) DEFAULT NULL,
  `SLS` bigint(20) DEFAULT NULL,
  `SLSCODE` tinyint(1) DEFAULT NULL,
  `EMPLOYEESALLSITES` int(10) DEFAULT NULL,
  `EMPLOYEESTHISSITE` int(10) DEFAULT NULL,
  `EMPLOYEESALLSITESCODE` tinyint(1) DEFAULT NULL,
  `SUBSIDIARYSTATUSCODE` tinyint(1) DEFAULT NULL,
  `EXPORTERINDICATORCODE` tinyint(1) DEFAULT NULL,
  `MINORITYOWNEDINDICATORCODE` tinyint(1) DEFAULT NULL,
  `OWNSRENTSINDICATORCODE` tinyint(1) DEFAULT NULL,
  `WOMANOWNEDINDICATORCODE` tinyint(1) DEFAULT NULL,
  `YEAR1EMPLOYEEGROWTH` int(10) DEFAULT NULL,
  `YEAR1SLSGROWTH` int(20) DEFAULT NULL,
  `FACILITYSIZE` int(10) DEFAULT NULL,
  `FAXNO` varchar(25) DEFAULT NULL,
  `LINEOFBUSINESS` varchar(128) DEFAULT NULL,
  `PHONENO` varchar(25) DEFAULT NULL,
  `URL` varchar(255) DEFAULT NULL,
  `FAMILYTREEDIASCODE` varchar(9) DEFAULT NULL,
  `GLOBALULTDUNSNO` int(8) DEFAULT NULL,
  `ZIPCODE` varchar(16) DEFAULT NULL,
  `CURRENCY` varchar(4) DEFAULT NULL,
  `PHYSICALADDRESS` varchar(150) DEFAULT NULL,
  `PHYSICALCITY` varchar(64) DEFAULT NULL,
  `PHYSICALCITYCODE` varchar(10) DEFAULT NULL,
  `PHYSICALSTATE` varchar(50) DEFAULT NULL,
  `PHYSICALSTATEPROVINCECODE` varchar(20) DEFAULT NULL,
  `PHYSICALCOUNTY` varchar(50) DEFAULT NULL,
  `PHYSICALZIPCODE` varchar(16) DEFAULT NULL,
  `PHYSICALCOUNTRY` varchar(100) DEFAULT NULL,
  `MAILINGADDRESS` varchar(150) DEFAULT NULL,
  `MAILINGCITY` varchar(64) DEFAULT NULL,
  `MAILINGCITYCODE` varchar(10) DEFAULT NULL,
  `MAILINGSTATE` varchar(50) DEFAULT NULL,
  `MAILINGSTATEPROVINCECODE` varchar(20) DEFAULT NULL,
  `MAILINGCOUNTY` varchar(50) DEFAULT NULL,
  `MAILINGZIPCODE` varchar(16) DEFAULT NULL,
  `MAILINGCOUNTRY` varchar(100) DEFAULT NULL,
  `EXPORTERINDICATOR` varchar(1) DEFAULT NULL,
  `LATITUDE` varchar(12) DEFAULT NULL,
  `LONGITUDE` varchar(12) DEFAULT NULL,
  `MANUFACTURERINDICATOR` varchar(1) DEFAULT NULL,
  `MARKETINGPRESCREENSCORE` varchar(1) DEFAULT NULL,
  `MINORITYOWNEDINDICATOR` varchar(1) DEFAULT NULL,
  `NAICCODE` varchar(6) DEFAULT NULL,
  `NAICDESC` varchar(150) DEFAULT NULL,
  `NAICS2` varchar(6) DEFAULT NULL,
  `NAICSDESC2` varchar(120) DEFAULT NULL,
  `NAICS3` varchar(6) DEFAULT NULL,
  `NAICSDESC3` varchar(120) DEFAULT NULL,
  `NAICS4` varchar(6) DEFAULT NULL,
  `NAICSDESC4` varchar(120) DEFAULT NULL,
  `NAICS5` varchar(6) DEFAULT NULL,
  `NAICSDESC5` varchar(120) DEFAULT NULL,
  `NAICS6` varchar(6) DEFAULT NULL,
  `NAICSDESC6` varchar(120) DEFAULT NULL,
  `SICCODE` varchar(8) DEFAULT NULL,
  `SICDESC` varchar(150) DEFAULT NULL,
  `PRIMARY8DIGITSIC` varchar(8) DEFAULT NULL,
  `PRIMARY8DIGITSICDESC` varchar(150) DEFAULT NULL,
  `SIC2` varchar(8) DEFAULT NULL,
  `SICDESC2` varchar(150) DEFAULT NULL,
  `SIC3` varchar(8) DEFAULT NULL,
  `SICDESC3` varchar(150) DEFAULT NULL,
  `SIC4` varchar(8) DEFAULT NULL,
  `SICDESC4` varchar(150) DEFAULT NULL,
  `SIC5` varchar(8) DEFAULT NULL,
  `SICDESC5` varchar(150) DEFAULT NULL,
  `SIC6` varchar(8) DEFAULT NULL,
  `SICDESC6` varchar(150) DEFAULT NULL,
  `COMPANYTYPE` varchar(10) DEFAULT NULL,
  `OWNSRENTSINDICATOR` varchar(1) DEFAULT NULL,
  `LOCATIONTYPE` varchar(1) DEFAULT NULL,
  `ACCOUNTINGFIRM` varchar(50) DEFAULT NULL,
  `WOMANOWNEDINDICATOR` varchar(1) DEFAULT NULL,
  `USMETROPOLITANAREA` varchar(4) DEFAULT NULL,
  `GLOBALULTDUNS` varchar(9) DEFAULT NULL,
  `GLOBALULTNAME` varchar(120) DEFAULT NULL,
  `IMMEDIATEPARENTDUNS` varchar(9) DEFAULT NULL,
  `IMMEDIATEPARENTNAME` varchar(120) DEFAULT NULL,
  `FAMILYTREEHIERARCHYCODE` varchar(2) DEFAULT NULL,
  `FAMILYTREEMEMBERCOUNT` int(10) DEFAULT NULL,
  `FAMILYTREERELATIONTYPE` varchar(1) DEFAULT NULL,
  `PHONEAREACODE` varchar(9) DEFAULT NULL,
  `SUBSIDIARYSTATUS` varchar(1) DEFAULT NULL,
  `STOCKEXCHANGE` varchar(50) DEFAULT NULL,
  `STOCKSYMBOL` varchar(12) DEFAULT NULL,
  `YEAROFFOUNDING` varchar(4) DEFAULT NULL,
  `TRADESTYLE` varchar(120) DEFAULT NULL,
  `URLTYPE` varchar(1) DEFAULT NULL,
  `MSANAME` varchar(100) DEFAULT NULL,
  `STATENAME` varchar(100) DEFAULT NULL,
  `COMPANYNAMESHORT` varchar(40) DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;

created a function for calculating a Distance in MYSQL :
getdistance( has logic code for calculating distance);

SELECT COUNT(1) AS FIELDCOUNT FROM WORLDBASE WHERE getdistance(‘33.560’, -112.131’, LATITUDE, LONGITUDE) <= 10

taking 4 mins in WORLDBASE table,pls suggest

]]> Query performance tag:infobright.org,2013:Forums/register/viewthread/.3416 2013-05-29T14:12:20Z 2013-05-29T15:35:31Z casatedu Hi,

I was doing some performance testing and was wondering if the results are in a bulk park with what is “theoretically” expected.

I create a table T1 with about 110 columns (halve of them Int and rest date, varchar and char . Most of chars and varchars are defined as a ‘lookup’.

Then I loaded bit over 216 Million records. Then I created exactly the some table T2 and loaded the same load file 15 times, so my T2 has more than 3.2 Billion records.

Than I executed queries:

SELECT B_Name, count(1) FROM T1 GROUP BY B_Name;

the 65 rows were returned after 6sec.
(B_Name is a varchar lookup having 65 unique values)

Then I run

SELECT B_Name, count(1) FROM T2 GROUP BY B_Name;
the 65 rows were returned after 93sec.

look like straight linear scalability (6 * 15 = 90)

So, once again my question is: are both numbers what is expected (actual numbers and the proportional difference)?
Thanks

Hi again,

After I posted this entry I had another query running and this one surprised me a bit.
The table from above has B_Name column that is: varchar(40) DEFAULT NULL COMMENT ‘lookup’,
and B_Name_ID that is defined as an integer and B_Name_Full that is varchar(100) DEFAULT NULL COMMENT ‘lookup’.

Here are the final results:

SELECT B_Name_ID,  count(1) from T2   -  40 sec.
SELECT B_Name,      count(1) from T2   -  90 -100 sec.
SELECT B_Name_Full,  count(1) from T2   -  130 sec.

I was expecting the same result from all queries - was the expectation incorrect?
C_G

]]> Hadoop &amp; Distributed Load Processor (DLP) tag:infobright.org,2013:Forums/register/viewthread/.3298 2013-03-22T15:14:10Z 2013-03-25T12:02:52Z candeal01 Hi,

I have Hadoop configuration (happens to be on Amazon but doesn’t have to) as follow:

Master1
Master2
Slave1
Slave2
Slave3

So my question is about the best practices using DLP:

1) Should I have DLP running on each slave (I presume there is the data) and if so
2) Should each slave run only one DLP? 

Generally, my question is about best architectural design of Hadoop/Infobright environment.
Thanks

C_G

]]> Optimal Data types tag:infobright.org,2013:Forums/register/viewthread/.3388 2013-05-09T09:54:37Z casatedu HI,

I was wondering if there is any performance difference between:
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

Is there any benefit using, for instance, TINYINT as oppose to INT or BIGINT
C_G

]]> Infobright and Tableau tag:infobright.org,2013:Forums/register/viewthread/.3404 2013-05-23T12:24:42Z casatedu Hi guys,

I know that this questions should be posted on the Tableau forum (and I will do that shortly), but I was wondering if anybody had some similar experience.
I query IEE trial version (4.07) with Tableau and Toad.
The difference in result is quite significant.
I have a single table with aprox 250 Millions records.
A simple select count(*) from T took 2min when direct query against Infobright took lest than millisecond.

The similar issue is with other queries.
Does anybody experience this?
G_C

]]> Infobright Load tag:infobright.org,2013:Forums/register/viewthread/.3408 2013-05-24T11:30:16Z casatedu Hi there,

I’m loading data to IEE and wanted to verify one fact.
My understanding is that if I run load with AUTOCOMMIT=0 than at the end of the load only session that was loading can see new data and any other session do not until I issue COMMIT.
Furthermore, I can run all sort of verifying queries against new data as long as I run them in the same session/connection that loaded data. At that point I can commit or rollback, based upon my test queries.

Is this correct?
Thanks

C_G

]]> Cluster on Load tag:infobright.org,2013:Forums/register/viewthread/.3376 2013-05-08T10:07:36Z casatedu Hi Guys,

The recent patent on Cluster on Load is very excited news.
I was wondering if it is already available in the current IEE eval copy.
Thx
C_G

]]> Supported platform tag:infobright.org,2013:Forums/register/viewthread/.3356 2013-04-29T09:35:28Z candeal01 Hi,

I Have choice of running Proof of Concept with IEE on Windows or Ubumtu 13.04.
If I’m correct the Ubuntu is not supported.
What do I risk trying to run anyway? Is it worth of trying or should I just stick with Windows?
Thanks
C_G

]]> ICE Max Memory for Linux x86_64 tag:infobright.org,2013:Forums/register/viewthread/.3304 2013-04-03T16:33:57Z speedyk1 HI.  Expert help needed.

I can not find any documentation on this.

What is the max memory that ICE could use on a Linux Redhat x86_64?

Thank you in advance.

Regards,
speedyk1

]]> Using Infobright on Amazon EC2 tag:infobright.org,2011:Forums/register/viewthread/.2628 2011-11-08T23:55:12Z 2011-11-09T13:38:02Z Jeff Kibler In a recent blog post, we discussed loading data on Infobright via Amazon EC2 instances using DLP.  If you have any questions regarding that thread or using Infobright on EC2, please post them here.

The original blog post is here: http://bit.ly/ib_blog_ec2_dlp

]]>