| Author |
 Topic  |
|
vedjha
Posting Yak Master
India
221 Posts |
 Posted - 07/31/2009 : 12:12:26
|
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 |
 |
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 07/31/2009 : 13:25:37
|
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" |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 07/31/2009 : 13:45:11
|
Yes.. Thanks a lot sir. :)
Ved Prakash Jha |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 07/31/2009 : 14:22:40
|
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" |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 07/31/2009 : 14:34:22
|
I am using SQL server 2005 enterprise edition
Ved Prakash Jha |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 07/31/2009 : 14:35:44
|
please process this query in your Laptop:
[prcLeftProcess] M100002 ,'1/1/2009','1/1/2010'
Ved Prakash Jha |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 07/31/2009 : 16:52:32
|
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" |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 07/31/2009 : 17:51:06
|
ok
let me check
Ved Prakash Jha |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 07/31/2009 : 18:13:25
|
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 |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 07/31/2009 : 19:40:07
|
please sir...
Ved Prakash Jha |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 07/31/2009 : 21:48:40
|
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" |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 08/07/2009 : 13:33:47
|
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 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
4658 Posts |
Posted - 08/07/2009 : 13:42:02
|
all temp tables (#tables) are in the database: tempdb
Be One with the Optimizer
TG |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 08/07/2009 : 13:52:28
|
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 |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 08/07/2009 : 14:04:08
|
Please help....
Ved Prakash Jha |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
4658 Posts |
Posted - 08/07/2009 : 14:08:45
|
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 |
|
|
|
vedjha
Posting Yak Master
India
221 Posts |
Posted - 08/07/2009 : 17:48:19
|
I am trying to solve an existing problem. Data is not changing but no of users are increment
Ved Prakash Jha |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 08/28/2009 : 16:12:47
|
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 tableSELECT 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" |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
4658 Posts |
Posted - 08/28/2009 : 16:30:33
|
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 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 09/09/2009 : 07:13:06
|
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" |
|
|
|
Topic |
|
Binary Tree
