-
1. Re: Teiid embedded query result set supporting upto Integer.max value ?
shawkins Aug 22, 2016 8:57 AM (in response to durgadatta)Unfortunately count is required to return an int value. Rather than cap to the max or overflow, an exception is thrown. Like other databases, a variation on count would be needed, such as COUNT_BIG (Transact-SQL)
Can you log an enhancement request if you need exact counts beyond max integer?
-
2. Re: Teiid embedded query result set supporting upto Integer.max value ?
durgadatta Aug 23, 2016 5:02 AM (in response to shawkins)Thanks Steven, Here the TEIID performance is very slow, When I am retrieving 2 billion records (doing some aggregation), its taking time around 2 to 3 hr.
Is there any way we can optimize TEIID performance ?
-
3. Re: Teiid embedded query result set supporting upto Integer.max value ?
shawkins Aug 23, 2016 8:06 AM (in response to durgadatta)The first thing to look at is the query plan. Even just fetching the records may be taking a significant amount of time. Can you post the query plan and how much time does it take to just retrieve that number of records?
-
4. Re: Teiid embedded query result set supporting upto Integer.max value ?
durgadatta Aug 23, 2016 8:26 AM (in response to shawkins)I am using following type query ,
Select count(emp_name) from EMP_TABLE where timestamps BETWEEN {timestamps '2016-08-19 10:51:00.0'} AND {timestamps '2016-08-19 22:51:00.0'}
It is returning 705254568 records ,in 4,429.367 seconds .i.e.73 minutes
-
5. Re: Teiid embedded query result set supporting upto Integer.max value ?
shawkins Aug 23, 2016 9:23 AM (in response to durgadatta)I'm asking what is the time without the count? The query plan will help clarify what processing is going on. For example is EMP_TABLE a view? What source(s) are you accessing, etc.?
-
6. Re: Teiid embedded query result set supporting upto Integer.max value ?
durgadatta Aug 31, 2016 4:04 AM (in response to shawkins)Hi Steven Sorry for late response , here is the generated query plan for "select * ..." query, and its taking arround 5 to 6hour to retrive 2billions record .
For example is EMP_TABLE a view?
Its temporary table created using ,
metadataFactory
.addTable("employee");
What source(s) are you accessing, etc.?
Accessing Rest source and internally parsing the json data and populating the record by overriding "public List<?> next() throws TranslatorException, DataNotAvailableException "
What is the max limit for number of record we can retrieve using select * query in Teiid embedded-9.0.1 version ?
AccessNode
+ Relational Node ID:0
+ Output Columns:
0: emp_type (long)
1: emp_name (string)
2: emp_last_name (string)
3: emp_loc_add (string)
4: dept_name (string)
5: emp_address (string)
6: emp_address2 (string)
7: emp_address3 (string)
8: value (long)
9: reg_date (timestamp)
10: value1 (long)
11: value2 (long)
12: value3 (long)
13: value4 (long)
+ Statistics:
0: Node Output Rows: 256
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 0
3: Node Cumulative Process Time: 0
4: Node Next Batch Calls: 2
5: Node Blocks: 1
+ Cost Estimates:Estimated Node Cardinality: 20596.4
+ Query:SELECT testmodel.employee.emp_type, testmodel.employee.emp_name, testmodel.employee.emp_last_name, testmodel.employee.emp_loc_add, testmodel.employee.dept_name, testmodel.employee.emp_address, testmodel.employee.emp_address2, testmodel.employee.emp_address3, testmodel.employee.value, testmodel.employee.reg_date, testmodel.employee.value1, testmodel.employee.value2, testmodel.employee.value3, testmodel.employee.value4 FROM testmodel.employee WHERE (testmodel.employee.emp_name = 'test emp') AND (testmodel.employee.value = 2500) AND (testmodel.employee.reg_date >= {ts'2016-08-17 00:00:00.0'}) AND (testmodel.employee.reg_date <= {ts'2016-08-20 23:59:59.0'})
+ Model Name:testmodel
+ Data Bytes Sent:30059
+ Planning Time:8
Thanks,
Durga
-
7. Re: Teiid embedded query result set supporting upto Integer.max value ?
shawkins Aug 31, 2016 11:54 AM (in response to durgadatta)> Accessing Rest source and internally parsing the json data and populating the record by overriding "public List<?> next() throws TranslatorException, DataNotAvailableException
I'm still not completely clear. Is EMP_TABLE a Teiid temporary table, or is is it backed by a custom rest source, such as shown with employee above? In general have you validated the custom translator is behaving as expected - in terms of the number of remote calls it needs to make and the performance of the parsing etc.? Also is there any transaction that has been started in this scenario?
> What is the max limit for number of record we can retrieve using select * query in Teiid embedded-9.0.1 version ?
The limit for retrieval is still max int. Internally we can handle up to max long - [TEIID-3601] support larger row counts - JBoss Issue Tracker