Comments by SriNivas IT (Top 17 by date)

Ok the code i pasted here


TransactionOptions options = new TransactionOptions();
options.Timeout = new TimeSpan(0, 10, 4);
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, options))
{
try
{
serviceDetail.ServiceRequestNumber = ServicerequestNo;
if (HttpContext.Current.Session[EnumSession.EntityID.ToString()] != "")
{
serviceDetail.EntityID = Convert.ToInt32(HttpContext.Current.Session[EnumSession.EntityID.ToString()]);
}
if (customerDetail.enableCustomerPanel == true)
{
dictParam = new Dictionary<string, parameteritem="">();
dictParam.Add("@FirstName", new ParameterItem(0, DbType.String, customerDetail.FirstName, ParameterDirection.Input));
dictParam.Add("@LastName", new ParameterItem(1, DbType.String, customerDetail.LastName, ParameterDirection.Input));
dictParam.Add("@Address", new ParameterItem(2, DbType.String, customerDetail.Address, ParameterDirection.Input));
dictParam.Add("@CityID", new ParameterItem(3, DbType.Int32, customerDetail.CityID, ParameterDirection.Input));
dictParam.Add("@PinCode", new ParameterItem(4, DbType.String, customerDetail.PinCode, ParameterDirection.Input));
dictParam.Add("@Mobile", new ParameterItem(5, DbType.String, customerDetail.Mobile, ParameterDirection.Input));
dictParam.Add("@Phone", new ParameterItem(6, DbType.String, customerDetail.Phone, ParameterDirection.Input));
dictParam.Add("@UserId", new ParameterItem(7, DbType.Int32, HttpContext.Current.Session[EnumSession.UserID.ToString()], ParameterDirection.Input));
this.Connection.AddParameters(dictParam);
customerDetail.CustomerID = Convert.ToInt32(this.Connection.ExecuteScalar(CommandType.StoredProcedure, Constants.USP_INSERT_CUSTOMER));
this.Connection.ClearParameters();
}
if (productDetail.enableProductPanel == true)
{
......

and also save data three more methods

There is issue of procedure performance ,proc takes more than 10 minuts
so in some place i use
OUTER APPLY with where condition
in place of LEFT JOIN

ok thanks alot gvprabhu !
it works!!!

ok, gvprabu but i want to create non-unique index actually for fast searching of data.

from this solution unique key constraint violates and error message comes

Violation of UNIQUE KEY constraint 'UQ__#6D181FE__2C5D12DB71DCD509'

I replaced union to union all

and with common table expression i used with distinct due to duplicacy

Like that


with cte_result
(
select colums.... from A
union all
select colums.... from B
union all
select colums.... from C
....
....
....
....
)
select distinct * from cte_result

can this increase some performance????

OK !

But as i say that my problem here is that

sp_1 returning rows 17736

sp_2 returning rows 17731

how i can find out which rows are extra in this huge result

Actually SP has more than 50 columns and i just want to store this result very short time Like select * from (select * from Table_Name) mainly i want to compare result set of 2 SPs whose result set returns 17736 and 17731 rows respectively now i want to intersect result for this only

Actually SP has more than 50 columns and i just want to store this result very short time

Like select * from (select * from Table_Name)

mainly i want to compare result set of 2 SPs whose result set returns 17736 and 17731 rows respectively now i want to intersect result for this only.

But I can not use sp_configure proc to change setting can i not do that like


select * from (Exec SP_Name 'p1',p2,'p3')

???

Ya thanks

Rahul

Actually Arun you may understand by query situation as below:

Let A is a table which has 100000 or more records and i fire query as

select top 400 col1 from A

then affected rows are as 400

but i want to find total scanned rows which are defenately 100000

can it possible????

Hi GVPrabu,

I want to return 100000 (in rows affected message or any medium) not 500 rows

Thanks

Shreeniwas