?
Solved

How to speed up a SELECT

Posted on 2010-01-12
20
Medium Priority
?
424 Views
Last Modified: 2013-12-09
I have a table with 4 indexes (Author, ISBN, SKU and TITLE), no primary index on the table.  I want a SELECT using one of the index's I have created (SKU).  I tried the code below, but it doesn't like it.

How do I do this?
select BookNbr, Title, ISBN, NbrOfCopies, Locn, Price, Stat from tBooks 
PLAN(tBooks index (SKU))
 WHERE BookNbr = 2217  ORDER BY BookNbr ASC

Open in new window

0
Comment
Question by:rmmarsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
20 Comments
 
LVL 7

Assisted Solution

by:twinsoft
twinsoft earned 2000 total points
ID: 26297040
Hi,
 you must create an index on field Booknbr (for example booknbr_idx)since the where clause and the order by clause are based on this field...

Then modify your query like this:

select BookNbr, Title, ISBN, NbrOfCopies, Locn, Price, Stat from tBooks
PLAN(tBooks index (booknbr_idx))
 WHERE BookNbr = 2217  ORDER BY BookNbr ASC
0
 

Author Comment

by:rmmarsh
ID: 26297603
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?
0
 
LVL 7

Expert Comment

by:twinsoft
ID: 26297710
hi, can you please attach the fdb ?. I take a look at it and let you know....
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rmmarsh
ID: 26297775
fdb?
0
 
LVL 7

Expert Comment

by:twinsoft
ID: 26297886

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

Author Comment

by:rmmarsh
ID: 26297937
EE won't let me upload a zipped .fdb file...

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

Expert Comment

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

Author Comment

by:rmmarsh
ID: 26298028
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
0
 
LVL 7

Expert Comment

by:twinsoft
ID: 26298261
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...
0
 

Author Comment

by:rmmarsh
ID: 26298652
here it is...

Index.zip
0
 
LVL 7

Accepted Solution

by:
twinsoft earned 2000 total points
ID: 26298799
Hi again,
  found your problem. The plan statement should be after the where clause not before. Sorry for not seeing that before, but you should try to write your SQL like the one i sent you so that it is easier to be read.

Here is your SQL:

select BookNbr, Title, ISBN, NbrOfCopies, Locn, Price, Stat
  from tBooks
 WHERE BookNbr = 2217
  PLAN(tBooks index (SKU))
 ORDER BY BookNbr ASC

Open in new window

0
 

Author Comment

by:rmmarsh
ID: 26298837
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
0
 
LVL 7

Assisted Solution

by:twinsoft
twinsoft earned 2000 total points
ID: 26298996
Hmm,
  when you run your sql in Maestro does it write somewhere what plan was used ? If yes, see if both SQLs (with or without PLAN) use the same index (SKU). One more question, how much time does it take to execute the above SQL ? (not from C#, from Maestro). I filled tbooks with 17000 records and executed the SQL and i get 16ms execution time (very fast) the first time and 0ms the second (results are cached). I tried that with and without the PLAN statement and i still get the same results. I am using Firebird 2.0.4...
0
 

Author Comment

by:rmmarsh
ID: 26299114
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/)
0
 
LVL 7

Assisted Solution

by:twinsoft
twinsoft earned 2000 total points
ID: 26299375
Hi,
you are right Maestro does not provide that kind of info... Try using IBExpert Personal (Free from IBExpert.com) or FlameRobin from

http://sourceforge.net/projects/flamerobin/files/

They both provide Plan info after executing a SQL (IBExpert is better)

0
 

Author Comment

by:rmmarsh
ID: 26299634
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.



0
 
LVL 7

Assisted Solution

by:twinsoft
twinsoft earned 2000 total points
ID: 26301489
Hi,
  yes Interbase is clever enough to use an index, when there is one, to speed up the select. So, do you still have a delay when executing the SQL ?
0
 

Author Comment

by:rmmarsh
ID: 26303837
No... but there is no index on BookNbr, it's just unique... does Firebird see that and use it as the index?
0
 
LVL 7

Expert Comment

by:twinsoft
ID: 26306867
Hi, yes unique constraint creates an index too...
0
 

Author Closing Comment

by:rmmarsh
ID: 31676320
Thank you so very much... saved my  bacon!  :D
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question