Optimizing Query Execution...

Dec 17, 2005

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?

View 1 Replies


ADVERTISEMENT

Optimizing A Query

Jun 3, 2004

Hello,
I am hoping someone here can help me optimize the following query:
SELECT
INCOMING.DATE_TIME, INCOMING.URL, INCOMING.HITS,
USER_NAMES.USER_LOGIN_NAME,
CATEGORY.NAME
FROM
(wsHQMay2004.dbo.INCOMING INCOMING INNER JOIN wsHQMay2004.dbo.CATEGORY CATEGORY ON INCOMING.CATEGORY = CATEGORY.CATEGORY)
INNER JOIN wsHQMay2004.dbo.USER_NAMES USER_NAMES ON INCOMING.USER_ID = USER_NAMES.USER_ID
WHERE
INCOMING.DATE_TIME >= '2004-05-01 00:00:00.00' AND
INCOMING.DATE_TIME < '2004-06-01 00:00:00.00'
ORDER BY
INCOMING.URL ASC


I am just hoping to get some tips on perhaps a better way to write this query as right now, due to the size of the incoming table, this query just takes forever.

Any advise will be apreciated.

Thanks.

View 5 Replies View Related

Need Help Optimizing A Sql Query...

May 28, 2004

we have an insurance program up and running in our regions and we get random reports of slowness. in an effort to track down all facets of slowness i am looking at all my sql code to make sure it is as efficient as possible. I know a little about SQL and writing SQL statements, enough to help me do my job well. but i do not write optimized code.

if request.form("selPolicyNum") <> "" then
sqlPolicyInfo = "SELECT PIEffectiveDate, PIExpirationdate from PIMaster where PIPolicyNum='" & request.form("selPolicyNum") & "'"
Set rsPolicyInfo = Server.CreateObject("ADODB.Recordset")
Set rsPolicyInfo.ActiveConnection = webLookupConn
'rsPolicyInfo.CursorType = adOpenDynamic
'rsPolicyInfo.LockType = adLockOptimistic
rsPolicyInfo.Source = sqlPolicyInfo
'rsPolicyInfo.CursorLocation = adUseClient
rsPolicyInfo.Open
'response.write sqlPolicyInfo
end if


if request.form("selPolicyNum") <> "" then
sqlRemarkInsert="INSERT INTO clRemarks (PolicyNum, AccountNum, UserID, RemarkBody, CreationDate, PolEffectiveDate, PolExpDate, RemarkCategory1, RemarkCategory2, RemarkCategory3, RemarkCategory4, RemarkCategory5, RemarkCategory6, RemarkCategory7, RemarkCategory8, RemarkCategory9, RemarkCategory10, RemarkCategory11, RemarkCategory12, RemarkCategory13) VALUES ('"
sqlRemarkInsert=sqlRemarkInsert & request.form("selPolicyNum") & "', '" & request.form("txtAcctNum") & "', '" & request("aysmenu")("userid") & "', '" & Replace(request.form("RemarkBody"),"'","''") & "', '" & CurrDate & "', '" & rsPolicyInfo("PIEffectiveDate") & "', '" & rsPolicyInfo("PIExpirationDate") & "', '" & request.form("chkIssuingInstructions") & "', '" & request.form("chkLossControl") & "', '" & request.form("chkLossHistoryClaims") & "', '" & request.form("chkReinsurance") & "', '" & request.form("chkMVRDriverIssues") & "', '" & request.form("chkBilling") & "', '" & request.form("chkGeneral") & "', '" & request.form("chkCancellationDNR") & "', '" & request.form("chkPolicyAmendments") & "', '" & request.form("chkExperienceRatingInfo") & "', '" & request.form("chkDiscretionaryPricing") & "', '" & request.form("chkPremiumAudit") & "', '" & request.form("chkQuotingInstructions") & "')"
else
sqlRemarkInsert="INSERT INTO clRemarks (AccountNum, UserID, RemarkBody, CreationDate, RemarkCategory1, RemarkCategory2, RemarkCategory3, RemarkCategory4, RemarkCategory5, RemarkCategory6, RemarkCategory7, RemarkCategory8, RemarkCategory9, RemarkCategory10, RemarkCategory11, RemarkCategory12, RemarkCategory13) VALUES ('"
sqlRemarkInsert=sqlRemarkInsert & request.form("txtAcctNum") & "', '" & request("aysmenu")("userid") & "', '" & Replace(request.form("RemarkBody"),"'","''") & "', '" & CurrDate & "', '" & request.form("chkIssuingInstructions") & "', '" & request.form("chkLossControl") & "', '" & request.form("chkLossHistoryClaims") & "', '" & request.form("chkReinsurance") & "', '" & request.form("chkMVRDriverIssues") & "', '" & request.form("chkBilling") & "', '" & request.form("chkGeneral") & "', '" & request.form("chkCancellationDNR") & "', '" & request.form("chkPolicyAmendments") & "', '" & request.form("chkExperienceRatingInfo") & "', '" & request.form("chkDiscretionaryPricing") & "', '" & request.form("chkPremiumAudit") & "', '" & request.form("chkQuotingInstructions") & "')"
end if
rsRemarkInsert=ComProConn.Execute (sqlRemarkInsert)

