Replication & Report execution

I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
8.4 s to be completely accurate
To get a consistent database state you should schedule your distribution agent. This way it will not run continuously but rather in a batch.
You might also want to split the tables into their own publication and then use the independent agent option. This should solve the synchronization problems.
The most significant thing you can do to improve your transactional performance is to try to minimize the indexes, triggers and indexed views hanging off the subscriber tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:e7ivuSSlEHA.3988@.TK2MSFTNGP14.phx.gbl...
I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.

Replicating Stored Proc Execution

Hi Guys:
When we run the proc from Query Analyzer, we can see the replication of
store proc execution on subscriber/distributor. However when we run this
proc from Java application, we don't see the proc being executed on
subscriber even though we see it on distributor. Any reason why execution of
proc is not replicated properly when executing it from Java. Please let me
know.
Thanks
When you say you see it in the distributor do you mean it shows up in the
output of sp_browsereplcmds? If so run profiler on the subscriber to ensure
that it is running there with the correct parameters. Then run it manually
on the subscriber to see if it works there. It is possible that the proc is
referencing non-existent tables or data which is not present on the
subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:157726CB-E10F-4F1D-991C-C8FCB84A5CE7@.microsoft.com...
> Hi Guys:
> When we run the proc from Query Analyzer, we can see the replication of
> store proc execution on subscriber/distributor. However when we run this
> proc from Java application, we don't see the proc being executed on
> subscriber even though we see it on distributor. Any reason why execution
> of
> proc is not replicated properly when executing it from Java. Please let
> me
> know.
> Thanks
|||that is what we did we used the profiler on the subscriber but we don't find
that proc when running from Java. You are saying that if the proc is
referencing non-existance table/data, it will not show up in profiler. We
are pretty sure that table is there. Any other thoughts.
Thanks
"Hilary Cotter" wrote:

> When you say you see it in the distributor do you mean it shows up in the
> output of sp_browsereplcmds? If so run profiler on the subscriber to ensure
> that it is running there with the correct parameters. Then run it manually
> on the subscriber to see if it works there. It is possible that the proc is
> referencing non-existent tables or data which is not present on the
> subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Sal" <Sal@.discussions.microsoft.com> wrote in message
> news:157726CB-E10F-4F1D-991C-C8FCB84A5CE7@.microsoft.com...
>
>
|||This is bewildering. There are some options for stored procedure execution -
like only running within a serializable transaction. Are you using any of
these?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:8DE46216-94FC-4258-9241-083B06C0758B@.microsoft.com...[vbcol=seagreen]
> that is what we did we used the profiler on the subscriber but we don't
> find
> that proc when running from Java. You are saying that if the proc is
> referencing non-existance table/data, it will not show up in profiler. We
> are pretty sure that table is there. Any other thoughts.
> Thanks
> "Hilary Cotter" wrote:
|||Thanks for your setting. That is what we thought because we were using that
option (serializable). Now we changed it to execute procedure every time but
still having this problem. Not sure whether Java is using some setting
which is causing this.
"Hilary Cotter" wrote:

> This is bewildering. There are some options for stored procedure execution -
> like only running within a serializable transaction. Are you using any of
> these?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Sal" <Sal@.discussions.microsoft.com> wrote in message
> news:8DE46216-94FC-4258-9241-083B06C0758B@.microsoft.com...
>
>