SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 StoredProc is slow from ASP but not from QA
 Forum Locked
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

larrykl
Yak Posting Veteran

67 Posts

Posted - 11/04/2004 :  07:00:10  Show Profile
P.S. all varialbes are of type int
Go to Top of Page

jkaldon
Starting Member

USA
13 Posts

Posted - 11/22/2004 :  15:27:04  Show Profile  Send jkaldon a Yahoo! Message
Interestingly, I have what seems to be this exact same problem. I can run a query in QA with speedy results, but when I run it from an ado or ado.net client, it MAY hang for a prolonged period of time using 50% cpu (dual processor amd system). Interestingly, if I drop and recreate the procedure, the query runs in its usual 10 seconds.

Here is the relevant procedure code...hope it's readable:


CREATE procedure dbo.spPF_Stats_Detail(@DateStart datetime, @DateEnd datetime, @SortBy int, @ShowAdmin bit=0)
as
begin
set nocount on
declare @HideAdminChar char(1)

if(@ShowAdmin=0)
set @HideAdminChar = '*'
else
set @HideAdminChar = ''

declare @StatsDetail table (AgentID int, Agent varchar(50), CampaignID int, Campaign varchar(50), Days int, Sales int, Calls int, SalesAmt decimal(19,2), Payout decimal(19,2), Hours decimal(19,2), AllDays int, AllSales int, AllCalls int, AllSalesAmt decimal(19,2), AllPayout decimal(19,2), AllHours decimal(19,2), Hourly decimal(19,2), CPH decimal(19,2), ConversionRate decimal(19,2), DailySales decimal(19,2), DailySalesAmt decimal(19,2), Daily decimal(19,2), CPD decimal(19,2), HPD decimal(19,2), HourlySales decimal(19,2))

declare @StatsSummary table ( CampaignID int, Campaign varchar(50), Days int, Sales int, Calls int, SalesAmt decimal(19,2), Payout decimal(19,2), Hours decimal(19,2), AllDays int, AllSales int, AllCalls int, AllSalesAmt decimal(19,2), AllPayout decimal(19,2), AllHours decimal(19,2), Hourly decimal(19,2), CPH decimal(19,2), ConversionRate decimal(19,2), DailySales decimal(19,2), DailySalesAmt decimal(19,2), Daily decimal(19,2), CPD decimal(19,2), HPD decimal(19,2), HourlySales decimal(19,2))

insert into @StatsDetail (AgentID, Agent, CampaignID, Campaign, Days, Sales, Calls, SalesAmt, Payout, Hours, AllDays, AllSales, AllCalls, AllSalesAmt, AllPayout, AllHours)

select

tblUser.zUserID as AgentID,

tblUser.zNameFirst + ' ' + tblUser.zNameLast as Agent,

tpf_agentcampaigns.CampaignID,

tblCampaign.zCampaignDescShort as ShortDescription,

isnull((select count(*) FROM dbo.fPF_AgentDetailCampaignCallsDaily(@ShowAdmin) t1 WHERE t1.AgentID=tblUser.zUserID AND t1.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND Calls>=5),0) as Days,

isnull((select count(*) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.AgentID=tblUser.zUserID AND tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND CallResultID in (17,18) AND len(ExcludeReason)=0),0) as Sales,

isnull((select count(*) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.AgentID=tblUser.zUserID AND tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND WasDialed > 0),0) as Calls,

isnull((select sum(Sale) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.AgentID=tblUser.zUserID AND tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND len(ExcludeReason)=0),0) as SalesAmt,

isnull((select sum(Payout*(100/PayoutPercent)) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.AgentID=tblUser.zUserID AND tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND CallResultID != 0 AND len(ExcludeReason)=0),0) as Payout,

isnull((select sum(TimeWorked) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.AgentID=tblUser.zUserID AND tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd), 0) / cast(3600 as money) as Hours,

isnull((select count(*) FROM dbo.fPF_AgentDetailCampaignCallsDaily(@ShowAdmin) t1 WHERE t1.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND Calls>=5),0) as AllDays,

isnull((select count(*) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND CallResultID in (17,18) AND len(ExcludeReason)=0),0) as AllSales,

isnull((select count(*) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND WasDialed > 0),0) as AllCalls,

isnull((select sum(Sale) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND len(ExcludeReason)=0),0) as AllSalesAmt,

