Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Slow Query


This question is not answered. Helpful answers available: 1. Correct answers available: 1.


Permlink Replies: 6 - Last Post: Mar 3, 2016 2:05 AM Last Post By: Melissa Torn
Melissa Torn

Posts: 143
Registered: 4/30/09
Slow Query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 29, 2016 12:58 AM
I have total 520 records in Orders table
below query shows the result in 4, 5 seconds
in a network system. Is that normal or
there is something to do for fast result

Server Computer = Intel i7, 16 gb ram, Sata III SSD,Win8,1
User Computer = Intel i7, 8 gb ram, Sata III SSD, Win7

Thank You

Statement: SELECT X.*,C.CUSTNAME,C.CURRENCY,C.DISCPERC,C.PAYMENTDAY,C.SHIPTYPE
FROM ORDERS X
JOIN CUSTOMER C ON C.CUSTNO=X.CUSTNO
WHERE X.TNAME IN ('PO') and X.INVRNO IS NULL or
X.TDATE>=(SELECT MAX(TDATE) FROM ORDERS)-15

PLAN (ORDERS NATURAL)
PLAN JOIN (X INDEX (ORDERS_TNAME,ORDERS_TDATE),C INDEX (RDB$PRIMARY19))
p.s : I have also an Index on X.INVRNO but cant see it on the Plan
quinn wildman

Posts: 156
Registered: 4/20/07
Re: Slow Query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 29, 2016 8:17 AM   in response to: Melissa Torn in response to: Melissa Torn
Melissa Torn wrote:
I have total 520 records in Orders table
below query shows the result in 4, 5 seconds
in a network system. Is that normal or
there is something to do for fast result

Server Computer = Intel i7, 16 gb ram, Sata III SSD,Win8,1
User Computer = Intel i7, 8 gb ram, Sata III SSD, Win7

Thank You

Statement: SELECT X.*,C.CUSTNAME,C.CURRENCY,C.DISCPERC,C.PAYMENTDAY,C.SHIPTYPE
FROM ORDERS X
JOIN CUSTOMER C ON C.CUSTNO=X.CUSTNO
WHERE X.TNAME IN ('PO') and X.INVRNO IS NULL or
X.TDATE>=(SELECT MAX(TDATE) FROM ORDERS)-15

PLAN (ORDERS NATURAL)
PLAN JOIN (X INDEX (ORDERS_TNAME,ORDERS_TDATE),C INDEX (RDB$PRIMARY19))
p.s : I have also an Index on X.INVRNO but cant see it on the Plan

Your query seems too long to me. The place to start is where you suggest
- look at the plan. I recommend IBPlanalyzer.

--
I used to work for Embarcadero - I don't work there any more.
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Slow Query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 29, 2016 11:19 AM   in response to: Melissa Torn in response to: Melissa Torn
Melissa Torn wrote:
I have total 520 records in Orders table
below query shows the result in 4, 5 seconds
in a network system. Is that normal or
there is something to do for fast result

Server Computer = Intel i7, 16 gb ram, Sata III SSD,Win8,1
User Computer = Intel i7, 8 gb ram, Sata III SSD, Win7

Thank You

Statement: SELECT X.*,C.CUSTNAME,C.CURRENCY,C.DISCPERC,C.PAYMENTDAY,C.SHIPTYPE
FROM ORDERS X
JOIN CUSTOMER C ON C.CUSTNO=X.CUSTNO
WHERE X.TNAME IN ('PO') and X.INVRNO IS NULL or
X.TDATE>=(SELECT MAX(TDATE) FROM ORDERS)-15

PLAN (ORDERS NATURAL)
PLAN JOIN (X INDEX (ORDERS_TNAME,ORDERS_TDATE),C INDEX (RDB$PRIMARY19))
p.s : I have also an Index on X.INVRNO but cant see it on the Plan

This part

(SELECT MAX(TDATE) FROM ORDERS)-15

is scanning the table. Not once, but once for each record it needs to look at
(The optimizer can't really tell that that is really a constant value for the
lifetime of the query). Best is to find that value once then pass it as a
parameter to the query. Alternatively a descending index on TDATE could be used
to solve that part faster (Ascending can solve Min problems Descending Max
problems).

