Poor Performance from Web Server

Hi,

I am having a problem with one of my stored procedures in SQL Server 2005. Basically the proc brings back a data set for the ASP.NET front end, but it is running very slowly from .NET.

I have run SQL profiler on the procedure and its taking around 20 seconds to bring back the data for the .NET, where as if I copy and paste the executed SP from profiler into the management studio and run it in a query window, it runs in around 1 second, even if I run DBCC DROPCLEANBUFFERS before I run it. More worryingly, the CPU usage is 40 times higher and the number of reads is 50% higher from .NET.

We have the .NET front end spread over 3 clustered web servers with load balancers and the SQL db is on a dedicated rig. I am having the same problem on my locally published version of the site as well, so I don't think it's an issue with the web site.

If anyone has got any ideas on this then please let me know as I am completely stuck. I should mention that the issue has only recently started occuring and it used to be fine and the rest of the site is fine...

Thanks in advance

Tom

maybe put some trace statements to echo out the time it execute a line of code. This way you can maybe find the bottleneck in your DAL. Are you using the Data Access Application Blocks. I have found those to be very valuable to manage my connections. I never see issues like you are describing anymore. Back in the day, like 4 years ago maybe when I was doing things on my own. Are you trying to fill a custom list or collection with a large set of records? I found that takes way too long and just op for datasets, readers or change my procedure to return a fixed set of rows.

But try the tracing thing to see what line(s) take the longest to execute and I think you will find your issue.

Poll - Stored Procedure vs. Command Text?

Has anyone found a clear benefit to one over the other for complex SQL
in Reporting Services reports?
I've stayed away from stored procedures so that my report development
lifecycle can remain mostly independent from my team's database
migrations, but if there is a clear benefit, I will strongly consider
it.
Thanks!
Mike"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in
news:1123614436.830410.247520@.g14g2000cwa.googlegroups.com:
> Has anyone found a clear benefit to one over the other for complex SQL
> in Reporting Services reports?
> I've stayed away from stored procedures so that my report development
> lifecycle can remain mostly independent from my team's database
> migrations, but if there is a clear benefit, I will strongly consider
> it.
> Thanks!
> Mike
>
I've used both. I prefer to use queries. I'll only used stored procs if 1)
I'll be reusing the same query in a number of reports, and I want to
simplify my life for maintenance, or 2) I need to do some processing on
values prior to returning data.
I query data from 3rd party software and I hate to place stored procs in
those databases. It's too easy to forget them, have have the software
company blow them away during an update.

Pls. help me optimize this stored procedure

Hi,

I'm kind of rusty when it comes stored procedures and I was wondering if someone could help me with the following SP. What I need to do is to query the database with a certain parameter (ex. Culture='fr-FR' and MsgKey='NoUpdateFound') and when that returns no rows I want it to default back to Culture 'en-US' with the same MsgKey='NoUpdateFound'). This way our users will always get an error message back in English if their own language is not defined. Currently I'm doing the following,

- Check row count on the parameter passed to sql and if found again do a select returning the values
- else do a select on the SQL with the default parameters.

I'm guessing there is a way to run a SQL and return it if it contains rows otherwise issue another query to return the default.

How can I improve this query. Your help is greatly appreciated. Also, if I do any assignment since 'Message' is defined as text I get the following error message

Server: Msg 279, Level 16, State 3, Line 1
The text, ntext, and image data types are invalid in this subquery or
aggregate expression.

CREATE PROCEDURE GetMessageByCulture
(
@.Culture varchar(25),
@.MsgKey varchar(50)

)
AS
SET NOCOUNT ON;

IF ( (SELECT COUNT(1) FROM ProductUpdateMsg WHERECulture=@.Culture AND MsgKey='NoUpdateFound') > 0)
SELECT Message FROM ProductUpdateMsg WHERECulture=@.Culture ANDMsgKey=@.MsgKey
ELSE
SELECT Message FROM ProductUpdateMsg WHERE Culture='en-US' ANDMsgKey=@.MsgKey
GO

Thank you
M.

Why don't you use resource files with globalization? This functionality is built in; all you have to do is edit an XML file to update the messages, and the message will always be displayed in the default language if the user's culture is not supported.

|||

IF EXISTS (SELECT * FROM ProductUpdateMsg WHERECulture=@.Culture AND MsgKey='NoUpdateFound')

SELECT Message FROM ProductUpdateMsg WHERECulture=@.Culture ANDMsgKey=@.MsgKey
ELSE
SELECT Message FROM ProductUpdateMsg WHERE Culture='en-US' ANDMsgKey=@.MsgKey

I am using * for the first statement because I don't know your columns. Ideally, you just only select one column instead of *.