-
15. Re: SQL script doesn't get pushed down to Aster Teradata data source
stavroskalamat Nov 3, 2014 4:31 PM (in response to shawkins)Does
[TEIID-3196] Remove Teradata join pushdown restriction - JBoss Issue Tracker
mean that Teiid support for Teradata outer joins will be back because according to Teradata docs (see below URL)
outer joins are supported?
Thanks
sk
-
16. Re: SQL script doesn't get pushed down to Aster Teradata data source
rareddy Nov 3, 2014 6:20 PM (in response to stavroskalamat)Yes, TEIID-3196 an effort to turn the "outer join" support that in teradata translator. See fixed in field for which release this will be available.
-
17. Re: SQL script doesn't get pushed down to Aster Teradata data source
stavroskalamat Nov 3, 2014 11:03 PM (in response to rareddy)That's great news. Really looking forward to it.
Should I expect a performance benefit if I query the physical model (blue xmis), instead of the VBL ?
Any credit for this thought goes to the Redhat engineer who recommended it in the past.
Thanks
sk
-
18. Re: SQL script doesn't get pushed down to Aster Teradata data source
rareddy Nov 4, 2014 8:28 AM (in response to stavroskalamat)What it means is when Teiid sees query with outer join, instead of rewriting it, it will get pushed to source as is. Yes, that may increase the performance in few situations.
-
19. Re: SQL script doesn't get pushed down to Aster Teradata data source
stavroskalamat Nov 5, 2014 10:36 AM (in response to rareddy)I was asked to explore the JBOSS capabilities and find out whether it supports pushdown optimization (we've already discussed part of that) but also if it supports inline macros for scripts created in the Data Virtualization layer before they get submitted to a data source. Any idea about that ?
-
20. Re: SQL script doesn't get pushed down to Aster Teradata data source
rareddy Nov 5, 2014 10:44 AM (in response to stavroskalamat)Macros you mean user defined functions?
-
21. Re: SQL script doesn't get pushed down to Aster Teradata data source
stavroskalamat Nov 6, 2014 12:10 PM (in response to rareddy)I believe there's a point of confusion in the question and that's between inline functions and macros. I may be wrong but I think Java doesn't support macros and if I recall correctly, in Java all optimization takes place in the JVM so when it comes to inline processes all work is done at runtime. Those asked the Q. may think that the translator can handle a script the way JVM will perform inlining. I'll request a clarification. and re-port.
-
22. Re: SQL script doesn't get pushed down to Aster Teradata data source
stavroskalamat Nov 10, 2014 1:47 PM (in response to stavroskalamat)As far as best practices on writing SQL from dbVis against a VDB, does anything else come to mind along with the following.
Follow standard SQL code practices
Finish a statement with a ;
Use fully qualified database names
Avoid returning all fields by using .*
Use either an inner join or a where condition to define a join between tbls
Prefer CAST & COALESCE as they are standard functions
Implement indexes if there’s a need but they are not present
use materialized views on stale or less frequently updated data sets
whenever the query optimizer relies on a field’s index ordering and the field is used in a where condition avoid manipulation on that field in the Where, e.g., select t1.fld1, t2.fld2 from t1 inner join t2 on t1.fld1=t2.fld2 where t1.fld1 = 3 (avoid manipulation like: where Right(t1.fld1,5) = 2
Use the newest teiid jdbc driver to ensure the latest translator is in use.
Make sure the Teiid technical documentation you’ll refer to for assistance is in sync with the version of Teiid currently used.
Internal materialized views and temp table usage from a session and within procedures can take advantage of greater transaction support.
If a developer has access to Dev Studio then studying Query Plans and logs will help troubleshooting sluggish performance
-
23. Re: SQL script doesn't get pushed down to Aster Teradata data source
shawkins Nov 11, 2014 9:56 AM (in response to stavroskalamat)> Avoid returning all fields by using .*
Ideally you'd avoid all star qualifications.
> Prefer CAST & COALESCE as they are standard functions
Most of the Teiid system functions have good push down support. The exceptions are xml, security, json, and parse/format functions.
> Use the newest teiid jdbc driver to ensure the latest translator is in use.
The driver will not influence the translators, but it is a good idea to use the latest.
> does anything else come to mind
Probably the most important is utilizing plans to understand execution even before performance is an issue and to make sure table cardinality and column stats are set to assist Teiid in making good planning decisions.
-
24. Re: SQL script doesn't get pushed down to Aster Teradata data source
stavroskalamat Nov 12, 2014 2:53 PM (in response to shawkins)In an effort to improve the execution time of our scripts we followed the valid advice of a Redhat engineer, who volunteers his time to consult when he can, to override the translator.
We overrode only a few of the properties shown below (Translators - Teiid 8.10 (draft) - Project Documentation Editor)
Name Description Default Immutable Set to true to indicate that the source never changes. false RequiresCriteria Set to true to indicate that source SELECT/UPDATE/DELETE queries require a where clause. false SupportsOrderBy Set to true to indicate that the ORDER BY clause is supported. false SupportsOuterJoins Set to true to indicate that OUTER JOINs are supported. false SupportsFullOuterJoins If outer joins are supported, true indicates that FULL OUTER JOINs are supported. false SupportsInnerJoins Set to true to indicate that INNER JOINs are supported. false SupportedJoinCriteria If joins are supported, defines what criteria may be used as the join criteria. May be one of (ANY, THETA, EQUI, or KEY). ANY MaxInCriteriaSize If in criteria are supported, defines what the maximum number of in entries are per predicate. -1 indicates no limit. -1 MaxDependentInPredicates If in criteria are supported, defines what the maximum number of predicates that can be used for a dependent join. Values less than 1 indicate to use only one in predicate per dependent value pushed (which matches the pre-7.4 behavior). -1 DirectQueryProcedureName if the direct query procedure is supported on the translator, this property indicates the name of the procedure. native SupportsDirectQueryProcedure Set to true to indicate the translator supports the direct execution of commands false ThreadBound Set to true to indicate the translator's Executions should be processed by only a single thread These properties were : SupportsOrderBy, SupportsInnerJoins and SupportsDirectQueryProcedure
we overrode them on the teradata translator by setting them all to true
We paid close attention to the case sensitivity of the properties names when we were adding them to the translator override
then we re-deployed and re-executed the VDB
We re-ran the scripts but we observed no improvement at all, as if we didn't override any property
Any ideas why not ? Any recommendations on how to override the translator properly, if the steps above indicate an error on our end ?
Thanks in advance
sk
-
25. Re: SQL script doesn't get pushed down to Aster Teradata data source
shawkins Nov 12, 2014 4:03 PM (in response to stavroskalamat)> These properties were : SupportsOrderBy, SupportsInnerJoins and SupportsDirectQueryProcedure
> we overrode them on the teradata translator by setting them all to true
SupportsOrderBy and SupportsInnerJoins are already true for Teradata. SupportsDirectQueryProcedure will only be relevant if you are issuing direct to source queries: teradata_model.native('some source sql')
> Any ideas why not ? Any recommendations on how to override the translator properly, if the steps above indicate an error on our end ?
You may done the above correctly (although for effectively no result). First we'd need an idea of why you are overriding properties, then we can make sure there will be a difference with the properties changed.
-
26. Re: SQL script doesn't get pushed down to Aster Teradata data source
stavroskalamat Nov 21, 2014 10:48 AM (in response to shawkins)Performance tuning is still work in progress so, which cases the translator override helps or not, is yet TBD and requires further investigation.
The jpg attached shows the generic getTextFiles function for a txt data source we created to expedite import tbls (instead of doing a separate import for each tbl and having it show as a different data source). That way I need to reproduce the VBL SQL transformations only and present the tbls to a VDB faster.
Meanwhile, more data came around, this time about 60 tbls in MS Access 2007/2010 format.
I'm going over the slow way (Way1) to load txt files (one tbl at a time), but the Q. is how can I connect to MS Access db directly (Way2) to bring it into my existing VDB as a another data source ?
I'm under the gun to complete this ASAP so any doc. leads or any other type of advice via a response to this thread will be helpful.
Meanwhile on Way1 I produced the txt files with pipe (|) text qualifiers but there are a lot of abnormal chars in some of the string flds of the tbls, such as " (2ble quotes) that create problems rendering the data from the txt files.
I want to use ~ (tilde) as txt qualifiers and re-produce the txt files to avoid the aforementioned.
Where can I tell Teiid what qualifiers I have specified in the txt file and how can I manually specify what txt qualifier I'm using in the sql transformation of a VBL tbl like the one below ?
SELECT A.DeersID, A.ID, A.AUD_QuickSIN_Patient_ID, A.AUD_QuickSIN_Reg, A.AUD_QuickSIN_Rev, A.AUD_QuickSIN_Rev_Fast FROM (EXEC Flat_File_Sources.getTextFiles(
'.\NICoE_Data\vn_AUD_QuickSIN.txt'
)) AS f, TEXTTABLE(f.file COLUMNS DeersID string , ID string , AUD_QuickSIN_Patient_ID string , AUD_QuickSIN_Reg string , AUD_QuickSIN_Rev string , AUD_QuickSIN_Rev_Fast string DELIMITER '|' HEADER) AS A
Going back to Way 2 :
will a DSN-less conn. be good for MS Access
DSN Less Connection - Teiid 8.10 (draft) - Project Documentation Editor
or
Configuring the Data Source Name (DSN) - Teiid 8.10 (draft) - Project Documentation Editor
is preferred ?
thanks in advance
-
Untitled.jpg 399.8 KB
-
-
27. Re: SQL script doesn't get pushed down to Aster Teradata data source
rareddy Nov 21, 2014 11:05 AM (in response to stavroskalamat)Stavros,
If I were you I will not do your WAY (1), that may be my back up solution rather than primary way of doing it, and you are discovering why? you need to export, then import using the File resource adapter and use TEXTTABLE etc.
WAY(2) is the way to go. MS Access provides ODBC access to the database. However the link you were looking at is wrong. The link you provided is ODBC access to Teiid, not ODBC access from Teiid to MS Access. Take look at the "docs/teiid/datasources/odbc" directory for some instructions. The instructions there are for Excel sheet, but you can do similar DSN for the MS Access and connect to it and import the tables.
Ramesh..
-
28. Re: SQL script doesn't get pushed down to Aster Teradata data source
stavroskalamat Nov 21, 2014 12:18 PM (in response to rareddy)Glad a direct conn. (Way2) is an option
sorry for the URL error, you're right, I'm looking at setting up a conn. from Teiid to MS Access, not the reverse.
Are the following URLs those I should be looking at ?
also, is the following True?
For ODBC datasource, we use JDBC <--> ODBC bridge driver provided by Oracle that comes with Sun JDK. So, the driver
is already installed and available.
If yes then I need only a DSN conn. to Access and an xml addition to standalone xml to introduce the new MS Access datasource to Teiid, correct ?
thanks again.
sk
-
29. Re: SQL script doesn't get pushed down to Aster Teradata data source
rareddy Nov 21, 2014 12:37 PM (in response to stavroskalamat)yes