Question from a developer:
"Why does this query take so long to process?"
SELECT
1
FROM
HEADER
WHERE
1 IN (SELECT 1 FROM DETAIL WHERE
1=1
AND DETAIL.HEADERID = '3432223'
AND DETAIL.SITEID='OVA3'
AND DETAIL.status in ('DKAPPR','DFFAPPR','UIUAPPR'))
;
Can anybody spot the problem?
They are looking for a simple return, they do not look at the value of
the return as long as one row is returned, the java returns TRUE and
they continue on.
They run the above SQL for every record in HEADER that they are looking
to update to look to see if there are corresponding detail lines. The
HEADER table has 200K + records.
I suggested this one to them.
SELECT
1
FROM
HEADER
WHERE 1=1
AND HEADERID IN (SELECT
HEADERID
FROM
DETAIL
WHERE
1=1
AND DETAIL.HEADERID = '200504505'
AND DETAIL.SITEID='OVA3'
AND DETAIL.status in ('DKAPPR','DFFAPPR','UIUAPPR')
)
;
There are much better ways to do this, I will let the developer work on
those, he is very embaressed and is spending the needed time on the
issue and has since this one found other queries that were along the similiar lines.
7 comments:
And that's why God created the DBA :D
I would check the return vectors. If the input parameter and output parameters are of a different user defined data-type, you may find your queries take much longer than expected.
Don't forget to reserve your turkey!!
THis webpage is not bad either. It displayed this nich connect string with userid/password and a tablename ....
<%
strUrl = LCase(Request("strUrl"))
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={MySQL};SERVER=localhost;DATABASE=fjernsynet_dk;UID=fjernsynet_dk;PWD=aTVDEanc;"
objConn.Open
objConn.Execute "INSERT INTO tblKlik(strUrl, strIP) VALUES('" & strUrl & "', '" & Request.ServerVariables("REMOTE_ADDR") & "');",3,3
objConn.Close
Set objConn = Nothing
Response.Redirect strUrl
%>
Very nice with all these info :-)
From:
http://www.fjernsynet.dk/Includes/Url.asp?strUrl=http://www.gratissiden.dk/publikum/publikum.php?show_id=63
It is appropriate time to make a few plans for the longer term and it is time to be happy. I've learn this publish and if I could I desire to suggest you some attention-grabbing things or advice.
More tags:
wow gold buy cheap
wow buy gold cheap
wow buy cheap gold
wow gold fast cheap
cheap wow gold fast
Can anybody spot the problem? They are looking for a simple return, they do not look at the value of the return as long as one row is returned, the java returns TRUE and they continue on. They run the above SQL for every record in HEADER that they are looking to update to look to see if there are corresponding detail lines. The HEADER table has 200K + records. I suggested this one to them.guild wars 2 gold
buy guild wars 2 gold
cheap guild wars 2 gold
cheapest guild wars 2 gold
guild wars 2 gold for sale
And that's why God created the DBA :Dswtor gold
buy swtor gold
cheap swtor gold
tor credits
buy tor credits
cheap tor credits
Post a Comment