Query Variables

Hi,

I'm new to SQL Server, but an experienced .Net developer. I'm trying
to accomplish a query the most efficient way possible. My question is
if you can define a temporary variable within a query to store tables
or fields. (Like the LET clause of LINQ) My query makes use of
subqueries which filter my table (WHEREs, not SELECTs) in the same
exact way. I'd like to have a subquery at the beginning of my query
to filter the table(s) once, and then SELECT off it of later in the
query.

Here is an (utterly poor) example. No, this is not from my project.
My filter is a little more complex than 'c=@.p'.
('c' is a column/field, 't' is a table', '@.p' is a parameter)

SELECT *
FROM (SELECT COUNT(c) FROM t WHERE c=@.p GROUP BY c)
CROSS JOIN (SELECT c FROM t WHERE c=@.p)

Bottom line, would something like the following be possible?

@.v = (SELECT c FROM t WHERE a=@.p)
SELECT *
FROM (SELECT COUNT(c) FROM @.v GROUP BY c)
CROSS JOIN (SELECT c FROM @.v)

I'd like to know if this is possible within a query, but I can move to
a Stored Procedure if I must. (I'll still need help then.)

Thank you all>My question is if you can define a temporary variable within a query to >store tablesor fields. (Like the LET clause of LINQ).

The answer is of course! Jump in anywhere and you will see what makes
sense:) You can start here:
http://beyondsql.blogspot.com/2007/...-variables.html
best,.
www.beyondsql.blogspot.com|||steve wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>My question is if you can define a temporary variable within a query to >store tablesor fields. (Like the LET clause of LINQ).

>
The answer is of course! Jump in anywhere and you will see what makes
sense:) You can start here:
http://beyondsql.blogspot.com/2007/...-variables.html
>
best,.
www.beyondsql.blogspot.com

Posting only to discuss one's own product is one of the ten early
warning signs of crankery.|||On Oct 17, 11:51 am, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

steve wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

My question is if you can define a temporary variable within a query to >store tablesor fields. (Like the LET clause of LINQ).

>

Quote:

Originally Posted by

The answer is of course! Jump in anywhere and you will see what makes
sense:) You can start here:
http://beyondsql.blogspot.com/2007/...les-are-typed-v...

>

Quote:

Originally Posted by

best,.
www.beyondsql.blogspot.com

>
Posting only to discuss one's own product is one of the ten early
warning signs of crankery.

If you had bothered to read the link you'd see it was exactly what the
op was asking for. I guess you can be an sql cop. I'm not so sure
about a detective :)

Query Timeout Issue with .NET

Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout=3D"1800"/>
<add key=3D"XYZ"
value=3D" ;Server=3Dserver01;UID=3Dsa;PASSWORD=3Dp
assword;DATABASE=3Ddatabas=
e;Connecti=ADon
Timeout=3D 40000;"/>=20
I'd really appreciate any help.=20
SunshineI don't believe there is a connection string keyword that controls command
timeouts. You need to set the command CommandTimeout property to the
desired value.
Hope this helps.
Dan Guzman
SQL Server MVP
<sunshinevaldes@.yahoo.com> wrote in message
news:1145276070.473701.247630@.i40g2000cwc.googlegroups.com...
Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout="1800"/>
<add key="XYZ"
value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connecti_
on
Timeout= 40000;"/>
I'd really appreciate any help.
Sunshine|||Wouldn't I have to do the CommandTimeout for every call to the server?
If so that would be over a hundred so I was hoping there was something
in the web.config I could tweak. I did set the query timeout on the
server to 0 but that didn't help either.
Sunshine|||the connection timeout in your connection string indicates the timeout
for the connection to be formed, not for the actual query to complete.
This means that if for some reason it was having trouble connecting to
the database server, it would wait that long before reporting an error.
As Dan said you will need to set your CommandTimeout property in your
SQL command (I hope you've centralised your data access code or you're
in for a lot of code changes). But you should also make sure you remove
that absurd level of timeout from your connection string.
Cheers
Will|||sunshinevaldes@.yahoo.com wrote:
> Hello all and thank you for your time.
> I have an application with reports taking over 30 seconds to run.
> This
> is producing the
> "Timeout expired. The timeout period elapsed prior to completion of
> the
> operation or the server is not responding." error. Is there a way to
> increase the timeout expired default of 30 seconds until I have
> resources to optimize or rewrite the querys? There are over 120
> reports and querys!
>
> I've tried:
> <httpRuntime executionTimeout="1800"/>
> <add key="XYZ"
> value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connect
i_on
> Timeout= 40000;"/>
>
> I'd really appreciate any help.
>
This is slightly off-topic here:
Come to this group if you want help in reducing the time it takes for your
queries to run (query optimization).
For help with client applications utilizing your queries, go to the
newsgroup that is focussed on your client application: in this case,
microsoft.public.dotnet.framework.aspnet.
My recommendation is to optimize your queries so that they don't take so
long to run. My suggestion for the interim would be to use IIS Manager to
increase the Script Timeout setting for your application. You can increase
the setting on a per-page basis by using the Server.ScriptTimeout property.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||you would indeed have to set it for every call to the server. Hence my
comment about centralising data access code.
It is not possible to set this in the web.config.|||Unfortunately, the data access code is not centralized. This
application was written by consultants who say they can't fix the timed
out issue. I was hoping there was an easy fix until I can fix their
queryies.
Thanks for all your responses.
Sunshine|||Bob,
I went to microsoft.public.dotnet.framework.aspnet first but after
almost of w with no replies I came here and have had great replies
within the same day.
Can you tell me how the increase the Script Timeout in the IIS Manager?
Sunshine|||(sunshinevaldes@.yahoo.com) writes:
> Unfortunately, the data access code is not centralized. This
> application was written by consultants who say they can't fix the timed
> out issue.
Hm, if they don't enough about data-access programming to address
timeout issues properly, who says that you should be able to fix
their invoices? :-)