rsPolicyInfo.close
Set rsPolicyInfo=nothing


that is the code used to store a remark into the system. is this code optimized already or should some of the db parameters be changed to make things faster? this is just an example of many of the SQL statements that i may or may not have to fix. thank you for any and all help.

View 1 Replies View Related

Help Optimizing Query

Jun 23, 2008

I have the following query that works fine but i'm wondering if there is a way to optimize it better as when I analyze through sql profiler it is at the top of the list of using the cpu

SELECT DISTINCT site, d, (SELECT COUNT(id) FROM anP aPV2 WHERE aPV2.confirmed=1 and aPV2.stage=2 and aPV2.inserted=0 and aPV2.site=aPV1.site and aPV2.d>=aPV1.d and aPV2.d<=aPV1.d) AS mycount FROM anP aPV1 WHERE confirmed=1 AND stage=2 AND inserted=0 ORDER BY site,d

View 8 Replies View Related

Optimizing Query

Sep 18, 2007

Hi,

I have the below query written so that i do not insert entries that is already existing in the table. I am trying to put in 70000 entries at a single shot and it breaks down. Can anybody help me optimize the below query so that it doesnt break? Is there any other way I can write this query?

Please do help me with this. Thanks in advance. The table in which i am inserting the entries has a composite key composed of ACCT_NUM_MIN and ACCT_NUM_MAX. I am getting this from a table which doesnt have a primary key(CORE)


INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
SELECT UID , LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), BIN, BUS_ID, BUS_NM, ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE o
WHERE NOT EXISTS (SELECT * FROM CRF i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN
AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)

View 20 Replies View Related

Optimizing A Query

Nov 27, 2007

hi all

View 8 Replies View Related

Optimizing A Big Query

Feb 9, 2007

To start with, I'll give a simplified overview of my data.BaseRecord (4mil rows, 25k in each Region)ID | Name | Region | etcOtherData (7.5mil rows, 1 or 2 per ID)ID | Type(1/2) | DataProblemTable (4mil rows)ID | ConcatenatedHistoryThe concatenated history field is a nvarchar with up to 20 differentpipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|11/12/2004Q|'Using left outer joins (all from base, the rest optional) I've got aview something like:View (4mil rows)ID | Name | Region | etc | Data | Data2 | ConcatenatedHistoryQuerying it, it takes about 15-20 seconds to do this:Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory


Quote:

View 5 Replies View Related

Help With Optimizing Query

Feb 2, 2007

if I have the following tables:

Table Customer_tbl

cust_id as int (PK), ...

Table Item_tbl

item_id as int (PK), ...

Table Selected_Items_tbl

selected_item_id as int (PK), cust_id as int (FK), item_id as int (FK), ...

-------

With the following query:

select cust_ID from selected_items_tbl WHERE item_id in (1, 2, n) GROUP BY cust_id, item_id HAVING cust_id in (select cust_id from selected_items_tbl where item_id = 1) AND cust_id in (select cust_id from selected_items_tbl where item_id = 2) AND cust_id in (select cust_id from selected_items_tbl where item_id = n)

-------

Each of these tables has other items included. Selected_Items_tbl holds zero to many of the items from the item_tbl for each customer. If I am searching for a customer who has item 1 AND item 2 AND item n, what would be the most efficient query for this? Currently, the above query is what I am working with. However, it seems that we should be able to do this type of search in a single query (without subqueries).

View 3 Replies View Related

Optimizing Query To Run

Sep 27, 2006

I'm trying to get a query to run which looks at completed orders that have not had another paid order in 180 days. The database I'm running it against is very large so I can't get it to complete. Where's what I've got:

