optimizing stored procedures

hi all,
i'm working in a database, i'm not the creater of it, and was assigned to optimize the stored procedures running against this database.
can someone tell me what steps should i follow to check these SP's and to what should i look at and what can be done to optimise them.

thanks expertsIt might be a two line question but it is a massive subject. People make their careers out of this.

read stuf here:
http://www.sql-server-performance.com/
Learn to read execution plans.
Learn about SET STATISTICS IO
Learn to use profiler.
Read BoL (section on Optimising Database Performance).
Learn your database.
Learn about indexing strategies.|||First make sure all the critical joins are indexed. Then eliminate any cursors or loops.|||I usually I just ask the QA people what parts of the app sucks the will to live out of them and then I trace those screens to identify the really big pieces of suck ass code and I look at the execution plans of those queries. I like being reactive.|||exactly. the ONLY sure way to find where the perf problems are is to measure.

ask any dev where the most time is spent in their code, and 90% will give you the wrong answer. the other 10% are right only because they have profiled it. :)|||I would trac it all to a table, th query the damn thing for the longest running pieces of code...

I guess we could make you look like a hero, sinve it seems no one else knows what's going on.

How big is this company?

And how what's your experience level

And do you have SQL Server client tools installed

Do you know what books online is?

Do you know what Google is?|||I guess we could make you look like a hero
...
Do you know what Google is?Downgrading as you type.|||There are some good articles about perf tuning here:

http://www.sql-server-performance.com/articles_performance.asp
http://www.sql-server-performance.com/articles_audit.asp

in particular this one will show you how to use profiler for catching the biggest pigs in your database:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

and elsewhere on that site.

Be aware, however, sql is not the only process that can kill your perf. compiled code can be poorly written too. for compiled code you need to use a different profiling tool (I usually use perf studio in VS).|||I trace all the queries to a table, as Brett mentioned. Then query it for the total number of reads grouped by say the first 50 or 100 characters of the textdata field. I find it is usually better to tune the average running query that runs 1,000's of times per day than the one pig that runs once at midnight. With all the variations that the textdata field can have, getting that aggregate right is a challenge, though.|||...
Downgrading as you type.

Are you saying that you should assume something about someone's experience?|||Nope - but if you take a poster on a forum from not knowing what google is to being a hero dba then I will eat all my hats.

Anyway yes - the OP needs to identify the sprocs to tune and then needs to learn the skills to tune them. Or post them here of course.

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!
Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
--
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution tim
e
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-base
d
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger becaus
e
> multiple procedural statements in triggers will create a bottleneck. For t
he
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues
or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

Optimizing a hidious query...

