Query execution conflicting results

[Kevin]

Hiya.  I am new to Telerik, using Telerik 2011 Q3 in Visual Studios 2010 on a latop running Windows 7.  Currently I am trying to create a Telerik report using Oracle data and am confused by what is happening with the query. 

The query is fairly large, but executes properly in TOAD.  When I move it to Visual Studios I get mixed results:

1.  When I try to execute the query in Query Builder, I get an "SQL Syntax Errors Encountered" message that says
"Error in SELECT clause: expression near 'SELECT'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Error in SELECT clause: expression near 'FROM'.
Unable to parse query text."

2. If I bypass Query Builder and continue to the Preview Data Source Results and click Execute Query, the query completes consistantly in approx 2 min.

3. When I go to the Telerik Report Preview mode, enter the variables and click PREVIEW, the hour glass spins but the query never completes.  I have waited 15 min by the clock and not gotten any results (remember the query completes in 2 min in the Preview Data Source Results pane.

I don't know Visual Studios well enough to understand the significance of it working in the  Preview Data Source Results but failing in both the Query Builder and the Report Preview.  It seems strange to me that the Query Builder reports multiple syntax errors, yet the query completes successfully in Preview Data Source Results. I've never seen an SQL query with syntax errors work before, so I am sceptical about the Query Builder error message.  The report does have nested SELECTstatements, which I  assume has something to do with the messages about problems near SELECT and FROM. At the same time, it isn't completing in PREVIEW mode in the report, so something is obviously wrong.

Can someone please shed some light on what is happening?

Thanks!
Kevin

[IvanY]

Hi Kevin,

Please be aware that handling reports (be it with large data or large number of report items) usually requires additional resources in terms of powerful CPUs, and enough memory to handle all the operations. As far as the processing time and consumed memory is concerned, the complexity of the report layout, the complexity of the data calculations (aggregates, conditional formatting),  the rendering media, the size of the generated document (in pages), and the capabilities of the underlying machine are crucial. 

In general, Telerik Reporting is an embedded reporting engine, which primary goal is to analyze data and represent it in a human-readable format that is also suitable for printing. During this process, certain complex layout and paging rules are applied, so the great flexibility offered by the reporting engine comes at a price: increased processing times and memory footprint. You should agree that dumping millions of records from a database at once hardly fits in the category of report generation. Perhaps in your specific scenario another component such as our RadGrid control might be more appropriate, moreover our RadGrid control offers several export options.

As for the query error - we cannot provide you with a valid input as we do not know how your select statement looks like and therefore we can only guess what the problem might be.

Regards,
IvanY
the Telerik team

[Kevin]

Ivan,

Thanks for your reply.  Sorry about being confusing.  When I said the query was fairly large I didn't mean the number of records it would pull in.  I just meant that I didn't include the query in the post due to its size.  I am correcting that now - the query is below.

My computer setup up has more than enough capacity to handle what I am trying to do.  It is not a resource issue.  Here's the query:

SELECT a.Caller,  a.Repeat_Call, a.scim, c.TOTAL_RECORDS, b.Original_Call, b.Original_Description, a.Repeat_Description, b.Original_Analyst, a.Repeat_Analyst, b.Ticket_Num
FROM
(
SELECT  a.Incident_ID as Repeat_Call, a.Contact_Name || '~' || a.category || '~' || a.subcategory || '~' || a.product_type as SCIM, a.contact_name as Caller, a.opened_by as Repeat_Analyst,
(select   rtrim (xmlagg (xmlelement (e, Description|| '. ')).extract ('//text()'), ',') as Description from   sc.INCIDENTSA1 z where z.incident_id= a.incident_id and rownum <10 group by  z.Incident_id ) as Repeat_Description
FROM sc.IncidentsM1 a
WHERE a.ENTRY_POINT=11 and
a.incident_id <> 'C8081999' and a.incident_id <> 'C8155955' and
a.incident_id <> 'C8162399' and
a.contact_name <> 'unknown' and a.contact_name <> 'unkown' and
a.category <> 'STATUS CHECK'
and
a.open_time > NEW_TIME(:startdate,'CST' ,'GMT') and
a.open_time < NEW_TIME(:enddate,'CST' ,'GMT')
) a,
(
SELECT  distinct(b.Incident_ID) as Original_Call,  b.Contact_Name || '~' || b.category || '~' || b.subcategory || '~' || b.product_type as SCIM, b.opened_by as Original_Analyst, d.source as Ticket_Num,

(select   rtrim (xmlagg (xmlelement (e, Description|| '. ')).extract ('//text()'), ',') as Description from   sc.INCIDENTSA1 z where z.incident_id= b.incident_id and rownum <10  group by  z.Incident_id ) as Original_Description

FROM sc.IncidentsM1 b

left outer join sc.screlationA1 d on (d.depend = b.incident_id)

WHERE
b.ENTRY_POINT=11 and
b.incident_id <> 'C8081999' and b.incident_id <> 'C8155955' and
b.contact_name <> 'unknown' and  b.contact_name <> 'unkown' and
b.category <> 'STATUS CHECK'
and
b.open_time > NEW_TIME(:startdate,'CST' ,'GMT') and
b.open_time < NEW_TIME(:enddate,'CST' ,'GMT')
) b,

(
SELECT  COUNT(c.Contact_Name || '~' || c.category || '~' || c.subcategory || '~' || c.product_type) as TOTAL_RECORDS
FROM sc.IncidentsM1 c
WHERE
c.ENTRY_POINT=11 and
c.incident_id <> 'C8081999' and c.incident_id <> 'C8155955' and
c.contact_name <> 'unknown' and  c.contact_name <> 'unkown' and
c.category <> 'STATUS CHECK'
and
c.open_time > NEW_TIME(:startdate,'CST' ,'GMT') and
c.open_time < NEW_TIME(:enddate,'CST' ,'GMT')
) c

WHERE a.scim = b.scim
Group By a.Repeat_Call, a.scim, c.TOTAL_RECORDS, b.Original_Call, a.Caller, b.Original_Analyst, a.Repeat_Analyst, b.Ticket_Num

This query works in TOAD.  It works if I skip the Telerik Reporting Query Builder and run it from the PREVIEW DATA SOURCE RESULTS page.  It works in PREVIEW mode.  It does NOT work in Query Builder, generating the error in my first post. 

The result of this is I no longer trust Query Builder.  Not a good thing.  Now that you have the query, can you shed any light on why this is happening?

Thanks,
Kevin

[IvanY]

Hi Kevin,

How do you bind the report - do you use ObjectDataSource Component or EntityDataSource Component? What is the count of the records that your query returns? As stated in previous post the complex paging and layout rules that are being applied come at a price - increased processing time and memory footprint. Additionally the performance depends on the report layout and how complex it is. For more information please check the Performance Considerations KB article.
 
On a side note the Query Builder is part of Visual Studio and Telerik Reporting just uses the exposed Visual Studio service as many other tools. So you can check in another application, not related to Telerik, if things work as they are supposed to. If you face the same problem you should contact or report to Microsoft your issue.

Just for clarification, at runtime everything works and the only place where you have problem and get the error is the query builder, is that correct?

Greetings,
IvanY
the Telerik team

[Peter]

Hi Kevin,

In Q3 2014 the Standalone Report Designer provides a Query Designer to help you create queries to retrieve data from a relational database. Use the query designer to explore the database tables and views, interactively build an SQL SELECT statement and view the result of your query.

Regards,
Peter
Telerik