Why is my Lumira SQL Query timing out?

[Former Member]

I am trying to connect to a large SQL report, but keep getting timed out ("The query has timed out."). My query will be approximately 1.5 million rows. I can execute this well in SQL Server or PowerPivot, but would like to build out using Lumira visualizations. SELECT a.Profit_Center, a.Product_Hierarchy, a.Material, Sold_to, End_Customer, Report_Date, SUM(Dist_WAC_Amount_KZWI1) POS_COST ,sum(a.Agency_Fee_KZWI6) as Agency_Fee ,sum(a.Chargeback_NETWR) as Chargeback FROM cmpa.dbo.SAP_POS_2014 as a --product description LEFT JOIN CMPA.dbo.SAP_MARA AS m WITH(NOLOCK) ON   m.material = RIGHT(a.material,10)   --Profit Center Hierarchy info LEFT OUTER JOIN cmpa.dbo.SAP_Business_Hierarchy AS pc WITH(NOLOCK) ON   pc.Profit_Center = m.Profit_Center   where PC.Div = 'ep' GROUP BY a.Profit_Center, a.Product_Hierarchy, a.Material, Sold_to, End_Customer, Report_Date Thank you so much for your time and feedback.

[Former Member · Accepted solution]

Thank you for looking into this Alan. After further digging, I have taken the joins and where clause out of my SQL code and the query worked. I have not tested to see which was causing the issue. Appreciate the support.

[alan_han]

Hello Brent,

Please reproduce the issue again and collect Lumira Desktop trace logs from "%temp%\sapvi\logs\" folder on your desktop. The filename starts with "TraceLog_***". Please paste the log entries here so we can analyze them.

Please also let us know:

1) How long does the query take to return results in SQL Server (assuming you are using the Management Studio), and Powerpivot?

2) If you limit the amount of results in your SQL query (select top ###), does it return data successfully?

Thanks,

Alan

[Former Member]

Hi Alan,

Thank you for the support and a fast reply.

The query takes 16 minutes to run in SQL Server Management Studio.

If I limit it to Top 100 rows, it takes 22 minutes in SQL Server Management Studio and I get the same time out within Lumira.

I've attached the trace log as it is quite long.

Kind regards,

Brent

TraceLog_10916_2016_04_27_20_59_35_076_trace.txt.zip

[alan_han]

Hi Brent,

Can you please check your SQL Server side and see if there is any log entries with regards to the timeout of the query being sent from Lumira Desktop? It may help us to further understand the error.

Thanks,

Alan