database query taking too long

[Mala Sharma]

Hello,
We are using SQl Server2000 and have to run this below query for the results. The query itself is taking more that 10 seconds. I tried the query on the query analyzer itself and took the same amount of time. the query is:

SELECT a.itemno, a.iedesc,a.icode2,b.pcprc1,b.pcprc8,        
b.pcprc9,    a.igroup,   b.ptaxfb,  b.ptp01, d.serial_required_ind,    
    a.icode1,   a.istat,    a.ilcst, (sqtyo1 + sqtyo2 + sqtyo3 + sqtyo4)
 - (sqtyal + sqtybo)FROM webPos.dbo."HN.ITEM"  a LEFT OUTER JOIN      
webPos.dbo."HN.PRICE" b ON a.itemno = b.itemno join  webPos.dbo."HN.STOCK" 
c on a.itemno = c.itemno,  webPos.dbo.WEBPOS_SERIAL_RQ d WHERE (b.precid =
 'C' OR b.precid IS NULL)   AND a.icode2 in ('F','B', 'C', 'K', 'L', 'M', 
'S', 'X', 'Z', 'D', 'N', ' ')    AND a.isgrup = d.isgrup  AND a.itemno = 
c.itemno  AND a.igroup != 'UU'AND c.sloc = 1  AND a.itemno in (SELECT 
c.pitmn FROM webPos.dbo."HN.PALPH" c   INNER JOIN webPos.dbo."HN.PALPH" d  
  ON c.pitmn = d.pitmn               AND c.palph1 LIKE UPPER ('nikon%') AND 
d.palph1 LIKE UPPER('d70%') )  ORDER BY a.iedesc

the parameters at the end of the query nikon and d70are dynamice parameters. I also have indexes on a.itemno,a.icode2,a.iedesc,b.itemno,b.ptp01,b.pcprc1,b.pcprc9,c.itemno
i'm new to database performance issues. Could someone please guide me on how to make this query run faster?. I would really be appreciative. Thanks alot for your time and guidance.

Mala

[ October 10, 2005: Message edited by: Mala Sharma ]

[edited to add line breaks]
[ October 10, 2005: Message edited by: Jeanne Boyarsky ]

[Jeanne Boyarsky]

Mala,
Where does query analyzer say the query is spending the most time? This would be the place to start looking to optimize.

A few general things I can think of:
1) If you aren't using all 14 returned values in the select statement, don't return them. This will result in less network traffice.
2) Try flattening the query. Can you make do with a regular join instead of the "a.itemno in"
3) Try moving the most restrictive clauses earlier in the query. Again, the database will have less rows to work with. [A smart database will optimize this away, but sometimes it needs a hint.]
4) If you are executing this statement more than once, use a PreparedStatement. This is a complex query, so you will save time on figuring out the execution plan.

[aliya sharma]

HI Jeanne,

Thanks for your response. How would i know which part query analyzer is taking too long to execute. I just see the total time when its done executing. I think its the last self join on the table itself where it's taking time(i ran it separetely and it took real long). BUt the self join is necessary because if you look at the statement, each partnumber has multiple keywords associated to it and the user has option of entering 2 keywords. So the self join returns the item number that has both keywords. It's the palph table i'm talking about.

 AND a.itemno in (SELECT c.pitmn FROM webPos.dbo."HN.PALPH" c   INNER JOIN webPos.dbo."HN.PALPH" d    ON c.pitmn = d.pitmn  
AND c.palph1 LIKE UPPER ('nikon%') AND d.palph1 LIKE UPPER('d70%') ) 

Can you or anyone else please suggest some other way of achieving the same result but doesn't require selfjoining a table. I would be grateful. Thanks for your time , it's really appreciated

[ October 10, 2005: Message edited by: aliya sharma ]
[ October 10, 2005: Message edited by: aliya sharma ]

[Kenneth Albertson]

