How to speed up a SELECT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

It doesn't like the PLAN statement... there is no booknbr_idx... the index on BookNbr is called SKU.

Do you want me to take a screen shot in Maestro so you can see what I have?

hi, can you please attach the fdb ?. I take a look at it and let you know....

ASKER

fdb?

yes, your Database file (Fdb if Firebird, Gdb if Interbase)...

ASKER

EE won't let me upload a zipped .fdb file...

What next? You want an image of the index's showing the names?

Just take a backup of the DB and upload it (Fbk or Gbk). What version of Firebird/Interbase do you use ?

ASKER

Here it is... (don't know why I didn't think of a backup... frustrated, I guess!)

I had to rename the .gbk file to .png so it would upload... what a mess! So just rename it back to .gbk

20100112131650.png

Hmm,
I cannot restore it since i have Firebird installed. It would be better to send me the metadata of the DB. Your DB tool should have an Extract Metadata function in order to extract your DB's metadata...

ASKER

here it is...

Index.zip

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Interesting... I get the same timings on 17,000 entries with and without the PLAN statement.

Is this telling me that I don't need it? or is Firebird indexing the first unique field anyway?

And you're right about writing my SQL; however, this is embedded within a C# program, so it's all one line. :D

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

How do I find out what plan was used? I just use Maestro to run sample SQL statements to make sure I have them right.

Have you every played with Maestro? (http://www.sqlmaestro.com/products/firebird/maestro/help/)

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thank you, thank you, thank you! I downloaded 'flamerobin', which l like immensely and it shows the following, which indicates to me that I don't need the plan statement. Do you agree?

This is the SQL statement:

select BookNbr, Title, ISBN, NbrOfCopies, Locn, Price, Stat
from tBooks
WHERE BookNbr = 'AIM_1701266815'
ORDER BY BookNbr ASC

and here is the output:
Preparing query: select BookNbr, Title, ISBN, NbrOfCopies, Locn, Price, Stat
from tBooks
WHERE BookNbr = 'AIM_1701266815'
ORDER BY BookNbr ASC

Prepare time: 0.000s
Field #01: TBOOKS.BOOKNBR Alias:BOOKNBR Type:STRING(15)
Field #02: TBOOKS.TITLE Alias:TITLE Type:STRING(100)
Field #03: TBOOKS.ISBN Alias:ISBN Type:STRING(13)
Field #04: TBOOKS.NBROFCOPIES Alias:NBROFCOPIES Type:STRING(3)
Field #05: TBOOKS.LOCN Alias:LOCN Type:STRING(10)
Field #06: TBOOKS.PRICE Alias:PRICE Type:NUMERIC(18,2)
Field #07: TBOOKS.STAT Alias:STAT Type:STRING(10)
PLAN (TBOOKS ORDER SKU INDEX (SKU))
Script execution finished.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

No... but there is no index on BookNbr, it's just unique... does Firebird see that and use it as the index?

Hi, yes unique constraint creates an index too...