I have the following query...
/****** Object: Stored Procedure dbo.BENESP_JournalEntrySearch Script
Date: 4/23/2004 11:45:30 AM ******/
CREATE PROCEDURE [DBO].[BSP_JournalEntrySearch]
@.Type int = null,
@.User varchar(50) = null,
@.EntryState int = 2, -- 1 open, 0 closed, 2 either
@.DateEnteredType int = 0, -- 0 both, 1 entrys only, 2 events only
@.DateEnteredStart datetime = null,
@.DateEnteredEnd datetime = null,
@.DateDueStart datetime = null,
@.DateDueEnd datetime = null,
@.AccountFor int = null,
@.messageText varchar(4000) = null,
@.loginname varchar(50) = '', -- Required login name for user perfoming
search
@.IncludeRouted bit = null,
@.enrollee int = null
AS
-- REMEMBER TO REMOVE TIME FROM DATES SO WE CAN CHECK THE DATE ONLY
-- DBO.RemoveTimeFromDate function removes time from datetime's
SET NOCOUNT ON
-- get message list and join it to its initial message from the events table
based on most recent message
select a.EventMessageText,JETR.* from
(select DISTINCT
People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntrySu
bTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
(select count(*) from JournalEntryJunStoredFiles where JournalEntryID =
journalentries.JournalEntryID) as AttachmentCount
from JournalEntries left outer join JournalEvents on
JournalEntries.JournalEntryID = JournalEvents.JournalEntryID
left outer join JournalEntrySubTypes on JournalEntries.JETSubTypeID =
JournalEntrySubTypes.JETSubTypeID
left outer join JournalEntryTypes on JournalEntrySubTypes.JETypeID =
JournalEntryTypes.JETypeID
left outer join JournalEntryTypePermissions on
JournalEntryTypes.JETypeID = JournalEntryTypePermissions.JETypeID
left outer join Accounts on JournalEntries.RelatedAccountID =
Accounts.AccountID
left outer join People on JournalEntries.Enrollee = People.PersonID
where 1=1 and (journalentries.creator = @.loginname or
JournalEntries.JournalEntryID in (select
JournalEntryJunJournalRoutingClass.JournalEntryID
from (JournalEntryJunJournalRoutingClass left outer join
JournalRoutingClasses on JournalEntryJunJournalRoutingClass.JRCID =
JournalRoutingClasses.JRCID)
left outer join JRCJunUsers on JournalRoutingClasses.JRCID
= JRCJunUsers.JRCID
where JRCJunUsers.loginname = @.loginname))
-- search criteria
AND (@.messageText IS NULL OR FREETEXT(JournalEvents.*,@.messagetext)) --
TEXT SEARCH
AND (@.enrollee IS NULL OR JournalEntries.Enrollee = @.enrollee)
AND (@.IncludeRouted IS NULL OR 1=1) -- is routed to user or their
own messages
AND (@.Type IS NULL OR JournalEntrySubTypes.JETSubTypeID = @.Type)
AND (@.AccountFor IS NULL OR JournalEntries.RelatedAccountID = @.AccountFor)
AND ((@.DateDueStart IS NULL AND @.DateDueEnd IS NULL) OR
DBO.RemoveTimeFromDate(JournalEntries.DueDate) between
DBO.RemoveTimeFromDate(@.DateDueStart) AND
DBO.RemoveTimeFromDate(@.DateDueEnd))
AND (@.User IS NULL OR (JournalEntries.Creator = @.user or
JournalEvents.Creator = @.user))
AND ((@.entrystate = 0 and JournalEntries.EntryClosed = 1)
OR (@.entrystate = 1 and JournalEntries.EntryClosed = 0)
OR (@.entrystate = 2 and (JournalEntries.EntryClosed = 0 or
JournalEntries.EntryClosed = 1)))
-- date range entry check stuff
AND (@.DateEnteredStart IS NULL OR ((@.DateEnteredType = 0 OR
@.DateEnteredType = 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate)
>= @.dateEnteredStart) OR ((@.DateEnteredType = 0 OR @.DateEnteredType = 2) AND
DBO.RemoveTimeFromDate(JournalEvents.EventDate) >=
DBO.RemoveTimeFromDate(@.DateEnteredStart)))
AND (@.DateEnteredEnd IS NULL OR ((@.DateEnteredType = 0 OR @.DateEnteredType
= 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate) <=
@.DateEnteredEnd) OR ((@.DateEnteredType = 0 OR @.DateEnteredType = 2) AND
DBO.RemoveTimeFromDate(JournalEvents.EventDate) <=
DBO.RemoveTimeFromDate(@.DateEnteredEnd)))
AND permissionid in (select permissionid from
bene_users.dbo.UsersPermissions where loginname = @.loginname)) JETR
-- gets initial message for the entry after we know what messages we have
searched for
left outer join (SELECT JournalEntryID,EventMessageText FROM JournalEvents
je1 WHERE je1.EventDate = (SELECT MIN(je2.EventDate ) FROM JournalEvents
je2 WHERE je1.JournalEntryID = je2.JournalEntryID)) a on JETR.JournalEntryID
= a.JournalEntryID
GO
which takes about 14 seconds to run when I have the last left outer join on
it to get the initial text from the JouranlEvents table... without it it
takes under 1 second to run on a table of thousands of entries in the
journalentries table...
here is my trace on it
SET STATISTICS PROFILE ON
SQL:StmtCompleted 0 0 0 0 SET NOCOUNT ON -- get message list and join
it to its initial message from the events table based on most recent message
SP:StmtCompleted 0 0 0 0 select a.EventMessageText,JETR.* from (select
DISTINCT
People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntrySu
bTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
(select count(*) f
SP:StmtCompleted 153 34 91 0 exec BENESP_JournalEntrySearch
@.loginname='brian_henry'
SQL:StmtCompleted 169 50 97 0 SET STATISTICS PROFILE OFF
SQL:StmtCompleted 0 0 0 0
statistics on it
Application Profile Statistics
Timer resolution (milliseconds)
0 0 Number of INSERT, UPDATE, DELETE statements
0 0 Rows effected by INSERT, UPDATE, DELETE statements
0 0 Number of SELECT statements
2 2 Rows effected by SELECT statements
100 100 Number of user transactions
5 5 Average fetch time
0 0 Cumulative fetch time
0 0 Number of fetches
0 0 Number of open statement handles
0 0 Max number of opened statement handles
0 0 Cumulative number of statement handles
0 0
Network Statistics
Number of server roundtrips
3 3 Number of TDS packets sent
3 3 Number of TDS packets received
209 209 Number of bytes sent
252 252 Number of bytes received
832650 832650
Time Statistics
Cumulative client processing time
0 0 Cumulative wait time on server replies
2.75185e+007 2.75185e+007
any idea how to speed up that last join?! i need that message appended
server side because it takes even more time client side to do it
programmatically in the application
thanks for any help!
here is the basic DDL for this too... i didnt include relations or keys in
it though...
=====================================
CREATE TABLE [dbo].[JRCJunUsers] (
[JRCID] [int] NOT NULL ,
[loginName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntries] (
[JournalEntryID] [int] IDENTITY (1, 1) NOT NULL ,
[RelatedAccountID] [int] NULL ,
[DueDate] [datetime] NULL ,
[Creator] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AutoCreation] [bit] NOT NULL ,
[EntryDate] [datetime] NOT NULL ,
[CloseDate] [datetime] NULL ,
[LastUpdatedDate] [datetime] NULL ,
[LastModifiedBy] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EntryClosed] [bit] NOT NULL ,
[JETSubTypeID] [int] NOT NULL ,
[Enrollee] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryJunJournalRoutingClass] (
[JournalEntryID] [int] NOT NULL ,
[JRCID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryJunStoredFiles] (
[JournalEntryID] [int] NOT NULL ,
[FileID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryJunUsersStatus] (
[loginName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JournalEntryID] [int] NOT NULL ,
[IsClosed] [bit] NOT NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntrySubTypes] (
[JETSubTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JETypeID] [int] NOT NULL ,
[DefaultJRC] [int] NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryTypePermissions] (
[JETypeID] [int] NOT NULL ,
[PermissionID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEntryTypes] (
[JETypeID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeName] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AssociatedFormIDCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEventActions] (
[EventActionID] [int] IDENTITY (1, 1) NOT NULL ,
[EventActionName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ContacteeRequired] [bit] NOT NULL ,
[ActionPerformed] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsAvailable] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalEvents] (
[EventID] [int] IDENTITY (1, 1) NOT NULL ,
[JournalEntryID] [int] NOT NULL ,
[EventDate] [datetime] NOT NULL ,
[EventActionID] [int] NOT NULL ,
[Contactee] [int] NULL ,
[ContacteeHandEntered] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Creator] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EventMessageText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IssueResolved] [bit] NOT NULL ,
[ActionRequired] [bit] NOT NULL ,
[EventHappenedDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[JournalRoutingClasses] (
[JRCID] [int] IDENTITY (1, 1) NOT NULL ,
[JRCName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Creator] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsPublic] [bit] NOT NULL ,
[Active] [bit] NOT NULL ,
[SystemClass] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Accounts] (
[AccountID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountName] [char] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AccountIndustry] [int] NULL ,
[AccountSubIndustry] [int] NULL ,
[RGCompanyID] [int] NULL ,
[BrokerOfRecordsID] [int] NULL ,
[AccountOwner] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Inactive] [bit] NOT NULL ,
[SICCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountDirector] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountBA] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountCSA] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountWeBill] [bit] NOT NULL ,
[AccountBillingAssociate] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AccountExecutive] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountType] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[People] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleInitial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Suffix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prefix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOB] [datetime] NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JobTitle] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InformalName] AS (rtrim([firstname]) + ' ' + rtrim([lastname]))
) ON [PRIMARY]
GOYes, the query is hidious :-)
A few remarks:
1) you should consider using Dynamic SQL instead of all the optional
parameter handling. The current query will not be able to use any index
on the columns mentioned in the WHERE clause
2) you should use inner joins instead of outer joins when you can. For
example, the query contains the subquery
select JournalEntryJunJournalRoutingClass.JournalEntryID
from JournalEntryJunJournalRoutingClass
left outer join JournalRoutingClasses
on JournalEntryJunJournalRoutingClass.JRCID =
JournalRoutingClasses.JRCID
left outer join JRCJunUsers
on JournalRoutingClasses.JRCID = JRCJunUsers.JRCID
where JRCJunUsers.loginname = @.loginname
The WHERE clause of this query will remove all rows of "left outer"
table JournalEntryJunJournalRoutingClass without related rows in
JournalRoutingClasses and JRCJunUsers. IOW, you can change the two "left
outer join"s to "inner join"s. This will give the optimizer more freedom
in its access path analysis.
3) The resultset "a" will contain just one row for each JournalEntryID.
This means, that you could rewrite the current statement from:
select a.EventMessageText,JETR.*
from (
select DISTINCT
People.InformalName
,Accounts.AccountName
..
,(
select count(*)
from JournalEntryJunStoredFiles
where JournalEntryID = journalentries.JournalEntryID
) as AttachmentCount
from JournalEntries
..
) JETR
left outer join (
SELECT JournalEntryID,EventMessageText
FROM JournalEvents je1
WHERE je1.EventDate = (
SELECT MIN(je2.EventDate)
FROM JournalEvents je2
WHERE je1.JournalEntryID = je2.JournalEntryID
)
) a on JETR.JournalEntryID = a.JournalEntryID
To:
select DISTINCT
People.InformalName
,Accounts.AccountName
..
,(
select count(*)
from JournalEntryJunStoredFiles
where JournalEntryID = journalentries.JournalEntryID
) as AttachmentCount
,(
SELECT EventMessageText
FROM JournalEvents je1
WHERE je1.JournalEntryID = journalentries.JournalEntryID
AND je1.EventDate = (
SELECT MIN(je2.EventDate)
FROM JournalEvents je2
WHERE je2.JournalEntryID = journalentries.JournalEntryID
)
) as EventMessageText
from JournalEntries
..
4) Remove the DISTINCT keyword if it is not necessary.
Hope this helps,
Gert-Jan
Brian Henry wrote:
> I have the following query...
> /****** Object: Stored Procedure dbo.BENESP_JournalEntrySearch Script
> Date: 4/23/2004 11:45:30 AM ******/
> CREATE PROCEDURE [DBO].[BSP_JournalEntrySearch]
> @.Type int = null,
> @.User varchar(50) = null,
> @.EntryState int = 2, -- 1 open, 0 closed, 2 either
> @.DateEnteredType int = 0, -- 0 both, 1 entrys only, 2 events only
> @.DateEnteredStart datetime = null,
> @.DateEnteredEnd datetime = null,
> @.DateDueStart datetime = null,
> @.DateDueEnd datetime = null,
> @.AccountFor int = null,
> @.messageText varchar(4000) = null,
> @.loginname varchar(50) = '', -- Required login name for user perfoming
> search
> @.IncludeRouted bit = null,
> @.enrollee int = null
> AS
> -- REMEMBER TO REMOVE TIME FROM DATES SO WE CAN CHECK THE DATE ONLY
> -- DBO.RemoveTimeFromDate function removes time from datetime's
> SET NOCOUNT ON
> -- get message list and join it to its initial message from the events tab
le
> based on most recent message
> select a.EventMessageText,JETR.* from
> (select DISTINCT
> People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntry
SubTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
> (select count(*) from JournalEntryJunStoredFiles where JournalEntryID =
> journalentries.JournalEntryID) as AttachmentCount
> from JournalEntries left outer join JournalEvents on
> JournalEntries.JournalEntryID = JournalEvents.JournalEntryID
> left outer join JournalEntrySubTypes on JournalEntries.JETSubTypeID =
> JournalEntrySubTypes.JETSubTypeID
> left outer join JournalEntryTypes on JournalEntrySubTypes.JETypeID =
> JournalEntryTypes.JETypeID
> left outer join JournalEntryTypePermissions on
> JournalEntryTypes.JETypeID = JournalEntryTypePermissions.JETypeID
> left outer join Accounts on JournalEntries.RelatedAccountID =
> Accounts.AccountID
> left outer join People on JournalEntries.Enrollee = People.PersonID
> where 1=1 and (journalentries.creator = @.loginname or
> JournalEntries.JournalEntryID in (select
> JournalEntryJunJournalRoutingClass.JournalEntryID
> from (JournalEntryJunJournalRoutingClass left outer join
> JournalRoutingClasses on JournalEntryJunJournalRoutingClass.JRCID =
> JournalRoutingClasses.JRCID)
> left outer join JRCJunUsers on JournalRoutingClasses.JRC
ID
> = JRCJunUsers.JRCID
> where JRCJunUsers.loginname = @.loginname))
> -- search criteria
> AND (@.messageText IS NULL OR FREETEXT(JournalEvents.*,@.messagetext)) -
-
> TEXT SEARCH
> AND (@.enrollee IS NULL OR JournalEntries.Enrollee = @.enrollee)
> AND (@.IncludeRouted IS NULL OR 1=1) -- is routed to user or their
> own messages
> AND (@.Type IS NULL OR JournalEntrySubTypes.JETSubTypeID = @.Type)
> AND (@.AccountFor IS NULL OR JournalEntries.RelatedAccountID = @.AccountFo
r)
> AND ((@.DateDueStart IS NULL AND @.DateDueEnd IS NULL) OR
> DBO.RemoveTimeFromDate(JournalEntries.DueDate) between
> DBO.RemoveTimeFromDate(@.DateDueStart) AND
> DBO.RemoveTimeFromDate(@.DateDueEnd))
> AND (@.User IS NULL OR (JournalEntries.Creator = @.user or
> JournalEvents.Creator = @.user))
> AND ((@.entrystate = 0 and JournalEntries.EntryClosed = 1)
> OR (@.entrystate = 1 and JournalEntries.EntryClosed = 0)
> OR (@.entrystate = 2 and (JournalEntries.EntryClosed = 0 or
> JournalEntries.EntryClosed = 1)))
> -- date range entry check stuff
> AND (@.DateEnteredStart IS NULL OR ((@.DateEnteredType = 0 OR
> @.DateEnteredType = 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate)
> DBO.RemoveTimeFromDate(JournalEvents.EventDate) >=
> DBO.RemoveTimeFromDate(@.DateEnteredStart)))
> AND (@.DateEnteredEnd IS NULL OR ((@.DateEnteredType = 0 OR @.DateEnteredTy
pe
> = 1) AND DBO.RemoveTimeFromDate(JournalEntries.EntryDate) <=
> @.DateEnteredEnd) OR ((@.DateEnteredType = 0 OR @.DateEnteredType = 2) AND
> DBO.RemoveTimeFromDate(JournalEvents.EventDate) <=
> DBO.RemoveTimeFromDate(@.DateEnteredEnd)))
> AND permissionid in (select permissionid from
> bene_users.dbo.UsersPermissions where loginname = @.loginname)) JETR
> -- gets initial message for the entry after we know what messages we have
> searched for
> left outer join (SELECT JournalEntryID,EventMessageText FROM JournalEvents
> je1 WHERE je1.EventDate = (SELECT MIN(je2.EventDate ) FROM JournalEvents
> je2 WHERE je1.JournalEntryID = je2.JournalEntryID)) a on JETR.JournalEntry
ID
> = a.JournalEntryID
> GO
> which takes about 14 seconds to run when I have the last left outer join o
n
> it to get the initial text from the JouranlEvents table... without it it
> takes under 1 second to run on a table of thousands of entries in the
> journalentries table...
> here is my trace on it
> SET STATISTICS PROFILE ON
> SQL:StmtCompleted 0 0 0 0 SET NOCOUNT ON -- get message list and join
> it to its initial message from the events table based on most recent messa
ge
> SP:StmtCompleted 0 0 0 0 select a.EventMessageText,JETR.* from (select
> DISTINCT
> People.InformalName,Accounts.AccountName,JournalEntryTypes.TypeName,JournalEntry
SubTypes.[Name],JournalEntryTypes.AssociatedFormIDCode,journalentries.*,
> (select count(*) f
> SP:StmtCompleted 153 34 91 0 exec BENESP_JournalEntrySearch
> @.loginname='brian_henry'
> SQL:StmtCompleted 169 50 97 0 SET STATISTICS PROFILE OFF
> SQL:StmtCompleted 0 0 0 0
> statistics on it
> Application Profile Statistics
> Timer resolution (milliseconds)
> 0 0 Number of INSERT, UPDATE, DELETE statements
> 0 0 Rows effected by INSERT, UPDATE, DELETE statements
> 0 0 Number of SELECT statements
> 2 2 Rows effected by SELECT statements
> 100 100 Number of user transactions
> 5 5 Average fetch time
> 0 0 Cumulative fetch time
> 0 0 Number of fetches
> 0 0 Number of open statement handles
> 0 0 Max number of opened statement handles
> 0 0 Cumulative number of statement handles
> 0 0
> Network Statistics
> Number of server roundtrips
> 3 3 Number of TDS packets sent
> 3 3 Number of TDS packets received
> 209 209 Number of bytes sent
> 252 252 Number of bytes received
> 832650 832650
> Time Statistics
> Cumulative client processing time
> 0 0 Cumulative wait time on server replies
> 2.75185e+007 2.75185e+007
> any idea how to speed up that last join?! i need that message appended
> server side because it takes even more time client side to do it
> programmatically in the application
> thanks for any help!
> here is the basic DDL for this too... i didnt include relations or keys in
> it though...
[snip]|||for security reasons dynamic SQL is not an option, but thanks for the other
ideas, no one gets select persmission on any tables, just stored procedure
execute permissions
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:435D5DE8.704A3C31@.toomuchspamalready.nl...
> Yes, the query is hidious :-)
> A few remarks:
> 1) you should consider using Dynamic SQL instead of all the optional
> parameter handling. The current query will not be able to use any index
> on the columns mentioned in the WHERE clause
> 2) you should use inner joins instead of outer joins when you can. For
> example, the query contains the subquery
> select JournalEntryJunJournalRoutingClass.JournalEntryID
> from JournalEntryJunJournalRoutingClass
> left outer join JournalRoutingClasses
> on JournalEntryJunJournalRoutingClass.JRCID =
> JournalRoutingClasses.JRCID
> left outer join JRCJunUsers
> on JournalRoutingClasses.JRCID = JRCJunUsers.JRCID
> where JRCJunUsers.loginname = @.loginname
> The WHERE clause of this query will remove all rows of "left outer"
> table JournalEntryJunJournalRoutingClass without related rows in
> JournalRoutingClasses and JRCJunUsers. IOW, you can change the two "left
> outer join"s to "inner join"s. This will give the optimizer more freedom
> in its access path analysis.
> 3) The resultset "a" will contain just one row for each JournalEntryID.
> This means, that you could rewrite the current statement from:
> select a.EventMessageText,JETR.*
> from (
> select DISTINCT
> People.InformalName
> ,Accounts.AccountName
> ...
> ,(
> select count(*)
> from JournalEntryJunStoredFiles
> where JournalEntryID = journalentries.JournalEntryID
> ) as AttachmentCount
> from JournalEntries
> ...
> ) JETR
> left outer join (
> SELECT JournalEntryID,EventMessageText
> FROM JournalEvents je1
> WHERE je1.EventDate = (
> SELECT MIN(je2.EventDate)
> FROM JournalEvents je2
> WHERE je1.JournalEntryID = je2.JournalEntryID
> )
> ) a on JETR.JournalEntryID = a.JournalEntryID
> To:
> select DISTINCT
> People.InformalName
> ,Accounts.AccountName
> ...
> ,(
> select count(*)
> from JournalEntryJunStoredFiles
> where JournalEntryID = journalentries.JournalEntryID
> ) as AttachmentCount
> ,(
> SELECT EventMessageText
> FROM JournalEvents je1
> WHERE je1.JournalEntryID = journalentries.JournalEntryID
> AND je1.EventDate = (
> SELECT MIN(je2.EventDate)
> FROM JournalEvents je2
> WHERE je2.JournalEntryID = journalentries.JournalEntryID
> )
> ) as EventMessageText
> from JournalEntries
> ...
> 4) Remove the DISTINCT keyword if it is not necessary.
> Hope this helps,
> Gert-Jan
> Brian Henry wrote:
> [snip]|||I respect that security policy. Please note that the stored procedure
could be the one that is generating and executing the dynamic SQL. Maybe
that is within the limits of the security policy.
Good luck,
Gert-Jan
Brian Henry wrote:
> for security reasons dynamic SQL is not an option, but thanks for the othe
r
> ideas, no one gets select persmission on any tables, just stored procedure
> execute permissions
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:435D5DE8.704A3C31@.toomuchspamalready.nl...|||thanks! forgot to check the joins... must of been in a daze... been sick for
a few ws now... trying to code while sick with mono... which is resulting
in some very hidious SQL... just the last join alone being an outer join
which should of been an inner join reduced it from 14 seconds of execution
time (it created over 5 million rows server side and filtered it to 500)
with the outer join... to the inner join which now takes under 1 second to
execute and only created 600 rows max for the 500 it displays server side
(baseing on the trace of the execution plan and how many rows are being
moved where and such) thanks for the help
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:435E6ED8.89722775@.toomuchspamalready.nl...
>I respect that security policy. Please note that the stored procedure
> could be the one that is generating and executing the dynamic SQL. Maybe
> that is within the limits of the security policy.
> Good luck,
> Gert-Jan
>
> Brian Henry wrote:sql

