Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: IB2017 Sort on index


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


Permlink Replies: 13 - Last Post: Apr 14, 2018 6:00 PM Last Post By: Sriram Balasubr... Threads: [ Previous | Next ]
Todor Geshev

Posts: 14
Registered: 10/1/00
IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2018 6:04 AM
SELECT * FROM TABLE
WHERE COL1=:PARAM1
ORDER BY COL1

COL1 Is PRIMARY KEY

Executing on Interbase XE give this plan PLAN (TABLE ORDER PK_TABLE)
On 2017 is PLAN SORT ((TABLE INDEX (PK_TABLE)))
And execute is so sloooow, because not using index for where clause

Things are scared with component index( on 2 or 3 columns) ....
quinn wildman

Posts: 856
Registered: 12/2/99
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2018 9:46 AM   in response to: Todor Geshev in response to: Todor Geshev
While I can't explain the speed difference, it should be noted that Plan's are considered the solution of last resort - only done in times of desperation. In almost all cases, you should let InterBase decide the plan. That would certainly be what I would recommend here for such a simple query.

If you are committed to ignore my advise, then I'd look at the plan you get without specifying one and see if you see any surprises.

Todor Geshev wrote:
SELECT * FROM TABLE
WHERE COL1=:PARAM1
ORDER BY COL1

COL1 Is PRIMARY KEY

Executing on Interbase XE give this plan PLAN (TABLE ORDER PK_TABLE)
On 2017 is PLAN SORT ((TABLE INDEX (PK_TABLE)))
And execute is so sloooow, because not using index for where clause

Things are scared with component index( on 2 or 3 columns) ....
Todor Geshev

Posts: 14
Registered: 10/1/00
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2018 1:17 AM   in response to: quinn wildman in response to: quinn wildman
Thanks for answer
I not force Interbase to use any plan. This is just info returned from IB2017 and IB XE. I think this is bug in IB2017

quinn wildman wrote:
While I can't explain the speed difference, it should be noted that Plan's are considered the solution of last resort - only done in times of desperation. In almost all cases, you should let InterBase decide the plan. That would certainly be what I would recommend here for such a simple query.

If you are committed to ignore my advise, then I'd look at the plan you get without specifying one and see if you see any surprises.

Todor Geshev wrote:
SELECT * FROM TABLE
WHERE COL1=:PARAM1
ORDER BY COL1

COL1 Is PRIMARY KEY

Executing on Interbase XE give this plan PLAN (TABLE ORDER PK_TABLE)
On 2017 is PLAN SORT ((TABLE INDEX (PK_TABLE)))
And execute is so sloooow, because not using index for where clause

Things are scared with component index( on 2 or 3 columns) ....
quinn wildman

Posts: 856
Registered: 12/2/99
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2018 8:23 AM   in response to: Todor Geshev in response to: Todor Geshev
Todor Geshev wrote:
Thanks for answer
I not force Interbase to use any plan. This is just info returned from IB2017 and IB XE. I think this is bug in IB2017
Then you should report it at quality.embarcadero.com. This is not the place to report defects. Be sure to include a reproduce-able test case. This is critical.
Todor Geshev

Posts: 14
Registered: 10/1/00
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2018 11:47 PM   in response to: quinn wildman in response to: quinn wildman
quinn wildman wrote:
Todor Geshev wrote:
Thanks for answer
I not force Interbase to use any plan. This is just info returned from IB2017 and IB XE. I think this is bug in IB2017
Then you should report it at quality.embarcadero.com. This is not the place to report defects. Be sure to include a reproduce-able test case. This is critical.

Added comment with case to https://quality.embarcadero.com/browse/IBP-72
Todor Geshev

Posts: 14
Registered: 10/1/00
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 27, 2018 6:13 AM   in response to: Todor Geshev in response to: Todor Geshev
Todor Geshev wrote:
quinn wildman wrote:
Todor Geshev wrote:
Thanks for answer
I not force Interbase to use any plan. This is just info returned from IB2017 and IB XE. I think this is bug in IB2017
Then you should report it at quality.embarcadero.com. This is not the place to report defects. Be sure to include a reproduce-able test case. This is critical.

Added comment with case to https://quality.embarcadero.com/browse/IBP-72

