Long running procedure returns 0 results

(10 posts) (2 voices)
  • Started 7 months ago by pfsit
  • Latest reply from myDBR Team

Tags:

No tags yet.

  1. pfsit, Member

    I have a stored procedure that runs a query which includes calling another stored procedure and putting the results in an temp table.

    When I run it on the server it runs fine (takes about 1.5 minutes), returns about 100k results. I can call it from a php file on the same server as mydbr it loads fine.

    When I try to run it in myDBR it immediately returns all of the columns with no results. I have tried using the refresh feature and it never returns any results.

    Is there anything else I should try?

    Edit: sorry didn't realize I was under the Installation section

    Posted 7 months ago #
  2. myDBR Team, Key Master

    If the execution returns an empty set immediately, it would indicate that the query does not return any data. If there would be a timeout, it would not return the empty set immediately.

    Does the query return an empty set if you run the exactly same procedure call in the SQL Editor as you do in the server side.

    What is the database you are using (and which db driver)? If you are using MS SQL Server, there might be different connection initialization (ARITHABORT/ANSI_NULLS) which might cause the execution to abort without error messages. You can change the initialization parameters by defining the db_connection -> sql_server_init/sqlsrv_server_init in the user/defaults.php.

    Note that with 100K rows your browser will have hard time rendering the HTML (will require a lot of memory). Although, your first problem is to get the data into client.

    --
    myDBR Team

    Posted 7 months ago #
  3. pfsit, Member

    I am using a MS-SQL 2012 R2 Database, with the sql_server driver

    I tried setting this in the defaults.php file
    $mydbr_defaults['db_connection']['sqlsrv_server_init'] = 'SET ANSI_WARNINGS ON;SET QUOTED IDENTIFIER OFF;';

    But it still just goes to the result screen with empty results, but the correct column headings.

    I can call the stored procedure in SSMS and in another PHP file and it returns results. I have a couple of others that I have been putting in that have the same problem

    The things in common on my procedures that aren't running is that they all take a little more than a minute and a half, and they call stored procedures and put results into a temp table with-in the procedure for the report.

    I have other reports that run just fine.

    Posted 7 months ago #
  4. myDBR Team, Key Master

    Just to be sure check the connection initialization first. The report below will show the settings. Run the procedure from myDBR and from SSMS to see if the connection initialization (@@options) is different. That could explain the immediate empty result (command execution is aborted without errors).

    create procedure sp_DBR_sql_server_options
    as
    begin
    declare @options int
    select @options = @@options create table #options (
    opt varchar(50)
    ) if ( (1 & @options) = 1 ) insert into #options values ('DISABLE_DEF_CNST_CHK')
    if ( (2 & @options) = 2 ) insert into #options values ('IMPLICIT_TRANSACTIONS' )
    if ( (4 & @options) = 4 ) insert into #options values ('CURSOR_CLOSE_ON_COMMIT')
    if ( (8 & @options) = 8 ) insert into #options values ('ANSI_WARNINGS')
    if ( (16 & @options) = 16 ) insert into #options values ('ANSI_PADDING')
    if ( (32 & @options) = 32 ) insert into #options values ('ANSI_NULLS')
    if ( (64 & @options) = 64 ) insert into #options values ('ARITHABORT')
    if ( (128 & @options) = 128 ) insert into #options values ('ARITHIGNORE' )
    if ( (256 & @options) = 256 ) insert into #options values ('QUOTED_IDENTIFIER')
    if ( (512 & @options) = 512 ) insert into #options values ('NOCOUNT')
    if ( (1024 & @options) = 1024 ) insert into #options values ('ANSI_NULL_DFLT_ON')
    if ( (2048 & @options) = 2048 ) insert into #options values ('ANSI_NULL_DFLT_OFF')
    if ( (4096 & @options) = 4096 ) insert into #options values ('CONCAT_NULL_YIELDS_NULL')
    if ( (8192 & @options) = 8192 ) insert into #options values ('NUMERIC_ROUNDABORT')
    if ( (16384 & @options) = 16384 ) insert into #options values ('XACT_ABORT') select * from #options end

    --
    myDBR Team

    Posted 7 months ago #
  5. pfsit, Member

    Sorry for the time in between posts

    The problems I run into seem to be only when my query runs a different stored procedure inside of the query and dumps the results into a temporary table.

    When I run the above command I get the following from SSMS

    opt
    ANSI_WARNINGS
    ANSI_PADDING
    ANSI_NULLS
    ARITHABORT
    QUOTED_IDENTIFIER
    ANSI_NULL_DFLT_ON
    CONCAT_NULL_YIELDS_NULL

    myDBR gives me the following result

    opt
    ANSI_PADDING
    ANSI_NULLS
    QUOTED_IDENTIFIER
    ANSI_NULL_DFLT_ON
    CONCAT_NULL_YIELDS_NULL
    # 5

    Posted 4 months ago #
  6. myDBR Team, Key Master

    Change the $mydbr_defaults['db_connection']['sqlsrv_server_init'] (assuming you are using the SQLSRV-driver) to match the SSMS settings. This way the query should work identically between the platforms.

    --
    myDBR Team

    Posted 4 months ago #
  7. pfsit, Member

    Sorry it would probably help to see the query. I have tried setting ansi_warnings and arithabort off and adding a refresh and running it for smaller date ranges.

    if object_id('sp_DBR_Beverage_Report','P') is not null
    drop procedure sp_DBR_Beverage_Report
    go
    create procedure sp_DBR_Beverage_Report
    @startdate datetime,
    @enddate datetime
    as
    begin

    SET ANSI_WARNINGS OFF;
    SET ARITHABORT OFF;

    select 'dbr.refresh', 90, 'counter';

    SET @startdate = CAST(@startdate AS DATE);
    SET @enddate = CAST(@enddate AS DATE);

    IF OBJECT_ID('tempdb.dbo.#TempResults','U') IS NOT NULL
    DROP TABLE #TempResults;

    IF OBJECT_ID('tempdb.dbo.#coolersbytype','U') IS NOT NULL
    DROP TABLE #coolersbytype;

    IF OBJECT_ID('tempdb.dbo.#coolersbymarket','U') IS NOT NULL
    DROP TABLE #coolersbymarket;

    IF OBJECT_ID('tempdb.dbo.#mmlocations','U') IS NOT NULL
    DROP TABLE #mmlocations;

    IF OBJECT_ID('tempdb.dbo.#BevManufacturers','U') IS NOT NULL
    DROP TABLE #BevManufacturers;

    CREATE TABLE #TempResults (
    pos_code varchar(32) NULL,
    pos_description varchar(32) NULL,
    loc_code varchar(32) NULL,
    loc_description varchar(32) NULL,
    cus_code varchar(32) NULL,
    cus_description varchar(32) NULL,
    nat_code varchar(32) NULL,
    nat_description varchar(32) NULL,
    sit_code varchar(32) NULL,
    sit_description varchar(32) NULL,
    revenue_collected varchar(32) NULL,
    product_costs varchar(32) NULL,
    total_comm varchar(32) NULL,
    total_taxes varchar(32) NULL,
    items_sold varchar(32) NULL,
    price varchar(32) NULL,
    net_revenue varchar(32) NULL,
    gross varchar(32) NULL,
    net_profit varchar(32) NULL,
    pro_code varchar(32) NULL,
    pro_description varchar(32) NULL,
    Product_SKU varchar(32) NULL,
    pdf_code varchar(32) NULL,
    pdf_description varchar(32) NULL,
    pro_source_id varchar(32) NULL,
    pro_id varchar(32) NULL,
    pkp_source_id varchar(32) NULL,
    pkp_id varchar(32) NULL,
    pdf_source_id varchar(32) NULL,
    pdf_id varchar(32) NULL,
    pos_source_id varchar(32) NULL,
    pos_id varchar(32) NULL,
    loc_source_id varchar(32) NULL,
    loc_id varchar(32) NULL,
    cus_source_id varchar(32) NULL,
    cus_id varchar(32) NULL,
    nat_source_id varchar(32) NULL,
    nat_id varchar(32) NULL,
    sit_source_id varchar(32) NULL,
    sit_id varchar(32) NULL,
    pro_revenue varchar(32) NULL,
    pro_net_revenue varchar(32) NULL,
    pro_gross varchar(32) NULL,
    pro_gross_pr varchar(32) NULL,
    pro_net_profit varchar(32) NULL,
    pro_net_profit_pr varchar(32) NULL,
    pdf_revenue varchar(32) NULL,
    pdf_net_revenue varchar(32) NULL,
    pdf_gross varchar(32) NULL,
    pdf_gross_pr varchar(32) NULL,
    pdf_net_profit varchar(32) NULL,
    pdf_net_profit_pr varchar(32) NULL,
    pos_revenue varchar(32) NULL,
    pos_net_revenue varchar(32) NULL,
    pos_gross varchar(32) NULL,
    pos_gross_pr varchar(32) NULL,
    pos_net_profit varchar(32) NULL,
    pos_net_profit_pr varchar(32) NULL,
    loc_revenue varchar(32) NULL,
    loc_net_revenue varchar(32) NULL,
    loc_gross varchar(32) NULL,
    loc_gross_pr varchar(32) NULL,
    loc_net_profit varchar(32) NULL,
    loc_net_profit_pr varchar(32) NULL,
    cus_revenue varchar(32) NULL,
    cus_net_revenue varchar(32) NULL,
    cus_gross varchar(32) NULL,
    cus_gross_pr varchar(32) NULL,
    cus_net_profit varchar(32) NULL,
    cus_net_profit_pr varchar(32) NULL,
    nat_revenue varchar(32) NULL,
    nat_net_revenue varchar(32) NULL,
    nat_gross varchar(32) NULL,
    nat_gross_pr varchar(32) NULL,
    nat_net_profit varchar(32) NULL,
    nat_net_profit_pr varchar(32) NULL,
    sit_revenue varchar(32) NULL,
    sit_net_revenue varchar(32) NULL,
    sit_gross varchar(32) NULL,
    sit_gross_pr varchar(32) NULL,
    sit_net_profit varchar(32) NULL,
    sit_net_profit_pr varchar(32) NULL,
    pos_grp_param_desc varchar(32) NULL,
    pos_param_code varchar(32) NULL,
    pos_param_desc varchar(32) NULL,
    loc_grp_param_desc varchar(32) NULL,
    loc_param_code varchar(32) NULL,
    loc_param_desc varchar(32) NULL,
    cus_grp_param_desc varchar(32) NULL,
    cus_param_code varchar(32) NULL,
    cus_param_desc varchar(32) NULL,
    pdf_grp_param_desc varchar(32) NULL,
    pdf_param_code varchar(32) NULL,
    pdf_param_desc varchar(32) NULL,
    pro_grp_param_desc varchar(32) NULL,
    pro_param_code varchar(32) NULL,
    pro_param_desc varchar(32) NULL,
    sit_grp_param_desc varchar(32) NULL,
    sit_param_code varchar(32) NULL,
    sit_param_desc varchar(32) NULL,
    nat_param_code varchar(32) NULL,
    nat_param_desc varchar(32) NULL,
    GroupNumber varchar(32) NULL
    )

    INSERT INTO #TempResults
    EXEC Vendmax.dbo.RPT_Financials_By_PDF
    @NPB_Date = @startdate,
    @NPE_Date = @enddate,
    @STR_Basis = 'Product Sales',
    @STR_Top_Level_Grouping = 'POS',
    @STR_Include_MicroMarket = 'MicroMarket Only',
    @STR_Show_MM_Sales_Based_On = 'Actual MM Sales',
    @STR_Level_Of_Detail = 'PRO',
    @STR_Machine_Type = 'All',
    @STR_Point_Of_Sale_Type = 'All',
    @STR_Show_Outsourced = 'No',
    @STR_Choose_Record = 'All',
    @STR_Packaged_Product_Detail = 'Yes',
    @STR_Vend_Visits = 'All Vend Visits';

    CREATE TABLE #coolersbymarket (
    locID varchar(32) NULL,
    locSource varchar(32) NULL,
    locationCode varchar(32) NULL,
    nveCode varchar(32) NULL,
    locationDesc varchar(32) NULL,
    posID varchar(32) NULL,
    posDesc varchar(32) NULL,
    nveID varchar(32) NULL,
    customer varchar(32) NULL,
    locationID varchar(32) NULL,
    nveDesc varchar(32) NULL
    )

    INSERT INTO #coolersbymarket
    SELECT
    loc.loc_id AS 'locID'
    ,loc.loc_source_id AS 'locSource'
    ,loc.code AS 'locationCode'
    ,nve.code AS 'nveCode'
    ,loc.description AS 'locationDesc'
    ,pos.pos_id AS 'posID'
    ,pos.description AS 'posDesc'
    ,nve.nve_id AS 'nveID'
    ,cus.description AS 'customer'
    ,loc.loc_id AS 'locationID'
    --,loc.code AS 'locationCode'
    --,nve.code AS 'nveCode'
    ,nve.description AS 'nveDesc'
    FROM Vendmax.dbo.non_vending_equipment nve
    JOIN Vendmax.dbo.equipment_placement_history eph
    ON nve.nve_id = eph.nve_id
    AND nve.nve_source_id = eph.nve_source_id
    JOIN Vendmax.dbo.points_of_sale pos
    ON eph.pos_id = pos.pos_id
    AND eph.pos_source_id = pos.pos_source_id
    JOIN Vendmax.dbo.locations loc
    ON pos.loc_id = loc.loc_id
    AND pos.loc_source_id = loc.loc_source_id
    JOIN Vendmax.dbo.customers cus
    ON loc.cus_id = cus.cus_id
    AND loc.cus_source_id = cus.cus_source_id
    WHERE eph.placement_status = 'A'
    AND LEFT(nve.code,3) IN ('60-','50-','40-')
    --AND pos.pos_active = 'N'
    AND nve.description LIKE '%cooler%'
    --AND LEFT(pos.description, 3) != 'zzz'
    AND loc.loc_id IN
    (SELECT loc_id FROM Vendmax.dbo.points_of_sale WHERE pos_type = 'M' AND pos_active = 'Y')
    ORDER BY cus.description,loc.code
    ;

    CREATE TABLE #mmlocations (
    locCode varchar(32) NULL,
    locID varchar(32) NULL,
    locSource varchar(32) NULL
    );

    CREATE TABLE #coolersbytype (
    locID varchar(32) NULL,
    locSource varchar(32) NULL,
    locCode varchar(32) NULL,
    hasCoke varchar(32) NULL,
    hasPepsi varchar(32) NULL
    )

    INSERT INTO #mmlocations
    SELECT
    MAX(locationCode) AS 'locCode',
    locID,
    locSource
    FROM #coolersbymarket
    GROUP BY locID, locSource;

    INSERT INTO #coolersbytype
    SELECT
    MAX(mml.locID) AS 'locID'
    ,MAX(mml.locSource) AS 'locSource'
    ,MAX(mml.locCode) AS 'locCode'
    ,(SELECT COUNT(*) FROM #coolersbymarket WHERE MAX(mml.locCode) = locationCode AND LEFT(nveCode,3) = '50-') AS 'hasCoke'
    ,(SELECT COUNT(*) FROM #coolersbymarket WHERE MAX(mml.locCode) = locationCode AND LEFT(nveCode,3) = '60-') AS 'hasPepsi'
    FROM #mmlocations mml
    JOIN #coolersbymarket cbm
    on mml.locCode = cbm.locationCode
    AND mml.locCode = cbm.locationCode
    GROUP BY mml.locCode

    CREATE TABLE #BevManufacturers (
    productID varchar(32) NULL
    ,proSource varchar(32) NULL
    ,manufacturerID varchar(32) NULL
    )

    INSERT INTO #BevManufacturers
    SELECT pro.pro_id
    ,MAX(pro.pro_source_id)
    ,MAX(grpm.grp_id)
    FROM Vendmax.dbo.products pro
    JOIN Vendmax.dbo.group_members grpm
    ON pro.pro_id = grpm.member_id
    AND pro.pro_source_id = grpm.member_source_id
    WHERE grpm.grp_id IN ('5639364','5645249','5698088','5717422','4679846', '5727297', '5800150')
    GROUP BY pro.pro_id;

    SELECT
    CONCAT(DATENAME(month, @startdate),' ',DATEPART(day,@startdate),', ',DATEPART(year,@startdate)) AS 'ORT FROM DATE'
    ,CONCAT(DATENAME(month, @enddate),' ',DATEPART(day,@enddate),', ',DATEPART(year,@enddate)) AS 'ORT TO DATE'
    ,CASE when bev.manufacturerID = '5639364' then 'Pepsi'
    when bev.manufacturerID = '5645249' then 'Coke'
    when bev.manufacturerID = '5698088' then 'Red Bull'
    when bev.manufacturerID = '5717422' then 'Monster'
    when bev.manufacturerID = '4679846' then '7-UP'
    when bev.manufacturerID = '5727297' then 'VSA'
    when bev.manufacturerID = '5800150' then 'All Brands Beverage'
    else 'Not in Pepsi/Coke Product Group'
    end AS 'Beverage Product Manufacturer'
    ,'206' AS 'Franchise OPS'
    ,'Premier Food Service' AS 'Franchise Name'
    ,temp.cus_description AS 'Customer'
    ,temp.loc_description AS 'Location'
    ,loc.addr1 AS 'Address 1'
    ,loc.addr2 AS 'Address 2'
    ,loc.addr3 AS 'Address 3'
    ,loc.city AS 'City'
    ,loc.state AS 'State'
    ,loc.zip AS 'Zip'
    ,pos_code AS 'POS Code'
    ,CASE
    WHEN cool.hasCoke > '0' AND cool.hasPepsi = '0' THEN 'COKE'
    WHEN cool.hasCoke = '0' AND cool.hasPepsi > '0' THEN 'CONSUMER'
    WHEN cool.hasCoke > '0' AND cool.hasPepsi > '0' THEN 'COKE/CONSUMER'
    WHEN cool.hasCoke = '0' AND cool.hasPepsi = '0' THEN 'CONSUMER'
    ELSE 'Not Found'
    END AS 'Cooler'
    ,(SELECT TOP 1 barcode
    FROM Vendmax.dbo.barcode_map WHERE pkp_id = pkp.pkp_id AND pkp_source_id = pkp.pkp_source_id ORDER BY LEN(barcode) DESC) AS 'UPC'
    ,temp.pro_description AS 'Product Name'
    ,temp.price AS 'TotalPrice'
    ,temp.items_sold AS 'Quantity'
    ,temp.pdf_description AS 'Product Family'
    FROM #TempResults temp
    LEFT JOIN Vendmax.dbo.locations loc
    ON temp.loc_id = loc.loc_id
    AND temp.loc_source_id = loc.loc_source_id
    JOIN Vendmax.dbo.products pro
    ON temp.pro_id = pro.pro_id
    AND temp.pro_source_id = pro.pro_source_id
    JOIN Vendmax.dbo.packaged_products pkp
    ON pro.pro_id = pkp.pro_id
    AND pro.pro_source_id = pkp.pro_source_id
    AND (pkp.uni_id = 1 AND pkp.uni_source_id = 1)
    LEFT JOIN #coolersbytype cool
    ON loc.loc_id = cool.locID
    AND loc.loc_source_id = cool.locSource
    LEFT JOIN #BevManufacturers bev
    ON temp.pro_id = bev.productID
    AND temp.pro_source_id = bev.proSource
    WHERE
    LEFT(temp.pdf_description,1) != 'z'
    AND LEFT(temp.pdf_description,3) != 'BAG'
    ORDER BY cus_description, loc_description;

    end
    go

    Posted 4 months ago #
  8. myDBR Team, Key Master

    A bit long but nothing major there.

    You have the "select 'dbr.refresh', 90, 'counter';" in the report and the report produces 100K rows into the temp table. You really want to run the query on every 90 seconds?

    And the GROUP BY in the following query looks bit weird (does not really make sense):

    SELECT
    MAX(mml.locID) AS 'locID'
    ,MAX(mml.locSource) AS 'locSource'
    ,MAX(mml.locCode) AS 'locCode'
    ,(SELECT COUNT(*) FROM #coolersbymarket WHERE MAX(mml.locCode) = locationCode AND LEFT(nveCode,3) = '50-') AS 'hasCoke'
    ,(SELECT COUNT(*) FROM #coolersbymarket WHERE MAX(mml.locCode) = locationCode AND LEFT(nveCode,3) = '60-') AS 'hasPepsi'
    FROM #mmlocations mml
    JOIN #coolersbymarket cbm on mml.locCode = cbm.locationCode AND mml.locCode = cbm.locationCode
    GROUP BY mml.locCode

    --
    myDBR Team

    Posted 4 months ago #
  9. pfsit, Member

    Sorry you are correct on the refresh, I had set that previously when I thought it would poll for new data ( for the query to finish running on the server side).

    I have turned the refresh off and then set the following on the user/defautlts.php file

    $mydbr_defaults['db_connection']['sqlsrv_server_init'] = 'SET ANSI_WARNINGS ON;SET ARITHABORT OFF;SET ANSI_NULLS ON;SET QUOTED_IDENTIFIER OFF;';

    The report still doesn't return any data. When I run it for a month it takes ~60 seconds to run on the server side, but the page is loading immediately.

    Posted 4 months ago #
  10. myDBR Team, Key Master

    Are you using same credentials to run the queries on both platforms? Does myDBR have execute rights tp Vendmax.dbo.RPT_Financials_By_PDF?

    The next step would be to isolate the problem. Put in debug selects so you see what queries are executed. If the report returns immediately, it would indicate problems with the Vendmax.dbo.RPT_Financials_By_PDF call.
    --
    myDBR Team

    Posted 4 months ago #

Reply

You must log in to post.