Optimizer in Stored Procedure

Hello
We have a question about the optimizer in Stored Procedure.
The Problem ist as follow
We have 3 tabels, every tabel has columns
key1 ((rtrim(([number] + right(('0000' + convert(varchar
(4),[position])),4) + right(('00' + convert(varchar(2),
[type])),2)))))
number char(8)
position num(4)
type num(2)
every tabel has a index on the column key1
We take the following statement in der sql query analyzer
set rowcount 1
select key1 from tabel1 where key1 < '12345678123412'
union all
select key1 from tabel2 where key1 < '12345678123412'
union all
select key1 from tabel3 where key1 < '12345678123412'
order by key1 desc
The executen plan gives the following information
a index seek over index from key1 ordered backward for
tabel1, tabel2 and tabel3 ist executed (exectly 1 lines is
selected for every tabel)
with merg join concatenation are the tabels merged.
now we take an output with max 3 lines very fast
this is exactil what we want.
When we put this statement in a Stored procedure and
execute this stored procedure, the execution is very long
when there are many lines in the tabels
The executen plan gives for the executen from the stored
procedure the following information
a index scann over index form key1 for tabel1, tabel2 and
tabel3 ist executed (most of the lines of the tabels will
be selected)
a compute scalar / filter / sort ist peformed vor every
scann
with merg join concatenation are the tabels merged.
now we take an output with max 3 lines very slow when
there are many lines in the tables
Wy is there a difference betwen this two execution planes
can anybody help us
thanks very much
PeterPeter,
this question can only be answered based on the actual queries. Usually,
the queries you run in Query Analyser or not identical to the queries in
a stored procedure (because of local variables, etc.).
If you are using SQL Server 7.0 or later, you could drop the "set
rowcount" and use the TOP keyword.
select TOP 1 key1 from (
select key1 from tabel1 where key1 < '12345678123412'
union all
select key1 from tabel2 where key1 < '12345678123412'
union all
select key1 from tabel3 where key1 < '12345678123412'
) as T
order by key1 desc
Hope this helps,
Gert-Jan
Peter Wyss wrote:
> Hello
> We have a question about the optimizer in Stored Procedure.
> The Problem ist as follow
> We have 3 tabels, every tabel has columns
> key1 ((rtrim(([number] + right(('0000' + convert(varchar
> (4),[position])),4) + right(('00' + convert(varchar(2),
> [type])),2)))))
> number char(8)
> position num(4)
> type num(2)
> every tabel has a index on the column key1
> We take the following statement in der sql query analyzer
> set rowcount 1
> select key1 from tabel1 where key1 < '12345678123412'
> union all
> select key1 from tabel2 where key1 < '12345678123412'
> union all
> select key1 from tabel3 where key1 < '12345678123412'
> order by key1 desc
> The executen plan gives the following information
> a index seek over index from key1 ordered backward for
> tabel1, tabel2 and tabel3 ist executed (exectly 1 lines is
> selected for every tabel)
> with merg join concatenation are the tabels merged.
> now we take an output with max 3 lines very fast
> this is exactil what we want.
> When we put this statement in a Stored procedure and
> execute this stored procedure, the execution is very long
> when there are many lines in the tabels
> The executen plan gives for the executen from the stored
> procedure the following information
> a index scann over index form key1 for tabel1, tabel2 and
> tabel3 ist executed (most of the lines of the tabels will
> be selected)
> a compute scalar / filter / sort ist peformed vor every
> scann
> with merg join concatenation are the tabels merged.
> now we take an output with max 3 lines very slow when
> there are many lines in the tables
> Wy is there a difference betwen this two execution planes
> can anybody help us
> thanks very much
> Petersql

Optimizer goes bad but then recovers

I have a 10 gig database that is pretty consistent on performance.
But every few days some of the main stored procedures get a bad
execution plan which kills performance. It has happened while we have
had many users hitting the database and also when only a single person
is running.
The interesting part is that SQL Server has always corrected itself
after about 2 hours. We have not been able to repeat this in the test
lab. It only happens in production - lucky me.
Sometimes just doing a sp_updatestats will cure it for a while. One
time we changed a stored procedure to set ARITHABORT ON and the system
recovered immediately.
Any clue as to what might cause the optimizer to go a miss and
likewise why does it always recover in 2 hours?
Thanks
Hardware / Software being used
ASP.NET C# application using SqlClient
SQL Server 2000 Standard Edition, SP3a
Full rebuild of indexes is performed nightly
Possible that a SQL Server is using a cached execution plan that was
optimimal for the previous user but not for the other users? Don't know
about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next time.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly
|||I would investigate whether it has to do with statistics. Perhaps auto-update of statistics kicks in
,does a not so good job, then kicks in after two hours and then does a good job again. Perhaps you
can see a trend whether this is happening in conjunction with some mass load, removal or update?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly
|||And you can do the checking by running profiler... Trace auto update
statistics, and SP plan reuse..
If you have a particular sp in mind... TEST it using extreme values for
parameters, looking at the execute plan to see if any plan changes between
index seek, and index scan...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly
|||I agree about your assumption re: the 'not optimal for previous user', but I
don't think I woudl recommend dbcc freeproccache on a busy production
server.
You could simple sp_recompile that one procedure in question...
If the procedure is not exectued all that often and the cost of a recompile
is low compared to the cost of a 'bad plan' you might simply create the proc
using the with recompile.
Or even better... yuo might research what set of paramaters require widely
divergent exection plans and then create two new procs. Have the current
proc call each of the child procs based on it's knowledge of which proc will
have the better plan.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23TIpE1ztEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Possible that a SQL Server is using a cached execution plan that was
> optimimal for the previous user but not for the other users? Don't know
> about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next
time.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
> news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
>