select Date =cast(cl1.cl_rundate as datetime(102)),count(cl1.cl_recno) as 'Completed Initials', cl1.cl_status as Status from dbo.vw_Completedorders cl1 where cl1.lob_lineofbusiness = 'aaa'
and cl1.cl_rundate > '20060801' and not exists (
select cl2.cl_company from dbo.vw_Paidorders cl2 where
cl2.lob_lineofbusiness = 'aa'and cl2.cl_company = cl1.cl_order and cl2.cl_rundate > '20060101' and datediff(day,cl2.cl_rundate,cl1.cl_rundate) < 180)
group by cl1.cl_status, cl1.cl_rundate

View 11 Replies View Related

Optimizing SELECT Query

Nov 7, 2007

Hi
I have one table (tableDemo) with following structure:
TSID                          bigint (Primary Key)TskID                         bigint Sequence                   bigint Version                      bigint Frequency                  varchar(500) WOID                         bigint DateSchedule             datetime TimeStandard             real MeterEstimated          real MeterLast                   real Description                 ntext CreatedBy                  bigint CreatedDate               datetime ModifiedBy                 bigint ModifiedDate              datetime Id                               uniqueidentifier 
I have 8000 records in this table. When I fire simple select command (i.e. select * from tableDemo) it takes more than 120 seconds.
Is there any techniques where I can access all these records within 2-3 seconds ?
Regards,
ND

View 2 Replies View Related

Optimizing The Query And Index

Nov 4, 2000

hi, what are the tools that I can use to Optimize the query/ index.

I know that if I am running a query on a table I create index on the fields where I use in the where clause, is this a right thinking.

Someone told me how do I determine what columns should be indexed, I told him the fields that I use in the where clause should be indexed to speed up the process of retrieving the data. ..... is this answer correct. if not please advice the correct one.
Thanks

Ahmed

View 1 Replies View Related

Poor Query Optimizing

Nov 4, 2007

Hi there,

I've got a SQL 2005 table(DataTable) with 5.5 million rows and a clustered index on an integer column. The column is a foreign key pointing to another table (LookupTable) that associates the integer with an nvarchar(50) column. Both integer and nvarchar(50) columns are in a clustered index.

I'm running equivalent select statements with slightly different where clauses, and am not seeing the performance similarities I'd expect.

select * from DataTable join LookupTable on DataTable.Integer = LookupTable.Integer

1. Setting the where clause to the nvarchar(50) value on the LookupTable takes 64 seconds.
2. Setting the where clause to Integer = (select integer from LookupTable where nvarchar(50) = 'mysearchstring') takes 61 seconds.
3. Setting the where clause to Integer = 526 takes 1 second.

Is this poor design in SQL, or is it a misconfiguration? I would have expected SQL to modify my query to essentially be #3 in all cases before touching the DataTable, which would cause all three versions to take 1 second.

Thanks,
Jason

View 2 Replies View Related

Optimizing This Duplicate Finding Query

Sep 11, 2005

Hi everyone!

I am parsing a database structed like the following for duplicates.


Code:


keywordnegativeexactbroadphrase
Alpha0.120.36
Alpha0.120.37
Alpha0.120.37
Alpha0.220.37
Alpha0.120.37
Alpha0.120.37
Alpha0.120.37
Beta0.43212
Charlie0.240.31
Delta0.72212
Epsilon410.31
Foxtrot250.22
Grape420.215
Hotel 230.13
Indigo0.3721
Juliet21220.14
Kilroy0.3110.15
Lemon0.2201
Mario0.2502
Nugget0.130.72
Oprah2141
Polo01225
Polo01224
Polo01225
Q-Bert31442
Romeo12100.1
Salty2200.1
Tommy10.30.132.4
Uri10.30.132
Uri20.20.132
Uri20.30.122
Uri20.30.131
Vamprie0.120.1315
Wilco0.210.21
X-Ray00.220
Yeti020.20
Zebra1310



The duplicates that this thread relates to are the kind with duplicate "keyword" entries AND dissimilar field entries; i.e. :


Code:


keyword negative exact broad Phrase
Polo 0 122 4
Polo 0 122 5



I've come up with an SQL query that seems to return all of these duplicates (save one of each type- the 'real', unique entry). However I think I made the query very inefficient. My SQL is very bad; this query will be running over tens of thousands of rows, so if it can be at all optimized I would greatly appreciate your help!

