Slow SP embedded in Report 2

[RL624]

I enabled the server grouping function and created parameter in the Stored Procedure, and the report still has performance problem. It took almost 10 minutes to complete the job. What else I can do to avoide the performance bottleneck? Thanks.

 

[paulmarr]

RL624 ,

Just because you ticked "perform grouping on server", this doesn't mean it will automatically do it.

To Group on the server, the following conditions must be met by your report:

1. only works on SQL or ODBC data sources
2. have "Perform Grouping on Server" turned on - File>Report Options>
3. group records
4. have details section hidden
5. not contain Average or Distinct Count
6. not contain TopN values
7. not contain specified order grouping

hope this helps

Cheers,

paulmarr@arcom.com.au

 

[turtlemaster]

I had the same problem and I was using two data sources. This forces all of the grouping to be done locally. I had to migrate my data to all the same database on the server (27min to 7sec)

 

[RL624]

I am using one data source for my stored procedure. However, I link different databases within my SP. Is that the problem? There is no way for me to combine all data into one database due to the nature of the software I am trying to produce report for. This accounting software (Great Plains) has one master database contains all system data, and other custom databases for particular company created under the software environment. So, how should I do this besides migrating all data into one database?? Please help....

 

[turtlemaster]

Well unfortunately that was the only solution that I could come up with. We use Visual Manufacturing by Lilly and I had to create the tables right in the application database. It worked amazing well though. Becuase however you are using a stored procedure you may be able to force the grouping from the SP itself, not from crystal. Check out the documentation on SP's and see if there is a keyword or something that would force grouping on the server before the recordset is returned.

 

[RL624]

The following is the copy of my SP. Could you enlighten me with the grouping function in SP? i am not sure where should I start. Thank you very much.


CREATE PROCEDURE dbo.dt_receiver21
@recNumber varchar(30),
@POSTED int -- 0 not posted, 1 posted
AS
if @POSTED = 0
SELECT JPUS.dbo.POP10310.POPRCTNM AS POPRCTNM,
JPUS.dbo.POP10310.RCPTLNNM AS RCPTLNNM,
JPUS.dbo.POP10310.PONUMBER AS PONUMBER,
JPUS.dbo.POP10310.ITEMNMBR AS ItemNumber,
JPUS.dbo.POP10310.ITEMDESC AS ItemDesc,
JPUS.dbo.POP10310.UMQTYINB AS UMQTYINB,
JPUS.dbo.POP10310.UNITCOST AS UnitCost,
JPUS.dbo.POP10310.EXTDCOST AS ExtCost,
JPUS.dbo.POP10300.VENDORID AS VendorID,
JPUS.dbo.POP10300.PYMTRMID AS PayMethod,
JPUS.dbo.POP10300.USER2ENT AS USER2ENT,
JPUS.dbo.POP10500.QTYSHPPD AS QtyShipped,
JPUS.dbo.PM00200.VENDNAME AS VendorName,
JPUS.dbo.PM00200.VNDCNTCT AS VendorContact,
JPUS.dbo.PM00200.ADDRESS1 AS VendorADD1,
JPUS.dbo.PM00200.ADDRESS2 AS VendorADD2,
JPUS.dbo.PM00200.CITY AS VendorCity,
JPUS.dbo.PM00200.STATE AS VendorState,
JPUS.dbo.PM00200.ZIPCODE AS VendorZip,
JPUS.dbo.PM00200.COUNTRY AS VendorCountry,
JPUS.dbo.PM00200.PHNUMBR1 AS PHONE,
JPUS.dbo.PM00200.FAXNUMBR AS FAX,
JPUS.dbo.PM00200.SHIPMTHD AS SHIPMTHD,
JPUS.dbo.POP10110.QTYORDER AS QTYORDER,
dbo.tblPoFile.PONUMBER AS PONUMBER,
dbo.tblPoFile.FILENUMBER AS FILENUMBER,
dbo.GP6purline.lineSeq as lineSeq,
dbo.GP6purline.application as app,
dbo.GP6purline.manufacturer as mang,
dbo.GP6purline.condition as con,
dbo.GP6purline.notes as notes,
dbo.GP6purline.docReq as docReq,
dbo.GP6purline.inspector as insp,
dbo.GP6purline.inspection_date as inspdate,
dbo.GP6purline.tagNumber as tag,
dbo.GP6purline.itemNumber as itemNo,
DYNAMICS.dbo.SY01400.USERNAME AS USERNAME,
OrderBy = ' '

FROM JPUS.dbo.POP10310 INNER JOIN JPUS.dbo.POP10300 ON JPUS.dbo.POP10310.POPRCTNM = JPUS.dbo.POP10300.POPRCTNM
INNER JOIN JPUS.dbo.POP10500 ON JPUS.dbo.POP10310.POPRCTNM = JPUS.dbo.POP10500.POPRCTNM
FULL JOIN DYNAMICS.dbo.SY01400 ON JPUS.dbo.POP10300.USER2ENT = DYNAMICS.dbo.SY01400.USERID
FULL JOIN JPUS.dbo.PM00200 ON JPUS.dbo.POP10300.VENDORID = JPUS.dbo.PM00200.VENDORID
FULL JOIN dbo.GP6purline ON JPUS.dbo.POP10310.PONUMBER = dbo.GP6purline.docNumber AND JPUS.dbo.POP10500.POLNENUM = dbo.GP6purline.lineSeq
INNER JOIN dbo.tblPoFile ON JPUS.dbo.POP10310.PONUMBER = dbo.tblPoFile.PONUMBER
INNER JOIN JPUS.dbo.POP10110 ON JPUS.dbo.POP10310.PONUMBER = JPUS.dbo.POP10110.PONUMBER

