Can any one please provide link for flow of query exection in sql
For eg.
FROM, [JOIN CONDITION, JOIN TABLE ...], WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, TOP
Suppose when we write query, how that query will get execute behind. I would like to know about that.
Can you please provide ariticles or link related to this
I have a query:
Select a from tbl_abc where id in ( select id from tbl_xyz where mainid = 12)
When I am executing this query, it is taking 1-2 seconds to execute, but when I am using the same query in stored procedure, the below query is taking more than 5 minute:
If(Select a from tbl_abc where id in ( select id from tbl_xyz where mainid = 12))
BEGIN
-- CREATE TEMPORARY TABLE [Say: #temp1]
#temp1 => Select a from tbl_abc where id in ( select id from tbl_xyz where mainid = 12)
inserting the same value in the temp table
drop #temp1
END
what could be the reason of this? and how can I resolve this? I am running the SP from asp.net
I have a big query that it execute in 4 minutes. (for example an important trigger)
I want to show situation of query or count of records that is affected in every 10 second in to a web page.
what should I do? (complete explain)
I need to find out the query execution time from the front end .Where should I insert the code for that.
I am using the bleow query:
OracleConnection con = new
OracleConnection(ConnStr);
con.Open();
OracleCommand cmd =
new
OracleCommand("Stored_Proc",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add();
....................
................
OracleDataAdapter
oda = new
OracleDataAdapter
(cmd);
when i am executing a simple query it is takeing 47 sec .... that is only for one table.. other then that remaing table perfromance is good... Help me out... and is there chance to get good perfomance through .net coding..
View 3 RepliesI'm executing one stored procedure from the '.net' code. Since there is a lot of data, it is taking too much time to execute. Is there any way to stop this execution from the c# code?
In other words, if we execute the query from database itself, there is a option to stop its execution but in the code is it possible?
can anyone explain me how below query will execute internally ??
select sal from emp e1 where 3>(select count(*) ct from emp e2 where e1.sal < e2.sal)
i am using the transaction-per-request (session-in-view) pattern for an asp.net web application. I have a couple of points in the application where i want to Save an NHibernate managed entity and then do a couple of more inserts and updates using common sql. These inserts/updates depend on the ID that the NH saved entity will take.
The problem is that the generated id does not exist in the transactions' scope. If i force a flush/commit the id is persisted but if the inserts/updates fail i have to rollback but the flushed/committed entity will not. Currently I'm doing a manual insert for these cases but that is something i want to change. So, is there a way to execute the SQL statement (inside the already open transaction) after the Save() but without forcing a flush/commit?
EDIT: I'm adding a semi-pseudocode example, i got 4 wrong answers so i think people don't understand (how NHibernate works)
At the Begin request i issue a
nhsession.BeginTransaction()
then at some point i do
FooClass fc = new FooClass("value");
nhsession.Save(fc);
ITransaction trans = nhsession.Transaction;
SqlCommand sc = new SqlCommand("some insert/update query that depends on fc's id", (SqlConnection)nhsession.Connection);
sc.Parameters.Add("id", fc.Id); //NHibernate generates the id, note i'm using assigned/hi-lo so no round trip to the db takes place
transaction.Enlist(sc);
try {
sc.ExecuteNonQuery();
}
catch (SqlException ex){
transaction.RollBack();
nhsession.Close();
}
and at the end of the Request i issue a CommitTransaction() and nhsession.Close()
Now this will do absolutely nothing: the FooClass (fc) has not been flushed/commited to the database. The Save() operation that NH has done is up to that point in-memory. That means no sql command has been issued by nhibernate and that means that the SqlCommand (sc) that i fire afterwards will fail miserably as the id does not exist.
If i do a flush/commit between Save() and the SqlCommand the FooClass(fc) _cannot_be_rolled_back_ and that is a bad bad thing.Currently, for this to work i make vanila sql insert using an SqlCommand, and i want to change that. (Why? because i don't want to make vanilla inserts they are susceptible to errors due to schema/model changes, and i got the OR/M for that)
How? i want to notify NHibernate somehow to execute the SqlCommand to corresponds to the Save() insert (hell, it can do all the SqlCommands it has gathered) but without it commiting or flushing!.
Currently i'm also searching for the prepared sql statement that nhibernate produces when flushing/commiting a saved object. Maybe i can just take that string and run it in my SqlCommand that is enlisted in the Transaction.
I'm building an ASP.NET MVC site that uses LINQ to SQL. In my search method that has some required and some optional parameters, I want to build a LINQ query while testing for the existence of those optional parameters.
Here's what I'm currently thinking:
using(var db = new DBDataContext())
{
IQueryable<Listing> query = null;
//Handle required parameter
query = db.Listings.Where(l => l.Lat >= form.bounds.extent1.latitude && l.Lat <= form.bounds.extent2.latitude);
//Handle optional parameter
if (numStars != null)
query = query.Where(l => l.Stars == (int)numStars);
//Other parameters...
//Execute query (does this happen here?)
var result = query.ToList();
//Process query...
Will this implementation "bundle" the where clauses and then execute the bundled query? If not, how should I implement this feature?
i have a complex MSSQL query in my ASP.NET page with rows showing in GridView control.
I want to know what is the best solution for stopping the query if it takes more than X seconds to show in GridView.
I have a class that being used to connect with the DB. Now I want to count how many times each web request executes the queries, but I've no idea where to store the counted value. I mean, Session wont, ViewState wont work as site also have webservices. What else I can use?
View 12 Repliesi am new at reporting service things when i try to create a report i got this error and idea how to fix it
An error occured during local report processing.
An error has occured during report processing.
Qery execution failed for dataset 'ds_testtablosu'
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MMDB"
I am using multiple threads in my .net web application. Certain functions may need to execute at any arbitrary time. However, I do not need these (and the entire application) to be fully asynchronous, as there is an acceptable tolerance in this execution time. Here is a rough example of what I need to do:
main()
{
loop
{
if (flagA) then doTaskA();
if (flagB) then doTaskB();
}
}
...where flagA and flagB are set via asynchronous timers in critical section. The actual execution of the requested tasks would thereafter run in series.
This is very easy to do in C/C++, etc. But I cannot find any access to such control in .NET, specifically in the web server/application architecture. It may be that it doesn't exist; however, I am assuming that the server must do something of this sort as it manages applications. I would like to hook into that somehow; even if via some application function that is called regularly during this loop.
I have two different database connection strings for a web form that I have to use when running on either my local pc or web hosting.best way to determine whether I'm running locally or on the server at runtime? I have dodgy logic at the moment which uses a local file to determine whether or not, but that means having to include and change the code on every page if I ever needed to.
View 5 Replies[Code]....
why I am not getting the desired result. I want to retrieve records in DataReader and still I am not getting any result.
when I use 'RowNumber' = ROW_NUMBER() OVER(ORDER BY ColumnName) then due to orderby Column name consume 76% usage in execution plan . in such case what I have to do? I can see sorting taking much time in execution plan.
View 9 Repliesi am using sql server inbuilt mail system for sending mail.and i made SP to call dbmail sp
now my problem is when i call sp it get executed fine but in last suppose 1000 mails queued then 1000 times i get mail queued.and that my connection time out .i extend connection time out limit but i get error msg like
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Mail queued."
my mail get queued well proper but due to 1000 or 100000 mail queued its lost of msg make problem.
I need to capture the amount of time that ASP.net takes to execute each page request in my application, but I need to exclude any network latency. I am currently capturing render times by using the StopWatch class and starting the stopwatch during the OnInit method of the page lifecycle and stopping it after the Unload method completes. It seems that the Unload method includes the time it takes send the request to the client, thus including any internet/network latency. What is the last possible point I could stop the stopwatch in the Page Life Cycle that would not include the time it takes to send the request to the client. Would it be directly before the Unload event?
Related question: Does ASP.net finish building the response before it starts sending to the client? Or does it start sending asynchronously, while the response is being formed?
I am using ASP.Net 2.0 with IIS 5 currently.
I have this code in a class that all of my pages inherit from:
readonly Stopwatch _serverExecutionTime = new Stopwatch();
protected override void OnInit(EventArgs e)
{
_serverExecutionTime.Start();
base.OnInit(e);
}
protected override void OnUnload(EventArgs e)
{
_serverExecutionTime.Stop();
base.OnUnload(e);
}
UPDATE
I tried capturing the execution time at the end of the OnRender method, at the start of the OnUnload method and at the end of the OnUnload method. In all three cases the difference in times was at most 1 millisecond. Even when testing this from a client in Europe to a server in the USA, the times were identical.
By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.
View 10 RepliesI am trying to create statitics for game downloads and am having dificulty with a sub part of my query. the goup by command is not having the disired result and i think i have been looking at it too long to see what i am doing wrong. [Code]....
At the moment it is producing each result in many rows when only 4 rows should appear. its the download column that is doing it. needs to be sub queried or something to display total in relation to month,gameid,userid.
Month-download-GameID-UserID
1 1
1 1
1 1
1 1
I am trying to display this query result in an aspx page.
It is very slow in loading. Here is the query. The inner query inside the outer quesry is the problem.(Please see the underlined part in the query) - (If I remove that part it is very fast.)
select
top 500
--This column is the issue
,Governing_Class=( case when exists (select top 1 tqc.class_code from
t_quote_class tqc
inner join t_quote_class_premium tqcm on tqc.class_code =tqcm.
class_code
where tqc.appid=pi.appid and tqc.class_code not in('8742' ,'8810','7380')
order by tqcm.premium_amt desc
)
then ( select top 1 tqc.class_code from
t_quote_class tqc
inner join t_quote_class_premium tqcm on tqc.class_code =tqcm.
class_code
where tqc.appid=pi.
appid
order by tqcm.premium_amt desc
)
......... From tables
I am a biggner in SQL DB . but i started a complicated and painfull work in SQL SERVER 2008. the problem convert Oracle hierarchical query to SQL query. the query
SELECT DISTINCT
LEVEL LVL,
SCH.NSCHEDULE_SL,
SCH.NSCHEDULE_SL_FM,
SCH.CSHED_CNAME
FROM FA_SCHEDULES SCH
WHERE LEVEL = 1
AND NSCHEDULE_SL_FM IS NULL
AND NBRANCH_SL = 2
CONNECT BY PRIOR SCH.NSCHEDULE_SL_FM = SCH.NSCHEDULE_SL
AND NBRANCH_SL = 2
i have tow query in one stored procedure
first query and the second one every of them return only value
i want to copy the result of tow values in a new row in new table
as i want to check on this tow values in ado.net
i want to get only this tow values as a row not first and secon query
only a row has result of first and second query results
[Code]....
How can I SELECT second if first SELECT returns nothing?
View 8 RepliesI'm integrating a number of e-comm sites into different banks and decided the easiest method was to add in the dotnetcharge (www.dotnetcharge.com) library.It works well and means I can keep much of my code the same for each bank type and transaction.However, their support is a bit sucky (4 emails sent, 1 reply) and I'm utterly baffled on the 3D Secure issue.Does anyone have experience with dotnetcharge and 3D Secure?I have set the MerchantURL and the actual 3D Secure screen comes up - but I'm unsure how to get the system to 'flow' properly.Does anyone have any code examples or even pointers in the right direction?Failing that.This particular integration is with SagePay,which also has God-awful documentation and support.
Code for reference is as follows;
[code]...