Optimizer goes bad but then recovers

I have a 10 gig database that is pretty consistent on performance.
But every few days some of the main stored procedures get a bad
execution plan which kills performance. It has happened while we have
had many users hitting the database and also when only a single person
is running.
The interesting part is that SQL Server has always corrected itself
after about 2 hours. We have not been able to repeat this in the test
lab. It only happens in production - lucky me.
Sometimes just doing a sp_updatestats will cure it for a while. One
time we changed a stored procedure to set ARITHABORT ON and the system
recovered immediately.
Any clue as to what might cause the optimizer to go a miss and
likewise why does it always recover in 2 hours?
Thanks
Hardware / Software being used
ASP.NET C# application using SqlClient
SQL Server 2000 Standard Edition, SP3a
Full rebuild of indexes is performed nightlyPossible that a SQL Server is using a cached execution plan that was
optimimal for the previous user but not for the other users? Don't know
about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next time.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||I would investigate whether it has to do with statistics. Perhaps auto-updat
e of statistics kicks in
,does a not so good job, then kicks in after two hours and then does a good
job again. Perhaps you
can see a trend whether this is happening in conjunction with some mass load
, removal or update?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||And you can do the checking by running profiler... Trace auto update
statistics, and SP plan reuse..
If you have a particular sp in mind... TEST it using extreme values for
parameters, looking at the execute plan to see if any plan changes between
index seek, and index scan...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||I agree about your assumption re: the 'not optimal for previous user', but I
don't think I woudl recommend dbcc freeproccache on a busy production
server.
You could simple sp_recompile that one procedure in question...
If the procedure is not exectued all that often and the cost of a recompile
is low compared to the cost of a 'bad plan' you might simply create the proc
using the with recompile.
Or even better... yuo might research what set of paramaters require widely
divergent exection plans and then create two new procs. Have the current
proc call each of the child procs based on it's knowledge of which proc will
have the better plan.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23TIpE1ztEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Possible that a SQL Server is using a cached execution plan that was
> optimimal for the previous user but not for the other users? Don't know
> about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next
time.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
> news:ec64e251.0410201939.5b4324a2@.posting.google.com...
>

Optimizer goes bad but then recovers

I have a 10 gig database that is pretty consistent on performance.
But every few days some of the main stored procedures get a bad
execution plan which kills performance. It has happened while we have
had many users hitting the database and also when only a single person
is running.
The interesting part is that SQL Server has always corrected itself
after about 2 hours. We have not been able to repeat this in the test
lab. It only happens in production - lucky me.
Sometimes just doing a sp_updatestats will cure it for a while. One
time we changed a stored procedure to set ARITHABORT ON and the system
recovered immediately.
Any clue as to what might cause the optimizer to go a miss and
likewise why does it always recover in 2 hours?
Thanks
Hardware / Software being used
ASP.NET C# application using SqlClient
SQL Server 2000 Standard Edition, SP3a
Full rebuild of indexes is performed nightlyPossible that a SQL Server is using a cached execution plan that was
optimimal for the previous user but not for the other users? Don't know
about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next time.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||I would investigate whether it has to do with statistics. Perhaps auto-update of statistics kicks in
,does a not so good job, then kicks in after two hours and then does a good job again. Perhaps you
can see a trend whether this is happening in conjunction with some mass load, removal or update?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||And you can do the checking by running profiler... Trace auto update
statistics, and SP plan reuse..
If you have a particular sp in mind... TEST it using extreme values for
parameters, looking at the execute plan to see if any plan changes between
index seek, and index scan...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance. It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours. We have not been able to repeat this in the test
> lab. It only happens in production - lucky me.
> Sometimes just doing a sp_updatestats will cure it for a while. One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
> Thanks
> Hardware / Software being used
> ASP.NET C# application using SqlClient
> SQL Server 2000 Standard Edition, SP3a
> Full rebuild of indexes is performed nightly|||I agree about your assumption re: the 'not optimal for previous user', but I
don't think I woudl recommend dbcc freeproccache on a busy production
server.
You could simple sp_recompile that one procedure in question...
If the procedure is not exectued all that often and the cost of a recompile
is low compared to the cost of a 'bad plan' you might simply create the proc
using the with recompile.
Or even better... yuo might research what set of paramaters require widely
divergent exection plans and then create two new procs. Have the current
proc call each of the child procs based on it's knowledge of which proc will
have the better plan.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23TIpE1ztEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Possible that a SQL Server is using a cached execution plan that was
> optimimal for the previous user but not for the other users? Don't know
> about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next
time.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
> news:ec64e251.0410201939.5b4324a2@.posting.google.com...
> > I have a 10 gig database that is pretty consistent on performance.
> > But every few days some of the main stored procedures get a bad
> > execution plan which kills performance. It has happened while we have
> > had many users hitting the database and also when only a single person
> > is running.
> >
> > The interesting part is that SQL Server has always corrected itself
> > after about 2 hours. We have not been able to repeat this in the test
> > lab. It only happens in production - lucky me.
> >
> > Sometimes just doing a sp_updatestats will cure it for a while. One
> > time we changed a stored procedure to set ARITHABORT ON and the system
> > recovered immediately.
> >
> > Any clue as to what might cause the optimizer to go a miss and
> > likewise why does it always recover in 2 hours?
> >
> > Thanks
> >
> > Hardware / Software being used
> > ASP.NET C# application using SqlClient
> > SQL Server 2000 Standard Edition, SP3a
> > Full rebuild of indexes is performed nightly
>sql

Optimizer chooses different plans

We are using Sql 2000 sp3.
All of our database access is coded in stored procedures.
When we execute a stored procedure it will do table scans, wether it is called from COM+ using ADO or executed in Query Analyzer.
If we cut and paste the code from the stored proc directly to Query Analyzer and execute it, it uses the indexes(Index Seeks).
We have added index hints and the stored proc will work for a while, but then starts doing table scans again.
An example:
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr (index=pk_mileage_rate)
LEFT JOIN EMPLOYEE e (index=uq_employee) ON
mr.union_id = e.union_id AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number AND
(mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_date)
We pass @.payroll_number and @.work_date, then return @.milage_rate.
The pk_mileage_rate index is on the columns: union_id,local_union,effective_start_date.
The uq_employee index is on the column payroll_number.
After we added the hints it worked for a while but now does table scans.
If I cut and paste this code into Query Analyzer AND remove the index hints it does Index Seeks on both tables using the indexes.
If I execute it as a stored proc it does table scans most of the time BUT index seeks sometimes.
What can we do to make our stored procs use the indexes that are there?
What can we do to make the Optimizer be consistent?
"Don" <Don@.discussions.microsoft.com> wrote in message
news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is
called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query
Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but
then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >=
@.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns:
union_id,local_union,effective_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index
hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
>
When you paste it into QA, are you removing the variables and hard-coding
the values?
Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
restriction on it? That makes no sense, and it could screw up the plan.
Try this, instead
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr
INNER JOIN EMPLOYEE e
ON mr.union_id = e.union_id
AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number
AND mr.effective_start_date <= @.work_date
AND mr.effective_end_date >= @.work_date
David
|||We are Declaring the variables and using a SELECT to set the value when we cut and paste the code to QA.
I think the code used the LEFT JOIN because there was a concern that an Employees Union may not have been entered correctly which comes from our mainframe.
All our Employee data is from the mainframe, but the mileage_rate table has no mainframe dependency.
Anyway, still doesn't answer why the code ALWAYS uses the indexes when cut and pasted to QA, but not so when the stored proc is executed.
Don
"David Browne" wrote:

> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> called from COM+ using ADO or executed in Query Analyzer.
> Analyzer and execute it, it uses the indexes(Index Seeks).
> then starts doing table scans again.
> @.work_date)
> union_id,local_union,effective_start_date.
> hints it does Index Seeks on both tables using the indexes.
> index seeks sometimes.
> When you paste it into QA, are you removing the variables and hard-coding
> the values?
> Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
> restriction on it? That makes no sense, and it could screw up the plan.
> Try this, instead
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr
> INNER JOIN EMPLOYEE e
> ON mr.union_id = e.union_id
> AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number
> AND mr.effective_start_date <= @.work_date
> AND mr.effective_end_date >= @.work_date
> David
>
>
|||Don,
Search Google for "parameter sniffing", because this is probably the
cause of your problem. It can be circumvented by not using parameters in
the query, but local variables.
For example
CREATE PROCEDURE MyProc (@.param int) AS
SELECT * FROM MyTable WHERE MyColumn = @.Param
would then become
CREATE PROCEDURE MyProc (@.param int) AS
Declare @.local int
Set @.local=@.param
SELECT * FROM MyTable WHERE MyColumn = @.local
Hope this helps,
Gert-Jan
Don wrote:
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns: union_id,local_union,effective_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
(Please reply only to the newsgroup)
sql

Optimizer chooses different plans

We are using Sql 2000 sp3.
All of our database access is coded in stored procedures.
When we execute a stored procedure it will do table scans, wether it is call
ed from COM+ using ADO or executed in Query Analyzer.
If we cut and paste the code from the stored proc directly to Query Analyzer
and execute it, it uses the indexes(Index Seeks).
We have added index hints and the stored proc will work for a while, but the
n starts doing table scans again.
An example:
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr (index=pk_mileage_rate)
LEFT JOIN EMPLOYEE e (index=uq_employee) ON
mr.union_id = e.union_id AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number AND
(mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_da
te)
We pass @.payroll_number and @.work_date, then return @.milage_rate.
The pk_mileage_rate index is on the columns: union_id,local_union,effective_
start_date.
The uq_employee index is on the column payroll_number.
After we added the hints it worked for a while but now does table scans.
If I cut and paste this code into Query Analyzer AND remove the index hints
it does Index Seeks on both tables using the indexes.
If I execute it as a stored proc it does table scans most of the time BUT in
dex seeks sometimes.
What can we do to make our stored procs use the indexes that are there?
What can we do to make the Optimizer be consistent?"Don" <Don@.discussions.microsoft.com> wrote in message
news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is
called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query
Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but
then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >=
@.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns:
union_id,local_union,effective_start_dat
e.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index
hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
>
When you paste it into QA, are you removing the variables and hard-coding
the values?
Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
restriction on it? That makes no sense, and it could screw up the plan.
Try this, instead
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr
INNER JOIN EMPLOYEE e
ON mr.union_id = e.union_id
AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number
AND mr.effective_start_date <= @.work_date
AND mr.effective_end_date >= @.work_date
David|||We are Declaring the variables and using a SELECT to set the value when we c
ut and paste the code to QA.
I think the code used the LEFT JOIN because there was a concern that an Empl
oyees Union may not have been entered correctly which comes from our mainfra
me.
All our Employee data is from the mainframe, but the mileage_rate table has
no mainframe dependency.
Anyway, still doesn't answer why the code ALWAYS uses the indexes when cut a
nd pasted to QA, but not so when the stored proc is executed.
Don
"David Browne" wrote:

> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> called from COM+ using ADO or executed in Query Analyzer.
> Analyzer and execute it, it uses the indexes(Index Seeks).
> then starts doing table scans again.
> @.work_date)
> union_id,local_union,effective_start_dat
e.
> hints it does Index Seeks on both tables using the indexes.
> index seeks sometimes.
> When you paste it into QA, are you removing the variables and hard-coding
> the values?
> Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
> restriction on it? That makes no sense, and it could screw up the plan.
> Try this, instead
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr
> INNER JOIN EMPLOYEE e
> ON mr.union_id = e.union_id
> AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number
> AND mr.effective_start_date <= @.work_date
> AND mr.effective_end_date >= @.work_date
> David
>
>|||Don,
Search Google for "parameter sniffing", because this is probably the
cause of your problem. It can be circumvented by not using parameters in
the query, but local variables.
For example
CREATE PROCEDURE MyProc (@.param int) AS
SELECT * FROM MyTable WHERE MyColumn = @.Param
would then become
CREATE PROCEDURE MyProc (@.param int) AS
Declare @.local int
Set @.local=@.param
SELECT * FROM MyTable WHERE MyColumn = @.local
Hope this helps,
Gert-Jan
Don wrote:
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is ca
lled from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query Analyz
er and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but t
hen starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >
= @.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns: union_id,local_union,effectiv
e_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index hint
s it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
(Please reply only to the newsgroup)

optimize the stored procedure

CREATE PROCEDURE emp_summary

@.emp_id int ,@.start_date datetime=0,@.end_date datetime=0

AS

SET NOCOUNT ON

IF @.start_date=0 AND @.end_date=0

BEGIN

SET @.end_date=getdate()

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date

END

ELSE

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date+1
GO

This is the Stored procedure i wrote to get the emp summary with date range and with no date ranges.If i pass start_date and end_Date Sp executes 'else' part if dont pass the parameters it execultes 'IF' part.Can i optimize this SP further?I'd use:CREATE PROCEDURE
@.emp_id INT
, @.start_date DATETIME = NULL
, @.end_date DATETIME = NULL
AS

SELECT *
FROM emp
WHERE emp_id = @.emp_id
AND join_date BETWEEN Coalesce(@.start_date, join_date) AND Coalesce(@.end_date, join_date)

RETURN
GOIf you can't afford the table scan, I'd resort to using dynamic SQL, but I'd try this first.

-PatP

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegr oups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>
|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas

"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegr oups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize Temp tables

Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
With that much code running just to generate a report, you might consider
having a background process (or a data warehouse) pre-aggregate the data.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In addition to Aaron's response , create a temp tables at the beginning of
the stored procedure (reduce recompile).
Also, it may help to improve performance if you add NC/CI indexes depending
on your requrements.
Look at an execution plan of the stored procedure to ensure that optimizer
is available to use indexes.
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
> Hi All,
> My application makes heavy use of temp tables because we have a few
> thousands of stored proc running every day to renerate reports.
> What are the positive and negative impact to add clustered index on the
> temp table?
> what is the negative impact if I use derived table instead temp table?
> When I have big stored proc more than 1500 lines and have at least 5
> temp tables, what is the best solution to make the heavy stored proc
> run faster?
> Thanks so much,
> Silaphet,
>|||Another possibility would be to consider an INDEXED VIEW.
Typically, you want to keep your OLTP and DSS/OLAP operations and data
seperated. It might be wise to reconsider the cohosting of this data in the
same database and reconsider creating something new elsewhere that better
fits your architecture.
Sincerely,
Anthony Thomas
"Silaphet" <kmounkhaty@.cox.net> wrote in message
news:1112583294.022932.260500@.z14g2000cwz.googlegroups.com...
Hi All,
My application makes heavy use of temp tables because we have a few
thousands of stored proc running every day to renerate reports.
What are the positive and negative impact to add clustered index on the
temp table?
what is the negative impact if I use derived table instead temp table?
When I have big stored proc more than 1500 lines and have at least 5
temp tables, what is the best solution to make the heavy stored proc
run faster?
Thanks so much,
Silaphet,

Optimize procedure

hi

below is stored procedure takes 5 to 10 minuute to execute..

i want to make this fast...below is code......

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

exec USp_Reconciliation 'RECONCILIATION'

ALTER PROCEDURE USp_Reconciliation
(
@.CommandType varchar(50)=null,
@.fileFormatId numeric =null,
@.FDate varchar(10)=null,
@.TDate varchar(10)=null
)

AS

BEGIN

IF @.CommandType='RECONCILIATION'
BEGIN

DECLARE @.DataFormat_Name VARCHAR(50)
DECLARE @.DataFormat_ID NUMERIC
DECLARE @.Folio_No VARCHAR(50)
DECLARE @.Scheme_Code VARCHAR(50)
DECLARE @.SchemeGroup VARCHAR(50)
DECLARE @.Amount NUMERIC
DECLARE @.Cheque_No VARCHAR(50)
DECLARE @.Compare_Status VARCHAR(50)
DECLARE @.Tradedate DATETIME
DECLARE @.Sip_Status CHAR(1)
DECLARE @.DrCr CHAR(1)
DECLARE @.Format_ID NUMERIC

Blocks for Mutiple equal countExact 'Mutiple Records' updated to 'Success' //for Instrm_no and Cheque_no

select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
into #MultipleChequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Multiple Records'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)
--Select * from #MultipleChequeno where format_id=82

--//For Folio_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name
into #Multiplefoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Multiple Records'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)

//For Channels User_Trxn_no for Multiple Records
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
into #Multiple_user_trxnno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Multiple Records'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)

-- Update CAMS files-
--

For IBank CMS
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultipleChequeno.cams_upload_Details_id from #MultipleChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultipleChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultipleChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultipleChequeno.additionalfield6 and
#MultipleChequeno.format_id in ('82'))

--For HDFC CMS
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultipleChequeno.cams_upload_Details_id from #MultipleChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultipleChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultipleChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultipleChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultipleChequeno.additionalfield13 and
#MultipleChequeno.format_id in ('83'))

--IBANK SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('85','86'))

--BJ SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='EC' and
#Multiplefoliono.format_id in ('88','89'))

--HDFC SI-
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiple_user_trxnno.cams_upload_Details_id from #Multiple_user_trxnno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#Multiple_user_trxnno.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiple_user_trxnno.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiple_user_trxnno.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Multiple_user_trxnno.scheme_code and
#Multiple_user_trxnno.format_id in ('98'))

--RTGS--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Multiplefoliono.cams_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Multiplefoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Multiplefoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.format_id in ('99'))

-- Update CMS files-
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplechequeno.cms_upload_Details_id from #Multiplechequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplechequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Multiplechequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield6=#Multiplechequeno.additionalfield6 and
#Multiplechequeno.format_id in ('82'))

-HDFC-
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplechequeno.cms_upload_Details_id from #Multiplechequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Multiplechequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplechequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Multiplechequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield13=#Multiplechequeno.additionalfield13 and
#Multiplechequeno.format_id in ('83'))

IBANK SI-
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('85','86'))

-BJ SI

update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='EC' and
#Multiplefoliono.format_id in ('88','89'))

HDFC SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiplefoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.Payment_Mechanism='M' and
#Multiplefoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiple_user_trxnno.cms_upload_Details_id from #Multiple_user_trxnno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#Multiple_user_trxnno.user_trxnno
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiple_user_trxnno.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiple_user_trxnno.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Multiple_user_trxnno.scheme_code and
#Multiple_user_trxnno.format_id in ('98'))

--RTGS--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Multiplefoliono.cms_upload_Details_id from #Multiplefoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Multiplefoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Multiplefoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Multiplefoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Multiplefoliono.Folio_no and
#Multiplefoliono.format_id in ('99'))

drop table #MultipleChequeno
drop table #Multiplefoliono
drop table #Multiple_user_trxnno

Exact 'Pending' to pending updated to 'Success'Blocks for Pending equal count
--//for Instrm_no and Cheque_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
into #PendingChequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Pending' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no) and count(cams.instrm_no)>1

--//For Folio_no Exact 'Pending' updated to 'Success
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name
into #Pendingfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Pending' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no) and count(cams.Folio_no)>1

//For Channels User_Trxn_no for 'Pending'-- Exact 'Pending' updated to 'Success
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
into #Pending_user_trxnno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Pending' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2) and count(cams.user_trxnno)>1

-- Update CAMS files-
--
--IBANK--

update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #PendingChequeno.cams_upload_Details_id from #PendingChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#PendingChequeno.additionalfield6 and
#PendingChequeno.format_id in ('82'))

--HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #PendingChequeno.cams_upload_Details_id from #PendingChequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#PendingChequeno.additionalfield13 and
#PendingChequeno.format_id in ('83'))

--IBANK SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('85','86'))

BJ SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='EC' and
#Pendingfoliono.format_id in ('88','89'))

HDFC SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('90','91'))

--Channel
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pending_user_trxnno.cams_upload_Details_id from #Pending_user_trxnno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#Pending_user_trxnno.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pending_user_trxnno.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pending_user_trxnno.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Pending_user_trxnno.scheme_code and
#Pending_user_trxnno.format_id in ('98'))

--RTGS-
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Pendingfoliono.cams_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Pendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Pendingfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.format_id in ('99'))

-- Update CMS files-pending to pending --
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #PendingChequeno.cms_upload_Details_id from #PendingChequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield6=#PendingChequeno.additionalfield6 and
#PendingChequeno.format_id in ('82'))

--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #PendingChequeno.cms_upload_Details_id from #PendingChequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#PendingChequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#PendingChequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#PendingChequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield13=#PendingChequeno.additionalfield13 and
#PendingChequeno.format_id in ('83'))

--IBANK SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('85','86'))

--BJ SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='EC' and
#Pendingfoliono.format_id in ('88','89'))

