Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPEN]Long adapter wait time when trying to run against very simple stored proc:

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE OPEN]Long adapter wait time when trying to run against very simple stored proc:
 Login/Join
 
Virtuoso
posted
Hey all,

Has anyone here ran into the scenario where you write a very simple stored procedure in SQL Server Mgmt Studio (or wherever) with a few IN parameters requested by it; create a synonym for it; then create a very simple report request against it passing it the same IN parameters as you did to test it in SQL Server Mgmt Studio (or wherever you created it), and it takes far longer than it did in SQL Server Mgmt Studio (or wherever)??

My stored procedure with supplied IN parameters takes less than a second to return on the SQL side. I try to run a very simple report with the very same parameters passed to it and it doesn't come back until after 1 min, 13 seconds of waiting for it.

I look at the stats record for it under the monitored sessions drill report, and it is saying adapter time is 1 min, 10 secs??!! Why is the adapter taking forever to do its thing? We have other reports and synonyms utilizing this adapter and we have no troubles with them. Why are we all the sudden having issues??

My stored procedure query (comes back in less than 1 second):
DECLARE @weekending AS Date 
SET @weekending = '20151121'
DECLARE @dsdivision AS int 
SET @dsdivision = 99
DECLARE @dsdistrict AS int 
SET @dsdistrict = 2
DECLARE @dsbannerlgl AS int
SET @dsbannerlgl = 10
DECLARE @dsacctno AS int 
SET @dsacctno = 10982

SELECT * FROM CashierQtrHr_Test a
, DIMDIVISION b
, DIMDISTRICT c
, DIMBANNER d
, DIMSTORE e
, DIMDATE f
WHERE 
a.ddvID = b.ddvID
and a.ddsID = c.DDSID	
and a.dbid = d.DBID
and a.dsID = e.DSID
and a.datekey = f.datekey
and b.ddvDivision = ISNULL(@dsdivision, b.ddvDivision)
and c.dsDistrict = ISNULL(@dsdistrict, c.dsDistrict) 
and d.dbBannerGL = ISNULL(@dsbannerlgl, d.dbBannerGL) 
and e.dsAcctNo = ISNULL(@dsacctno, e.dsAcctNo)
and f.weekending = ISNULL(@weekending, f.weekending)


My report request (not even requesting every column available, 1 min 13 secs):
TABLE FILE SP_TEST
SUM FCQMEMBERTRANSACTIONS AS 'Member,Transactions'
BY DSACCTNO AS 'Store'
BY FCQLANENO AS 'Lane'
BY FCQCASHIERNO AS 'Cashier'
BY FCQTRANSDATE AS 'Business,Date'
BY FCQTRANSTIME AS 'Time of,Trans.'
WHERE DSDIVISION EQ '99'
WHERE DSDISTRICT EQ '02'
WHERE DSBANNERGL EQ '10'
WHERE DSACCTNO EQ '10982'
WHERE WEEKENDING EQ '20151121'
WHERE FCQTRANSDATE EQ '20151120'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
     LINES-PER-PAGE=UNLIMITED,
$
ENDSTYLE
END


Any insights, help, experience, etc. is much appreciated!

Thanks in advance!

This message has been edited. Last edited by: CoolGuy,


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Expert
posted Hide Post
Does the return all the records in the second ?

You may have to turn tracing on to see what is happening with the generated SQL.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
could you change your WebFOCUS procedure just to check if it is about the connection time or the grouping:

TABLE FILE SP_TEST
PRINT FCQMEMBERTRANSACTIONS AS 'Member,Transactions'
DSACCTNO AS 'Store'
FCQLANENO AS 'Lane'
FCQCASHIERNO AS 'Cashier'
FCQTRANSDATE AS 'Business,Date'
FCQTRANSTIME AS 'Time of,Trans.'
WHERE DSDIVISION EQ '99'
WHERE DSDISTRICT EQ '02'
WHERE DSBANNERGL EQ '10'
WHERE DSACCTNO EQ '10982'
WHERE WEEKENDING EQ '20151121'
WHERE FCQTRANSDATE EQ '20151120'
WHERE RECORDLIMIT EQ 1
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
     LINES-PER-PAGE=UNLIMITED,
$
ENDSTYLE
END


If it still takes long, check your isolation level at the adapter settings.

If it is fast, then the differences in query cause this. Is FCQTRANSDATE a defined field? Is the TABLE request properly translated to SQL?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Virtuoso
posted Hide Post
Waz,

I have had tracing on during my testing and I get zero errors or anything in regards to whether the SQL translation was optimized or not, or whether aggregation was gravy or not. Must mean it did its thing.

Frans,

I added the clause to ensure only 1 record was going to be returned and it still took around 40+ seconds to return my 1 record report.

How does one check their isolation level in the adapter settings? Poked around and couldn't find it. What is an isolation level?

FCQTRANSDATE is not a defined field. It is an actual field within the table the stored procedure queries from.

I've added a SQL trace and am not getting any optimization errors. All code runs as it should from what I'm seeing from the output.

Thanks for your suggestions! I will continue to research what this isolation level is to see if it's something that may help. Thanks!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Expert
posted Hide Post
RECORDLIMIT may not get passed to SQL.

One misconception is that on the SQL side, all the records are returned at once, in fact the return is buffered, and what I have seen is that it can take longer.

can you call the sp from another tool to see if its is also slow, say an obdc or SQL server test tool.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
Waz,

My stored procedure query in SQL Server Mgmt Studio comes back in less that 1 second, so using another tool wouldn't help I don't think. Would it?

Even if the record return is buffered, I think 1 record shouldn't be a concern there. Am I wrong?

My grey area is what is going on in the BI layer to cause there to be so much wait time for just 1 record. It shouldn't be a thing, especially if the query comes back so fast on the SQL side.

I have a case open for this issue currently, and am expecting a remote session to go down tomorrow in the afternoon with my case worker.

Thanks!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Expert
posted Hide Post
quote:
Even if the record return is buffered, I think 1 record shouldn't be a concern there. Am I wrong?


Ah, didn't realise that you were expecting only 1 record back.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
No worries man. Should of been more clear. Sorry.


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Expert
posted Hide Post
The logical next step probably would be the adapter and the comms to the SQL Server.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPEN]Long adapter wait time when trying to run against very simple stored proc:

Copyright © 1996-2020 Information Builders