I'm having an issue where a query ran on the publisher completes in 2-3 seconds while on the subscriber the same query takes 15-20 seconds to complete. I'm running Transaction replication. The difference between the Pub/Sub has to do with Indexes. I've created a script that rebuilds the indexes on the sub DB. We need to move the DB's from a dev environ to stage. Here's the process:
- Move the DB from Dev to Stage using a one time snapshot replication
- Indexes on Sub are created from a script which has no issues running
- Defaults are created via a script
- Triggers created via script
After this we notice that the query takes over 15-20 seconds to complete on the Sub but only takes 2-3 seconds on the Pub.
If I backup/restore the DB to the stage server the query again runs in 2-3 seconds. When I replicate the DB from stage to stage and create the indexes the queries again take about 15-20 seconds.
Any help would be much appreciated!
Wesley