--HDFC SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.Payment_Mechanism='M' and
#Pendingfoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pending_user_trxnno.cms_upload_Details_id from #Pending_user_trxnno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#Pending_user_trxnno.user_trxnno
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pending_user_trxnno.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pending_user_trxnno.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Pending_user_trxnno.scheme_code and
#Pending_user_trxnno.format_id in ('98'))

--RTGS-
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Pendingfoliono.cms_upload_Details_id from #Pendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Pendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Pendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Pendingfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Pendingfoliono.Folio_no and
#Pendingfoliono.format_id in ('99'))

drop table #PendingChequeno
drop table #Pendingfoliono
drop table #Pending_user_trxnno

For Updating the Exact count 'Redundant' to Success'
--//for Instrm_no and Cheque_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13 into #Redundantchequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Redundant'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)

--//For Folio_no
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name into #Redundantfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Redundant'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)

//For Channels User_Trxn_no for 'Pending'
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno into #Redundant_user_trxnno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Redundant' and cms.Compare_Status='Redundant'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)

-- Update CAMS filesREDUNDANT to REDUNDANT-
--
--IBANK
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantchequeno.cams_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Redundantchequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_slip_No=#Redundantchequeno.additionalfield6 and
#Redundantchequeno.format_id in ('82'))
-- HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantchequeno.cams_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Redundantchequeno.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.Payin_slip_No=#Redundantchequeno.additionalfield13 and
#Redundantchequeno.format_id in ('83'))

--IBANK SI --
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='EC' and
#Redundantfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
pruamc.Tbl_CAMS_UploadDetails.name=#Redundantfoliono.additionalfield2 and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundant_user_trxnno.cams_upload_Details_id from #Redundant_user_trxnno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#Redundant_user_trxnno.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundant_user_trxnno.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundant_user_trxnno.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#Redundant_user_trxnno.scheme_code and
#Redundant_user_trxnno.format_id in ('98'))

--RTGS--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #Redundantfoliono.cams_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#Redundantfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.format_id in ('99'))

-- Update CMS filesREDUNDANT to REDUNDANT-
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantchequeno.cms_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Redundantchequeno.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.additionalfield6=#Redundantchequeno.additionalfield6 and
#Redundantchequeno.format_id in ('82'))

--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantchequeno.cms_upload_Details_id from #Redundantchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#Redundantchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Redundantchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield13=#Redundantchequeno.additionalfield13 and
#Redundantchequeno.format_id in ('83'))

--IBANK SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='EC' and
#Redundantfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundantfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.Payment_Mechanism='M' and
#Redundantfoliono.format_id in ('90','91'))

--CHANNEL--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundant_user_trxnno.cms_upload_Details_id from #Redundant_user_trxnno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#Redundant_user_trxnno.user_trxnno
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundant_user_trxnno.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundant_user_trxnno.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#Redundant_user_trxnno.scheme_code and
#Redundant_user_trxnno.format_id in ('98'))

--RTGS--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #Redundantfoliono.cms_upload_Details_id from #Redundantfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#Redundantfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#Redundantfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#Redundantfoliono.Folio_no and
#Redundantfoliono.format_id in ('99'))

drop table #Redundantchequeno
drop table #Redundantfoliono
drop table #Redundant_user_trxnno

-- Update CAMS filesREDUNDANT to PENDING-
--

-for Channels exact enter of 'Pending' in CMS and 'Redundant' in Purchase
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno into #RedundantchequenoChannel from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Redundant' and cms.Compare_Status='Pending' and cms.format_id='98'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)

--Select * from #RedundantchequenoChannel
--CHANNEL-- CAMS UPDATE

update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantchequenoChannel.cams_upload_Details_id from #RedundantchequenoChannel join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#RedundantchequenoChannel.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantchequenoChannel.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantchequenoChannel.scheme_code and
#RedundantchequenoChannel.format_id in ('98'))

--CHANNEL-- CMS UPDATE
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantchequenoChannel.cms_upload_Details_id from #RedundantchequenoChannel join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#RedundantchequenoChannel.user_trxnno
where --pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantchequenoChannel.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantchequenoChannel.scheme_code and
#RedundantchequenoChannel.format_id in ('98'))

For Updating the Exact count 'Redundant' in Purchase and 'Pending' in CMS to Success'
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13 into #RedundantPendingchequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)

--//For exact count of 'Pending' in CMS and 'Redundant' in Purchase for CMS Banks on folio_no basis
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name into #RedundantPendingfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Redundant' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)

--IBANK--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingchequeno.cams_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#RedundantPendingchequeno.additionalfield6 and
#RedundantPendingchequeno.format_id in ('82'))

--HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingchequeno.cams_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#RedundantPendingchequeno.additionalfield13 and
#RedundantPendingchequeno.format_id in ('83'))

--IBNAK SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='EC' and
#RedundantPendingfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
pruamc.Tbl_CAMS_UploadDetails.name=#RedundantPendingfoliono.additionalfield2 and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('90','91'))

--RTGS

update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #RedundantPendingfoliono.cams_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#RedundantPendingfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.format_id in ('99'))

-- Update CMS filesREDUNDANT to PENDING-
--
--IBANK
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingchequeno.cms_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield6=#RedundantPendingchequeno.additionalfield6 and
#RedundantPendingchequeno.format_id in ('82'))

--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingchequeno.cms_upload_Details_id from #RedundantPendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#RedundantPendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#RedundantPendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield13=#RedundantPendingchequeno.additionalfield13 and
#RedundantPendingchequeno.format_id in ('83'))

--IBNAK SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('85','86'))

--BJ SI
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='EC' and
#RedundantPendingfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#RedundantPendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.Payment_Mechanism='M' and
#RedundantPendingfoliono.format_id in ('90','91'))

--RTGS --
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #RedundantPendingfoliono.cms_upload_Details_id from #RedundantPendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#RedundantPendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#RedundantPendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#RedundantPendingfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#RedundantPendingfoliono.Folio_no and
#RedundantPendingfoliono.format_id in ('99'))

drop table #RedundantPendingchequeno
drop table #RedundantPendingfoliono
drop table #RedundantchequenoChannel

-- Update FOR CHANNEL filesMULTIPLE to PENDING-
--

-for Channels exact enter of 'Pending' in CMS and 'Multiple Records' in Purchase
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno into #MultiplechequenoChannel from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.user_trxnno = cms.additionalfield2
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Pending' and cms.format_id='98'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name,cams.user_trxnno
having count(cams.user_trxnno) = count(cms.additionalfield2)

-- CHANNEL--CAMS UPDATE
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplechequenoChannel.cams_upload_Details_id from #MultiplechequenoChannel join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.user_trxnno=#MultiplechequenoChannel.user_trxnno
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplechequenoChannel.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplechequenoChannel.scheme_code and
#MultiplechequenoChannel.format_id in ('98'))

-- CHANNEL--CMS UPDATE
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplechequenoChannel.cms_upload_Details_id from #MultiplechequenoChannel join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.additionalfield2=#MultiplechequenoChannel.user_trxnno
where --pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#Redundantfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplechequenoChannel.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplechequenoChannel.scheme_code and
#MultiplechequenoChannel.format_id in ('98'))

-- Update CAMS filesMULTIPLE to PENDING-
--

For Updating the Exact count 'Multiple Records' in Purchase and 'Pending' in CMS to Success'
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13 into #MultiplePendingchequeno from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.instrm_no = cms.cheque_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,name,Payin_Slip_No,cms.additionalfield6,cms.additionalfield13
having count(cams.instrm_no) = count(cms.cheque_no)

--//For exact count of 'Pending' in CMS and 'Multiple Records' in Purchase for CMS Banks on folio_no basis
select cms_upload_details_id,cams_upload_details_id,cheque_no,instrm_no,cms.folio_no,cams.folio_no as CAMSFolio,trade_date,tradedate,cms.amount,cams.amount as CAMSAmount,cams_schemegroup,cms_schemegroup,cams.Compare_Status,cms.Compare_Status as CMSCompare_Status,cms.scheme_code,cams.scheme_code as CAMSScheme_Code,format_id,Payment_Mechanism,cms.additionalfield2,name into #MultiplePendingfoliono from tbl_cams_uploadDetails cams with(nolock)
full outer join tbl_cms_uploadDetails cms
on cams.folio_no = cms.folio_no
where cams.Compare_Status='Multiple Records' and cms.Compare_Status='Pending'
group by instrm_no, cheque_no, cams.Compare_Status,cams_upload_details_id,cms_upload_details_id, cms.Compare_Status,cms.Folio_No,cams.Folio_No,cams.Trade_Date,cms.Tradedate,cms.Amount,cams.Amount,cams_schemegroup,cms_schemegroup,cms.scheme_code,cams.scheme_code,format_id,Payment_Mechanism,cms.additionalfield2,name
having count(cams.Folio_no) = count(cms.folio_no)

--IBANK--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingchequeno.cams_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultiplePendingchequeno.cams_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultiplePendingchequeno.additionalfield6 and
#MultiplePendingchequeno.format_id in ('82'))

--HDFC--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingchequeno.cams_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CAMS_UploadDetails.instrm_no=#MultiplePendingchequeno.instrm_no and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultiplePendingchequeno.cams_schemegroup and
--pruamc.Tbl_CAMS_UploadDetails.Payin_Slip_No=#MultiplePendingchequeno.additionalfield13 and
#MultiplePendingchequeno.format_id in ('83'))

--IBANK SI
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='EC' and
#MultiplePendingfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('90','91'))

--RTGS--
update Tbl_CAMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cams_upload_Details_id in (
select distinct #MultiplePendingfoliono.cams_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CAMS_UploadDetails on pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CAMS_UploadDetails.Trade_Date=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CAMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CAMS_UploadDetails.cams_schemegroup=#MultiplePendingfoliono.cms_schemegroup and
pruamc.Tbl_CAMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.format_id in ('99'))

-- Update CMS filesMULTIPLE to PENDING-
--
--IBANK--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingchequeno.cms_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#MultiplePendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield6=#MultiplePendingchequeno.additionalfield6 and
#MultiplePendingchequeno.format_id in ('82'))

--HDFC--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingchequeno.cms_upload_Details_id from #MultiplePendingchequeno join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.instrm_no
where pruamc.Tbl_CMS_UploadDetails.cheque_no=#MultiplePendingchequeno.cheque_no and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingchequeno.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#MultiplePendingchequeno.cms_schemegroup and
--pruamc.Tbl_CMS_UploadDetails.additionalfield13=#MultiplePendingchequeno.additionalfield13 and
#MultiplePendingchequeno.format_id in ('83'))

