Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Binary Tree
 Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 4

vedjha
Posting Yak Master

India
228 Posts

Posted - 07/31/2009 :  12:12:26  Show Profile  Visit vedjha's Homepage  Reply with Quote
Thanks a lot Peso sir,It is working.
But I have to run it online . approx 1000 no of user visits sites simultaneously.
We are Creating a temporary table #temp. will it mismatch data from one user to another when they submit request simultaneously.
i have created a procedure with the help of your advise query as:




create proc [prcLeftProcess] @sid varchar(9),@fromdate datetime,@endDate datetime
as
begin
if exists (select * from members where vaid=@sid)
begin


CREATE TABLE #Temp
(
vaid varchar(20),
xID varchar(20),
dtdoj datetime
)
INSERT #Temp
(
vaid,
xID,
dtdoj
)
SELECT vaid,vspid as xID,dtdoj
FROM members as m
where vspid is not null

union

SELECT vaid,vunderspon as xID,dtdoj
FROM members
where vunderspon is not null

create clustered index ix_temp ON #temp (xid, vaid,dtdoj)

;WITH catTree(vaID,dtdoj)
AS (
SELECT vaID,dtdoj
FROM members
WHERE (vspid = @sid or vunderspon = @sid)
and (cplacement = 'L' or vunderplacement = 'L')


UNION ALL

SELECT d.vaid,d.dtdoj
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
)

SELECT distinct vaid
FROM CatTree
where dtdoj between @fromdate and @enddate
order by vaid
drop table #temp




end

else

begin

print 'Please check your ID'

end

end


executing:
[prcLeftProcess] M100002 ,'1/1/2009','1/1/2010'

for this user ID ("M100002") having more levels
and it takes 4 min 35 sec. is there any another option to process it more fast...

thank u sir...



Ved Prakash Jha
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 07/31/2009 :  13:25:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think I can come up with something.
Still, 4 minutes 35 seconds is a lot better than 1 hour 6 minutes and 23 seconds, right?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 07/31/2009 :  13:45:11  Show Profile  Visit vedjha's Homepage  Reply with Quote
Yes.. Thanks a lot sir. :)


Ved Prakash Jha
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 07/31/2009 :  14:22:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I am interested in why a query run on my laptop with SQL Server 2008 Developer Edition runs in less than a second, and same query is run on 4 minutes on your production server.

Something doesn't add up.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 07/31/2009 :  14:34:22  Show Profile  Visit vedjha's Homepage  Reply with Quote
I am using SQL server 2005 enterprise edition

Ved Prakash Jha
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 07/31/2009 :  14:35:44  Show Profile  Visit vedjha's Homepage  Reply with Quote
please process this query in your Laptop:

[prcLeftProcess] M100002 ,'1/1/2009','1/1/2010'

Ved Prakash Jha
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 07/31/2009 :  16:52:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Subsecond performance!
ALTER PROCEDURE prcLeftProcess
(
	@ID CHAR(7),
	@FromDate DATETIME,
	@EndDate DATETIME
)
AS

SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM Members WHERE vaID = @ID)
	BEGIN
		PRINT 'Please check your ID'
		RETURN -100
	END

CREATE TABLE	#Temp
		(
			vaID CHAR(7) NOT NULL,
			xID CHAR(7) NOT NULL,
			dtDOJ DATETIME NOT NULL,
			Anchor INT NOT NULL
		)

CREATE CLUSTERED INDEX IX_Temp ON #Temp (Anchor, xID, vaID, dtDOJ)

INSERT	#Temp
	(
		vaID,
		xID,
		dtDOJ,
		Anchor
	)
SELECT	vaID,
	vspID,
	dtDOJ,
	CASE
		WHEN 'L' IN (cPlacement, vUnderPlacement) AND vspID = @ID THEN 1
		ELSE 0
	END
FROM	Members
WHERE	vspID IS NOT NULL

UNION ALL

SELECT	vaID,
	vUnderSpon,
	dtDOJ,
	CASE
		WHEN 'L' IN (cPlacement, vUnderPlacement) AND vUnderSpon = @ID THEN 1
		ELSE 0
	END
