Hi guys,

I have one website which can able to show reports. The Data of the reports are coming from database. The report is simple gridview. While fetching the records through remote desktop from sql server directly, I am able to get all data withing 10 sec but the similar record is returning by my code is minimum 3 min. Can you please suggest me for tuning up my connection to get the data as soon as possible.

Any help will be accepted. Thanks all...Have a nice day!

Well instead of getting the whole data to client side in one go you can implement  an ajax model where:

Get 10 rows of data in one go and further request the data from the server using xmlHttpObject via javascript.

 Thanks for your suggession...But as per my requirement the all data should be selected at a time in a dataset.

Secondly why the two time difference is so high...10 sec and 3 min...

Any more suggession?

 how many record you try to return ?
try it with XML before ?

Use dottrace profiler to track which function is taking longer time to execute and then optimize that function.

Also if you're using SQL server then run SQL profiler and check how long the query takes to execute ?

tapojjwal:

The report is simple gridview.

What is the size of the source of page?
What is the actual speed of your internet connection?

Hi tapojjwal,

As they suggested, since you didn't show us the source code, we can only give you some suggestions to improve performance. Please show us more details if you still can't resolve the problem.

Here's a good example of using paging:

http://msdn.microsoft.com/en-us/library/aa479347.aspx

Thanks.

Hello,

In ASP.net Database connections are added in connection pool.

Hence, it will take time for the first time.

I suggest you to fillup a dataset and you may use paging to bing records to your GridView.

Again, GridView is a heavy object.

You may create your own custom control (derived from CustomList), which can be light-weight.

Hope this will help you.

 Hi Wencui Qian,

Thank you for replying me. Actually from my Code its simply a generate button click event is there. From my asp.net page I am just sending from date and to date and trying to fetching the whole transaction record from the database. The db contains more than 5 lakhs of records. I am having different database server. So while connecting the db by sql client and executing the query I am getting the records within 22 Seconds but while trying to coneect the db by asp.net and trying to execute the same query by giving a breakpoint in my code behind I am geting the result within more than 3 minutes.

My question is that why the difference of time is coming so much. Is there any faster way to connect the database and getting the result immediately. For reference I am giving some sample of my code.

-- [sp_AFF_RPT_FetchEarningsReport_New_V1] 1939,16, '04/01/2009 00:00:00', '04/30/2009 23:59:59'
ALTER PROCEDURE [dbo].[sp_AFF_RPT_FetchEarningsReport_New_V1]
@MemberId int,
@MerchantID int,
@startDate nvarchar(50),
@endDate nvarchar(50)
AS
  Begin
