face time out when run query with nested select
-
hi to all when i run this query i faced with timeout
DELETE FROM bml.LoanInstallment WHERE ID IN (select * from wfInstanceNodes where ID=@nodeID)
this occurred when nested select return null and i think it is because of that i have no index on id; and i set index to id but still this query faced with time out note that bml.LoanInstallment has 11000000 record and this query run without problem for a table with a few records. thanks for any help
-
hi to all when i run this query i faced with timeout
DELETE FROM bml.LoanInstallment WHERE ID IN (select * from wfInstanceNodes where ID=@nodeID)
this occurred when nested select return null and i think it is because of that i have no index on id; and i set index to id but still this query faced with time out note that bml.LoanInstallment has 11000000 record and this query run without problem for a table with a few records. thanks for any help
Do you run into a timeout when you do a SELECT instead of a DELETE? E.g. SELECT COUNT(ID) FROM bml... etc.? And how many entries are to be deleted? Are there indices for the ID columns in both tables? Are there any constraints, e.g. an OnDeleteCascade?
-
hi to all when i run this query i faced with timeout
DELETE FROM bml.LoanInstallment WHERE ID IN (select * from wfInstanceNodes where ID=@nodeID)
this occurred when nested select return null and i think it is because of that i have no index on id; and i set index to id but still this query faced with time out note that bml.LoanInstallment has 11000000 record and this query run without problem for a table with a few records. thanks for any help
-
-
Do you run into a timeout when you do a SELECT instead of a DELETE? E.g. SELECT COUNT(ID) FROM bml... etc.? And how many entries are to be deleted? Are there indices for the ID columns in both tables? Are there any constraints, e.g. an OnDeleteCascade?
-
hi to all when i run this query i faced with timeout
DELETE FROM bml.LoanInstallment WHERE ID IN (select * from wfInstanceNodes where ID=@nodeID)
this occurred when nested select return null and i think it is because of that i have no index on id; and i set index to id but still this query faced with time out note that bml.LoanInstallment has 11000000 record and this query run without problem for a table with a few records. thanks for any help
Personally, I don't like sub-selects (they seem so 90s), I'd rather JOIN them. (Provided your system allows it)
-- Transact-SQL extension
USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00; -
ok i use select id instead of select * but im still face with timeout i think that this is because of that my nested select return an empty column what you thinks ? how can i handle this problem? thanks for answer
mhd.sbt wrote:
i use select id instead of select * but im still face with timeout
A command-timeout or a connection-timeout? How long does the query take when run on the server directly? Can you post the schema of the tables here? It would be weird if you'd select the same key that you're filtering on.
mhd.sbt wrote:
what you thinks ?
I'm pretty sure that you can select "where in null" without hitting performance-problems of said magnitude.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
mhd.sbt wrote:
i use select id instead of select * but im still face with timeout
A command-timeout or a connection-timeout? How long does the query take when run on the server directly? Can you post the schema of the tables here? It would be weird if you'd select the same key that you're filtering on.
mhd.sbt wrote:
what you thinks ?
I'm pretty sure that you can select "where in null" without hitting performance-problems of said magnitude.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]