I have a query like this:
SELECT *,isnull(dbo.fn_1(a),'') f_a,
isnull(dbo.1(b),'') f_b FROM tablea with(nolock)
WHERE ID = 12345 and ID<10000000 and dbo.fn_1(7)='asdfasdf' and Active='Y'
Does it effect performance as i am using a udf here? Also let me know the order in which the conditions will be applied.
Thanks in advance.
Thanvi
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
Hi all I'm using Sql server 2000 and sometimes i need to run my Queries in Query analizer before using them in my application just to test them...BUT most of the time when i run a query in query-analizer for second time ,query analizer populates the result (records) more quicker then the first time. Apparently it caches the query !!! i don't know but for some reasons i dont't want this , so how can i prevent Query-analizer from doing so?
Thanks in advance. Regards.
What are the Query Execution plan. How to read and interpret query execution plan.
spatle
Hi,
I want to know is there any way by which we can come to know
which query is going to be executed by the Engine prior to its
execution. I think if this is possible then this will affect the
performance. But It will be very helpful for me.
Is this is possible and how we can achieve this.
Plz help me out its very urgent .
Thanks for the help in Advanced.
Thanks.
Hi all,
I have sql query to search for fields in a rather big view. If I execute the
query in sql server enterprise manager, the results will be displayed in
less than 6 seconds. However, if I execute it using asp.net, it will take
very long (more than 2 minutes).
The query is a simple one like "SELECT * FROM myview WHERE name LIKE
'%Microsoft%'". And the code I use to execute it in asp.net is
Dim dsRtn As DataSet
Dim objConnection As OleDbConnection
Try
objConnection = GetOleDbConnection()
objConnection.Open()
Dim objDataAdapter As New OleDbDataAdapter(strSearch, objConnection)
Dim objDataSet As New DataSet()
objDataAdapter.Fill(objDataSet, strTableName)
dsRtn = objDataSet
Catch ex As Exception
dsRtn = Nothing
Finally
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
End Try
Where strSearch is the sql search string.
I don't have any problem using such code for other queries.
Could somebody suggest the cause of the problem and how to solve it? Thanks!
Best regards,
David
Hi,
I am having a query where I am connecting to eight different tables using joins. When I join one table to another the speed of the execution becomes less. Even on my local server it is taking nearly 2 to 3 minutes to execute the query. How can I increase the speed of execution of my query.
Thanks in advance,
Uday
Hi,
Is there anything to be gained in a single table query by using :
tablename.columnname1, tablename.columnname2 etc
vs just columnname1,columnname2 ?
Thanks,
Judith
I'd like to run 4 stored procedures in parallel on 4 different processors on our server.
Does anyone know the syntax to do that? (Could not find it on the web so far).
Anything else I need to do in addition to the specific syntax (i.e. any specific properties to set in Enterprise Manager)?
Your help is greatly appreciated!
Lana
Hello ,
I wanted to know whether we have an execution plan enabled in SQL 6.5 as we have it in SQL 7.0 and SQL 2000 .
I.e when we execute a query and if we enable ' show execution plan 'then it creates a map and shows the vital statistics .
If that is available on SQL 6.5 then i am missing that tool .
How can i have it installed on my SQL 6.5 server ??
Thanks.
I have a procedure (used to create a report) and was used in sql 7.0 service pack 3.
Problem is that we are upgrading to SQL 2000 and this procedure now takes 1 minute and 30 seconds to execute vs. 10 seconds previously.
Everything is same between the sql 7 and sql 2000 server. i.e. database size, indexes, hardware etc.
I looked at the query execution plan and it seems to do a sort which is taking majority of the resources on sql 2000 even though there is no sort stmt issued in the procedure itself.
Any help would be appreciated?? I am more curious to find out why this is the case when all the variables are same between the two servers yet sql 2000 performance is much worse than sql 7.0. It should be the other way around!!
Hi,
I am using a store procedure and in this sp i am having a simple select statement. Now i found that when i executes this sp in query analyzer it takes about 8-10 min to show the output. Table is having thousands of records. I can rebuild indexes on table, but apart from this what else i can do to speed up the query.
I know there is something like we can use indexes explicitly in sql query. Is it true? if yes plz show me how to use it, by giving example
Also is there any other way to run the query much faster.
Plz help me, its very urgent
Thanks And Regards,
Shailesh
We have SQL Server 2000 and int is an Oracle linked server. I'm trying to run the following query...
SELECT DISTINCT a.auf_nr AS OrderNo,
e.ku_name AS Customer,
d.bestell_dat AS OrdDate,
d.liefer_dat AS DelvDate,
CAST(SUM(b.anz) AS FLOAT) Qty,
CAST(SUM((CAST(c.breite AS FLOAT) / 1000 * CAST(c.hoehe AS FLOAT) / 1000) * b.anz) AS FLOAT) SQM,
CAST(SUM(a.liefer_offen) - (SUM(a.anz) - SUM(b.anz)) AS FLOAT) AvailDelv,
CAST(SUM(a.liefer_anz) AS FLOAT) Delvd,
CAST(SUM(c.sum_brutto*a.anz) AS FLOAT) Value
FROM liorder..LIORDER.AUF_STAT a,
liorder..LIORDER.AUF_LIP_STATUS b,
liorder..LIORDER.AUF_POS c,
liorder..LIORDER.AUF_KOPF d,
liorder..LIORDER.KUST_ADR e
WHERE a.auf_nr = b.auf_nr and
b.auf_nr = c.auf_nr and
c.auf_nr = d.auf_nr and
d.kunr = e.ku_nr and
a.auf_pos = b.auf_pos and
b.auf_pos = c.auf_pos and
b.lip_status = 7 and
c.ver_art !='V' and
a.history = 0 and
a.rg_stat != 2 and
e.ku_name IS not null and
e.ku_vk_ek = 0 and
d.bestell_dat BETWEEN '01/01/2005' and '12/17/2005'
GROUP BY a.auf_nr,
d.liefer_dat,
b.lip_status,
d.bestell_dat,
e.ku_name,
d.kopf_tour,
d.kopf_firma
HAVING CAST(SUM(a.liefer_offen)-(SUM(a.anz)-SUM(b.anz)) AS FLOAT) > 0
..and it takes around 2 minutes to show the results even if the date range is of the same date. I even tried to use an indexed column but I still get the same slow execution time. I even tried to create a UDF so that the WHERE clause would be resolved remotely on the Oracle DB but still the same. Is there anyway I can do it in much more efficient and faster way?
Hello
I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete.
Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @@rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT.
TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows.
What could be wrong?
can anybody help How to solve the below error.While calling the sp from the front application the below error is thrown.but if i executed the sp in backend No error is thrown ,resultset is produced.
Error thrown:
The query has been canceled because the estimated cost of this query (7) exceeds the configured threshold of 6. Contact the system administrator.
A query that runs in a second or so in Query Analyzer requires 20 seconds in a linked Access Project.
What's the secret of MS_Access poor performance, and can it be improved?
Tom Stuart
I have a strong feeling that this isn't possible but I thought I might as well ask...
I'm developing a database application (SQL Server 2000 backend) where the client and the server is separated over a slow network connection (satellite). There are parts in the application where I will have to query a large resultset so I was wondering if there is a way to determine the percent complete of a query so I can put a progress bar on the interface so the user can see it loading data instead of having a frozen form.
I thought about spliting up the query into different ones and update the bar once each separate one is complete but I'd rather not do that because in the application development environment I'm working in, I have to close the resultset and reopen it every time I do a query... unless this isn't a big deal but I'm under the impression its something to avoid.
Thanks!
Hi there - i'm hoping someone can help me!I'm having a problem with a live database that i'm running on MSDE - Itseems to have slowed down quite considerably from the test environment(even when all the data is the same). The is notably different on oneparticular query that takes 1 sec on the test machine and almost 1 minon the live machineThe total number of user connections on the live machine is normally 4or so (found out through the Performance monitor). So I can't see thatit's MSDE's performance throttler...Has anybody got any ideas on things i can check for??Many thanksJames
View 8 Replies View RelatedWe have a console.php which takes in SQL queries and displays them in aresult.php webpage.Sometimes the query takes minutes to execute or crashes the PHPapplication. Is it possible to cancel a query during execution?If yes, how does one go about it.The PHP Console is used by multiple users simultaneously.The queries are executed on a Remote Database.
View 2 Replies View RelatedHi,We are trying to solve a real puzzle. We have a stored procedure thatexhibits *drastically* different execution times depending on how itsexecuted.When run from QA, it can take as little as 3 seconds. When it iscalled from an Excel vba application, it can take up to 180 seconds.Although, at other times, it can take as little as 20 seconds fromExcel.Here's a little background. The 180 second response time *usually*occurs after a data load into a table that is referenced by the storedprocedure.A check of DBCC show_statistics shows that the statistics DO getupdated after a large amount of data is loaded into the table.*** So, my first question is, does the updated statistics force arecompile of the stored procedure?Next, we checked syscacheobjects to see what was going on with theexecution plan for this stored procedure. What I expected to see wasONE execution plan for the stored procedure.This is not the case at all. What is happening is that TWO separateCOMPILED PLANs are being created, depending on whether the sp is runfrom QA or from Excel.In addition, there are several EXECUTABLE PLANs that correspond to thetwo COMPILED PLANs. Depending on *where* the sp is run, the usecountincreases for the various EXECUTABLE PLANS.To me, this does not make any sense! Why are there *multiple* compileand executable plans for the SAME sp?One theory we have is, that we need to call the sp with the dboqualifier, ie) EXEC dbo.spHas anyone seen this? I just want to get to the bottom of this andfind out why sometimes the query takes 180 seconds and other timesonly takes 3 seconds!!Please help.Thanks much
View 5 Replies View RelatedHi,
I have a group of reports using a shared datasource. Going to the preview of the report works fine in the report designer, but when I try and view it from a browser (deployed on a website), it gives the error:
"An error has occurred during report processing.
Query execution failed for data set 'DataSet1_ticketInfo'.
Failed to parse SQL.[long sql query here]"
If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.
Thanks.
How to get the execution time of a query in sql server 2000?
Thanks in advance
Hi All
Is there anyway you can estimate the execution time/cost of a query prior to actually executing it?
Steve
EDF Man International
sfarmer@edfman.com
We recently upgraded from SQL 6.5 to SQL 7. I have a few .sql files that were each running around 5 - 8 minutes under 6.5. These same files now each take over 30 minutes to run. Has anybody had problems with their queries taking longer to run under 7.0? These files are quite large and are comprised of 3 - 4 batches with several queries in each batch. If anybody has any thoughts on the cause please let me know.
Thanks in advance.
how can I get the execution time in millisecond of an MDX query in SSAS?For sql we can get it by:
Code:
set statistics time on
--query----
set statistics time off.But I am not getting anything for MDX.
I have same query but when executed from different server use different plan. when it runs on QA box it is faster and when it runs on PRD it is slow.
Is it possible to force SQL Server to use QA plan by giving a hint?
if t-sql query is perfectly run in development and when I execute in production at that time I want to use execution plan which is in development . so how I can do using cache? I know about hint we can use hint USE_PLANE. but I want to do with cache .
View 1 Replies View RelatedI need to implement one logic similar to rule engine. Below is the example.how to execute all queries in second table order?? what is the best way to implements this
declare @tblRules AS Table (RuleNo INT, RuleDesc NVARCHAR(500), RuleQuery NVARCHAR(MAX), QueryExecutionInterval NVARCHAR(50))
declare @tblRuleResults AS Table (RuleResultID INT, RuleNo INT, ExecuteTime DateTime, NextExecutionTime DateTime, Result NVARCHAR(10))
INSERT INTO @tblRules VALUES ('1','Fail - 2 times within 1 Hour','XXX','Every 15 Minutes')
INSERT INTO @tblRules VALUES ('2','Fail- 2 times within 2 Hour','YYY','Every 30 Minutes')
[code]....
hello friends,
how can we findout the query execuetion time in mili seconds.
for sample
select * from tabelname;
how much time it will take to retrive result.
thanks.
I have another post here regarding SQL 2005 running a query 50% slower than on 2000. It was discovered that 2005 runs the query in series whereas 2000 runs it in parallel.
Even with "Cost Threshold For Parallelism" set to a default value – 0, 2005 still executes my query in series. Does anyone know how to force a query to run in parallel in SQL 2005. I specifically want to set it at the database level.
Hell All,
Following query takes 7 minutes to execute while using search criteria as shown below in blue text(ie. IN(2006,2007)
if criteria changes to =2006 as shown in 2),this takes 2minutes
But I want expected output as in query 1) in less time.
How to optimize following query for execution time?
1)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year
from tbtrans_prowaterbill PB
INNER JOIN MIDC_AREA MA
ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD
INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD
INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD
INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD
INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No
INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type
where pb.billpro_year IN('2006','2007') and MTR_Size = 15 and TCT.Cons_Type = '1A2'
and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF'
group by PB.BillPro_Year
2)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year
from tbtrans_prowaterbill PB
INNER JOIN MIDC_AREA MA
ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD
INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD
INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD
INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD
INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No
INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type
where pb.billpro_year = '2006' and MTR_Size = 15 and TCT.Cons_Type = '1A2'
and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF'
group by PB.BillPro_Year
I have a SQL 7 db with a union query (view), and I'm getting the error, "Thequery processor could not start the necessary thread resources for parallelquery execution." This union query has been in place for about two years nowwith no problems until just now, though I haven't changed anything. Also, Ihave a local copy of the database on my machine, and the query runs fine.As noted, I haven't changed anything in the query, nor in the SQL settings.There is a network administrator, so it's possible that he may have changeda setting, but I don't know what. The query is reproduced below. Any ideasas to what's going on would be appreciated.NeilMain query:SELECT Tmp.INVCUST, Tmp.SDNBR, Tmp.SDBOOK, Tmp.SDIVCLN,Tmp.SDPAID, Tmp.SDPRICE, Tmp.SDCOPIES, Tmp.Location,INVTRY.AUTHILL1, Tmp.INVDATE, INVTRY.SaleSrc,INVTRY.HoldInitFROM (SELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,SDPAID, SDPRICE, SDCOPIES, 'P' AS LocationFROM vwInvoiceDetUNION ALLSELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,SDPAID, SDPRICE, SDCOPIES, 'N' AS LocationFROM vwInvoiceDetNUNION ALLSELECT INVDATE, INVCUST, SDNBR, SDBOOK, SDIVCLN,SDPAID, SDPRICE, SDCOPIES, 'M' AS LocationFROM vwInvoiceDetM) Tmp INNER JOINdbo.INVTRY ON Tmp.SDBOOK = dbo.INVTRY.[Index]vwInvoiceDet:SELECT tabInvoice.INVDATE, tabInvoice.INVCUST,SALEDET.SDNBR, SALEDET.SDBOOK, SALEDET.SDINVNUM,SALEDET.SDPRICE, SALEDET.SDPAID, SALEDET.SDCOPIES,SALEDET.SDIVCLN, tabInvoice.INVNBR, SALEDET.SDIDFROM dbo.tabInvoice INNER JOINdbo.SALEDET ONdbo.tabInvoice.INVNBR = dbo.SALEDET.SDNBR(vwInvoiceDetN and vwInvoiceDetM are similar to vwInvoiceDet.)
View 2 Replies View Related