FROM	Members 
WHERE	vUnderSpon IS NOT NULL

DECLARE	@Anchor INT

SET	@Anchor = 0

WHILE @@ROWCOUNT > 0
	BEGIN
		SET	@Anchor = @Anchor + 1

		UPDATE		t
		SET		t.Anchor = @Anchor + 1
		FROM		#Temp AS t
		INNER JOIN	#Temp AS x ON x.vaID = t.xID
					AND x.Anchor = @Anchor
		WHERE		t.Anchor = 0
	END

SELECT		vaID
FROM		#Temp
WHERE		dtDOJ BETWEEN @FromDate AND @EndDate
		AND Anchor > 0
GROUP BY	vaID
ORDER BY	vaID



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 07/31/2009 :  17:51:06  Show Profile  Visit vedjha's Homepage  Reply with Quote
ok
let me check


Ved Prakash Jha
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 07/31/2009 :  18:13:25  Show Profile  Visit vedjha's Homepage  Reply with Quote
wow! It is taking micro Second to Process Data.
Thank u sir. But I am not understanding The Term and Logic.
will you please explain me...
please.... :)


Ved Prakash Jha
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 07/31/2009 :  19:40:07  Show Profile  Visit vedjha's Homepage  Reply with Quote
please sir...

Ved Prakash Jha
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 07/31/2009 :  21:48:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The procedure has gone from 1 hour, 6 minutes and 23 seconds down to 0.1 of a second?
That is an improvement of nearly 40,000 times faster!



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 08/07/2009 :  13:33:47  Show Profile  Visit vedjha's Homepage  Reply with Quote
Hello Sir,
This Procedure is working fine.
may you tell me where #temp table is storing.
will it effect The System Memory when no of users increment on site.
please help me. its urgent...



Ved Prakash Jha
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 08/07/2009 :  13:42:02  Show Profile  Reply with Quote
all temp tables (#tables) are in the database: tempdb

Be One with the Optimizer
TG
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 08/07/2009 :  13:52:28  Show Profile  Visit vedjha's Homepage  Reply with Quote
I have a site who has 10,000 users. when i process payment these users then tempdb Database size becomes 15 GB and as I were terminate the process, it becomes 375 KB.
My Application is online. users are Visiting website regularly. This Procedure Satisfies me output but may be slow performance.
Is there any way to Reresh #temp table always by which I will get my output and process become fast...
please help me...

Ved Prakash Jha
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 08/07/2009 :  14:04:08  Show Profile  Visit vedjha's Homepage  Reply with Quote
Please help....

Ved Prakash Jha
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 08/07/2009 :  14:08:45  Show Profile  Reply with Quote
Are you trying to solve an existing problem OR are you anticipating a possible problem?
Are your user's constantly changing data in your [Members] table or is that relatively static?

Be One with the Optimizer
TG
Go to Top of Page

vedjha
Posting Yak Master

India
228 Posts

Posted - 08/07/2009 :  17:48:19  Show Profile  Visit vedjha's Homepage  Reply with Quote
I am trying to solve an existing problem. Data is not changing but no of users are increment

Ved Prakash Jha
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 08/28/2009 :  16:12:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If data is not changing, you problably should store the result of #temp table in a permanent table, and do a nighly recalculation.
This way all your users have to do is the last select statement againt the permanent table
SELECT		vaID
FROM		PermTable
WHERE		dtDOJ BETWEEN @FromDate AND @EndDate
		AND Anchor > 0
GROUP BY	vaID
ORDER BY	vaID



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 08/28/2009 :  16:30:33  Show Profile  Reply with Quote
Oh I forgot about this thread - Thanks Peter, that is exactly where I was going with the "static data" question

Be One with the Optimizer
TG
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 09/09/2009 :  07:13:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If the code runs in a fraction of a second, and there is 500 simultaneous users, there is no way the TempDB can grow to 15 GB.
It means all 500 users run the code EXACTLY the same time and generate 30 MB of data per user.
And with approximate 30 bytes per record in #Temp table it means you have 1 million records in #Temp table. Subsecond performance with 1 million records doesn't add up.

How many records are there in #Temp table before the WHILE clause?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Previous Page | Next Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000