isnull((select sum(Payout*(100/PayoutPercent)) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd AND CallResultID != 0 AND len(ExcludeReason)=0),0) as AllPayout,

isnull((select sum(TimeWorked) FROM tPF_AgentIncome (index(1), nolock) WHERE tPF_AgentIncome.CampaignID=tpf_agentcampaigns.CampaignID AND DateNoTime between @DateStart and @DateEnd), 0) / 3600.0 as AllHours

from tblUser
inner join tpf_agentcampaigns on tblUser.zUserID=tpf_agentcampaigns.AgentID
inner join tblCampaign on tpf_agentcampaigns.CampaignID=tblCampaign.zCampaignID
WHERE left(tblUser.zCallerLogin,1) != @HideAdminChar

insert into @StatsSummary (CampaignID, Campaign, Days, Sales, Calls, SalesAmt, Payout, Hours)
select
CampaignID,
Campaign,
isnull((select count(*) FROM dbo.fPF_CampaignSummaryCallsDaily(@ShowAdmin) t2 WHERE t2.CampaignID=t1.CampaignID AND DateNoTime between @DateStart and @DateEnd AND Calls>=5),0),
sum(Sales),
sum(Calls),
sum(SalesAmt),
sum(Payout),
sum(Hours)
from @StatsDetail t1
group by CampaignID, Campaign

update @StatsSummary set
AllDays = isnull((select count(*) FROM dbo.fPF_CampaignSummaryCallsDaily(@ShowAdmin) t2 WHERE DateNoTime between @DateStart and @DateEnd AND Calls>=5),0),
AllSales = (select sum(t1.Sales) from @StatsDetail t1),
AllCalls = (select sum(t1.Calls) from @StatsDetail t1),
AllSalesAmt = (select sum(t1.SalesAmt) from @StatsDetail t1),
AllPayout = (select sum(t1.Payout) from @StatsDetail t1),
AllHours = (select sum(t1.Hours) from @StatsDetail t1)

update @StatsSummary set
Hourly = dbo.fPF_Divide(Payout, Hours),
CPH = dbo.fPF_Divide(Calls, Hours),
ConversionRate = dbo.fPF_Divide(Sales, Calls),
DailySales = dbo.fPF_Divide(Sales, Days),
DailySalesAmt = dbo.fPF_Divide(SalesAmt, Days),
Daily = dbo.fPF_Divide(Payout, Days),
CPD = dbo.fPF_Divide(Calls, Days),
HPD = dbo.fPF_Divide(Hours, Days),
HourlySales = dbo.fPF_Divide(SalesAmt, Hours)

update @StatsDetail set
Hourly = dbo.fPF_Divide(Payout, Hours),
CPH = dbo.fPF_Divide(Calls, Hours),
ConversionRate = dbo.fPF_Divide(Sales, Calls),
DailySales = dbo.fPF_Divide(Sales, Days),
DailySalesAmt = dbo.fPF_Divide(SalesAmt, Days),
Daily = dbo.fPF_Divide(Payout, Days),
CPD = dbo.fPF_Divide(Calls, Days),
HPD = dbo.fPF_Divide(Hours, Days),
HourlySales = dbo.fPF_Divide(SalesAmt, Hours)

SELECT *
FROM @StatsSummary item WHERE Hours > 0

SELECT *
FROM @StatsDetail item WHERE Hours > 0
end


Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/22/2004 :  15:50:03  Show Profile  Visit jsmith8858's Homepage
the SQL in itself is very inefficient that you've posted. Why the table variables, updates, and re-querying the same stuff over and over to return different summaries?

Also, you are inserting rows into a table variable to be returned later, but when returning row from the table variable you are filtering (hours > 0). why put those rows in the table variable(s) in the first place?

- Jeff
Go to Top of Page

jkaldon
Starting Member

USA
13 Posts

Posted - 11/22/2004 :  16:15:03  Show Profile  Send jkaldon a Yahoo! Message
Jeff,

I think we're in danger of going off topic here...but:

I agree that the code that I've posted is incredibly unreadable and not the way I would've done it two years ago. However, I have to say that the way it was originally written returned data in 5 mins....this new code returns data in 10 seconds. Not to say that it's perfect...just much better.

What I found, when using lots of CASE statements instead of subqueries, is that the CASE statements take an incredible amount of time to process 200,000 rows of data. Instead, it was better to pull only the related subset of data using subqueries.

