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,

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.

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?

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!

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,

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!

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.

No worries man. Should of been more clear. Sorry.

The logical next step probably would be the adapter and the comms to the SQL Server.