Posted by vikram.mankar on 02/06/07 23:19

I'll give it a shot. Is it generally more efficient to check for
duplicates through T-SQL like with WHERE NOT EXISTS? or use
constraints on the table? I realize the latter is a little painful as
it clogs the error logs for the job history.

thanks, Vikram

On Feb 6, 6:05 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (vikram.man...@gmail.com) writes:
> > The application logs "raw" data. The SQL Job (stored procedure) is
> > adding attributes to that data and moving it to "report" tables. The
> > cursor was used to select the right attributes based on the data
> > logged.
>
> > The application is logging data from a hardware device (PLC) thats
> > generating data faster than the application can accept (at times) ..
> > hence the issue of duplicates to avoid any data loss during the timing
> > issue. There is unfortunately no way to control the duplicate problem
> > at the application level. But since its generating data so fast - we
> > need to just dump the raw data in a table and then copy it for
> > reporting purposes.
>
> I can understand that you log everthing in the raw tables. That
> certainly seems like the best strategy. And my suggestion was not
> that you have the WHERE NOT EXISTS in this place, but rather the in
> the Agent job.
>
> Whether the WHERE NOT EXISTS would be too costly in the Agent job,
> I don't think so. After all, if you can afford having a cursor, then
> you don't appear to be in a hurry...
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]