Data Access and ObjectDataSource Control

Re: Speed Up Database Connectivity

tapojjwal — Thu, 30 Jul 2009 05:53:04 GMT

Hi TATWORTH,

Thanks for your valuable guidelines. I will try to follow all these steps for increasing performance of my sql server.

Thanks you

Re: Speed Up Database Connectivity

TATWORTH — Tue, 28 Jul 2009 10:36:28 GMT

tapojjwal:
> 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)?

Yes it is possible.( I am using Sql Server 2005)

I suggest you make the date column a DateTime or even a SmallDateTime (If only storing a date and a restriction of January 1, 1900, through June 6, 2079, then SmallDateTime is preferable as it takes less space.

Before doing the conversion run the following TSQL

SELECT Convert(SmallDateTime, DateCol) FROM tableName

This will check if all the data can be converted.

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

MerchantId and MemberId is Index(Primary key of the corresponding merchant and member details.). Date is not index but it is using for selecting the scheduled time span.

You need to create a non-unique secondary index of DateCol, MerchantId, MemberId and possibly some of the other columns involved in the select.

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

Yes both is in same disk.

If the business can afford it, move the temp database to a separate disk with its own controller. This will cut out conflicting IOs.

Re: Speed Up Database Connectivity

tapojjwal — Tue, 28 Jul 2009 09:47:14 GMT

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

Yes

> 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)?

Yes it is possible.( I am using Sql Server 2005)

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

MerchantId and MemberId is Index(Primary key of the corresponding merchant and member details.). Date is not index but it is using for selecting the scheduled time span.

>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.

No..These are Not Null fields..i.e Mandatory.

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

Yes both is in same disk.

Re: Speed Up Database Connectivity

TATWORTH — Tue, 28 Jul 2009 07:52:24 GMT

A further thought - if the web application connects to the database server using a SQL account (user name and password) rather than a windows account, try connecting from SSMS using that same account to run the query.

Re: Speed Up Database Connectivity

TATWORTH — Tue, 28 Jul 2009 06:39:12 GMT

>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)?

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

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?

Re: Speed Up Database Connectivity

tapojjwal — Tue, 28 Jul 2009 05:43:39 GMT

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.

Re: Speed Up Database Connectivity

TATWORTH — Mon, 27 Jul 2009 08:29:59 GMT

tapojjwal:
1 Lakhs = 1,00,000

100, 000 or 1,000,000 ?

Re: Speed Up Database Connectivity

tapojjwal — Mon, 27 Jul 2009 08:14:06 GMT

1 Lakhs = 1,00,000

Re: Speed Up Database Connectivity

TATWORTH — Mon, 27 Jul 2009 07:51:18 GMT

>The db contains more than 5 lakhs of records.

What is a lakhs please?

Re: Speed Up Database Connectivity

Wencui Qian - MSFT — Mon, 27 Jul 2009 07:36:16 GMT

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.

Re: Speed Up Database Connectivity

tapojjwal — Mon, 27 Jul 2009 06:50:02 GMT

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

Re: Speed Up Database Connectivity

mihir.mone — Mon, 06 Jul 2009 06:18:21 GMT

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.

Re: Speed Up Database Connectivity

Wencui Qian - MSFT — Mon, 06 Jul 2009 06:07:10 GMT

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.

Re: Speed Up Database Connectivity

TATWORTH — Sat, 04 Jul 2009 10:06:47 GMT

tapojjwal:
The report is simple gridview.

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

Re: Speed Up Database Connectivity

izharulislam — Fri, 03 Jul 2009 11:03:43 GMT

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 ?