Stored Proc slow after switching to Replication?

  • jbalbo

    SSCertifiable

    Points: 7441

    Hi

    First, let me apologize in advance for my ignorance on this...

    We are in the process of switching our reporting DB from backup and restore to transactional replication.

    I am testing stored procedures and noticing some SP's run much slower in transactional replication.

    I've done some googling and have tried the three below, but the time is not any better

    set transaction isolation level read uncommitted

    ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON;

    ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON;

    I have sort of figured out where it got slow , I have selects within select (which I have commented out), which makes it run much faster under replication, the code WITHOUT being commented out runs fast in the DB that is backup and restore. It makes me think something about replication doesn't like the selects within select .

    Any ideas would be appreciated...

    Thanks

     SELECT  
    CASE
    WHEN(dbdata.dbo.db_ClientCases.[Open Date] IS NOT NULL
    AND dbdata.dbo.db_ClientCases.[Admission Date] IS NULL)
    THEN '1'
    WHEN(dbdata.dbo.db_ClientCases.[Open Date] IS NOT NULL
    AND dbdata.dbo.db_ClientCases.[Admission Date] IS not NULL) then '2'


    ELSE '3'
    END AS Admittype,
    dbdata.dbo.db_ProgramSites.ID,
    dbdata.dbo.db_ProgramSites.Program,

    --get the doc count

    /* CASE
    WHEN
    (
    SELECT COUNT(ce1.Subject)
    FROM dbdata.dbo.db_ClientCases cc1 with (nolock)
    INNER JOIN dbdata.dbo.db_ObjectMetadata om1 with (nolock) ON cc1.ObjectID = om1.[Case]
    INNER JOIN dbdata.dbo.db_CalendarEvents ce1 with (nolock) ON om1.ObjectID = ce1.ParentObject
    --INNER JOIN
    --dbdata.dbo.db_ProgramSites pc ON cc.Program_Site = ps.ID
    WHERE cc1.ObjectID = dbdata.dbo.db_ClientCases.ObjectID
    AND ce1.subject IN(@Workflowtype)) = 0
    THEN '1/1/1900'
    ELSE
    (
    SELECT MAX(ce.StartTime)
    FROM dbdata.dbo.db_ClientCases cc with (nolock)
    INNER JOIN dbdata.dbo.db_ObjectMetadata om with (nolock) ON cc.ObjectID = om.[Case]
    INNER JOIN dbdata.dbo.db_CalendarEvents ce with (nolock) ON om.ObjectID = ce.ParentObject
    WHERE cc.ObjectID = dbdata.dbo.db_ClientCases.ObjectID
    AND ce.subject IN(@Workflowtype)
    ) -- CHANGE TO PARAMTER

    END AS LastDate,
    (
    SELECT TOP 1 ce2.Subject
    FROM dbdata.dbo.db_ClientCases cc2
    INNER JOIN dbdata.dbo.db_ObjectMetadata om2 with (nolock) ON cc2.ObjectID = om2.[Case]
    INNER JOIN dbdata.dbo.db_CalendarEvents ce2 with (nolock) ON om2.ObjectID = ce2.ParentObject
    WHERE cc2.ObjectID = dbdata.dbo.db_ClientCases.ObjectID
    AND ce2.subject IN(@Workflowtype)
    ) -- CHANGE TO PARAMTER
    AS workflowtype, */

    ( SELECT top 1 dbdata.dbo.db_Teams.Name
    FROM dbdata.dbo.db_Teams with (nolock) INNER JOIN
    dbdata.dbo.db_Client_Teams with (nolock) ON dbdata.dbo.db_Teams.ID = dbdata.dbo.db_Client_Teams.Team

    where parent = dbdata.dbo.db_ClientCases.Client and dbdata.dbo.db_Teams.[Program/Site] = dbdata.dbo.db_ProgramSites.ID) as Team
    INTO #TEMP

    FROM dbdata.dbo.db_Programs INNER JOIN
    dbdata.dbo.db_ProgramSites ON dbdata.dbo.db_Programs.ObjectID = dbdata.dbo.db_ProgramSites.Program INNER JOIN
    dbdata.dbo.db_ClientCases ON dbdata.dbo.db_ProgramSites.ID = dbdata.dbo.db_ClientCases.Program_Site INNER JOIN
    dbdata.dbo.db_ProgramSites AS db_ProgramSites_1 ON dbdata.dbo.db_ClientCases.Program_Site = db_ProgramSites_1.ID INNER JOIN
    dbdata.dbo.db_Programs AS db_Programs_1 ON db_Programs_1.ObjectID = db_ProgramSites_1.Program INNER JOIN
    dbdata.dbo.db_People ON dbdata.dbo.db_ClientCases.Client = dbdata.dbo.db_People.ObjectID

    WHERE ....
  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • pietlinden

    SSC Guru

    Points: 62767

    are you querying the master or querying a replica?

  • jbalbo

    SSCertifiable

    Points: 7441

    So our vendor setup transactional replication to our report server.

    We were using a daily backup and restore.

    I tested with a Stored proc, I ran it against the backuped up DB and it ran fine(1 second)

    I tested with replicated DB and it ran for 5 minutes and didn't finish.

     

  • Sue_H

    SSC Guru

    Points: 90669

    Publisher or subscriber makes a difference and I'm not clear which you are referring to. But if you are testing this against a subscriber, the first thing to check would be if indexes are copied by viewing the properties for the articles. If defaults were used, nonclustered indexes are not copied. Look at the execution plans for the stored procedures in each environment.

    Sue

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply