Hi Team,
I am encountering weird situation, I am running webi report on snowflake universe. It’s fetching 1.7 million records. It is showing query time as 537 sec at webi query properties level. But at snowflake level the query execution time was just 2 sec. I copied same query from webi and run at snowflake, it’s pretty quick. is it data latency issue? or BO architecture issue?
Do I need to tweak query fetch size( default 10) ?
.
Did you try to increase the fetch size?
Importing 1.7M records in very small packages will slow down WebI a lot.
Yes, increasing the fetch size should speed up your query. You will need to test as it will may increase the work load on your database server but if your database server is processing the query that quickly, it should be able to handle the load.
Keep in mind that changing the fetch size will impact all reports that use that connection, not just the one you are tuning. You may want to make incremental changes and look at the overall performance of the reports using that connection.
Depending on how many queries your report contains, you may want to increase the Maximum Number of Parallel Queries to match. Again, this would be tuning to one specific report but will impact all reports that use that connection.
Thanks N8AKTIV and John for your quick response,
John, Yes I tweaked array fetch size to 300. Performance improved a lot. The report came back in 1.5 mins. I am wondering what’s the impact on other reports? I knew that it will be burden to database side.
Besides other reports will also run faster, any impact may depend on the design of the report. Only can determine if there is a negative impact on other reports.
John,
Thanks again for your quick response, what’s maximum limit for array fetch size for snowflake?
According to the Universe Designer Tool User Guide, it can be set from 1 to 999.
The Information Design Tool User Guide does not mention a maximum.
Testing will be the best determination for you setting. We have a connection that has the Array Fetch Size set to 1000 with a unv universe that goes against SQL Server that functions without issue but the query complexity also plays a role.