--IBANK SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('85','86'))

--BJ SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='EC' and
#MultiplePendingfoliono.format_id in ('88','89'))

--HDFC SI--
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.scheme_code=#MultiplePendingfoliono.Scheme_Code and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.Payment_Mechanism='M' and
#MultiplePendingfoliono.format_id in ('90','91'))

--RTGS --
update Tbl_CMS_UploadDetails set compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) where cms_upload_Details_id in (
select distinct #MultiplePendingfoliono.cms_upload_Details_id from #MultiplePendingfoliono join pruamc.Tbl_CMS_UploadDetails on pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.folio_no
where pruamc.Tbl_CMS_UploadDetails.TradeDate=#MultiplePendingfoliono.TradeDate and
pruamc.Tbl_CMS_UploadDetails.amount=#MultiplePendingfoliono.amount and
pruamc.Tbl_CMS_UploadDetails.cms_schemegroup=#MultiplePendingfoliono.cms_schemegroup and
pruamc.Tbl_CMS_UploadDetails.folio_no=#MultiplePendingfoliono.Folio_no and
#MultiplePendingfoliono.format_id in ('99'))

drop table #MultiplePendingchequeno
drop table #MultiplePendingfoliono
drop table #MultiplechequenoChannel

DECLARE CUR_DATAFORMAT CURSOR FOR
SELECT DataFormat_ID,DataFormat_Name FROM Tbl_DataFormat WITH(NOLOCK) WHERE DataFormat_Isactive =1 AND DataFormat_Name <>'CAMS'
OPEN CUR_DATAFORMAT
FETCH NEXT FROM CUR_DATAFORMAT INTO @.DataFormat_ID,@.DataFormat_Name

WHILE @.@.FETCH_STATUS =0
BEGIN

IF @.DataFormat_Name ='IBANK'

Begin

proper query for updating success record in purchase file
select instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessIBANK from tbl_cams_uploaddetails WITH(NOLOCK)
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
cams_schemegroup=cms_schemegroup --and
--tbl_cams_uploaddetails.instrm_date=tbl_cms_uploaddetails.instrm_date --'Added Gopal feb 07 new criteria
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and tbl_cms_uploaddetails.Format_ID=82 and
--Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploaddetails.additionalfield6 and
Payment_Mechanism<>'EC' and Payment_Mechanism<>'M' and Payment_Mechanism <> 'TR'
group by instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
having count(instrm_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANK
where tbl_cams_uploaddetails.instrm_no=#SuccessIBANK.instrm_no and
tbl_cams_uploaddetails.amount=#SuccessIBANK.amount and
tbl_cams_uploaddetails.cams_schemegroup=#SuccessIBANK.cams_schemegroup and
tbl_cams_uploaddetails.payin_slip_no=#SuccessIBANK.payin_slip_no and
--tbl_cams_uploaddetails.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cams_uploaddetails.compare_status='Pending'

proper query for updating success record in bank file
print 'g6'
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success', ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANK
where tbl_cms_uploaddetails.cheque_no=#SuccessIBANK.instrm_no and
tbl_cms_uploaddetails.amount=#SuccessIBANK.amount and
tbl_cms_uploaddetails.cms_schemegroup=#SuccessIBANK.cams_schemegroup and
tbl_cms_uploaddetails.additionalfield6=#SuccessIBANK.payin_slip_no and
--tbl_cms_uploaddetails.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cms_uploaddetails.compare_status='Pending'and tbl_cms_uploaddetails.Format_ID=82

drop table #SuccessIBANK

DD for IBank
select instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessIBANKDD from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
cams_schemegroup=cms_schemegroup --and
--tbl_cams_uploaddetails.instrm_date=tbl_cms_uploaddetails.instrm_date --'Added Gopal feb 07 new criteria
where tbl_cams_uploaddetails.compare_status='Pending' and
(0.98 * tbl_cams_uploaddetails.amount <= tbl_cms_uploaddetails.amount
and tbl_cams_uploaddetails.amount >= tbl_cms_uploaddetails.amount)

and tbl_cms_uploaddetails.compare_status='Pending' and tbl_cms_uploaddetails.Format_ID=82 and
Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploaddetails.additionalfield6 and
Payment_Mechanism in ('D','P')
group by instrm_no,
--tbl_cams_uploaddetails.instrm_date,
tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
having count(instrm_no) = 1

--

-After chnage as on 6th march
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success', ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANKDD
where tbl_cms_uploaddetails.cheque_no=#SuccessIBANKDD.instrm_no and
-- tbl_cms_uploaddetails.amount =0.98 * #SuccessIBANKDD.amount or
-- tbl_cms_uploaddetails.amount = #SuccessIBANKDD.amount or
-- tbl_cms_uploaddetails.amount < 0.98 * #SuccessIBANKDD.amount and
(0.98 * #SuccessIBANKDD.amount <= tbl_cms_uploaddetails.amount
and #SuccessIBANKDD.amount >= tbl_cms_uploaddetails.amount) and

tbl_cms_uploaddetails.cms_schemegroup=#SuccessIBANKDD.cams_schemegroup and
tbl_cms_uploaddetails.additionalfield6=#SuccessIBANKDD.payin_slip_no and
--tbl_cms_uploaddetails1.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cms_uploaddetails.compare_status='Pending'and tbl_cms_uploaddetails.Format_ID=82

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBANKDD
where tbl_cams_uploaddetails.instrm_no=#SuccessIBANKDD.instrm_no and
-- 0.98 * tbl_cams_uploaddetails.amount = #SuccessIBANKDD.amount or
-- tbl_cams_uploaddetails.amount = #SuccessIBANKDD.amount or
-- 0.98 * tbl_cams_uploaddetails.amount < #SuccessIBANKDD.amount and
(0.98 * tbl_cams_uploaddetails.amount <= #SuccessIBANKDD.amount
and tbl_cams_uploaddetails.amount >= #SuccessIBANKDD.amount) and

tbl_cams_uploaddetails.cams_schemegroup=#SuccessIBANKDD.cams_schemegroup and
tbl_cams_uploaddetails.payin_slip_no=#SuccessIBANKDD.payin_slip_no and
--tbl_cams_uploaddetails.instrm_date=#SuccessIBANK.instrm_date and --'Added Gopal feb 07 new criteria
tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cams_uploaddetails.Payment_Mechanism in ('D','P')

--
drop table #SuccessIBANKDD

end
IF @.DataFormat_Name ='HDFC'
Begin

proper query for updating success record in purchase file hdfc bank
select instrm_no
--,tbl_cams_uploadDetails.instrm_date
,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessHDFC from tbl_cams_uploaddetails WITH(NOLOCK)
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount AND
--tbl_cams_uploadDetails.instrm_date=tbl_cms_uploadDetails.instrm_date And --'Added Gopal feb 07
tbl_cams_uploaddetails.cams_schemegroup=tbl_cms_uploadDetails.cms_schemegroup --'Added Gopal feb 07
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending'and Format_ID=83 and
Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploadDetails.additionalfield13 and
Payment_Mechanism<>'EC'and Payment_Mechanism<>'M' and Payment_Mechanism <> 'TR'
group by instrm_no,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
--,tbl_cams_uploadDetails.instrm_date
having count(instrm_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFC
where tbl_cams_uploaddetails.instrm_no=#SuccessHDFC.instrm_no and
tbl_cams_uploaddetails.amount=#SuccessHDFC.amount and
--tbl_cams_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cams_uploaddetails.cams_schemegroup=#SuccessHDFC.cams_schemegroup and
--tbl_cams_uploaddetails.payin_slip_no=#SuccessHDFC.payin_slip_no and
tbl_cams_uploaddetails.compare_status='Pending'

proper query for updating success record in bank file hdfc bank

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFC
where tbl_cms_uploaddetails.cheque_no=#SuccessHDFC.instrm_no and
tbl_cms_uploaddetails.amount=#SuccessHDFC.amount and
tbl_cms_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cms_uploaddetails.cms_schemegroup=#SuccessHDFC.cams_schemegroup and --added 26-Feb- 07 by gopal
tbl_cms_uploaddetails.additionalfield13=#SuccessHDFC.payin_slip_no and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=83

drop table #SuccessHDFC

--DD for HDFC
--For DD charges for HDFC
select instrm_no
--,tbl_cams_uploadDetails.instrm_date
,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
into #SuccessHDFCDD from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
instrm_no=cheque_no and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount AND
--tbl_cams_uploadDetails.instrm_date=tbl_cms_uploadDetails.instrm_date And --'Added Gopal feb 07
tbl_cams_uploaddetails.cams_schemegroup=tbl_cms_uploadDetails.cms_schemegroup --'Added Gopal feb 07
where tbl_cams_uploaddetails.compare_status='Pending' and
(0.98 * tbl_cams_uploaddetails.amount <= tbl_cms_uploaddetails.amount
and tbl_cams_uploaddetails.amount >= tbl_cms_uploaddetails.amount)
and tbl_cms_uploaddetails.compare_status='Pending'and Format_ID=83 and
Tbl_CAMS_UploadDetails.payin_slip_no=tbl_cms_uploadDetails.additionalfield13 and
Payment_Mechanism in ('D','P')
group by instrm_no,tbl_cams_uploaddetails.amount,cams_schemegroup,payin_slip_no
--,tbl_cams_uploadDetails.instrm_date
having count(instrm_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFCDD
where tbl_cams_uploaddetails.instrm_no=#SuccessHDFCDD.instrm_no and
--0.98 * tbl_cams_uploaddetails.amount = #SuccessHDFCDD.amount or 0.98 * tbl_cams_uploaddetails.amount < #SuccessHDFCDD.amount or tbl_cams_uploaddetails.amount = #SuccessHDFCDD.amount and
(0.98 * tbl_cams_uploaddetails.amount <= #SuccessHDFCDD.amount
and tbl_cams_uploaddetails.amount >= #SuccessHDFCDD.amount) and

--tbl_cams_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cams_uploaddetails.cams_schemegroup=#SuccessHDFCDD.cams_schemegroup and
--tbl_cams_uploaddetails.payin_slip_no=#SuccessHDFCDD.payin_slip_no and
tbl_cams_uploaddetails.compare_status='Pending' and Payment_Mechanism in ('D','P')

--Added on 21 mar
update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFCDD
where tbl_cms_uploaddetails.cheque_no=#SuccessHDFCDD.instrm_no and
--tbl_cms_uploaddetails.amount=#SuccessHDFC.amount and
(0.98 * #SuccessHDFCDD.amount <= tbl_cms_uploaddetails.amount
and #SuccessHDFCDD.amount >= tbl_cms_uploaddetails.amount) and
tbl_cms_uploadDetails.instrm_date=#SuccessHDFC.instrm_date and --Added feb Gopal 2007
tbl_cms_uploaddetails.cms_schemegroup=#SuccessHDFCDD.cams_schemegroup and --added 26-Feb- 07 by gopal
tbl_cms_uploaddetails.additionalfield13=#SuccessHDFCDD.payin_slip_no and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=83

drop table #SuccessHDFCDD

--
end

IF @.DataFormat_Name ='RTGS'
Begin

proper query for updating success record in purchase file RTGS
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.cams_schemegroup,tbl_cams_uploaddetails.trade_date into #SuccessRTGS from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.cams_schemegroup from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
cams_schemegroup=cms_schemegroup and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no and
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=99
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.cams_schemegroup,trade_date
having count(tbl_cams_uploaddetails.folio_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessRTGS
where tbl_cams_uploaddetails.folio_no=#SuccessRTGS.folio_no and
tbl_cams_uploaddetails.amount=#SuccessRTGS.amount and
tbl_cams_uploaddetails.cams_schemegroup=#SuccessRTGS.cams_schemegroup and
tbl_cams_uploaddetails.trade_date=#SuccessRTGS.trade_date and
tbl_cams_uploaddetails.compare_status='Pending'

proper query for updating success record in bank file RTGS

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessRTGS
where tbl_cms_uploaddetails.folio_no=#SuccessRTGS.folio_no and
tbl_cms_uploaddetails.amount=#SuccessRTGS.amount and
tbl_cms_uploaddetails.cms_schemegroup=#SuccessRTGS.cams_schemegroup and
tbl_cms_uploaddetails.tradedate=#SuccessRTGS.trade_date and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=99

drop table #SuccessRTGS

end

IF @.DataFormat_Name='CHANNEL'
Begin

proper query for updating success record in purchase file Channel

select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.trade_date,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploadDetails.Folio_No,--tbl_cms_uploadDetails.Folio_No Folio_No1,
--Nachiket
--tbl_cams_uploaddetails.name
tbl_cams_uploaddetails.user_trxnno
into #SuccessChannel from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.trade_date,tbl_cams_uploaddetails.cams_schemegroup from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on

tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount --and
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate
--Nachiket

and tbl_cams_uploaddetails.user_trxnno=tbl_cms_uploaddetails.additionalfield2
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=98

group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.trade_date,tbl_cams_uploaddetails.Scheme_Code,
tbl_cams_uploadDetails.Folio_No,
--,tbl_cms_uploadDetails.Folio_No--Nachiket
--,tbl_cams_uploadDetails.name
tbl_cams_uploaddetails.user_trxnno
having count(tbl_cams_uploaddetails.trade_date) =1

--select * from #SuccessChannelG
-- drop table #SuccessChannelG

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessChannel
where -tbl_cams_uploaddetails.trade_date=#SuccessChannel.trade_date and by Nachiket on 15th March as per CR
tbl_cams_uploaddetails.amount=#SuccessChannel.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessChannel.Scheme_Code and
tbl_cams_uploaddetails.compare_status='Pending'

and tbl_cams_uploaddetails.user_trxnno=#SuccessChannel.user_trxnno

proper query for updating success record in bank file Channel

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessChannel
where -tbl_cms_uploaddetails.tradedate=#SuccessChannel.trade_date and by Nachiket on 15th March as per CR
tbl_cms_uploaddetails.amount=#SuccessChannel.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessChannel.Scheme_Code and
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID=98

and tbl_cms_uploaddetails.additionalfield2=#SuccessChannel.user_trxnno
--

drop table #SuccessChannel
End

IF @.DataFormat_Name ='IBANK SUCCESS' OR @.DataFormat_Name ='IBANK FAILURE'
Begin

proper query for updating success record in purchase file IBank Success
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism into #SuccessIBankSIP from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no AND
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate --ADDED GOPAL 07 FEB
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (85,86) and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism
having count(tbl_cams_uploaddetails.folio_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success' ,ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBankSIP
where tbl_cams_uploaddetails.folio_no=#SuccessIBankSIP.folio_no and
tbl_cams_uploaddetails.amount=#SuccessIBankSIP.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessIBankSIP.Scheme_Code and
tbl_cams_uploaddetails.trade_date=#SuccessIBankSIP.trade_date and--ADDED GOPAL 07 NEW CR
tbl_cams_uploaddetails.compare_status='Pending' and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'

proper query for updating success record in bank file IBank Success

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessIBankSIP
where tbl_cms_uploaddetails.folio_no=#SuccessIBankSIP.folio_no and
tbl_cms_uploaddetails.amount=#SuccessIBankSIP.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessIBankSIP.Scheme_Code and
tbl_cms_uploaddetails.tradedate=#SuccessIBankSIP.trade_date and --ADDED GOPAL 07 NEW CR tbl_cms_uploaddetails.compare_status='Pending'
#SuccessIBankSIP.Payment_Mechanism='M'
drop table #SuccessIBankSIP

end

IF @.DataFormat_Name ='BJ SUCCESS' OR @.DataFormat_Name ='BJ FAILURE'
Begin

proper query for updating success record in purchase file BJ Success
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism into #SuccessBJSIP from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no and
tbl_cams_uploaddetails.trade_date=tbl_cms_uploaddetails.tradedate --GOPAL 07 CR
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89) and Tbl_CAMS_UploadDetails.Payment_Mechanism='EC'
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date,Tbl_CAMS_UploadDetails.Payment_Mechanism
having count(tbl_cams_uploaddetails.folio_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success',
ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessBJSIP
where tbl_cams_uploaddetails.folio_no=#SuccessBJSIP.folio_no and
tbl_cams_uploaddetails.amount=#SuccessBJSIP.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessBJSIP.Scheme_Code and
tbl_cams_uploaddetails.trade_date=#SuccessBJSIP.trade_date and--GOPAL 07 CR
tbl_cams_uploaddetails.compare_status='Pending' and tbl_cams_uploaddetails.Payment_Mechanism='EC'

proper query for updating success record in bank file BJ Success

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessBJSIP
where tbl_cms_uploaddetails.folio_no=#SuccessBJSIP.folio_no and
tbl_cms_uploaddetails.amount=#SuccessBJSIP.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessBJSIP.Scheme_Code and
tbl_cms_uploaddetails.tradedate=#SuccessBJSIP.trade_date and--GOPAL 07 CR
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89) and #SuccessBJSIP.Payment_Mechanism='EC'

drop table #SuccessBJSIP
end

IF @.DataFormat_Name ='HDFC SUCCESS' OR @.DataFormat_Name ='HDFC FAILURE'
Begin

proper query for updating success record in purchase file HDFC SUCCESS
select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploadDetails.Trade_date
--Nachiket
,tbl_cams_uploaddetails.name,tbl_cams_uploaddetails.Payment_Mechanism
into #SuccessHDFCSIP from tbl_cams_uploaddetails WITH(NOLOCK)
--select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code from tbl_cams_uploaddetails
join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=tbl_cms_uploaddetails.Scheme_Code and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=tbl_cms_uploaddetails.folio_no and
tbl_cams_uploadDetails.Trade_date=tbl_cms_uploadDetails.Tradedate --GOPAL 07 CR
Nachiket
--and tbl_cams_uploadDetails.name=tbl_cms_uploadDetails.additionalfield2
where tbl_cams_uploaddetails.compare_status='Pending'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (90,91) and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploadDetails.Trade_date
--Nachiket
,tbl_cams_uploaddetails.name,tbl_cams_uploaddetails.Payment_Mechanism
having count(tbl_cams_uploaddetails.folio_no) = 1

update tbl_cams_uploaddetails
set tbl_cams_uploaddetails.compare_status='Success' ,
ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103) from #SuccessHDFCSIP
where tbl_cams_uploaddetails.folio_no=#SuccessHDFCSIP.folio_no and
tbl_cams_uploaddetails.amount=#SuccessHDFCSIP.amount and
tbl_cams_uploaddetails.Scheme_Code=#SuccessHDFCSIP.Scheme_Code and
tbl_cams_uploadDetails.Trade_date=#SuccessHDFCSIP.Trade_date and --GOPAL 07 CR
tbl_cams_uploaddetails.compare_status='Pending' and Tbl_CAMS_UploadDetails.Payment_Mechanism='M'
--Nachiket
--and tbl_cams_uploaddetails.name=#SuccessHDFCSIP.name

proper query for updating success record in bank file HDFC SUCCESS

update tbl_cms_uploaddetails
set tbl_cms_uploaddetails.compare_status='Success',
ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103)
from #SuccessHDFCSIP
where tbl_cms_uploaddetails.folio_no=#SuccessHDFCSIP.folio_no and
tbl_cms_uploaddetails.amount=#SuccessHDFCSIP.amount and
tbl_cms_uploaddetails.Scheme_Code=#SuccessHDFCSIP.Scheme_Code and
tbl_cms_uploadDetails.Tradedate=#SuccessHDFCSIP.Trade_date and --GOPAL 07 CR
tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (90,91) and #SuccessHDFCSIP.Payment_Mechanism='M'
--Nachiket
--and tbl_cms_uploaddetails.additionalfield2=#SuccessHDFCSIP.name

drop table #SuccessHDFCSIP
end

FETCH NEXT FROM CUR_DATAFORMAT INTO @.DataFormat_ID,@.DataFormat_Name

END
CLOSE CUR_DATAFORMAT
DEALLOCATE CUR_DATAFORMAT
--FORMAT END

SELECT top 30 CAMS.Name,CAMS.Scheme_Code,
CAMS.Trade_Date,CAMS.Posted_Date, CAMS.Amount, CAMS.Instrm_No, CAMS.Instrm_Date,
CAMS.Bank
from Tbl_CAMS_UploadDetails CAMS WITH(NOLOCK)
where CaMS.Compare_Status = 'Success' and ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103)

SELECT top 30 CAMS.Name,CAMS.Scheme_Code,
CAMS.Trade_Date,CAMS.Posted_Date, CAMS.Amount, CAMS.Instrm_No, CAMS.Instrm_Date,
CAMS.Bank
from Tbl_CAMS_UploadDetails CAMS WITH(NOLOCK) where CaMS.Compare_Status = 'Pending'

END

i know its very vast procedure....not written by me..so i want to optimize it........

any idea?

thanx a lot