|
Using sp_configure check option query governor cost limit. Most likely the value is 100000 meaning that if the cost is over that, query won't run.
The need to optimize rises from a bad design
|
|
|
|
|
thank you for your reply,
Can you give me an Exact Query to Run to do this ?
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
It may be that server properties window also shows this option in connection page (Use query governor...). But if it doesn't, you can do it in T-SQL like this:
EXEC sp_configure 'show advanced options', 1 -- enable advanced options
RECONFIGURE -- apply the new setting
sp_configure -- list all configuration options and their values
If you want to change the running value:
EXEC sp_configure 'query governor cost limit', 1000000 -- either modify the value
EXEC sp_configure 'query governor cost limit', 0 -- or disable query governor
RECONFIGURE -- apply the new setting
However, your original cost is really high (984356). It means that the estimated running time for your operation is about 11 days. So I believe that there must be some problem in your procedure PROPERTY_SUMMARY_RECREATE (starting from line 72).
The need to optimize rises from a bad design
|
|
|
|
|
Good Aftenoon
By Disabling the query governor helped a lot, now the SP is not giving any Errors, you definately right that the SP PROPERTY_SUMMARY_RECREATE needs to be optimized.
Thank you 
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
No problem
The need to optimize rises from a bad design
|
|
|
|
|
currently I have a table need some data populated into it, one attribute requires to show SYSDATE, can any one tell me how to insert a SYSDATE value into the table?
or can I set the value of this attribute the time I create the table??
many thanks....
|
|
|
|
|
SYSDATE? What database technology are you talking about?
|
|
|
|
|
|
You can use SYSDATE as a value so in INSERT statement you can say:
INSERT INTO TableName (Column1, Column2,...) VALUES (Value1, SYSDATE,...)
If you want to make SYSDATE a default value for a column, you can use DEFAULT constraint for the column.
Also you can set the default value in trigger using PL/SQL.
The need to optimize rises from a bad design
|
|
|
|
|
I need to be able to pull information from two tables. The first table is the master table that contains the essential information about some object, wherease the second table has some other data related to that object. I need to pull information from the first table regardless of whether there is a match in the second table (class LEFT JOIN), but if there is a record in the second table, I need to pull data only from the latest record in the second table. So, the query would look something like:
SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 ON table1.field1=table2.field1 WHERE ????
I have an autonumber field in the 2nd table that can be used to retrieve the latest record from table2. Could that be used to retrieve the latest record, and, if so, how?
Thanks.
|
|
|
|
|
Your concept is correct, but a better table representation would help. What is the data in table2? What fields do you need if present and what is the autonumber field?
Tim
|
|
|
|
|
Thanks. Let's see.
From table2 at present I only need to retrieve one field of table, so I don't actually need table2.* in the select statement. Rather, I would just need something like table2.VE. The data in table 2 is simply the results of some process run on the object described in table1. They are linked by some unique object identifier. I need to pull information about the object regardless of whether the process has been run or not. If the process has been run, I need to retrieve the VE field for that object. The basic problem is that there may be multiple records for the proces run on the object, so multiple records in table2 for the object it is linked to in table1. Some of those records may be from the same day, while others may be from 3 months, 6 months, or 10 years ago. I need the latest when pulling up the data.
The autonumber field in table2 is called ID and the name of the field in table2 that I need to retrieve now is called VE (but I may need to retrieve other fields later).
I appreciate the help.
|
|
|
|
|
select t1.*, t2.ve
from table1 t1 left outer join
(select id, VE, max(recorddate) from table2
group by id, VE) t2
on t1.id = t2.id
Does that help?
Tim
|
|
|
|
|
Thanks, Tim. The query, modified to best match my situation, seemed to work as it should for the most part, though the result set still had more records than the number of records in table1. Table1 has 10080 records, whereas the result set from the query had 10087. I'll have to sort through it and find out where those additional records are coming from, but this helped me to get started.
Thanks again.
|
|
|
|
|
|
Tim,
I don't know if you're still paying attention to this thread, but this is what I tried based on your advice:
SELECT Details.*, Results.VE
FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);
It worked for the most part, but there were 7 more records in this result table from this query than are present in the 'LEFT' table. I scanned through the result table to find the duplications and it seems that the duplications were instances in the 'RIGHT' table where more than one record was present for the object stored in the left table.
As I mentioned Friday, there can be more than one process result in the RIGHT table for the object stored in the LEFT table, but I only want the latest process result. In most cases, this query eliminates the duplicate process results, but if there were two process results with different VE data, then it seems the subquery in the following query returns both records. If you could help me understand why, I would appreciate it. Thanks.
SELECT Details.*, Results.VE
FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);
|
|
|
|
|
You are getting multiple values because you are selecting VE, SerialNumber and ID from results. If you only want the lastest VE, remove SerialNumber from your query. That should give you only the latest VE value.
If you have more questions, ask away.
Tim
|
|
|
|
|
Thanks, but I don't need just the latest VE, but the latest VE tied to that serial number. The VE field is simply a text field that can be PASS, FAIL, or an empty string; there is nothing remotely unique about it. I need the VE value, but it only matters insofar as it's "attached" to the serial number.
|
|
|
|
|
I believe what you are looking for is known as an "Outer Join".
|
|
|
|
|
Thanks, but I believe LEFT JOIN and LEFT OUTER JOIN are interchangeable with the database I am using. I believe, though, that with the LEFT JOIN, if there is more than one record in the 'right' table that matches the fields in the left table, then the additional records are also included in the result set. What I need is just one particular matching record from the 'right' table; in this case, the last matching record from the 'right' table.
|
|
|
|
|
ASALAMOALEKUM All,
i am working on virtual community and i am struct in btw...
actuall i have to maintain friend network, in this regard i have a Friend table in my sql database which contain columns of User_id and Friens_id (none of them is primary)
example: if table view is:
serial# User_id Friend_id
0 001 007
1 001 003
2 002 009
3 003 005
4 004 002
5 005 010
now if user 001 visits the profile 0f user with id 010 so as a friend network information it shud be shown "001->003->005->010"
the retrieval of this(which is between quotes) information is creating problem for me, can anyone suggest me a sql query for this... ??
reply as soon as possible!
i wud be very thankful!
Sana
$@N@
|
|
|
|
|
What database are you using? SQL Server, Oracle, something else?
The need to optimize rises from a bad design
|
|
|
|
|
i am using SQL server
$@N@
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