------------------------
-------
------------------------

	SET NOCOUNT ON;

	SELECT
			dbo.fn_Date_Notime(DATE)			AS DATE,
			A.MEMBERID							AS MEMBERID,
			B.AffiliateUserName					AS MemberName,
			C.MerchantName						AS Merchant,
			A.Merchantid						AS Merchantid,
			ISNULL(SUM(Impressions),0)			AS Impressions, 
			ISNULL(SUM(Clicks),0)				AS Clicks,
			ISNULL(SUM(Downloads),0)			AS Downloads,
			ISNULL(SUM(SignUps),0)				AS SignUps,
			ISNULL(SUM(Depositors),0)			AS Depositors,
			ISNULL(SUM(Deposits),0)				AS Deposits,
			ISNULL(SUM(GrossRevenue),0)			AS GrossRevenue,
			ISNULL(SUM(BonusAdj),0)				AS BonusAdj,
			ISNULL(SUM(Chargebacks),0)			AS Chargebacks,
			ISNULL(SUM(BannedRevenue),0)		AS BannedRevenue,
			ISNULL(SUM(UnbannedRevenue),0)		AS UnbannedRevenue,
			ISNULL(SUM(NetRevenue),0)			AS NetRevenue, 
			ISNULL(SUM(Earnings),0)				AS Earnings,
			ISNULL(SUM(Commission),0)		AS Commission,
			0									AS CPAAccounts,
			ISNULL(SUM(ReferralCommission),0)	AS ReferralCommission
	-----------------
		INTO	#Ear
	-----------------
	FROM
	(
			SELECT 
					DATE,
					MEMBERID,
					Merchantid,
					0	AS Impressions, 
					0	AS Clicks,
					0	AS Downloads,
					COUNT(DISTINCT SignUps) AS SignUps,
					0	AS Depositors,
					0	AS Deposits,
					0	AS GrossRevenue,
					0	AS BonusAdj,
					0	AS Chargebacks,
					0	AS BannedRevenue,
					0	AS UnbannedRevenue,
					0	AS NetRevenue, 
					0	AS Earnings,
					0	AS Commission,
					0	AS ReferralCommission
			FROM
			(
					SELECT 
							
							A.Merchantid,
							A.MEMBERID,
							A.DATE,
							SignUps	= CASE WHEN SUM(A.NewDepositedPlayersCount)	> = 0  THEN A.PLAYERID ELSE NULL END
					FROM
							TB_AFF_RPT_SUMMARY_NEW A
					JOIN
							TB_AFF_PLAYER_MAP B
					ON
							A.Playerid		=	B.Playerid	
					AND		A.MEMBERID		=	B.MEMBERID
					AND		A.MERCHANTID	=   B.MERCHANT
					WHERE
							((@MERCHANTID	= 0 AND A.merchantid	>	0)
					OR 
							(@MERCHANTID	> 0 AND A.merchantid	=	@MERCHANTID))
					AND 
							((@MEMBERID		= 0 AND A.memberid		>	0)
					OR	
							(@MEMBERID		> 0 AND A.memberid		=	@MEMBERID))
					AND	
							A.Date			BETWEEN @startDate		AND @endDate 
					AND
							B.signupdate	BETWEEN @startDate		AND @endDate 
					AND
							A.Date			=	B.signupdate
					AND 
							A.Merchantid	<>	10
					GROUP BY 
							A.Merchantid,A.PLAYERID,A.DATE,A.MEMBERID
			) AS RESULT
			GROUP BY 
					Merchantid,		MEMBERID,		DATE
			-----------------------
			UNION ALL
			-----------------------
			SELECT 
					DATE,
					MEMBERID,
					Merchantid,
					0	AS Impressions, 
					0	AS Clicks,
					0	AS Downloads,
					0   AS SignUps,
					COUNT(DISTINCT TotalDepositedPlayersCount)  AS Depositors,
					0	AS Deposits,
					0	AS GrossRevenue,
					0	AS BonusAdj,
					0	AS Chargebacks,
					0	AS BannedRevenue,
					0	AS UnbannedRevenue,
					0	AS NetRevenue, 
					0	AS Earnings,
					0	AS Commission,
					0	AS ReferralCommission
			FROM
			(
					SELECT 
							
							A.Merchantid,
							A.MEMBERID,
							A.DATE,
							TotalDepositedPlayersCount	= CASE WHEN SUM(A.TotalDepositedPlayersCount)	> 0  THEN A.PLAYERID ELSE NULL END
					FROM
							TB_AFF_RPT_SUMMARY_NEW A
					WHERE
							((@MERCHANTID	= 0 AND A.merchantid	>	0)
					OR 
							(@MERCHANTID	> 0 AND A.merchantid	=	@MERCHANTID))
					AND 
							((@MEMBERID		= 0 AND A.memberid		>	0)
					OR	
							(@MEMBERID		> 0 AND A.memberid		=	@MEMBERID))
					AND	
							A.Date			BETWEEN @startDate		AND @endDate 
					AND 
							A.Merchantid	<>	10
					GROUP BY 
							A.Merchantid, A.PLAYERID, A.DATE, A.MEMBERID
			) AS RESULT
			GROUP BY 
					Merchantid,	MEMBERID, DATE
			-----------------------
			UNION ALL
			-----------------------
			SELECT 
						DATE ,
						MEMBERID,
						MERCHANTID,
						0 AS Impressions,
						0 AS Clicks,
						0 AS Downloads,
						0	AS SignUps,
						0	AS Depositors,
						SUM(ISNULL(TotalDeposits,0))  AS Deposits,
						SUM(ISNULL(GrossRevenue,0)) AS GrossRevenue,
						SUM(ISNULL(FreeMoney,0)) AS BonusAdj,
						SUM(ISNULL(ProgressiveContribution,0))	AS Chargebacks,
						0.00									AS BannedRevenue,
						0.00									AS UnbannedRevenue,
						SUM(ISNULL(Netrevenue,0))				AS NetRevenue,
						SUM(ISNULL(GrossRevenue,0))-(SUM(ISNULL(FreeMoney,0))+SUM(ISNULL(ProgressiveContribution,0))) AS Earnings,
						Commission = (SELECT COMMISSION FROM [dbo].[FN_CALCULATE_COMMISSION_NEW_V1](@MEMBERID,@MERCHANTID,@startDate,@endDate)),
						ReferralCommission =(SELECT RF_COMMISSION FROM DBO.[FN_CALCULATE_REF_COMMISSION_SUMMARY_NEW](@MEMBERID,@MERCHANTID,SUM(Netrevenue)))
				FROM 
						TB_AFF_RPT_SUMMARY_NEW 
				WHERE 
						((@MERCHANTID	= 0 AND MERCHANTID	>	0)
				OR 
						(@MERCHANTID	> 0 AND MERCHANTID	=	@MERCHANTID))
				AND 
						((@MEMBERID		= 0 AND MEMBERID	>	0)
				OR	
						(@MEMBERID		> 0 AND MEMBERID	=	@MEMBERID))
				AND	
						Date			BETWEEN @startDate	AND @endDate 
				AND
						Merchantid	<>	10
				GROUP BY 
						Merchantid, DATE, MEMBERID
		-----------------------
		UNION ALL
		-----------------------
			SELECT 
					DATE,
					MEMBERID,
					Merchantid,
					SUM(ISNULL(Impressions,0)) AS  Impressions,
					0	AS Clicks,
					0	AS downloads,
					0	AS SignUps,
					0	AS Depositors,
					0	AS Deposits,
					0	AS GrossRevenue,
					0	AS BonusAdj,
					0	AS Chargebacks,
					0	AS BannedRevenue,
					0	AS UnbannedRevenue,
					0	AS NetRevenue, 
					0	AS Earnings,
					0	AS Commission,
					0	AS ReferralCommission
			FROM 
					vw_ImpressionsCount 
			WHERE
					((@MERCHANTID	= 0 AND MERCHANTID	>	0)
			OR 
					(@MERCHANTID	> 0 AND MERCHANTID	=	@MERCHANTID))
			AND 
					((@MEMBERID		= 0 AND MEMBERID	>	0)
			OR	
					(@MEMBERID		> 0 AND MEMBERID	=	@MEMBERID))
			AND 
					DATE			BETWEEN		@startDate	AND @endDate 
			GROUP BY
					DATE, MEMBERID,	Merchantid
			-----------------------
			UNION ALL
			-----------------------
			SELECT 
					DATE,
					MEMBERID,
					Merchantid,
					0	AS Impressions, 
					SUM(ISNULL(Clicks,0)) AS Clicks,
					0	AS downloads,
					0	AS SignUps,
					0	AS Depositors,
					0	AS Deposits,
					0	AS GrossRevenue,
					0	AS BonusAdj,
					0	AS Chargebacks,
					0	AS BannedRevenue,
					0	AS UnbannedRevenue,
					0	AS NetRevenue, 
					0	AS Earnings,
					0	AS Commission,
					0	AS ReferralCommission
			FROM 
					vw_ClicksCount 
			WHERE
					((@MERCHANTID	= 0 AND MERCHANTID	>	0)
			OR 
					(@MERCHANTID	> 0 AND MERCHANTID	=	@MERCHANTID))
			AND 
					((@MEMBERID		= 0 AND MEMBERID	>	0)
			OR	
					(@MEMBERID		> 0 AND MEMBERID	=	@MEMBERID))
			AND 
					DATE			BETWEEN		@startDate	AND @endDate 
			GROUP BY
					DATE, MEMBERID,	Merchantid
			-----------------------
			UNION ALL
			-----------------------
			SELECT 
					DATE,
					MEMBERID,
					Merchantid,
					0	AS Impressions, 
					0	AS Clicks,
					SUM(ISNULL(downloadCount,0)) AS downloads,
					0	AS SignUps,
					0	AS Depositors,
					0	AS Deposits,
					0	AS GrossRevenue,
					0	AS BonusAdj,
					0	AS Chargebacks,
					0	AS BannedRevenue,
					0	AS UnbannedRevenue,
					0	AS NetRevenue, 
					0	AS Earnings,
					0	AS Commission,
					0	AS ReferralCommission
			FROM 
					TB_AFF_STATS_DOWNLOADS_COUNT  
			WHERE
					((@MERCHANTID	= 0 AND MERCHANTID	>	0)
			OR 
					(@MERCHANTID	> 0 AND MERCHANTID	=	@MERCHANTID))
			AND 
					((@MEMBERID		= 0 AND MEMBERID	>	0)
			OR	
					(@MEMBERID		> 0 AND MEMBERID	=	@MEMBERID))
			AND 
					DATE	BETWEEN	 @startDate	AND @endDate 
			GROUP BY
					DATE, MEMBERID, Merchantid
	) AS A
	JOIN
		TB_AFF_MEMBER_DETAILS B
	ON
		A.memberid		=	B.memberid	
	JOIN
		TB_AFF_Merchant C
	ON
		A.Merchantid	=	C.Merchantid 
	WHERE 
		C.STATUS =1

	GROUP BY 
				dbo.fn_Date_Notime(DATE), A.MEMBERID, A.Merchantid, B.AffiliateUserName, C.MerchantName	

