4 Replies - 1544 Views - Last Post: 01 February 2013 - 02:40 PM Rate Topic: -----

#1 jediokie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 215
  • Joined: 07-February 08

Optimizing query

Posted 30 January 2013 - 02:46 PM

So I was asked to change a query and optimize it as it was calling all columns of a table that was huge. I submitted this code:
SELECT COUNT(TVCPMF) FROM TVCLIB.TVCAGTP WHERE TVAGTP = ? AND TVCPMF IN ('G', 'Y')


It's a DB2 database.
The whole purpose of the query is to see if the associate is group qualified(TVCPMF). The code accepts 'G' for group or 'Y' for yes. If they are qualified, later on code allows them access to an area of our web portal that is otherwise locked.
TVAGTP is the associate number and the ? is the current users associate number.
TVCLIB.TVCAGTP is our associate table and houses all information about our associates, I think there are over a hundred columns at this point.

Once submitted the response from my supervisor was this:

Quote

Code still uses less efficient technique

I've wracked my brain and courted the help of one of our DBA's as well as two or three other programmers and I cannot for the life of me figure out how to make that code more efficient than it is.

Any ideas?

This post has been edited by jediokie: 30 January 2013 - 03:34 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Optimizing query

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4421
  • View blog
  • Posts: 12,289
  • Joined: 18-April 07

Re: Optimizing query

Posted 30 January 2013 - 03:10 PM

You might want to do yourself a favor and instead of asterisks show some actual column names, explain what the table is like (what kind of data it contains) and explain what question you are trying to solve.

You have so much vagueness in that question that we have virtually no information about what you are trying to do to give you an accurate answer.

Thanks for helping us help you.

P.S. What database are you trying to work with also may help.

This post has been edited by Martyr2: 30 January 2013 - 03:13 PM

Was This Post Helpful? 1
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Optimizing query

Posted 30 January 2013 - 11:12 PM

I'm not familiar with DB2, but if it's like most other DBs, it would be quicker doing this:
SELECT COUNT(*)
FROM TVCLIB.TVCAGTP
WHERE TVAGTP = ?
AND (
    TVCPMF = 'G'
    OR TVCPMF = 'Y'
)


COUNT(*) will be quicker because it will increment the count if the row exists. COUNT(TVCPMF) counts unique instances of that column, so it first checks if that value has been counted before incrementing again. Yu may want to count unique instances, but from the looks of it you probably don't.

Using AND(x OR y) instead on IN (x,y) is technically quicker, but the optimiser is most likely making that conversion internally anyhow.

Another way of optimising the query would be putting an index on (TVAGTP, TVCPMF). Afurther way would be storing integer data in the column TVCPMF, since predicates that equate integers run a lot faster than predicates that equate char or varchar data types.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,854
  • Joined: 16-October 07

Re: Optimizing query

Posted 31 January 2013 - 05:28 AM

View Poste_i_pi, on 31 January 2013 - 01:12 AM, said:

COUNT(TVCPMF) counts unique instances of that column, so it first checks if that value has been counted before incrementing again.


This is incorrect. The behavior should be to count all non null values. The behavior you're describing is correct for COUNT(distinct TVCPMF).

As to optimization... What the OP has is about as basic as it gets. Breaking the IN up into OR logic shouldn't change anything. The only thing you could do better is index the columns or include more fields in the filter that make sense.

Note, some believe COUNT(name) to be better or worse than COUNT(*). It really depends on the database implementation, but shouldn't much matter either way. The * will get you all the rows and so will the name if it's the primary key. Generally, only use a field name if you're looking to drop the nulls of that field from the count.
Was This Post Helpful? 2
  • +
  • -

#5 jediokie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 215
  • Joined: 07-February 08

Re: Optimizing query

Posted 01 February 2013 - 02:40 PM

It appears after talking with him, he was looking at a different revision of the code than the one with my change on it. After further review he feels the above query is as efficient as it could be.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1