A week of silence :(
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 27, 2018 11:10 AM   in response to: Todor Geshev in response to: Todor Geshev
On 2/27/2018 9:13 AM, Todor Geshev wrote:
Todor Geshev wrote:
quinn wildman wrote:
Todor Geshev wrote:
Thanks for answer
I not force Interbase to use any plan. This is just info returned from IB2017 and IB XE. I think this is bug in IB2017
Then you should report it at quality.embarcadero.com. This is not the place to report defects. Be sure to include a reproduce-able test case. This is critical.

Added comment with case to https://quality.embarcadero.com/browse/IBP-72

A week of silence :(

It has been opened. QP is a bug reporting portal, it is not a support portal.
You won't get support there. That being said you don't want the index for
sorting in this instance anyways. Why are you selecting on the primary field
and ordering by that field which has only 1 item in it?

this

SELECT * FROM TABLE
WHERE COL1=:PARAM1
ORDER BY COL1

should just be this

SELECT * FROM TABLE
WHERE COL1=:PARAM1

the order by is only forcing the server to do more work with no gain.

Your QP example

select * from RDB$COLLATIONS
where RDB$COLLATION_ID=0
order by RDB$COLLATION_ID,RDB$CHARACTER_SET_ID

should be

select * from RDB$COLLATIONS
where RDB$COLLATION_ID=0
order by RDB$CHARACTER_SET_ID

yes the optimizer is getting confused when the index can be used both in the
where and the order by but your examples are easy to fix to be faster in both XE
and 2017.

Generally you do not want to use indexes to sort things as this causes out of
order disk fetches (not a concerned if on a SSD drive). Your solution is
almost always best solving with a where, loading the pages in natural scan order
and in memory sorting the results.

--
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)
Todor Geshev

Posts: 14
Registered: 10/1/00
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 28, 2018 12:14 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
On 2/27/2018 9:13 AM, Todor Geshev wrote:
Todor Geshev wrote:
quinn wildman wrote:
Todor Geshev wrote:
Thanks for answer
I not force Interbase to use any plan. This is just info returned from IB2017 and IB XE. I think this is bug in IB2017
Then you should report it at quality.embarcadero.com. This is not the place to report defects. Be sure to include a reproduce-able test case. This is critical.

Added comment with case to https://quality.embarcadero.com/browse/IBP-72

A week of silence :(

It has been opened. QP is a bug reporting portal, it is not a support portal.
You won't get support there. That being said you don't want the index for
sorting in this instance anyways. Why are you selecting on the primary field
and ordering by that field which has only 1 item in it?

this

SELECT * FROM TABLE
WHERE COL1=:PARAM1
ORDER BY COL1

should just be this

SELECT * FROM TABLE
WHERE COL1=:PARAM1

the order by is only forcing the server to do more work with no gain.

Your QP example

select * from RDB$COLLATIONS
where RDB$COLLATION_ID=0
order by RDB$COLLATION_ID,RDB$CHARACTER_SET_ID

should be

select * from RDB$COLLATIONS
where RDB$COLLATION_ID=0
order by RDB$CHARACTER_SET_ID

yes the optimizer is getting confused when the index can be used both in the
where and the order by but your examples are easy to fix to be faster in both XE
and 2017.

Generally you do not want to use indexes to sort things as this causes out of
order disk fetches (not a concerned if on a SSD drive). Your solution is
almost always best solving with a where, loading the pages in natural scan order
and in memory sorting the results.

--
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)

If this is a joke, it's not funny. This is a large CRM system launched on Interbase 5.6, then IB7.5, XE and now our clients begin upgrade to IB2017

What about this select

select * from MY_TABLE
where MY_DATE between :fromdate and :enddate
order by MY_DATE,MY_NUMBER_FOR_DAY

and MY_DATE,MY_NUMBER_FOR_DAY is Primary key.

MY_TABLE has at least a million records.

This select will read ALL table MY_TABLE although I may only have requested data for one week

Todor Geshev

Posts: 14
Registered: 10/1/00
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 14, 2018 4:40 AM   in response to: Todor Geshev in response to: Todor Geshev
After you have released Tokyo's 3 update, maybe you will have time to pay attention to Interbase 2017
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 14, 2018 12:33 PM   in response to: Todor Geshev in response to: Todor Geshev
On 3/14/2018 7:40 AM, Todor Geshev wrote:
After you have released Tokyo's 3 update, maybe you will have time to pay attention to Interbase 2017

Totally different teams. No overlap.

--
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)
Todor Geshev

Posts: 14
Registered: 10/1/00
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 21, 2018 7:35 AM   in response to: Todor Geshev in response to: Todor Geshev
InterBase 2017 Update 2: March 2018, bugs fixed in version 2017 Update 2

http://docwiki.embarcadero.com/InterBase/2017/en/Resolved_Defects

Everything is OK now
Sriram Balasubr...

Posts: 132
Registered: 10/19/99
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 22, 2018 4:02 PM   in response to: Todor Geshev in response to: Todor Geshev
Thank you for reporting the original problem via our Quality Portal and for validating the fix that we introduced in the just released InterBase 2017 Update 2.

Best wishes,
Sriram

Todor Geshev wrote:
InterBase 2017 Update 2: March 2018, bugs fixed in version 2017 Update 2

http://docwiki.embarcadero.com/InterBase/2017/en/Resolved_Defects

Everything is OK now
Todor Geshev

Posts: 14
Registered: 10/1/00
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 27, 2018 11:50 PM   in response to: Todor Geshev in response to: Todor Geshev
After update 2 IBExpert on every statement gives
"Dynamic SQL Error.
SQL error code = -804.
SQLDA missing or incorrect version, or incorrect number/type of variables."

Any clue ?
Thanks
Sriram Balasubr...

Posts: 132
Registered: 10/19/99
Re: IB2017 Sort on index  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 14, 2018 6:00 PM   in response to: Todor Geshev in response to: Todor Geshev
Don't know if IBExpert is using any batch update statements. We found a regression post-InterBase 2017 Update 2 release and a fix for that error.

Please contact Embarcadero Support if you want to get the fix before our next GA release.

Best wishes,
Sriram

Todor Geshev wrote:
After update 2 IBExpert on every statement gives
"Dynamic SQL Error.
SQL error code = -804.
SQLDA missing or incorrect version, or incorrect number/type of variables."

Any clue ?
Thanks
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02