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 Replies
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);
iam trying to fill my dataSet Iam getting Error: Fatal error encountered during command execution.Ima using Ms.Net 2.0 and MySql 5.0 So Thing is Iam Hanged Here .My Code as Like:
MySqlConnection con = new MySqlConnection("server=XXX.XXX.X.XXX;user id=murali;password=murali;database=MURRAGE;default command timeout=3600");
da = new MySqlDataAdapter("SELECT BLNO FROM IFORM", con); [code]....
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
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'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 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 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 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 am making some application in asp.net 1.1 in that I am displaying data in datagrid from database. In data grid first column is of check box and radio button and in the last column we have text box now thing is that when i click on check box crosponding textbox we fill some figure and at the end we press calculate button to get total. After getting total we have to press continue button to go next step
But suppose if some body remove that check the we should press calculate button or chnage some figure in the text box then we should press calculate button again to recalculate. for that i kept autopost back on check box oncheckedchanged event and text box ontextchanged event and hide continue button so user will click calcutae button abd then continue button will re-apper
Problem it that it taking too much refresh and taking long time. So was lloking for some ajax method to use in my application.
I need to insert values from the frontend into a mysql table. I am using a 3 layer architecture. the below is the code that is written in my dataaccess layer. the query is not being executed.
public int Insert(List<EmployeeUInfo> objInsert)
{
try
{
DataTable dtInsertRow = new DataTable();
[Code]....
Trying to modify an existing gridview in an aspx to access a db2 table on an iseries server. The iseries seems to have problems with the parameter specified in the query as @parameter giving a @parameter does not exist in table error. When specifying the gridview in VS the wizard did not recognize that the @parameter was a parm and prompt for the control etc. where it could be found. Of course there is no problem when accessing a mssql server table. Can someone help me with how to connect properly to the iseries and how to pass a parm on my query?
View 1 Repliesi have a SQL Query. It works fine in Sql server. let me know how to make it work in Oracle.
You can run below code in Sql server and check. It will work fine. But in Oracle,( I am using TOAD for Oracle )it is giving some errors.
Code]....
I am trying to do a SELECT ... WHERE ... IN construct, using a parametrized query with Oracle (odp.net) and I cannot make it work correctly.
[Code]....
The STATUS column is NUMBER(2) in the database. My problem seems to be related to getting the correct OracleDbType. I've tried Varchar2, Long, Decimal... nothing works. Should I go for the ArrayBindSize construct?
I run queries in my stored procedure and pass values to it as a perameter e.g.
SELECT p.Profile_Id
FROM Profile p
WHERE (p_Gender = p.gender_id)
now is there a way that i can get the actual query executed with paramaters value like this:-
SELECT p.Profile_Id
FROM Profile p
WHERE ('M' = p.gender_id)
I have a the following code to get data from an oracle database using
a parameterized query:
conn = New OracleConnection(connectionstring)
comm = New OracleCommand("select name from Cust_name
from Name_Table Where last_name = :lastname", conn)
parmLastName = New OracleParameter("lastname", OracleDbType.Varchar2)
parmLastName.Value = "Jones"
comm.Parameters.Add(parmLastName)
I am trying to write an audit that will tell me what users are doing, so I would like to get the actual sql that is submitted. This is just one example of the query, there are many more so I would like to make it as general as possible. I would like to actually see "
select name from Cust_name from Name_Table
Where last_name = "Jones". I know I could just create the text
I am generating page number for my listview control. I am using mysql as databast. Please check the sysntex it is giving error but works fine with sqlserver.Public Function GetPageNumber(ByVal PhotoID As Integer, ByVal AlbumID As Integer) As Integer
Dim con As New MySqlConnection
con.ConnectionString = "server=localhost;User Id=root;database=jis;password=papidm;Persist Security Info=True"
[code]...
This is the code. The error giving in the Mysql query syntex heighlighted
i 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 want to update multiple column of a table using subquery ...My sample code is shown below
[Code]....
its throwing syntax error...I dont know where i commiting mistake.
Not sure how to best accomplish this task. I do have several reports to make in Excel every day. The reports do have a shhet for every week and one column for each day in week. What is the best way to get the excel auto update and get data from the SQL table? As for now I run a query on a webpage and coying the data in to the excel sheet manually. Should I try making a database connection within the excel sheet? Should I try having a ADO connection working in .NET? I really donīt know where to begin.
View 2 Replies