What I have so far is:



Code:


string query1 = "SELECT * FROM TableName" +
" WHERE EXISTS (SELECT NULL FROM TableName" + " b" +
" WHERE b.[keyword]= " + "TableName"+ ".[keyword]" +
" AND b.[negative]<> " + "TableName"+ ".[negative]" +
" ORb.[keyword]= " + "TableName"+ ".[keyword]" +
" ANDb.[exact]<> " + "TableName"+ ".[exact]" +
" ORb.[keyword] = " + "TableName"+ ".[keyword]" +
" ANDb.[broad]<> " + "TableName"+ ".[broad]" +
" ORb.[keyword]= " +"TableName"+ ".[keyword]" +
" ANDb.[phrase]<> "+"TableName"+ ".[phrase]" +
" GROUP BY b.[keyword], b.[broad], b.[exact]" +
" HAVING Count(b.[keyword]) BETWEEN 2 AND 50000)" ;



the algoritm seems to check every column of every row in order to determine a duplicate. Seems straightforward to me, but alas slow...

Is there a better/faster way I can do this? Thanks for you help!

View 1 Replies View Related

Optimizing Query - Product Tips

Jan 6, 2004

Hello everyone!
I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it...
The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...

query (simplificated)
-------------------------------------------------
SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE
prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394))
AND
(prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2))
-------------------------------------------------
end query

(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...

Thanks for any time spent!

View 14 Replies View Related

Optimizing A Query To Delete Duplicates

Jul 20, 2005

I have a DELETE statement that deletes duplicate data from a table. Ittakes a long time to execute, so I thought I'd seek advice here. Thestructure of the table is little funny. The following is NOT the table,but the representation of the data in the table:+-----------+| a | b |+-----+-----+| 123 | 234 || 345 | 456 || 123 | 123 |+-----+-----+As you can see, the data is tabular. This is how it is stored in the table:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 || 3 | a | 123 || 3 | b | 234 |+-----+-----------+------------+What I need is to delete all records having the same "Row" when there existsthe same set of records with a different (smaller, to be precise) "Row".Using the example above, what I need to get is:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 |+-----+-----------+------------+A slow way of doing this seem to be:DELETE FROM XWHERE Row IN(SELECT DISTINCT Row FROM X x1WHERE EXISTS(SELECT * FROM X x2WHERE x2.Row < x1.RowAND NOT EXISTS(SELECT * FROM X x3WHERE x3.Row = x2.RowAND x3.FieldName = x2.FieldNameAND x3.FieldValue <> x1.FieldValue)))Can this be done faster, better, and cheaper?

View 3 Replies View Related

Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net

Mar 1, 2007

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

View 2 Replies View Related

DB Engine :: Multiple Execution Of Query Pattern Generates Same Query Plan

Oct 6, 2015

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?

View 3 Replies View Related

Prevent Query-Analizer From Caching The Query Execution?

Apr 11, 2008

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.

View 4 Replies View Related

Query Execution

May 20, 2008

What are the Query Execution plan. How to read and interpret query execution plan.

spatle

View 3 Replies View Related

Query Execution

Jan 23, 2007

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

View 1 Replies View Related

Query Execution............

Sep 21, 2007

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.

View 7 Replies View Related

Sql Query Execution Using Asp.net Became Very Slow

Dec 2, 2003

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

View 9 Replies View Related

Execution Of Query Is Very Slow

Jul 1, 2004

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

View 1 Replies View Related

Sql Query Execution Speed

Sep 25, 2000

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

View 1 Replies View Related

Parallel Query Execution

Dec 6, 2001

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

View 1 Replies View Related

SQL 6.5 Query Execution Plan .

Sep 24, 2002

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.

View 3 Replies View Related

SQL 2000 Query Execution

May 21, 2002

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!!

View 2 Replies View Related

How To Speed Up The Query Execution In Sp.

Aug 21, 2004

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

View 6 Replies View Related

Query Never Ends Execution

Apr 16, 2007

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?

View 3 Replies View Related

Query Execution Problem

Jun 18, 2008

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.

View 3 Replies View Related

Query Execution Time

Mar 21, 2007

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

View 2 Replies View Related

Track Query Execution

May 11, 2007

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!

View 4 Replies View Related

Query Execution Speed

Jul 23, 2005

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 Related







Copyrights 2005-15 www.BigResource.com, All rights reserved