SELECT * FROM #EAR
----------------------
---------------------- MEMBER WISE EARNINGS REPORT 
----------------------
----------------
----------------	SELECT 
----------------			Mem.MemberID,
----------------			B.AffiliateUserName					AS MemberName,
----------------			SUM(Impressions)					AS Impressions, 
----------------			SUM(Clicks)							AS Clicks,
----------------			CASE WHEN SUM(Impressions) > 0 THEN
----------------				SUM(Clicks)*1.00/SUM(Impressions)* 100    
----------------			ELSE
----------------				0
----------------			end									AS ClickThroughsRatio,
----------------			SUM(Downloads)						AS Downloads,
----------------			SUM(SignUps)						AS SignUps,
----------------			SUM(Depositors)						AS Depositors,
----------------			SUM(Deposits)						AS Deposits,
----------------			SUM(GrossRevenue)					AS GrossRevenue,
----------------			SUM(BonusAdj)						AS BonusAdj,
----------------			SUM(Chargebacks)					AS Chargebacks,
----------------			SUM(BannedRevenue)					AS BannedRevenue,
----------------			SUM(UnbannedRevenue)				AS UnbannedRevenue,
----------------			SUM(NetRevenue)						AS NetRevenue, 
----------------			SUM(Earnings)						AS Earnings,
----------------			SUM(ISNULL(PerCommission,0))		AS PerCommission,
----------------			SUM(ISNULL(FlatCommission,0))		AS FlatCommission,
----------------			SUM(ISNULL(CPAAccounts,0))			AS CPAAccounts,
----------------			SUM(ISNULL(ReferralCommission,0))	AS ReferralCommission,
----------------			SUM(ISNULL(PerCommission,0))  + SUM(ISNULL(FlatCommission,0)) + SUM(ISNULL(CPAAccounts,0))+ SUM(ISNULL(ReferralCommission,0)) TotalCommission
----------------	FROM
----------------	(
----------------		SELECT 
----------------				MemberID,
----------------				SUM(Impressions)	AS Impressions, 
----------------				SUM(Clicks)			AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				SUM(Downloads)		AS Downloads,
----------------				SUM(SignUps)		AS SignUps,
----------------				SUM(Depositors)		AS Depositors,
----------------				SUM(Deposits)		AS Deposits,
----------------				SUM(GrossRevenue)	AS GrossRevenue,
----------------				SUM(BonusAdj)		AS BonusAdj,
----------------				SUM(Chargebacks)	AS Chargebacks,
----------------				SUM(BannedRevenue)	AS BannedRevenue,
----------------				SUM(UnbannedRevenue)AS UnbannedRevenue,
----------------				SUM(NetRevenue)		AS NetRevenue, 
----------------				SUM(Earnings)		AS Earnings,
----------------				0					AS PerCommission,
----------------				0					AS FlatCommission,
----------------				0					AS CPAAccounts ,
----------------				0					AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				#Ear
----------------		GROUP BY 
----------------				MemberID,Merchantid
----------------		----------------------
----------------		UNION ALL
----------------		----------------------
----------------		SELECT 
----------------				A.MemberID,
----------------				0					AS Impressions, 
----------------				0					AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				0					AS Downloads,
----------------				0					AS SignUps,
----------------				0					AS Depositors,
----------------				0					AS Deposits,
----------------				0					AS GrossRevenue,
----------------				0					AS BonusAdj,
----------------				0					AS Chargebacks,
----------------				0					AS BannedRevenue,
----------------				0					AS UnbannedRevenue,
----------------				0					AS NetRevenue, 
----------------				0					AS Earnings,
----------------				A.PERCCOMMISSION	AS PerCommission,
----------------				0					AS FlatCommission,
----------------				0					AS CPAAccounts ,
----------------				0					AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				DBO.FN_TB_COMMISSION_PERC_OLD (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID') AS A
----------------		-----------------------
----------------		UNION ALL
----------------		-----------------------
----------------		SELECT 
----------------				B.MemberID,
----------------				0					AS Impressions, 
----------------				0					AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				0					AS Downloads,
----------------				0					AS SignUps,
----------------				0					AS Depositors,
----------------				0					AS Deposits,
----------------				0					AS GrossRevenue,
----------------				0					AS BonusAdj,
----------------				0					AS Chargebacks,
----------------				0					AS BannedRevenue,
----------------				0					AS UnbannedRevenue,
----------------				0					AS NetRevenue, 
----------------				0					AS Earnings,
----------------				0					AS PerCommission,
----------------				0					AS FlatCommission,
----------------				0					AS CPAAccounts ,
----------------				B.REFCOMMISSION		AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				DBO.[FN_TB_COMMISSION_REF_OLD] (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID') AS B
----------------		-----------------------
----------------		UNION ALL
----------------		-----------------------
----------------		SELECT
----------------				C.MemberID,
----------------				0					AS Impressions, 
----------------				0					AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				0					AS Downloads,
----------------				0					AS SignUps,
----------------				0					AS Depositors,
----------------				0					AS Deposits,
----------------				0					AS GrossRevenue,
----------------				0					AS BonusAdj,
----------------				0					AS Chargebacks,
----------------				0					AS BannedRevenue,
----------------				0					AS UnbannedRevenue,
----------------				0					AS NetRevenue, 
----------------				0					AS Earnings,
----------------				0					AS PerCommission,
----------------				C.FLATCOMMISSION	AS FlatCommission,
----------------				0					AS CPAAccounts ,
----------------				0					AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				DBO.FN_TB_COMMISSION_FLAT_OLD (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID') AS C
----------------	)AS Mem
----------------	JOIN
----------------		TB_AFF_MEMBER_DETAILS B
----------------	ON
----------------		Mem.memberid		=	B.memberid	
----------------	GROUP BY
----------------			Mem.memberid, B.AffiliateUserName	
----------------
--------------------------------------
-------------------------------------- MERCHANT WISE  EARNINGS REPORT 
--------------------------------------
----------------
----------------	SELECT 
----------------			DISTINCT
----------------			IDENTITY (INT,1,1) AS MerchantKey,
----------------			MemberID,MemberName,MerchantID
----------------	------------------------
----------------	INTO	#TEMP_MEMBER_MERCHANT
----------------	------------------------
----------------	FROM	
----------------			#Ear
----------------------------------------
-----------------------
----------------------------------------
----------------	SELECT 
----------------			B.MerchantKey,	
----------------			Mer.MemberID,
----------------			B.MemberName,
----------------			C.MerchantName				AS Merchant,
----------------			Mer.MerchantID,
----------------			SUM(Impressions)			AS Impressions, 
----------------			SUM(Clicks)					AS Clicks,
----------------			case when SUM(Impressions) >0 then
----------------			SUM(Clicks)*1.00/SUM(Impressions)*100   
----------------			else
----------------			0
----------------			end		AS ClickThroughsRatio,
----------------			SUM(Downloads)				AS Downloads,
----------------			SUM(SignUps)				AS SignUps,
----------------			SUM(Depositors)				AS Depositors,
----------------			SUM(Deposits)				AS Deposits,
----------------			SUM(GrossRevenue)			AS GrossRevenue,
----------------			SUM(BonusAdj)				AS BonusAdj,
----------------			SUM(Chargebacks)			AS Chargebacks,
----------------			SUM(BannedRevenue)			AS BannedRevenue,
----------------			SUM(UnbannedRevenue)		AS UnbannedRevenue,
----------------			SUM(NetRevenue)				AS NetRevenue, 
----------------			SUM(Earnings)				AS Earnings,
----------------			SUM(PerCommission)			AS PerCommission,
----------------			SUM(FlatCommission)			AS FlatCommission,
----------------			SUM(CPAAccounts)			AS CPAAccounts ,
----------------			SUM(ReferralCommission)		AS ReferralCommission,
----------------			SUM(ISNULL(PerCommission,0))  + SUM(ISNULL(FlatCommission,0))  + SUM(ISNULL(CPAAccounts,0))+ SUM(ISNULL(ReferralCommission,0)) AS TotalCommission
----------------	-----------------
----------------	INTO	#Merchant
----------------	-----------------
----------------	FROM
----------------	(
----------------		SELECT
----------------				a.MemberID,
----------------				a.MerchantID,
----------------				SUM(Impressions)			AS Impressions, 
----------------				SUM(Clicks)					AS Clicks,
----------------				case when SUM(Impressions) >0 then
----------------				SUM(Clicks)*1.00/SUM(Impressions)*100   
----------------				else
----------------				0
----------------				end		AS ClickThroughsRatio,
----------------				SUM(Downloads)				AS Downloads,
----------------				SUM(SignUps)				AS SignUps,
----------------				SUM(Depositors)				AS Depositors,
----------------				SUM(Deposits)				AS Deposits,
----------------				SUM(GrossRevenue)			AS GrossRevenue,
----------------				SUM(BonusAdj)				AS BonusAdj,
----------------				SUM(Chargebacks)			AS Chargebacks,
----------------				SUM(BannedRevenue)			AS BannedRevenue,
----------------				SUM(UnbannedRevenue)		AS UnbannedRevenue,
----------------				SUM(NetRevenue)				AS NetRevenue, 
----------------				SUM(Earnings)				AS Earnings,
----------------				0							AS PerCommission,
----------------				0							AS FlatCommission,
----------------				0							AS CPAAccounts ,
----------------				0							AS ReferralCommission,
----------------				0							AS TotalCommission
----------------		FROM
----------------				#Ear a  
----------------		GROUP BY 
----------------				a.MemberID		,	a.MemberName	,
----------------				a.Merchant		,	a.MerchantID
----------------		----------------------
----------------		UNION ALL
----------------		----------------------
----------------		SELECT 
----------------				MemberID,
----------------				MerchantID,
----------------				0					AS Impressions, 
----------------				0					AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				0					AS Downloads,
----------------				0					AS SignUps,
----------------				0					AS Depositors,
----------------				0					AS Deposits,
----------------				0					AS GrossRevenue,
----------------				0					AS BonusAdj,
----------------				0					AS Chargebacks,
----------------				0					AS BannedRevenue,
----------------				0					AS UnbannedRevenue,
----------------				0					AS NetRevenue, 
----------------				0					AS Earnings,
----------------				A.PERCCOMMISSION	AS PerCommission,
----------------				0					AS FlatCommission,
----------------				0					AS CPAAccounts ,
----------------				0					AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				DBO.FN_TB_COMMISSION_PERC_OLD (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID,MERCHANTID') AS A
----------------		-----------------------
----------------		UNION ALL
----------------		-----------------------
----------------		SELECT 
----------------				MemberID,
----------------				MerchantID,
----------------				0					AS Impressions, 
----------------				0					AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				0					AS Downloads,
----------------				0					AS SignUps,
----------------				0					AS Depositors,
----------------				0					AS Deposits,
----------------				0					AS GrossRevenue,
----------------				0					AS BonusAdj,
----------------				0					AS Chargebacks,
----------------				0					AS BannedRevenue,
----------------				0					AS UnbannedRevenue,
----------------				0					AS NetRevenue, 
----------------				0					AS Earnings,
----------------				0					AS PerCommission,
----------------				0					AS FlatCommission,
----------------				0					AS CPAAccounts ,
----------------				REFCOMMISSION		AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				DBO.[FN_TB_COMMISSION_REF_OLD] (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID,MERCHANTID') AS B
----------------		-----------------------
----------------		UNION ALL
----------------		-----------------------
----------------		SELECT
----------------				MemberID,
----------------				MerchantID,
----------------				0					AS Impressions, 
----------------				0					AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				0					AS Downloads,
----------------				0					AS SignUps,
----------------				0					AS Depositors,
----------------				0					AS Deposits,
----------------				0					AS GrossRevenue,
----------------				0					AS BonusAdj,
----------------				0					AS Chargebacks,
----------------				0					AS BannedRevenue,
----------------				0					AS UnbannedRevenue,
----------------				0					AS NetRevenue, 
----------------				0					AS Earnings,
----------------				0					AS PerCommission,
----------------				FLATCOMMISSION		AS FlatCommission,
----------------				0					AS CPAAccounts ,
----------------				0					AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				DBO.FN_TB_COMMISSION_FLAT_OLD (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID,MERCHANTID') AS C
----------------	) AS Mer
----------------	JOIN
----------------			#TEMP_MEMBER_MERCHANT B
----------------	ON
----------------			Mer.MemberId		=	B.MemberId
----------------	JOIN
----------------			TB_AFF_MERCHANT C
----------------	ON
----------------			Mer.MerchantId		=	C.MerchantId
----------------	WHERE 
----------------			Mer.MerchantId		=	B.merchantID
----------------	AND		
----------------			C.STATUS			=	1
----------------	GROUP BY 
----------------			B.MerchantKey, Mer.MemberID, B.MemberName, C.MerchantName, Mer.MerchantID
----------------------------------------
-----------------------
----------------------------------------
----------------	SELECT
----------------			MerchantKey,
----------------			MemberID,
----------------			MemberName,
----------------			Merchant,
----------------			MerchantID,
----------------			SUM(Impressions)					AS Impressions, 
----------------			SUM(Clicks)							AS Clicks,
----------------			CASE WHEN SUM(Impressions) > 0 THEN
----------------			SUM(Clicks)*1.00/SUM(Impressions)*100   
----------------			ELSE
----------------			0
----------------			END									AS ClickThroughsRatio,
----------------			SUM(Downloads)						AS Downloads,
----------------			SUM(SignUps)						AS SignUps,
----------------			SUM(Depositors)						AS Depositors,
----------------			SUM(Deposits)						AS Deposits,
----------------			SUM(GrossRevenue)					AS GrossRevenue,
----------------			SUM(BonusAdj)						AS BonusAdj,
----------------			SUM(Chargebacks)					AS Chargebacks,
----------------			SUM(BannedRevenue)					AS BannedRevenue,
----------------			SUM(UnbannedRevenue)				AS UnbannedRevenue,
----------------			SUM(NetRevenue)						AS NetRevenue, 
----------------			SUM(Earnings)						AS Earnings,
----------------			SUM(ISNULL(PerCommission,0))		AS PerCommission,
----------------			SUM(ISNULL(FlatCommission,0))		AS FlatCommission,
----------------			SUM(ISNULL(CPAAccounts,0))			AS CPAAccounts,
----------------			SUM(ISNULL(ReferralCommission,0))	AS ReferralCommission,
----------------			SUM(ISNULL(PerCommission,0))  + SUM(ISNULL(FlatCommission,0))  + SUM(ISNULL(CPAAccounts,0))+ SUM(ISNULL(ReferralCommission,0))  AS TotalCommission
----------------	FROM 
----------------			#Merchant 
----------------	GROUP BY
----------------			MerchantKey	, MemberID,	MemberName ,
----------------			Merchant	, MerchantID
----------------
--------------------------------------	
-------------------------------------- MERCHANT WISE EARNINGS REPORT IN MONTH YEAR FORMAT
--------------------------------------
----------------	SELECT 
----------------			DISTINCT	
----------------			IDENTITY (INT,1,1) AS MonthYearKey,
----------------			MemberID,
----------------			Membername, 
----------------			MerchantId,
----------------			dbo.fn_FirstofaMonth(Date) AS DATE
----------------	-------------------------
----------------	INTO	#TEMP_MERCHANT_MONTHYEAR
----------------	-------------------------
----------------	FROM 
----------------			#Ear
----------------------------------------
-----------------------
----------------------------------------
----------------
----------------	SELECT 
----------------			D.MonthYearKey,	
----------------			C.MerchantKey,
----------------			A.MemberID,
----------------			C.MemberName,
----------------			B.MerchantName AS Merchant,
----------------			A.Merchantid,
----------------			LEFT(LEFT(dbo.fn_FirstofaMonth(A.Date),11),3)+SPACE(1)+RIGHT(LEFT(dbo.fn_FirstofaMonth(A.Date),11),4) AS MonthYear,
----------------			SUM(Impressions)					AS Impressions, 
----------------			SUM(Clicks)							AS Clicks,
----------------			SUM(ClickThroughsRatio)				AS ClickThroughsRatio,
----------------			SUM(Downloads)						AS Downloads,
----------------			SUM(SignUps)						AS SignUps,
----------------			SUM(Depositors)						AS  Depositors,
----------------			SUM(Deposits)						AS Deposits,
----------------			SUM(GrossRevenue)					AS GrossRevenue,
----------------			SUM(BonusAdj)						AS BonusAdj,
----------------			SUM(Chargebacks)					AS Chargebacks,
----------------			SUM(BannedRevenue)					AS BannedRevenue,
----------------			SUM(UnbannedRevenue)				AS UnbannedRevenue,
----------------			SUM(NetRevenue)						AS NetRevenue, 
----------------			SUM(Earnings)						AS Earnings,
----------------			SUM(PerCommission)					AS PerCommission,
----------------			SUM(FlatCommission)					AS FlatCommission,
----------------			SUM(CPAAccounts)					AS CPAAccounts,
----------------			SUM(ReferralCommission)				AS ReferralCommission,
----------------			SUM(ISNULL(PerCommission,0))  + SUM(ISNULL(FlatCommission,0))  + SUM(ISNULL(CPAAccounts,0))+ SUM(ISNULL(ReferralCommission,0)) AS TotalCommission
----------------	-----------------	
----------------	INTO	#Month
----------------	-----------------
----------------	FROM
----------------	(
----------------			SELECT 
----------------					MemberID,
----------------					Merchantid,
----------------					dbo.fn_FirstofaMonth(Date)		AS DATE,
----------------					SUM(Impressions)					AS Impressions, 
----------------					SUM(Clicks)							AS Clicks,
----------------					CASE WHEN SUM(Impressions) >0 THEN
----------------					SUM(Clicks)*1.00/SUM(Impressions)*100    
----------------					ELSE
----------------					0
----------------					END									AS ClickThroughsRatio,
----------------					SUM(Downloads)						AS Downloads,
----------------					SUM(SignUps)						AS SignUps,
----------------					SUM(Depositors)						AS  Depositors,
----------------					SUM(Deposits)						AS Deposits,
----------------					SUM(GrossRevenue)					AS GrossRevenue,
----------------					SUM(BonusAdj)						AS BonusAdj,
----------------					SUM(Chargebacks)					AS Chargebacks,
----------------					SUM(BannedRevenue)					AS BannedRevenue,
----------------					SUM(UnbannedRevenue)				AS UnbannedRevenue,
----------------					SUM(NetRevenue)						AS NetRevenue, 
----------------					SUM(Earnings)						AS Earnings,
----------------					0									AS PerCommission,
----------------					0									AS FlatCommission,
----------------					0									AS CPAAccounts,
----------------					0									AS ReferralCommission,
----------------					0									AS TotalCommission
----------------			FROM
----------------					#Ear 
----------------			GROUP BY
----------------						MemberID, Merchantid, dbo.fn_FirstofaMonth(Date)
----------------			-----------------------
----------------			UNION ALL
----------------			-----------------------
----------------			SELECT 
----------------					A.MemberID,
----------------					A.Merchantid,
----------------					A.date,
----------------					0					AS Impressions, 
----------------					0					AS Clicks,
----------------					0					AS ClickThroughsRatio,
----------------					0					AS Downloads,
----------------					0					AS SignUps,
----------------					0					AS  Depositors,
----------------					0					AS Deposits,
----------------					0					AS GrossRevenue,
----------------					0					AS BonusAdj,
----------------					0					AS Chargebacks,
----------------					0					AS BannedRevenue,
----------------					0					AS UnbannedRevenue,
----------------					0					AS NetRevenue, 
----------------					0					AS Earnings,
----------------					A.PERCCOMMISSION	AS PerCommission,
----------------					0					AS FlatCommission,
----------------					0					AS CPAAccounts,
----------------					0					AS ReferralCommission,
----------------					0					AS TotalCommission
----------------			FROM
----------------					DBO.FN_TB_COMMISSION_PERC_OLD (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID,MERCHANTID,MONTH') AS A
----------------			-----------------------
----------------			UNION ALL
----------------			-----------------------		
----------------			SELECT 
----------------					A.MemberID,
----------------					A.Merchantid,
----------------					A.date,
----------------					0					AS Impressions, 
----------------					0					AS Clicks,
----------------					0					AS ClickThroughsRatio,
----------------					0					AS Downloads,
----------------					0					AS SignUps,
----------------					0					AS  Depositors,
----------------					0					AS Deposits,
----------------					0					AS GrossRevenue,
----------------					0					AS BonusAdj,
----------------					0					AS Chargebacks,
----------------					0					AS BannedRevenue,
----------------					0					AS UnbannedRevenue,
----------------					0					AS NetRevenue, 
----------------					0					AS Earnings,
----------------					0					AS PerCommission,
----------------					0					AS FlatCommission,
----------------					0					AS CPAAccounts,
----------------					A.REFCOMMISSION		AS ReferralCommission,
----------------					0					AS TotalCommission
----------------			FROM
----------------					DBO.[FN_TB_COMMISSION_REF_OLD] (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID,MERCHANTID,MONTH') AS A
----------------			-----------------------
----------------			UNION ALL
----------------			-----------------------
----------------			SELECT 
----------------					A.MemberID,
----------------					A.Merchantid,
----------------					A.date,
----------------					0					AS Impressions, 
----------------					0					AS Clicks,
----------------					0					AS ClickThroughsRatio,
----------------					0					AS Downloads,
----------------					0					AS SignUps,
----------------					0					AS  Depositors,
----------------					0					AS Deposits,
----------------					0					AS GrossRevenue,
----------------					0					AS BonusAdj,
----------------					0					AS Chargebacks,
----------------					0					AS BannedRevenue,
----------------					0					AS UnbannedRevenue,
----------------					0					AS NetRevenue, 
----------------					0					AS Earnings,
----------------					0					AS PerCommission,
----------------					0					AS FlatCommission,
----------------					0					AS CPAAccounts,
----------------					A.FLATCOMMISSION	AS ReferralCommission,
----------------					0					AS TotalCommission
----------------			FROM
----------------					DBO.[FN_TB_COMMISSION_FLAT_OLD]  (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID,MERCHANTID,MONTH') AS A
----------------	) AS A
----------------
----------------	JOIN
----------------			TB_AFF_MERCHANT B
----------------	ON
----------------			A.MerchantId	=	B.Merchantid 
----------------	JOIN
----------------			#TEMP_MEMBER_MERCHANT C
----------------	ON
----------------			A.memberid		=	C.memberid
----------------	JOIN
----------------			#TEMP_MERCHANT_MONTHYEAR D
----------------	ON	
----------------			A.memberid		=	D.memberid 
----------------	WHERE 
----------------			A.merchantid	=	C.merchantid 
----------------	AND		
----------------			A.merchantID	=	D.merchantID
----------------	AND		
----------------			dbo.fn_FirstofaMonth(A.Date)=D.date 
----------------	AND		
----------------			B.status		=	1
----------------	GROUP BY 
----------------			D.MonthYearKey,	C.MerchantKey, A.MemberID, C.MemberName,
----------------			B.MerchantName,	A.Merchantid, dbo.fn_FirstofaMonth(A.Date)	
----------------------------------------
-----------------------
----------------------------------------
----------------	SELECT
----------------			MonthYearKey,	
----------------			MerchantKey,
----------------			MemberID,
----------------			MemberName,
----------------			Merchant,
----------------			Merchantid,
----------------			MonthYear,
----------------			SUM(Impressions)					AS Impressions, 
----------------			SUM(Clicks)							AS Clicks,
----------------			CASE WHEN SUM(Impressions) >0 THEN
----------------			SUM(Clicks)*1.00/SUM(Impressions)*100    
----------------			ELSE
----------------			0
----------------			END									AS ClickThroughsRatio,
----------------			SUM(Downloads)						AS Downloads,
----------------			SUM(SignUps)						AS SignUps,
----------------			SUM(Depositors)						AS  Depositors,
----------------			SUM(Deposits)						AS Deposits,
----------------			SUM(GrossRevenue)					AS GrossRevenue,
----------------			SUM(BonusAdj)						AS BonusAdj,
----------------			SUM(Chargebacks)					AS Chargebacks,
----------------			SUM(BannedRevenue)					AS BannedRevenue,
----------------			SUM(UnbannedRevenue)				AS UnbannedRevenue,
----------------			SUM(NetRevenue)						AS NetRevenue, 
----------------			SUM(Earnings)						AS Earnings,
----------------			SUM(ISNULL(PerCommission,0))		AS PerCommission,
----------------			SUM(ISNULL(FlatCommission,0))		AS FlatCommission,
----------------			SUM(ISNULL(CPAAccounts,0))			AS CPAAccounts,
----------------			SUM(ISNULL(ReferralCommission,0))	AS ReferralCommission,
----------------			SUM(ISNULL(PerCommission,0))  + SUM(ISNULL(FlatCommission,0))  + SUM(ISNULL(CPAAccounts,0))+ SUM(ISNULL(ReferralCommission,0)) TotalCommission
----------------	FROM 
----------------			#Month
----------------	GROUP BY 
----------------			MonthYearKey, MerchantKey, MemberID,MemberName,	Merchant, Merchantid,MonthYear
----------------
--------------------------------------
-------------------------------------- DATE WISE MERCHANT  EARNING REPORT 
--------------------------------------
----------------	SELECT 
----------------				D.MonthYearKey,			
----------------				C.MerchantKey,
----------------				A.MemberID,
----------------				C.MemberName,
----------------				B.MerchantName AS Merchant,
----------------				A.Merchantid,
----------------				LEFT(LEFT(dbo.fn_FirstofaMonth(A.Date),11),3)	+	SPACE(1)	+	RIGHT(LEFT(dbo.fn_FirstofaMonth(A.Date),11),4) AS MonthYear,
----------------				RIGHT(LEFT(A.Date,6),2) +space(1)	+	LEFT(LEFT(A.Date,11),3)  AS Date,
----------------				SUM(Impressions)							AS Impressions, 
----------------				SUM(Clicks)									AS Clicks,
----------------				SUM(ClickThroughsRatio)						AS ClickThroughsRatio,
----------------				SUM(Downloads)								AS Downloads,
----------------				SUM(SignUps)								AS SignUps,
----------------				SUM(Depositors)								AS Depositors,
----------------				SUM(Deposits)								AS Deposits,
----------------				SUM(GrossRevenue)							AS GrossRevenue,
----------------				SUM(BonusAdj)								AS BonusAdj,
----------------				SUM(Chargebacks)							AS Chargebacks,
----------------				SUM(BannedRevenue)							AS BannedRevenue,
----------------				SUM(UnbannedRevenue)						AS UnbannedRevenue,
----------------				SUM(NetRevenue)								AS NetRevenue, 
----------------				SUM(Earnings)								AS Earnings,
----------------				SUM(PerCommission)							AS PerCommission,
----------------				SUM(FlatCommission)							AS FlatCommission,
----------------				SUM(CPAAccounts)							AS CPAAccounts,
----------------				SUM(ReferralCommission)						AS ReferralCommission,
----------------				SUM(ISNULL(PerCommission,0))  + SUM(ISNULL(FlatCommission,0))  + SUM(ISNULL(ReferralCommission,0)) AS TotalCommission
----------------	-----------------
----------------	INTO 
----------------				#Date
----------------	-----------------
----------------	FROM
----------------	(
----------------		SELECT
----------------				MemberID,
----------------				Merchantid,
----------------				DATE										AS DATE,
----------------				SUM(Impressions)							AS Impressions, 
----------------				SUM(Clicks)									AS Clicks,
----------------				CASE WHEN SUM(Impressions) >0 THEN
----------------				SUM(Clicks)*1.00/SUM(Impressions)*100    
----------------				ELSE
----------------				0
----------------				END											AS ClickThroughsRatio,
----------------				SUM(Downloads)								AS Downloads,
----------------				SUM(SignUps)								AS SignUps,
----------------				SUM(Depositors)								AS Depositors,
----------------				SUM(Deposits)								AS Deposits,
----------------				SUM(GrossRevenue)							AS GrossRevenue,
----------------				SUM(BonusAdj)								AS BonusAdj,
----------------				SUM(Chargebacks)							AS Chargebacks,
----------------				SUM(BannedRevenue)							AS BannedRevenue,
----------------				SUM(UnbannedRevenue)						AS UnbannedRevenue,
----------------				SUM(NetRevenue)								AS NetRevenue, 
----------------				SUM(Earnings)								AS Earnings,
----------------				0											AS PerCommission,
----------------				0											AS FlatCommission,
----------------				0											AS CPAAccounts,
----------------				0											AS ReferralCommission,
----------------				0											AS TotalCommission
----------------		FROM
----------------				#Ear
----------------		GROUP BY
----------------				MemberID, Merchantid, Date			
----------------		---------------------------
----------------		UNION ALL
----------------		---------------------------
----------------		SELECT
----------------				A.MemberID,
----------------				A.Merchantid,
----------------				A.DATE				AS DATE,
----------------				0					AS Impressions, 
----------------				0					AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				0					AS Downloads,
----------------				0					AS SignUps,
----------------				0					AS Depositors,
----------------				0					AS Deposits,
----------------				0					AS GrossRevenue,
----------------				0					AS BonusAdj,
----------------				0					AS Chargebacks,
----------------				0					AS BannedRevenue,
----------------				0					AS UnbannedRevenue,
----------------				0					AS NetRevenue, 
----------------				0					AS Earnings,
----------------				A.PERCCOMMISSION	AS PerCommission,
----------------				0					AS FlatCommission,
----------------				0					AS CPAAccounts,
----------------				0					AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				DBO.FN_TB_COMMISSION_PERC_OLD (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID,MERCHANTID,DATE') AS A
----------------		---------------------------
----------------		UNION ALL
----------------		---------------------------
----------------		SELECT
----------------				A.MemberID,
----------------				A.Merchantid,
----------------				A.DATE				AS DATE,
----------------				0					AS Impressions, 
----------------				0					AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				0					AS Downloads,
----------------				0					AS SignUps,
----------------				0					AS Depositors,
----------------				0					AS Deposits,
----------------				0					AS GrossRevenue,
----------------				0					AS BonusAdj,
----------------				0					AS Chargebacks,
----------------				0					AS BannedRevenue,
----------------				0					AS UnbannedRevenue,
----------------				0					AS NetRevenue, 
----------------				0					AS Earnings,
----------------				0					AS PerCommission,
----------------				0					AS FlatCommission,
----------------				0					AS CPAAccounts,
----------------				A.REFCOMMISSION		AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				DBO.[FN_TB_COMMISSION_REF_OLD] (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID,MERCHANTID,DATE') AS A
----------------		---------------------------
----------------		UNION ALL
----------------		---------------------------
----------------		SELECT
----------------				A.MemberID,
----------------				A.Merchantid,
----------------				A.DATE				AS DATE,
----------------				0					AS Impressions, 
----------------				0					AS Clicks,
----------------				0					AS ClickThroughsRatio,
----------------				0					AS Downloads,
----------------				0					AS SignUps,
----------------				0					AS Depositors,
----------------				0					AS Deposits,
----------------				0					AS GrossRevenue,
----------------				0					AS BonusAdj,
----------------				0					AS Chargebacks,
----------------				0					AS BannedRevenue,
----------------				0					AS UnbannedRevenue,
----------------				0					AS NetRevenue, 
----------------				0					AS Earnings,
----------------				0					AS PerCommission,
----------------				A.FLATCOMMISSION	AS FlatCommission,
----------------				0					AS CPAAccounts,
----------------				0					AS ReferralCommission,
----------------				0					AS TotalCommission
----------------		FROM
----------------				DBO.FN_TB_COMMISSION_FLAT_OLD (@MERCHANTID,@MEMBERID,@startDate,@endDate,'MEMBERID,MERCHANTID,DATE') AS A
----------------
----------------	) AS A
----------------	JOIN
----------------				TB_AFF_MERCHANT B
----------------	ON
----------------				A.MerchantId	=	B.Merchantid 
----------------	JOIN
----------------				#TEMP_MEMBER_MERCHANT C
----------------	ON	
----------------				A.memberid		=	C.memberid 
----------------	JOIN
----------------				#TEMP_MERCHANT_MONTHYEAR D
----------------	ON
----------------				A.memberid		=	D.memberid 
----------------	WHERE 
----------------				dbo.fn_FirstofaMonth(A.Date)	=	D.date 
----------------	AND		
----------------				A.merchantid	=	C.merchantid 
----------------	AND		
----------------				A.merchantID	=	D.merchantID
----------------	AND		
----------------				B.status		=	1
----------------	GROUP BY 
----------------				D.MonthYearKey,	C.MerchantKey, A.MemberID, C.MemberName, B.MerchantName, A.Merchantid,
----------------				A.Date,	dbo.fn_FirstofaMonth(A.Date)
----------------------------------------
-----------------------
----------------------------------------
----------------	SELECT
----------------			MonthYearKey,	
----------------			MerchantKey,
----------------			MemberID,
----------------			MemberName,
----------------			Merchant,
----------------			Merchantid,
----------------			MonthYear,
----------------			Date,
----------------			SUM(Impressions)							AS Impressions, 
----------------			SUM(Clicks)									AS Clicks,
----------------			CASE WHEN SUM(Impressions) >0 THEN
----------------			SUM(Clicks)*1.00/SUM(Impressions)*100    
----------------			ELSE
----------------			0
----------------			END											AS ClickThroughsRatio,
----------------			SUM(Downloads)								AS Downloads,
----------------			SUM(SignUps)								AS SignUps,
----------------			SUM(Depositors)								AS Depositors,
----------------			SUM(Deposits)								AS Deposits,
----------------			SUM(GrossRevenue)							AS GrossRevenue,
----------------			SUM(BonusAdj)								AS BonusAdj,
----------------			SUM(Chargebacks)							AS Chargebacks,
----------------			SUM(BannedRevenue)							AS BannedRevenue,
----------------			SUM(UnbannedRevenue)						AS UnbannedRevenue,
----------------			SUM(NetRevenue)								AS NetRevenue, 
----------------			SUM(Earnings)								AS Earnings,
----------------			SUM(ISNULL(PerCommission,0))				AS PerCommission,
----------------			SUM(ISNULL(FlatCommission,0))				AS FlatCommission,
----------------			SUM(ISNULL(CPAAccounts,0))					AS CPAAccounts,
----------------			SUM(ISNULL(ReferralCommission,0))			AS ReferralCommission,
----------------			SUM(ISNULL(PerCommission,0))  + SUM(ISNULL(FlatCommission,0))  + SUM(ISNULL(CPAAccounts,0))+ SUM(ISNULL(ReferralCommission,0)) TotalCommission
----------------	FROM 
----------------			#Date
----------------	GROUP BY 
----------------			MonthYearKey, MerchantKey, MemberID, MemberName, Merchant, Merchantid, MonthYear, Date
----------------------------------------
-----------------------
----------------------------------------
----------------	SELECT		
----------------			MemberID,
----------------			MemberName,
----------------			Merchant,
----------------			Merchantid,
----------------			MonthYear,
----------------			Date,
----------------			SUM(Impressions)						AS Impressions, 
----------------			SUM(Clicks)								AS Clicks,
----------------			CASE WHEN SUM(Impressions) >0 THEN
----------------			SUM(Clicks)*1.00/SUM(Impressions)*100    
----------------			ELSE
----------------			0
----------------			END										AS ClickThroughsRatio,
----------------			SUM(Downloads)							AS Downloads,
----------------			SUM(SignUps)							AS SignUps,
----------------			SUM(Depositors)							AS Depositors,
----------------			SUM(Deposits)							AS Deposits,
----------------			SUM(GrossRevenue)						AS GrossRevenue,
----------------			SUM(BonusAdj)							AS  BonusAdj,
----------------			SUM(Chargebacks)						AS Chargebacks,
----------------			SUM(BannedRevenue)						AS BannedRevenue,
----------------			SUM(UnbannedRevenue)					AS UnbannedRevenue,
----------------			SUM(NetRevenue)							AS NetRevenue, 
----------------			SUM(Earnings)							AS  Earnings,
----------------			SUM(ISNULL(PerCommission,0))			AS PerCommission,
----------------			SUM(ISNULL(FlatCommission,0))			AS  FlatCommission,
----------------			SUM(ISNULL(CPAAccounts,0))				AS  CPAAccounts,
----------------			SUM(ISNULL(ReferralCommission,0))		AS ReferralCommission,
----------------			SUM(ISNULL(PerCommission,0))  + SUM(ISNULL(FlatCommission,0))  + SUM(ISNULL(CPAAccounts,0))+ SUM(ISNULL(ReferralCommission,0)) TotalCommission
----------------	FROM 
----------------			#Date
----------------	GROUP BY 
----------------			MemberID, MemberName, Merchant, Merchantid,	MonthYear, Date
END

Hi tapojjwal,

tapojjwal:

My question is that why the difference of time is coming so much. Is there any faster way to connect the database and getting the result immediately. For reference I am giving some sample of my code.

Generally, for this kind of question, you have to find out the exact slow part. If you're sure the part is caused in database, you could get part of the data to improve the performance. Basically, if the data in database is not big, it'll be very quick in normal ways. Here's a sample:

http://asp.dotnetheaven.com/howto/doc/adoplus/sqldtreader.aspx

Thanks.

>The db contains more than 5 lakhs of records.

What is a lakhs please?

1 Lakhs = 1,00,000

tapojjwal:

1 Lakhs = 1,00,000

100, 000 or 1,000,000 ?

TATWORTH:

tapojjwal:

1 Lakhs = 1,00,000

100, 000 or 1,000,000 ?

I Think you got the sense... That is 100,000 (One hundred thousand) Now Is that clear to you ? I have mentined the amount for making understandable to the no of rows having my table...not to discuss on a certain unit.

Thanks for your reply.

>The db contains more than 500,000 records. I am having different database server. So while connecting the db by sql client and executing the query I am getting the records within 22 Seconds but while trying to connect the db by asp.net and trying to execute the same query by giving a breakpoint in my code behind I am getting the result within more than 3 minutes.

>My question is that why the difference of time is coming so much. Is there any faster way to connect the database and getting the result immediately. For reference I am giving some sample of my code.

Are these two times measured against the same database on the same server?

I notice that the date parameters are nvarchar(50) and not datetime - are you able to store the dates as datetime column (or if on SQL2008 as a Date column)?

>

1.                     WHERE  
2.                             ((@MERCHANTID   = 0 AND A.merchantid    >    0)  
3.                     OR   
4.                             (@MERCHANTID    > 0 AND A.merchantid =   @MERCHANTID))  
5.                     AND   
6.                             ((@MEMBERID     = 0 AND A.memberid      >    0)  
7.                     OR    
8.                             (@MEMBERID      > 0 AND A.memberid       =   @MEMBERID))  
9.                     AND   
10.                             A.Date          BETWEEN @startDate      AND @endDate 
    

Do you have a covering index on merchantid, memberid and Date ?

Are merchantid and memberid such that one is filled in and the other is not? If so two queries and two indexes (merchantid and Date,  memberid and Date) should give give an even faster select.

Is the temp database for the server on the same disk as the rest of the data?