-
1. Re: TEIID making multiple calls to DB when invoking VDB
rareddy Apr 9, 2013 5:47 PM (in response to aabrham)Anson,
Teiid 8.1.3? There is no such released version from Teiid.
You can take look at query plan. Check the documentation on how to get query plan. Also, how did you figured out it is called 1700 times? Did you turn on command logging?
Ramesh..
-
2. Re: TEIID making multiple calls to DB when invoking VDB
aabrham Apr 9, 2013 5:51 PM (in response to rareddy)I'm sorry I meant 8.1.0. The 3 got in there somehow.
I found the # of times, by looking @ the postgresql logging.
-
3. Re: TEIID making multiple calls to DB when invoking VDB
rareddy Apr 9, 2013 6:23 PM (in response to aabrham)Turn on command logging that should also print out each time the query is submitted to the source.
-
4. Re: TEIID making multiple calls to DB when invoking VDB
shawkins Apr 9, 2013 6:55 PM (in response to rareddy)Can you give the full ddl for your view and a sample user query?
-
5. Re: TEIID making multiple calls to DB when invoking VDB
aabrham Apr 10, 2013 9:43 AM (in response to shawkins)Here's a sample VDB:
<model name="External" visible="true" type="VIRTUAL">
<metadata type = "DDL"><![CDATA[
CREATE VIEW v_states OPTIONS OPTIONS (CARDINALITY 50000000) AS
/*+ cache(ttl:3600000) */
SELECT
id AS state_id, name AS state_name, "short" AS state_short_code
FROM Main.us_states;
]]>
</metadata>
</model>
and I how invoke it:
import groovy.sql.Sql
def db = [url:'jdbc:teiid:TestTeid@mm://192.169.1.1:31000', user:'RedHat', password:'Jboss', driver:'org.teiid.jdbc.TeiidDriver']
def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
sql.withTransaction {
sql.execute 'select * from External.v_states;'
}
-
6. Re: TEIID making multiple calls to DB when invoking VDB
shawkins Apr 10, 2013 10:34 AM (in response to aabrham)Your options should also have MATERIALIZED 'true'. You should check the SYSADMIN.MatViews table for information related to mat views. Also if you set logging to a more detailed level you should see INFO logs about the matview loading process.
Steve
-
7. Re: TEIID making multiple calls to DB when invoking VDB
aabrham Apr 10, 2013 11:06 AM (in response to shawkins)Sorry, I didn't copy all of the VDB DDL, but MATERIALIZED 'true' is there. I just invoke a call to SYSADMIN.MatViews?
I'm relatively new to TEIID, so how would i set the logging to more detail?
-
8. Re: TEIID making multiple calls to DB when invoking VDB
rareddy Apr 10, 2013 11:42 AM (in response to aabrham)By editing the standalone-teiid.xml file. You should see a subsection for logging, just change log level for "org.teiid"
<logger category="org.teiid"> <level name="ERROR"/> </logger>
All the logging contexts are defined here, if you need to only open up certain modules. https://docs.jboss.org/author/display/TEIID/Logging
Ramesh..
-
9. Re: TEIID making multiple calls to DB when invoking VDB
aabrham Apr 10, 2013 1:52 PM (in response to rareddy)So i looked @ command log and saw only 1 entry:
13:46:47,331 DEBUG [org.teiid.COMMAND_LOG] (Worker478_QueryProcessorQueue3454748) START DATA SRC COMMAND: startTime=2013-04-10 13:46:47.33 requestID=3oDIh32560yF.2072 sourceCommandID=6 txID=null modelName=External translatorName=postgresql-conf sessionID=3oDIh32560yF principal=RedHat@teiid-security sql=SELECT id AS state_id, name AS state_name, "short" AS state_short_code FROM Main.us_states
13:46:47,425 DEBUG [org.teiid.COMMAND_LOG] (Worker478_QueryProcessorQueue3454752) END SRC COMMAND: endTime=2013-04-10 13:46:47.425 requestID=3oDIh32560yF.2072 sourceCommandID=6 txID=null modelName=External translatorName=postgresql-conf sessionID=3oDIh32560yF principal=RedHat@teiid-security finalRowCount=50
But when I looked @ the postgres logs I saw a 1700 calls of the same query SELECT id AS state_id, name AS state_name, "short" AS state_short_code FROM Main.us_states to the database. with different pids. 1700 calls not @ once, but consecutively.
-
10. Re: TEIID making multiple calls to DB when invoking VDB
shawkins Apr 10, 2013 2:02 PM (in response to aabrham)> But when I looked @ the postgres logs I saw a 1700 calls of the same query SELECT id AS state_id, name AS state_name, "short" AS state_short_code FROM Main.us_states to the database. with different pids. 1700 calls not @ once, but consecutively.
As far as Teiid is concerned we're only making 1 call if there is 1 entry. Is there something odd about your pg datasource configuration that would issue multiple calls? Do different pids correspond to different connections - and are all of the connections coming from the app server?
Do you see an entry for your materialized view in SYSADMIN.MatViews?
Steve
-
11. Re: TEIID making multiple calls to DB when invoking VDB
aabrham Apr 10, 2013 2:19 PM (in response to shawkins)PID in postgres = Connection.
All the calls are coming from the app servers (based on IP address), and occurs when I initiate the call to VDB.
Sysadmin.matviews shows
Valid = True
LoadState=Loaded
Updated is basically about 2 minutes ago, when I queried it.
and Cardinality= 2707
when you mean pg datasource config, do you mean the database or the jdbc driver?
-
12. Re: TEIID making multiple calls to DB when invoking VDB
shawkins Apr 10, 2013 2:41 PM (in response to aabrham)At this point what you have shown does not seem related to mat views or even to Teiid.
> when you mean pg datasource config, do you mean the database or the jdbc driver?
Mostly the jdbc driver. You should try the source query through the datasource directly and not via Teiid to see if the pattern is the same on the pg side - we would expect that it would be as Teiid is just like any other consumer of Connections from a connection pool.
Steve