Finally, the reason for storing the data in an intermediate table is that it is faster to return two summaries of the intermediate table than it is to requery the entire 200k rows at 10 seconds a query. Also, in a future revision, I will need to return two more summaries of the intermediate table: Avg and Best.

I do have a theory that might make it faster yet, but this thread isn't mine and I'd like to stick to the topic: Why does dropping and recreating the procedure cause a drastic decrease in execution time? Why does running the procedure from QA take only 10 seconds while it times out when run from an ADO/ADO.Net client?

- Josh
Go to Top of Page

jkaldon
Starting Member

USA
13 Posts

Posted - 11/22/2004 :  16:24:28  Show Profile  Send jkaldon a Yahoo! Message
BTW, I have also read somewhere else of problems with using the Stored Procedure parameter variables...does anyone know why this could be causing the problem? When the problem happens here again, I will also create local variables and try to see if that fixes the problem. I suspect that the recompile caused by the switch to localy declared variables is having the same effect as dropping an recreating the procedure. Thus, the problem is only fixed temporarily.

- Josh
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 11/22/2004 :  16:29:35  Show Profile  Visit robvolk's Homepage
From time to time, stored procedure plans in cache don't perform as well as they used to, or as well as a regular query with the same exact statements. Clearing the procedure cache, or recompiling the sproc, should fix it but sometimes doesn't. Dropping the sproc guarantees that a new plan gets compiled and used.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/22/2004 :  16:38:28  Show Profile
quote:
Originally posted by jkaldon

Jeff,

I think we're in danger of going off topic here...



Like that's never happened before...

I always prefer to drop and recreate...especially now with Nigels advice about ALTER on a table wasting space....



Brett

8-)
Go to Top of Page

jkaldon
Starting Member

USA
13 Posts

Posted - 11/22/2004 :  17:03:44  Show Profile  Send jkaldon a Yahoo! Message
quote:
Originally posted by X002548

Like that's never happened before...

I always prefer to drop and recreate...especially now with Nigels advice about ALTER on a table wasting space....



Brett

8-)



:P

I guess I'm flame thrower shy from experiences on other boards.

For now, I added the WITH RECOMPILE option to the procedure to hopefully eliminate the problem.

- Josh

Go to Top of Page

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 11/23/2004 :  10:37:50  Show Profile
Interestingly i get time out for my application on VB and SQL. the query runs fast in QA.
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 11/26/2004 :  13:52:53  Show Profile
Try running Profiler and look at the connection settings - the first entry for your ASP connection - that shows SET QUOTED_IDENTIFIER, ANSI_NULLS, DATAFORMAT and a bunch of other things. Copy those same SET's into QA and run the query again. If it slows down it is probably ANSI_NULLS are off from ASP. You can either set ANSI_NULLS ON from your app, or ALTER DATABASE SET ANSI_NULLS ON to set the db default. Run sp_dboption mydb to see what the current default is.


--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page

g_makulik
Starting Member

5 Posts

Posted - 12/10/2004 :  05:18:48  Show Profile
Hi all,

Appearantly we faced the same problem here. What has attracted me to this thread was the initial description of the problem,
that the behaviour occured in context of the ASP process, but not in QueryAnalyzer.
BUT we managed to reproduce it in QueryAnalyzer attending Kens hints. I traced the SP call using SQL-Profiler, took all the
commands in the trace to QueryAnalyzer, and then it showed the same behaviour.
As far I have analyzed the reasons for the timeout together with a colleague, it has to do s.th. with locks appearing on
temporarily created objects used by the SQL-Engine internally.
As well it looked like it had to do s.th. with usage of table variables (used with user defined functions) and sub select stmts(?).

