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.

11 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

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
Oes Tsetnoc | Semangat Mengembalikan Jati Diri Bangsa

Love polo said...

Best Place to Buy Diablo 3 Gold with Prepaid Card
Nowadays, various payment methods were accepted by d3 gold suppliers. You must want a best place to buy diablo 3 gold in a safe, fast and simple way. Here, diablohome introduce prepaid card to you. Following are some details.
Buy Diablo 3 Gold with Prepaid Card
Diablo home accept prepaid card payment, such as MasterCard, VISA and Paysafecard, etc. They are safe and convenient, just take a look at the most important advantages of prepaid cards.
1. Prepaid cards can be applied online instantly.
Prepaid cards use the deposited money, there are no repayments to be made and thus no late payment fees and other associated penalties.
2. Prepaid cards are widely accepted.
They can be used on the internet, on phone and all other vends.
3. Adding money to your prepaid card account is a breeze using direct deposit.
tags: buy diablo 3 gold diablo gold buy diablo gold


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


han yu 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

han yu 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

emily said...

Thanks. I always enjoy reading your posts - they are always humorous and intelligent.I am a china tour lover,You can learn more: China travel packages | Asia tour packages | China city tour packages

Learn Chinese language on hanbridgemandarin said...

The best place to study Chinese online is in China. However, we understand that it isn't always possible to move here to study Chinese language. The next best thing is to study with our experienced teachers in a virtual classroom. Online students enjoy the same excellent way of Mandarin Online Course and custom designed courseware that we provide for our face to face clients.