Be careful with trying indexes like that. When InterBase does a sparse bitmap
merge of multiple indexes all the indexes intermediate results should be
relatively small. If one is large (and that TDATE >= part looks like it has the
potential to be) it will be faster to solve it with the much more selective part
of the index then individually look at the records to see if they satisfy the
rest of the WHERE conditions

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
Charlie Caro

Posts: 9
Registered: 4/22/12
Re: Slow Query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 29, 2016 1:32 PM   in response to: Melissa Torn in response to: Melissa Torn
Maybe InterBase doesn't recognize (SELECT MAX(TDATE) FROM ORDERS)-15 as a constant expression, which is invariant with respect to the outer query. You might try folding the constant 15 into the subquery, i.e., (SELECT MAX(TDATE) - 15 FROM ORDERS).

I'll take a look when I get a chance. You should post this as a case to Quality Central, so it gets attention as an issue to be resolved.
Melissa Torn

Posts: 143
Registered: 4/30/09
Re: Slow Query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 2, 2016 4:14 AM   in response to: Melissa Torn in response to: Melissa Torn
Thank You so much Quinn, Jeff, Charlie

I removed SELECT (MAX)...I tried below query nothing has changed
Statement: SELECT X.*,C.CUSTNAME,C.CURRENCY,C.DISCPERC,C.PAYMENTDAY,C.SHIPTYPE
FROM ORDERS X
JOIN CUSTOMER C ON C.CUSTNO=X.CUSTNO
WHERE X.TNAME IN ('PO') and X.INVRNO IS NULL

I also removed X.INVRNO IS NULL and tried below query, again nothing has changed
Statement: SELECT X.*,C.CUSTNAME,C.CURRENCY,C.DISCPERC,C.PAYMENTDAY,C.SHIPTYPE
FROM ORDERS X
JOIN CUSTOMER C ON C.CUSTNO=X.CUSTNO
WHERE X.TNAME IN ('PO') /* I also changed X.TNAME='PO' but nothing has changed */

I use IBXE3
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Slow Query
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 2, 2016 12:13 PM   in response to: Melissa Torn in response to: Melissa Torn
Melissa Torn wrote:
Thank You so much Quinn, Jeff, Charlie

I removed SELECT (MAX)...I tried below query nothing has changed
Statement: SELECT X.*,C.CUSTNAME,C.CURRENCY,C.DISCPERC,C.PAYMENTDAY,C.SHIPTYPE
FROM ORDERS X
JOIN CUSTOMER C ON C.CUSTNO=X.CUSTNO
WHERE X.TNAME IN ('PO') and X.INVRNO IS NULL

I also removed X.INVRNO IS NULL and tried below query, again nothing has changed
Statement: SELECT X.*,C.CUSTNAME,C.CURRENCY,C.DISCPERC,C.PAYMENTDAY,C.SHIPTYPE
FROM ORDERS X
JOIN CUSTOMER C ON C.CUSTNO=X.CUSTNO
WHERE X.TNAME IN ('PO') /* I also changed X.TNAME='PO' but nothing has changed */

I use IBXE3

Always show the new plans with changed SQL. Without the plans we can't tell if
your changes are changing anything.

Also can you build a sample database with just those two tables with data
(scrubbed if needed) and put it in the attachments group? That small of data
should not be slow even with the other potentially slow pieces.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
Melissa Torn

Posts: 143
Registered: 4/30/09
Re: Slow Query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 3, 2016 2:05 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thank you so much Jeff,
Here below the Plans

SELECT X.*,C.CUSTNAME,C.CURRENCY,C.DISCPERC,C.PAYMENTDAY,C.SHIPTYPE
FROM ORDERS X
JOIN CUSTOMER C ON C.CUSTNO=X.CUSTNO
WHERE X.TNAME IN ('PO')
PLAN JOIN (X INDEX (ORDERS_TNAME),C INDEX (RDB$PRIMARY19))

SELECT X.*,C.CUSTNAME,C.CURRENCY,C.DISCPERC,C.PAYMENTDAY,C.SHIPTYPE
FROM ORDERS X
JOIN CUSTOMER C ON C.CUSTNO=X.CUSTNO
WHERE X.TNAME IN ('PO') and X.INVRNO IS NULL
PLAN JOIN (X INDEX (ORDERS_INVRNO,ORDERS_TNAME),C INDEX (RDB$PRIMARY19))
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02