Stored Proc slow after switching to Replication?

Question

Post reply

Stored Proc slow after switching to Replication?

jbalbo

SSCertifiable

Points: 7536

April 1, 2020 at 2:02 pm

#3739626

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 ....

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

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

Site Owners SSC Guru Points: 80338 More actions · Answer 1

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: 63198 More actions · Answer 2

are you querying the master or querying a replica?

jbalbo SSCertifiable Points: 7536 More actions · Answer 3

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: 90860 More actions · Answer 4

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