> I was hoping there was an easy fix until I can fix their queryies.
On SQL Server level there isn't, because this completely idiotic
default timeout of 30 seconds is set in the client API, and all SQL Server
sees is the cancellation request.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Query Timeout

Hello,
Our system has experienced time out issues with on of the SQL Severs. We
have an ASP.NET application that calls a stored proc, which returns a result
set. This stored proc all of a sudden started to time out on Friday after
noon. I was able to execute the same procedure in Query Analyzer without any
problems. The timeout for My ASP.NET is 30 seconds and the query was
executing the Query Analyzer in less than 30 seconds.
The solution I found out for this is to update statistics on one of the
databases. The stored proc has a join with a table in different database
sitting on the same server. When I updated the statistics of this database
the ASP.NET app started behave normally. This time I did not run the update
stats, instead I was thinking to do more research this morning. To my
surprise, the time out issue went away and ASP.NET is working absolutely
fine. What I found is that the SQL server might have been rebooted this
weekend.
Now I am scratching my head and trying to figure out what could be the
problem. What is it'
I appreciate any comments.
ThanksIf you have SQL Server configured to auto update stats, it may have done an
update itself which improved the stats and allowed it to create a good query
plan again.
My experience has been that auto update is not reliable enough, so we
schedule update statistics ... with fullscan against all tables across the
course of a week.
--
Scott Nichol
<Srini> wrote in message news:eRWM0U7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> Our system has experienced time out issues with on of the SQL Severs. We
> have an ASP.NET application that calls a stored proc, which returns a
result
> set. This stored proc all of a sudden started to time out on Friday after
> noon. I was able to execute the same procedure in Query Analyzer without
any
> problems. The timeout for My ASP.NET is 30 seconds and the query was
> executing the Query Analyzer in less than 30 seconds.
>
> The solution I found out for this is to update statistics on one of the
> databases. The stored proc has a join with a table in different database
> sitting on the same server. When I updated the statistics of this database
> the ASP.NET app started behave normally. This time I did not run the
update
> stats, instead I was thinking to do more research this morning. To my
> surprise, the time out issue went away and ASP.NET is working absolutely
> fine. What I found is that the SQL server might have been rebooted this
> weekend.
>
> Now I am scratching my head and trying to figure out what could be the
> problem. What is it'
> I appreciate any comments.
>
> Thanks
>

Query Time in SQL Server

I am using SQL Server and ASP.NET. I am executing a couple of stored procedures and displaying the results in a datagrid. Since these Stored procedures takes around 2-4 minutes each, I want to display a status bar on the web by displaying the approximate time the user needs to wait before seeing the results.

My question is: Is there a way to find out the approximate EXECUTION TIME of the stored procedure before hand. Also, if that is possible, how do i access the same from the ASP.NET code..

Thanks
SathyaI do not know of a way to determine the approximate execution time of a stored procedure before it runs.

Perhaps you should use the worst-case execution time as your estimate for each?

And also, 2-4 minutes for a query to run is not reasonable. You should strongly consider spending some time trying to optimize these queries.

Terri|||I agree with Terri on this one. 2-4 minutes for a query to run especially as a stored procedure is saying a tremendous amount to the inefficiencies you may have in your design or execution.

Initially, I'd take the time to repair that before continuing further to make your future tasks with your application even more complicated.

Query taking ages for no apparent reason

Hope someone can help me with this because its driving me potty!

I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-3 minutes) and then fails, I'm assuming because it times out. I then check the SQL by excecuting it via query analyzer and it again takes ages but will work eventually (I'm assuming because this bypasses the timeout settings, but changing these isn't on).

This happens randomly, the scripts will be working fine and then fail a few times before magically working again!

Any ideas? Perhaps some database features that commonly cause this problem? The problem only occurs with one database, all our others are fine but we can't spot any differences!

Any help or tips would really be appreciated.

Thanks.sound like a locking issue.

When you are running the query via .net, in query analyzer in a seperate session run sp_who2. This will show you if there are any locked processes.

Even better use enterprise manager (if you have access)|||Originally posted by dbabren
sound like a locking issue.

When you are running the query via .net, in query analyzer in a seperate session run sp_who2. This will show you if there are any locked processes.

Even better use enterprise manager (if you have access)

Thanks for the advice.

There's no sign of locking when my problem is occuring using sp_who2 (I refreshed sp_who2 a few times whilst I was waiting for the query to give-up).

On the other hand, I had a look using enterprise manager->Locks/Object and there's a huge list of Table Locks (908!) owned by 'xact' (a transaction? )for the database i'm using. Other db's being used have database locks owned by the SESS (session I assume). I've never explicitly asked for a lock, but this db is someone else's so could there be somehting in there that aquires a lock?

Thanks for you help,

suddy.|||Suddy

Everytime you access the db, you will take a lock - the type and severity of that lock depends on what you are doing - have a look at locking in BOL (it can explain it better ..)

I find it easier to use locks/process id in Ent Manager as it is often easier to track the spid to a particular PC/trnsaction. It also tell you which process is blocking which other processes.

Another option may be to use profiler to track the SQL that is being ran, and capture blocking lock information - but this will have a performance impact itself (so be weary of it)|||Thanks for your help Dbabren.

Darned problem has mysteriously vanished this morning but I'm going to go away and have a look at BOL because this is bound to come back if I don't work out what's going on.

Thanks again.|||Originally posted by suddy
Hope someone can help me with this because its driving me potty!

I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-
Thanks.

Can you post the queries? Are you using the "NOLOCK" directive with your select statements?