Simple SQL query too long

Question

I just have a simple query. On SQL Server 2008, sometimes it queries too long, nearly hang, sometimes not. The same sql on Oracle server, it always return at once.

SELECT D.DESCITEM, D.LONGDESC, D.DESCTABL, D.DESCCOY, D.DESCPFX 
FROM VM1DTA.DESCPF D, VM1DTA.ITEMPF I 
WHERE D.DESCPFX='IT'AND D.DESCITEM=I.ITEMITEM AND I.VALIDFLAG='1' 
AND D.DESCTABL = I.ITEMTABL AND D.DESCCOY = I.ITEMCOY AND "LANGUAGE" =  'E' 
AND "VALIDFLAG" =  '1' AND DESCTABL IN('T1680') 
ORDER BY LONGDESC ASC;

Each table has about 100k records.

Could someone point me about the root cause? Thanks

It could be data locking. Oracle doesn't look data readers, but Sql Server will if there is a pending update. See http://msdn.microsoft.com/en-us/library/ms173763.aspx — shf301, Dec 15 '12 at 06:02
@shf301: how to check if there's a pending update using SQL Studio? — bnguyen82, Dec 15 '12 at 06:41
@bnguyen82 - See http://stackoverflow.com/questions/694581/how-to-check-which-locks-are-held-on-a-table — shf301, Dec 15 '12 at 06:53
100K rows is a small amount of data. Could it be that sometimes server is busy running other queries that require a lot of resources? In such case it might slow down execution of other queries. — Ivan Golović, Dec 15 '12 at 08:27
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard (**20 years** ago!). ***Please*** stop using it — marc_s, Dec 15 '12 at 09:39

score 0 · Answer 1 · answered Dec 15 '12 at 06:53

0

I'm not sure what the issue is but your query could use some refactoring. This line looks unneeded as well "VALIDFLAG" = '1' because it has no prefix. The IN may have something to do with it.

SELECT D.DESCITEM, D.LONGDESC, D.DESCTABL, D.DESCCOY, D.DESCPFX 
FROM
VM1DTA.DESCPF D INNER JOIN VM1DTA.ITEMPF I ON
D.DESCITEM=I.ITEMITEM
AND
D.DESCTABL = I.ITEMTABL
AND
D.DESCCOY = I.ITEMCOY
WHERE 
D.DESCPFX='IT'
AND
"LANGUAGE" =  'E' 
AND 
I.VALIDFLAG='1' 
AND
"VALIDFLAG" =  '1' 
AND 
I.DESCTABL = 'T1680'
ORDER BY 
LONGDESC ASC;

The other thing to look at is putting indexes on all of the join columns.

Hope this helps.

answered Dec 15 '12 at 06:53

NullRef

3,713
1
20
18

Your findings is right, but this sql is generated by code so it may not be optimized for all cases. Thanks. – bnguyen82 Dec 15 '12 at 07:02
Do you mean to say there is no way to change the code as it is automaticlly supplied? – glh Dec 15 '12 at 23:58

glh · Answer 2 · 2012-12-15T23:27:24.140

I find that joining two tables by free text fields takes up a lot of resource and time. Additionally these fields are traditionally not indexed in anyway.

Try to see if there are any indexes worth using instead or other joins.

Also your join to the I.ITEMTABL field is not required as the D.DESCTBL field has a filter making the processor work more to join the two, unless it's an index.

Another method would be to use a subquery in your where clause like:

SELECT D.DESCITEM, D.LONGDESC, D.DESCTABL, D.DESCCOY, D.DESCPFX
FROM   VM1DTA.DESCPF D
WHERE  D.DESCPFX = 'IT'
       AND D.LANGUAGE = 'E' 
       AND D.VALIDFLAG = '1' 
       AND D.DESCTABL = 'T1680'
       AND EXISTS (SELECT NULL
                   FROM   VM1DTA.ITEMPF I
                   WHERE  I.VALIDFLAG = '1' 
                          AND I.ITEMTBL = 'T1680'
                          AND D.DESCITEM = I.ITEMITEM 
                          AND D.DESCCOY = I.ITEMCOY)
ORDER BY LONGDESC ASC;

Simple SQL query too long

2 Answers2