Hi aliya. The first thing you need to do is to start formatting your code so that it is readable. That would certainly have saved me some time. Not reusing c and d as aliases in the last sub-query would remove another source of confusion. Then you need to read the doco and learn to use the query analyzer, because it is an awesome tool, and will tell you everything you need to know to solve this problem.

As you suggest, however, the problem is almost certainly in the final self-join sub-query. This is an uncorrelated sub-query, so the complete result set of the sub-query, for EVERY value of itemno / pitmn, will be generated again for EVERY candidate row of the outer a,b,c,d join. This is what you currently have:

WHERE (b.precid = 'C' OR b.precid IS NULL) 
  AND ...
  AND a.itemno IN (
      SELECT c.pitmn
      FROM webPos.dbo."HN.PALPH" c
      INNER JOIN webPos.dbo."HN.PALPH" d
      ON c.pitmn = d.pitmn
        AND c.palph1 LIKE UPPER ('nikon%')
        AND d.palph1 LIKE UPPER('d70%')
    )

This is semantically equivalent code which should run a great deal faster:

WHERE (b.precid = 'C' OR b.precid IS NULL) 
  AND ...
  AND EXISTS(
      SELECT *
      FROM webPos.dbo."HN.PALPH" g
      INNER JOIN webPos.dbo."HN.PALPH" h
      WHERE g.pitmn = h.pitmn
        AND g.pitmn = a.itemno
        AND h.pitmn = a.itemno
        AND g.palph1 LIKE UPPER ('nikon%')
        AND h.palph1 LIKE UPPER('d70%')
    )

The g.pitmn = h.pitmn condition might not be necessary, I'm not sure. Note that you should have an index on pitmn, and that a composite index on (pitmn, palph1) would probably be even better. You might also like the try the following version, which might be even better:

WHERE (b.precid = 'C' OR b.precid IS NULL) 
  AND ...
  AND EXISTS(
      SELECT *
      FROM webPos.dbo."HN.PALPH" g
      WHERE g.pitmn = a.itemno
        AND g.palph1 LIKE UPPER ('nikon%')
    )
  AND EXISTS(
      SELECT *
      FROM webPos.dbo."HN.PALPH" h
      WHERE h.pitmn = a.itemno
        AND h.palph1 LIKE UPPER('d70%')
    )

BTW, you didn't mention an index on d.isgrup. That would probably be useful as well.

[Amit Goel]

using hints in the query may also help in optimizing it

[Mala Sharma]

Hi Jeanne and Kym,

First of all, thanks a lot for your help. I turned on "show execution plan" to see where the cost was more. And it's saying table scan cost for "HN.STOCK" (where sloc = 1)is 80 %. So, i guess this is the problem now. I need that query and i have put an index on sloc along with index on itemno. Do you have any ideas on how to reduce that cost. ANy suggestions or advice would be greatly welcomed and appreciated. Thanks for your time and help.

[Mala Sharma]

Hi,
i think it's the datasource that we're using to manage SQl server because i ran it using JDBC through profiler and it's taking less that 4 sec. but through the application(it uses our vendors datasource to set up a connection pool and we don't have the source code for that), it could tak anywhere from 15 and up seconds.
We're using websphere 4.0 and sql server 2000. Could anyone guide me on how to set up a connection pool under that enviornment. Ay links, articles or advice would be greatly appreciated. Thanks a lot.

Mala

[Jeanne Boyarsky]

Mala,
It looks like you want to target the table scan and the driver. For the driver, post a question in the IBM/WebSphere forum below about connection pool configuration.

A table scan is a huge red flag. This is the kind of thing that gets worse very quickly as your data set grows. Try adding an index on "c.sloc = 1" alone. You don't necesarily need to include indexno on it. Also, note that optimization/tuning is very iterative. Once you've solved the c.sloc problem, see where the majority of the cost is going.

Some of the 15+ seconds is network time, which you can't optimize away. You can also check resources on your database/server.