Recompilation of the SP helped in some cases to get rid of the effect, but funny enough, in some cases caused it again :-(.
I think it definitely has to do with the state of the connection that is used. Arguments for this are:
- that temporary objects are living in connection scope
- the effect is reproducable as long you are using the same connection

What finally helped us to get rid of the problem was to reduce the complexity of the query used in the SP executed.
There was some stupid OR condition in the WHERE clause, and I guess the Optimizer could not catch it. This might lead to the
behaviour that the SQL-Engine has to store intermediary results and these are causing the lock problem, especially if you are
working on large amounts of data.
As far i have seen locks occurred on indices created for UDF / sub select results, that were used in the WHERE clause
(?? Microsoft says, its one of the drawbacks of table variables that there aren't any indices created ??).

I'm still interested in a detailed description of the reasons, and real solution for that problem. Maybe we discovered a bug/flaw
of the SQL-Server engine.

-- Günther
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 12/10/2004 :  07:38:26  Show Profile  Visit robvolk's Homepage
quote:
Microsoft says, its one of the drawbacks of table variables that there aren't any indices created
That's true, other than a primary key constraint, table variables have no indexes or statistics.
quote:
I'm still interested in a detailed description of the reasons...As far i have seen locks occurred on indices created for UDF / sub select results, that were used in the WHERE clause
I think you've got it already.
quote:
and real solution for that problem...to reduce the complexity of the query
Ditto on the solution.

UDF's, sub-selects, and correlated subqueries should be used very sparingly in a query, especially if the query already has a lot going on without them. Every time you use one, you are essentially asking the optimizer to generate another plan just for them. There's only so much it can do to determine an execution plan before it hands it over to be processed. Make things too complicated and you start getting stack-related errors, or other kinds.

If you can use regular joins instead of subqueries, do so. Same thing for UDF's that pull a value from a table. Or break up the query into smaller steps.
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 12/11/2004 :  19:02:49  Show Profile
Wow... a lot of activity while I've been away. This problem is till cropping up for me on occasion. I'm interested in a couple of points made:
Was there a suggestion to drop and create the SP each time it gets called? Won't this be a terrible performance hit?

I had followed up on a suggestion to pass the parameters of the SP call to local variables. That gave surprising but in consistent results. I replicated the database on another box. On the box with the slowdowns from ADO (which seems not to be a direct factor anymore), I pass params to local variables and the SP worked fine from ADO. On the other box, the same SP worked fine but slowed down when passing the params to locals. When changing the SP, I could see the execution plan change from using table scans to using indexes on both boxes. No doubt the index fragmentation and other factors are different between the two boxes.

Also, I did see the slowdown from QA as well, but rarely.

There's no doubt that coding the SP in the best way possible will give the best results, but it would still be good to understand why the slowdowns are occurring.

So now it seems like the issue is not really related to ADO as it happens also in QA. Dropping and recreating the SP is only a potential temporary fix and may not work?

If anyone comes up with solid info on this please post!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/12/2004 :  03:29:14  Show Profile
As I mentioned previous you could schedule

sp_recompile 'MySProc'

periodically to force a new query plan to be created. Not a solution, but might prevent the system "runnning slow" from your user's perspective.

Kristen
Go to Top of Page

g_makulik
Starting Member

5 Posts

Posted - 12/14/2004 :  04:29:05  Show Profile
quote:
Originally posted by Kristen

Not a solution, but might prevent the system "runnning slow" from your user's perspective.


I'm afraid it doesn't really help to get rid of the problem. As I mentioned in my previous post, recompilation didn't help all the time.
I guess only in some cases if you recompile procedures/UDFs the associated temporary index objects will be removed and no more locks are slowing down the queries.

Günther
Go to Top of Page

g_makulik
Starting Member

5 Posts

Posted - 12/15/2004 :  07:25:06  Show Profile
Maybe I was wrong with my opinion, that sp_recompile doesn't help. What I actually tested was just to drop and recreate the SP/UDF. Recompilation then occurs automatically on the 1st connection that calls the SP/UDF, and will remove(??) the associated temporary index objects. But in my test scenarios I used different connections to execute the SP/UDF, and still noticed locks, probably for those connections where no recompile occurred.

Günther
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/15/2004 :  15:53:33  Show Profile
I was proposing that you recompile periodically, at a suitable time (once a week say), to prevent the SProcs running very slowly due to them running with stale optimisation plan, relative to the statistics. This may prevent them running REALLY slowly, if they [in effect] degrade over time.

Kristen
Go to Top of Page

iansr
Starting Member

23 Posts

Posted - 12/28/2004 :  06:36:17  Show Profile
Hi,

Just to add, I ran into the same problem recently. Wherein, the exact same stored procedure, with the exact same parameters ran a few milliseconds in Query Analyzer but took several seconds longer in my ASP.NET page via ADO.NET. (The case is similar for ASP, since the changes needs to be done in the database not in the front end).

Here's how I solved it: (SQL Server 2000 - 8.00.760)

I Made sure that all the connection properties used in Query Analyzer (Tools >> Options >> Connection Properties) is the same as that used in ADO.NET. In my case, ARITHABORT is Checked in Query Analyzer, I'm not sure if that is the case in ADO.NET. So I had to set the ARITHABORT option ON by default for all users (by: ALTER DATABASE <dbname> SET ARITHABORT ON). This made all my .NET connections for this database use the same execution plans as those used by QA, and hence perform identically. (As for the other options, correct me If I'm wrong, but I believe they are already enabled by default in ado.net).


I did not want to put 'SET ARITHABORT' statements in my individual stored procs, as this would cause a recompile everytime. (Although you can probably do this just to check if your ADO.NET procs will use the same plans as that of QA, before performing an ALTER DATABASE).


Before this, I have tried several approaches including:
- forced recompiles
Both in EXEC, and stored proc creation
- localized parameters / removed parameters
I tried transferring the input parameters to sproc local ones, even tried removing all parameters completely. Also tried passing hard coded string literals as values for the parameters.
- simplified the code
I converted my sproc to one liner 'return 0'. It couldn't get anymore simpler than that.
- updated statistics

None of this worked in my case.


Upon further troubleshooting, I noticed that my ado.net queries would always use a different execution plan (to see the plans being used see this article by Kalen Delaney: http://www.win2000mag.com/SQLServer/Articles/ArticleID/6113/pg/2/2.html). And since I am absolutely sure that the same sql statement is in question (I copied the sql captured by profiler on both ado.net and QA), it's indeed most likely a difference in the connection setup that is causing the difference in behavior.

So I guess if you have a similar situation, you just need to make sure that the connection properties / database options are the same.


Hope this helps, As I also spent a while looking for a solution to this problem.
If you know of a better way to acheive the same results, please let me know.

Thanks,
Ian

Edited by - iansr on 12/28/2004 07:03:46
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 12/28/2004 :  22:28:11  Show Profile
Hi Ian,

Great post! I'm still a bit unclear about what you actually did to fix this issue though. It sounds like you've basically been through the exact same struggle I have trying the same things.
Please elaborate on what you did to improve your results.

Thanks!
Larry
Go to Top of Page

iansr
Starting Member

23 Posts

Posted - 12/28/2004 :  23:26:57  Show Profile
Hi Larry,

Ok let me clarify it a bit:

Say I have a storedprocedure named 'myStoredProc'. This proc runs fast in QA but slow outside of QA.

#1.First I checked the execution plans being used both by my ASP.NET page (ADO.NET connection) and QA script, by running this script in QA:

***
SELECT sql, cacheobjtype, usecounts
FROM master..syscacheobjects
WHERE objtype = 'Proc'
AND sql = '<myStoredProc>'
***

I found out that my ADO.NET connection is generating separate executable/compiled plans than the one being used by Query Analyzer. (Check the usercount column). I believe this is causing the performance difference.


#2. My theory is that there is something different in the way QA connects to SQL Server and the way ADO.NET connects. As a lead, I checked the connection properties in QA (Tools > Options > Connection Properties). I noticed that ARITHABORT is checked.

I don't know if that is the case when ADO.NET connects to SQL Server. So I thought of setting that first.


#3. To test my theory, I tried adding SET ARITHABORT ON to myStoredProc which is called by ADO.NET. Consequently, myStoredProc used the same execution plans and performed similarly regardless of where it was run.


#4. I don't want to keep the 'SET ARITHABORT ON' on each of my stored procs, as this would cause a recompile, and furthermore, it might be difficult to maintain. So I changed the setting in the database to implement it across all connections.

***
ALTER DATABASE <dbname>
SET ARITHABORT ON
***


I would think that problems against DTS packages might have a similar concern. (i.e. difference with connection properties). This might be the case as well for SqlJobs. One SqlJob I had running for 8 mins now went down to 4 mins after I implemented this fix.



To further pin down this situation, Does anyone know how to set these database options via the ADO.NET connection? What options are enabled by default in ADO.NET?

Furthermore, does anyone know how to determine the db options enabled by the query analyzer connection. I'm not sure if Options > Connection Properties is the most reliable way. Note I want to see the db options enabled by QA not the database itself (i.e. sp_dboption <dbname> would not work as it would give the options enabled in the database itself). I am interested in the way QA connects to the database.


Thanks,
Ian
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.53 seconds. Powered By: Snitz Forums 2000