What is it that takes 10 seconds in QA? Just one of the SELECT statements
(which one) or the entire script code? If the entire script code - how do
you supply the values that are passed to the procedure when you run the
script code directly in QA?
I didn't think that the keyword INTO was optional (INSERT INTO
#TempDownline)... Also, instead of using the IN predicate, use EXISTS with
a correlated subquery (much more efficient):
INSERT INTO #TempDownline (...)
Select ...
from CDINETINFO as C
where SponsorID EXISTS (Select DistribID from #TempDownLine as T
where T.LevelNumber = @LevelNumber
And T.DistribID = C.DistribID)
HTH,
Tore.
-----Original Message-----
From: Steve Abaffy [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 07, 2002 4:56 PM
To: ActiveServerPages
Subject: RE: SQL Query runs way faster in Query Window then as Stored
Pro cedure
that is 10 seconds before all rows are returned, and I do get all the rows,
20199 in all.
I am using SQL 2000 with SP 2 installed.
As far as the Execution plan all looks good. There is 31 loops in the
longest part of the stored procedure and each loop never asks for more than
2000 records at a time.
I thought I would include the SP here:
Set NoCount ON
Declare @NewLevelNumber int
Declare @LevelNumber int
Declare @CDIPIN float
Declare @mode int
Select @LevelNumber = 1
Select @mode = 1
Select @cdipin = 10501
Create Table #tempDownline (
DistribID float NOT NULL ,
FirstName varchar(50) NULL ,
LastName varchar(50) NULL ,
Title varchar(10) NULL ,
Phone varchar(20) NULL ,
Fax varchar(20) NULL ,
Email varchar(75) NULL ,
State varchar(5) NULL ,
Country varchar(10) NULL ,
AppDate varchar(20) NULL ,
LastInvoiceDate varchar(20) NULL ,
PV money NULL ,
GV money NULL ,
PPV money NULL ,
PGV money NULL ,
ISDistrib varchar(1) NULL ,
SponsorID float NOT NULL ,
OrigSponsor float NOT NULL ,
LevelNumber int NOT NULL
)
Insert #TempDownline
(DistribID,FirstName,LastName,Title,Phone,Fax,Email,State,Country,AppDate,La
stInvoiceDate,PV,GV,PPV,PGV,ISDistrib,SponsorID,OrigSponsor,LevelNumber)
Select
DistribID,FirstName,LastName,Title,Phone,Fax,Email,State,Country,AppDate,Las
tInvoiceDate,PV,GV,PPV,PGV,ISDistrib,SponsorID,OrigSponsor,@LevelNumber from
CDINETINFO where DistribID=@CDIPIN
KeepGoing:
Select @NewLevelNumber = @LevelNumber + 1
Insert #TempDownline
(DistribID,FirstName,LastName,Title,Phone,Fax,Email,State,Country,AppDate,La
stInvoiceDate,PV,GV,PPV,PGV,ISDistrib,SponsorID,OrigSponsor,LevelNumber)
Select
DistribID,FirstName,LastName,Title,Phone,Fax,Email,State,Country,AppDate,Las
tInvoiceDate,PV,GV,PPV,PGV,ISDistrib,SponsorID,OrigSponsor,@NewLevelNumber
from
CDINETINFO where SponsorID in (Select DistribID from #TempDownLine where
LevelNumber = @LevelNumber)
IF @@RowCount <> 0
Begin
Select @LevelNumber = @LevelNumber + 1
Goto KeepGoing
END
if @mode = 1
Begin
Select * From #tempDownLine Order by LevelNumber
End
If @mode = 2
Begin
Select Count(state) as MemberCount,state, SUM(PV) as PV from
#TempDownLine
Group BY State ORDER BY STATE
End
drop table #tempDownline
Set NoCount OFF
GO
-----Original Message-----
From: Bostrup, Tore [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 07, 2002 3:17 PM
To: ActiveServerPages
Subject: RE: SQL Query runs way faster in Query Window then as Stored
Pro cedure
Is that 10 seconds before the first row is returned, or until all rows are
returned? What version of SQL Server, what service pack?
If you are building the query in the SP, the optimizer may have preselected
a non-optimal execution plan for the final query. Try viewing the execution
plan for executing the procedure with the execution plan for the query in
QA. That may give you a clue.
HTH,
Tore.
-----Original Message-----
From: Steve Abaffy [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 07, 2002 3:23 PM
To: ActiveServerPages
Subject: OT: SQL Query runs way faster in Query Window then as Stored
Procedure
Hello,
I was just wondering if anybody knew why the exact same piece of SQL would
run faster in the Query Analyzer window than it does as a stored procdure. I
mean in the query window it takes 10 seconds and as a stored procedure it
takes 9 minutes and 21 seconds????? Any help in this are would be greatly
appreciated.
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
