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?
I'm sorry I meant 8.1.0. The 3 got in there somehow.
I found the # of times, by looking @ the postgresql logging.
Turn on command logging that should also print out each time the query is submitted to the source.
Can you give the full ddl for your view and a sample user query?
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) */
id AS state_id, name AS state_name, "short" AS state_short_code
and I how invoke it:
def db = [url:'jdbc:teiid:TestTeid@mm://184.108.40.206:31000', user:'RedHat', password:'Jboss', driver:'org.teiid.jdbc.TeiidDriver']
def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
sql.execute 'select * from External.v_states;'
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.
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?
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
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.
> 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?
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.
Valid = True
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?
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.