Hi,
I've got a table with about 500 000 records and growing monthly by about 40 000 records
When I perform the following query:
DELETE from [myTable] WHERE Month = '07' AND Year='2005'
This query will take about 10 minutes to execute. Columns Month & Year are both indexed.
Surely MSSQL can't be this slow on only 500 000 records.
Must I do some other database optimization ???
Thanks
Hi
I have a very large table which I want to clean. I have tried to do it with :
delete from largetable
where largetable.ID not IN(select smallertable.ID from smallertable)
The large table contains circa 6 millions posts.
The delete takes a very long time and the performance curves on the DB server peaks.
Is there any way to speed up a delete clause? Is there any way to not log a delete?
The DB is well indexed.
Hi,
I am trying to delete rows from two tables. My first delete statements are working fine but my second delete is too slow.
The 'results' table has 22728624 rows. The delete below works fine and fast.
DELETE FROM [results]
FROM [results]
INNER JOIN [lots]
ON [lots].[id] = [results].[lot_id]
WHERE lots.ww < 200808
Then I tried to delete the 'lots' table that only has 11524 rows. This delete below is too slow and I have to cancel it because it is taking forever. I have tried to delete one row and it took 11 seconds.
DELETE FROM [lots]
WHERE lots.ww < 200808
I am new in MS SQL and I dont know what to do next. Can you give some advice on how I can improve the delete operation?
Additional tables information:
table name: lots
columns:
id (PK)
lot
ww
table name: results
columns:
id (PK)
lot_id (FK)
(there are 10 more result columns after lot_id)
Regards,
vpdeguz
I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?
View 5 Replies View RelatedI have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
hello,I have the following query. it returns result in less than 1 second.select *from employee e, address awhere e.id=a.emp_idand e.id=1234The problem is that it becomes extremely slow if i take the last lineout. So the query looks like:select *from employee e, address awhere e.id=a.emp_idThe above query is only supposed to return ~500 rows. but i stillhaven't got the result back after 30 minutes.Does anyone have any suggestions about troubleshooting this problem?Thank you in advance!Eddy
View 5 Replies View RelatedI have been trying to optimize a few DAX calculations that I have in my cube and ran into this problem Basically my DAX Measure
Counterparty Collateral Base DKK:=
[Counterparty Collateral Contracts DKK (filtered)]
+
CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16)
is pretty fast,
whereas as soon as I change the 2nd part to only add Account Balance if its>0, it takes ages i.e.
Counterparty Collateral Base DKK:=
[Counterparty Collateral Contracts DKK (filtered)]
+
IF(CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16)>0,
CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16),0)
Offcourse because I check every row I suppose with that "if" statement. Question is how do I improve it?
B
y the Way: [Account Balances DKK] is in the table - Account Balances
[Counterparty Collateral Contracts DKK (filtered)] is in the table- Open Positions
This calculated measure [Counterparty Collateral Base DKK] is in Open Positions.And the query in MDX, that I run to pull this measure, looks like
select {[Measures].[Counterparty Collateral Base DKK]
}on 0,
Non Empty {[Accounts].[Counterpart ID].children
*[Base Currencies].[Currency Code].children
*[Products].[Product Name].children
} on 1
from [model]
where [Dates].[Date].&[2015-06-30T00:00:00]
The requirement is such that I have to add the measures in two separate tables.How can I improve the performance when adding the filter?
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
The following stored procedure is run whenever someone searches my database. When I first created the site, this search took less than 3 seconds. Now, there is more data in the system and the search is taking over 30 seconds.
I am not very good with SQL and I'm sure I am doing some dumb things. Is there a better way to write this query to speed things up?
Thanks a lot,
Chris MessineoSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Chris Messineo
-- Create date: 4/27/2007
-- Description:Get Search
-- =============================================
ALTER PROCEDURE [dbo].[Search]
@Keyword as varchar(40)=NULL
AS
BEGIN
SELECT
Script.Title,
Script.ScriptID,
Script.Logline,
Member.Name
FROM
Script
Join Contest on (Script.ContestID=Contest.ContestID)
Join ScriptComment on (Script.ScriptID=ScriptComment.ScriptID)
Join Member on (Script.MemberID=Member.MemberID)
WHERE
(Title like '%' + COALESCE(@Keyword, Title) + '%' or
Logline like '%' + COALESCE(@Keyword, Logline) + '%' or
Comments like '%' + COALESCE(@Keyword, Comments) + '%')
GROUP BY
Script.Title,
Script.ScriptID,
Script.Logline,
Member.Name
ORDER BY
Title
END
Hi, I have a query which returns normally in less than a couple of seconds. When coupled with a "TOP" statement however, the execution time changes in a way I can't explain.
When using "TOP 1000", the query returns in its normal time. Any top value greater than 10 seems to function normally. However, as soon as we hit this "magic number" the query begins taking 30+ seconds to return.
As far as we can see, there is nothing odd about this query or the data that it is searching, and this problem has us stumped! Can anyone explain why top is producing these results, and is there any optomisation changes that anyone can suggest to give us faster queries in these cases?
Many thanks, Andy J.
I support a website (ASP.NET 2.0) where recently users have been unable to insert data due to timeout issues. The functionality executes a query that inserts a single row into a SQL Server 2005 db table. I tried running this query from the backend, and it took 4:48 to insert a single row! Interestingly, after that initial agony any similar inserts I tried took no time whatsoever. I have checked the execution plan, but unfortunately don't really know what I'm looking for with inserts, as most of my experience with execution plans is with select statements. Any resources anyone could point me to for troubleshooting this would be much appreciated.
Thanks,
-Dave
We are using sql server 2005 with java and the last version of driver JDBC, some querys show bad performance when you send by application but if you use sql query analyzer the performance is excellent. We have take a profiler trace and saw that Java call SP_PREPEXEC when it makes a call of preparestatement. We taken the sentence from profiler (with SP_PREPEXEC) and saw the same problem, the query plan is bad, but if we avoid use the prepare statement the query works fine and the performance problem is overcome. Since we have the application with java and use every time the preparedstatement sentence from java, it is very expensive for us , change the code. Do you have any solution for these problem?
Hi,I would like to delete a record from a table on the condition that a corresponding ID is located in another table, ie. deleting an email message if the user ID is listed as a recipient in a recipient table etc. Here is my SQL statement: DELETE FROM id_email_message WHERE (id_message IN (SELECT id_message FROM recipients
WHERE id_user = 324) AND message.id_message_status = 2) OR (id_message IN
(SELECT id_message FROM message WHERE id_owner = 324 and id_message_status = 2)) The problem is the multiple select statements paired with the delete statement is too much overhead for the server and I always get a timeout server error (at least that's what I'm guessing, the error page and tracing isn't much helpful). Is there a more efficient way to do this?Thanks.Eitan
Hello there... I'm creating a ASP.net Web Application and storing all my data in SQL database.I'm trying to create a Stored Procedure to Delete info from a table:- I have 3 tables: A, B and C: A's primary key is aID and has no foreign key B's primary key is bID and has 1 foreign key: aID (linking this table to table A); C's primary key is cID and has 1 foreign key: bID (linking this table to table B);- As you can see, all 3 tables are linked (A to B and B to C)I want to be able to DELETE all info from C only by giving aID, is this possible in SQL? I can retrieve the info easily through a SELECT statement and using an INNER JOIN (*). I also know how to do solve this by coding it in C# but I'd have to create some unnecessary variables and more than 1 Stored Procedure when it's probably possible to do all in one!* SELECT cID FROM C INNER JOIN B ON B.bID = C.bIDWHERE A.aID= whateverIf anyone knows the solution to my problem, please don't hesitate :p Thanks,SuperJB
View 2 Replies View Relatedhi, i want to delete some records from my table if there is more then 150 records (it should always be max 150records, it can be less and then it shouldent delete anything),
so when it goes over to 151 records i want to delete the oldest record, so i get the new record + 149 old records, is there a simple way to do this?
When does the DELETE statement physically deletes the records? For example, if I execute the DELETE statement and in the middle of the execution I understand that it is wrong. What will happen if I stop it? Will it delete the records partially? I think the deletion happens when the full statement is done but need an expert answer. Thank you.
View 7 Replies View RelatedHi what do i need to add to this stmt to delete the result ?
---------------
select ct_cust1_text01,ct_address,ct_cust1_text09,count(*)from TABLE_NAME group by ct_cust1_text01,ct_address,ct_cust1_text09 having count(*) > 1
---------
i have tried delete * from TABLE_NAME where (select...)
not great at SQL appreciate any help...
Hello
I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys.
However, when I tried to delete a row in the first table I saw this error:
DELETE FROM [dbo].[Names_DB]WHERE [LName_Name]=N'andrews'
Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'.
I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables.
Please advise.
I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers.
Thanks
Regards
Lynn
Hi! I am deleting some records from the database and at the same time inserting them into a new table. I am doing all this with sql querries. How can that be done with triggers. Basically on the delete, i'd like to insert the affected records. Thanks!
James
Hey guys...
I am trying to tidy up my code a bit and have one SQL command (Sub class) to call when needing to insert, update, delete and select.
I have got one class I am testing with that delete from a table support_ticket and then calls RunSQL() again to delete the corresponding tickets in Support_Reply.
however it only seems to want to delete from one table at a time...as i commented out the first sql and it worked and the second fires...but if the first one is active it doesnt fire.
Do anyone on the forum know why this has happened?
Sub DeleteUserTicket(sender as Object, e as EventArgs)
Dim strSQL1 = "DELETE FROM Support_Ticket WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
strSQL1 = "DELETE FROM Support_Reply WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
End Sub
'One class to run the sql statements for the entire page this will reduce in repetitve code as well as reduce code size
Sub RunSQL(strSQL)
Dim objCmd As SqlCommand
Dim sqlConn = New SqlConnection(strConn)
objCmd = New SQLCommand(strSQL, sqlConn)
objCmd.Connection.Open()
'Check that the rows can be deleted if not then return a error.
Try
objCmd.ExecuteNonQuery()
response.redirect("ticketsystemtest2.aspx")
Message.InnerHtml = "<b>Ticket " & txtticketID & " Closed</b> <br/>"
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not update record, please " _
& "ensure the fields are correctly filled out <br>" & ex.Message & " " & ex.Number
Message.Style("color") = "red"
End If
End Try
objCmd.Connection.Close()
sqlConn = nothing
objcmd = nothing
End Sub
cheers
One of our developers just deleted a ton of records- is there any way we can recover this data? (we can't use a backup since a ton of changes were made since we last backed up)
Thanks-
Jack
i am having problem running a simple delete statement against a table. it just hangs is there anything i should look at? the table has 4 primary keys and the index makes up of the 4 keys and ideas?
i viewed the delete statement with the execusion plan and this is what i saw.
delete -> index delete/delete -> sorting the input -> table delete/delete -> Top -> Index scan.
Hello, currently I have a query like this:
PHP Code:
SELECT *
FROM relations INNER JOIN
paths ON relations.path = paths.path_id
WHERE
(paths.links = '161') AND (relations.node1 = 162) OR
(paths.links = '161') AND (relations.node2 = 162) OR
(paths.links = '162') AND (relations.node1 = 161) OR
(paths.links = '162') AND (relations.node2 = 161) OR
(paths.links LIKE '162%') AND (relations.node1 = 161) OR
(paths.links LIKE '%162') AND (relations.node2 = 161) OR
(paths.links LIKE '161%') AND (relations.node1 = 162) OR
(paths.links LIKE '%161') AND (relations.node2 = 162) OR
(paths.links LIKE '%161;162%') OR
(paths.links LIKE '%162;161%')
ORDER BY relations.node1
Don't pay attention to the 161 and 162 things, is just test data, now my problem is that I want to transform that into a DELETE statement, but I can't find the right way to do it, so far I managed to do something like:
PHP Code:
DELETE relations
FROM relations INNER JOIN
paths ON relations.path = paths.path_id
WHERE
(paths.links = '161') AND (relations.node1 = 162) OR
(paths.links = '161') AND (relations.node2 = 162) OR
(paths.links = '162') AND (relations.node1 = 161) OR
(paths.links = '162') AND (relations.node2 = 161) OR
(paths.links LIKE '162%') AND (relations.node1 = 161) OR
(paths.links LIKE '%162') AND (relations.node2 = 161) OR
(paths.links LIKE '161%') AND (relations.node1 = 162) OR
(paths.links LIKE '%161') AND (relations.node2 = 162) OR
(paths.links LIKE '%161;162%') OR
(paths.links LIKE '%162;161%')
But that would delete only from the relations table and not from the paths table. I need to delete from both tables.
Can anyone help me please? Its kinda urgent.
Thansk!
Hi,
I need to delete the following records (from enrollment_fact):
SELECT
a."STU_SID",
a."SCHOOL_YEAR",
a."DATE_SID",
a."LOC_SID"
FROM "dbo"."ENROLLMENT_FEX2" b,
"dbo"."LOCATION_DIM" c
LEFT OUTER JOIN
"dbo"."ENROLLMENT_FACT" a
on c."LOC_SID" = a."LOC_SID"
WHERE
b."LOC_KEY" = c."LOC_KEY"
and
a."DATE_SID" between b."MIN_DATE" and b."MAX_DATE"
This is the approach (excuse the misuse of the concat function, but you get the idea)
DELETE FROM "dbo"."ENROLLMENT_FACT"
WHERE CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID")
IN (
SELECT DISTINCT CONCAT (a."STU_SID",
a."SCHOOL_YEAR",
a."DATE_SID",
a."LOC_SID"
)
FROM "dbo"."ENROLLMENT_FEX2" b,
"dbo"."LOCATION_DIM" c
LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a
ON c."LOC_SID" = a."LOC_SID"
AND a."DATE_SID" BETWEEN b."MIN_DATE"
AND b."MAX_DATE")
comments? better way? (without using an sp)
thanks
well i have 2 table one name detcom and another entcom stored in DB1 the key for both to join on is lets say A, B, C . I need to check if there are records based on the key A, B, C of both table where C EQUALS to '80_300_113' and if there are delete them and then grab data from another
database named DB2 on same server (same instance) wich contains the same tables entcom and detcom and insert all the data from those tables into the same tables in DB1 based on the key and where C = '80_300_113'
PLZ help
Hi,My question is :
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table?
Thanks !
I have a rather tricky SQL delete query I’m trying to perform, preferably without cursors. I have the following data set, where I want each of the first changes to the status.
IDStatus
1Good
2So-So
3So-So
4Bad
5Bad
6Bad
7Bad
8Bad
9So-So
10Good
So given the above data set I would want to delete the following IDs: 3, 5, 6, 7, and 8. As they not the first in order. The problem is that while I can get isolate and save first instance of each status (in this case 1, 2, and 4), I can’t get the what I want. Is there a SQL way of accomplishing this.
i have problem to discuss regarding delete statement. Due to the bad design of application i have added some duplicate values in table. i have fixed the issue for new insert but how to delete my old bad id. i have table having three column,
ID1 ID2 ID3
101 2 3
102 2 3
103 2 4
104 3 4
in my case first two record are duplicate i want to keep first one and delete the other.
I have duplicate rows of data with the exact same data, for multiple sets of data.For example,
1 Fred Flintstone
1 Fred Flintstone
2 Barney Rubble
2 Barney Rubble
etc...
If I use the delete and where command, it will delete both rows and I want to keep one of them. I don't want to copy down each one and re insert as there are too many columns for each one and there are too many rows also.I was thinking of a delete row-number command in a where statement. but tried multiple forums and its not working.
I have to write a delete statement that deletes all customers that have not put in an order I must use a subquery in the exist operator.
View 5 Replies View RelatedIs it possible to create a function that deletes records from a table ?
CREATE FUNCTION F_TSImported_Delete()
returns int
as
Begin
delete from ts_imported
return 0
end
GO
This throws error like this:
Invalid use of side-effecting or time-dependent operator in 'DELETE' within a function.
i am creating an insert based on a select statement -- i need to delete the row from the select statment table after it has been inserted
something like
insert into table_insert(value1, value2)
(select table_exclude_id, value1, value2 from table exclude)
delete from table_exclude where table_exclude_id in "the select statement"
can you do this?
I have a simple database program which uses the following statement to delete rows by a date range. In testing the SQL statement, it worked, but when using it in the VB program, it says it worked, but doesn't remove the row. I am using a local database. Please help.
cmdDeletePayments.CommandType = Data.CommandType.Text
cmdDeletePayments.CommandText = "DELETE Payments WHERE (CheckDate BETWEEN @BeginningDate AND @EndingDate)"
cmdDeletePayments.Parameters("@BeginningDate").Value = CType(dtpBeginning.Text, Date)
cmdDeletePayments.Parameters("@EndingDate").Value = CType(dtpEnding.Text, Date)
Try
conPayments.Open()
iRows = cmdDeletePayments.ExecuteNonQuery()
conPayments.Close()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
MessageBox.Show(iRows.ToString + " records deleted.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)