Tuesday, May 30, 2006

Another for the WTF

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:

Peter K said...

And that's why God created the DBA :D

Anonymous said...

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.

Anonymous said...

Don't forget to reserve your turkey!!

Anonymous said...

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

Popular shoes said...

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


Unknown said...

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

Unknown said...

And that's why God created the DBA :Dswtor gold
buy swtor gold
cheap swtor gold
tor credits
buy tor credits
cheap tor credits