else --posted
SELECT JPUS.dbo.POP30390.POPRCTNM AS ReceiptNumber,
JPUS.dbo.POP30390.SEQNUMBR AS SEQNUMBR,
JPUS.dbo.POP30390.DISTTYPE AS DISTTYPE,
JPUS.dbo.POP30310.PONUMBER AS PONUMBER,
JPUS.dbo.POP30310.ITEMNMBR AS ITEMNUMBR,
JPUS.dbo.POP30310.ITEMDESC AS ITEMDESC,
JPUS.dbo.POP30310.ACTLSHIP AS ActualShipDate,
JPUS.dbo.POP30310.UNITCOST AS UNITCOST,
JPUS.dbo.POP30310.EXTDCOST AS EXTDCOST,
JPUS.dbo.POP30300.ACTLSHIP AS ActualShipDate2,
JPUS.dbo.POP30300.receiptdate AS ReceiptDate,
JPUS.dbo.POP30300.VENDORID AS VENDORID,
JPUS.dbo.POP30300.VENDNAME AS VENDNAME,
JPUS.dbo.POP30300.PYMTRMID AS PaymentMethod,
JPUS.dbo.POP30300.USER2ENT AS UserEnterID,
JPUS.dbo.POP30300.BACHNUMB AS BachNumber,
JPUS.dbo.IVC30101.INVCNMBR AS InvoiceNumber,
JPUS.dbo.IVC30101.DOCTYPE AS DocType,
JPUS.dbo.POP10110.QTYORDER AS QTYORDER,
JPUS.dbo.POP10110.UOFM AS UOFM,
JPUS.dbo.PM00200.VENDNAME AS VENDNAME,
JPUS.dbo.PM00200.VNDCNTCT AS VNDCNTCT,
JPUS.dbo.PM00200.ADDRESS1 AS ADD1,
JPUS.dbo.PM00200.ADDRESS2 AS ADD2,
JPUS.dbo.PM00200.CITY AS CITY,
JPUS.dbo.PM00200.STATE AS STATE,
JPUS.dbo.PM00200.ZIPCODE AS ZIPCODE,
JPUS.dbo.PM00200.COUNTRY AS COUNTRY,
JPUS.dbo.PM00200.PHNUMBR1 AS PHONE,
JPUS.dbo.PM00200.FAXNUMBR AS FAX,
JPUS.dbo.PM00200.SHIPMTHD AS SHIPMTHD,
JPUS.dbo.GL00105.ACTINDX AS AccountIndex,
JPUS.dbo.GL00105.ACTNUMST AS AccountNumber,
JPUS.dbo.POP10500.QTYSHPPD AS QTYSHIP,
JPUS.dbo.POP10500.DATERECD AS DateReceived,
JPUS.dbo.POP10500.NOTEINDX AS NoteIndex,
JPUS.dbo.SY03900.TXTFIELD AS NoteText,
dbo.tblPoFile.PONUMBER AS PONUMBER,
dbo.tblPoFile.FILENUMBER AS FILENUMBER,
dbo.GP6purline.lineSeq as lineSeq,
dbo.GP6purline.application as app,
dbo.GP6purline.manufacturer as mang,
dbo.GP6purline.condition as con,
dbo.GP6purline.notes as notes,
dbo.GP6purline.docReq as docReq,
dbo.GP6purline.inspector as insp,
dbo.GP6purline.inspection_date as inspdate,
dbo.GP6purline.tagNumber as tag,
dbo.GP6purline.itemNumber as itemNo,
DYNAMICS.dbo.SY01400.USERNAME AS USERNAME,
OrderBy = 'POSTED'

FROM JPUS.dbo.POP30390 INNER JOIN JPUS.dbo.POP30310 ON JPUS.dbo.POP30390.POPRCTNM = JPUS.dbo.POP30310.POPRCTNM
INNER JOIN JPUS.dbo.POP10500 ON JPUS.dbo.POP30390.POPRCTNM = JPUS.dbo.POP10500.POPRCTNM
FULL JOIN JPUS.dbo.SY03900 ON JPUS.dbo.POP10500.NOTEINDX = JPUS.dbo.SY03900.NOTEINDX
INNER JOIN JPUS.dbo.POP30300 ON JPUS.dbo.POP30310.POPRCTNM = JPUS.dbo.POP30300.POPRCTNM
FULL JOIN JPUS.dbo.IVC30101 ON JPUS.dbo.POP30300.BACHNUMB = JPUS.dbo.IVC30101.BACHNUMB --INNER JOIN find out exact matches
FULL JOIN JPUS.dbo.POP10110 ON JPUS.dbo.POP30310.PONUMBER = JPUS.dbo.POP10110.PONUMBER
INNER JOIN JPUS.dbo.GL00105 ON JPUS.dbo.POP30390.ACTINDX = JPUS.dbo.GL00105.ACTINDX
FULL JOIN dbo.tblPoFile ON JPUS.dbo.POP30310.PONUMBER = dbo.tblPoFile.PONUMBER
FULL JOIN dbo.GP6purline ON JPUS.dbo.POP30310.PONUMBER = dbo.GP6purline.docNumber AND JPUS.dbo.POP30390.SEQNUMBR = dbo.GP6purline.lineSeq
INNER JOIN JPUS.dbo.PM00200 ON JPUS.dbo.POP30300.VENDORID = JPUS.dbo.PM00200.VENDORID
INNER JOIN DYNAMICS.dbo.SY01400 ON JPUS.dbo.POP30300.USER2ENT = DYNAMICS.dbo.SY01400.USERID
GO