• Welcome, Guest. Please login.
 
November 21, 2019, 03:08:36 pm

News:

Welcome to the SQLitening support forums!


Optimize the time for reading

Started by Fim, May 16, 2015, 02:14:57 pm

Previous topic - Next topic

Fim

Is there any way to optimize the time for reading at the expense of the times for the update?
Fim W

Fim

Let me explain. I have a database with a table of 4,000,000 words. An index is word length. I am looking for words in this table based on word length. For example, I want to grab all the words that have a length of 5 letters and with the pattern "TY ?? A". I get 12.275 words when reading the database. 7 words of its match my word patterns. This takes 3.214 seconds which the actual reading of the database takes 2.743 seconds. This means that the reading of the database is the bottleneck. There are thousands of such a search per month.

The updating of the database occurs once per month and the time is irrelevant.

So, back to my question: Can I do something so the reading is faster at the expense of time for the update?
Fim W

Fim

I have built new and better index an now is performace very good.
Seeking for TY??A takes 0.188 seconds instead of 3.214.
Fim W

cj

May 19, 2015, 06:03:32 am #3 Last Edit: May 19, 2015, 09:03:29 am by cj
May we see it?

cj

May 19, 2015, 07:42:42 am #4 Last Edit: May 19, 2015, 09:02:18 am by cj
Did you run your routine through the query planner in Sqlite3.exe?

Some searches may seem fast with cached results and other factors, but are not using the index.
To be fast the query plan should always say SEARCH and not SCAN (which scans entire table.)

I could not get LIKE to use the index without the following:
TEXT  COLLATE NOCASE on the create statement and of course creating an index.


For anyone not familiar with the command line tool, it is excellent for testing.
I have include a little batch file named EASY.BAT and the command sent to it in EASY.TXT at the bottom of this thread.


#DIM ALL    'like.bas testing using explain query plan in SQlite3.exe command line tool
#INCLUDE "sqlitening.inc"
%DropTable = 1          'set to start with a new table
%InsertRecords = 999    'number of records that won't match + 1 valid record
%Loops = 4              'insert records this many times
%JustSearch = 0         'don't insert any more records

FUNCTION PBMAIN () AS LONG
  LOCAL InsideLoop,OutsideLoop,InsertRecords,Loops AS LONG
  LOCAL sArray() AS STRING
  InsertRecords = %InsertRecords '%InsertRecords can't be 0 in FOR/NEXT
  Loops         = %Loops         '%Loops can't be 0 in FOR/NEXT
  slopen "sample.db3","C"
  IF %DropTable THEN slexe "drop table if exists WordTable"
  'slexe "create table if not exists WordTable(Number integer primary key, Word not null collate nocase)"  ;'scan bad needs text
  slexe "create table if not exists WordTable(Number integer primary key, Word Text not null collate nocase)" 'search good
  'slexe "create table if not exists WordTable(Number integer primary key, Word Text collate nocase)" 'search good
  'slexe "create table if not exists WordTable(Number integer primary key, Word Text)" 'bad, scans needs collate nocase
  slexe "create index if not exists Word_idx on WordTable(Word)" 'required else SCAN entire table
  IF %JustSearch = 0 THEN
    slexe "Begin exclusive"
    IF %InsertRecords > 0 AND %Loops > 0 THEN
      slexe "drop index if exists Word_Idx"   'drop index for mass inserts
      FOR OutSideLoop = 1 TO Loops
        FOR InsideLoop = 1 TO InsertRecords
          slexe "insert into WordTable values(null,'dummy')"
        NEXT InsideLooop
        slexe "insert into WordTable values(null,'TYroa')"
      NEXT OutSideLoop
      slexe "create index if not exists Word_idx on WordTable(Word)" 'put index back z
      slexe "end"
    END IF
  END IF
  slSelAry "select Number,Word from WordTable where Word Like('TY__A')",sArray(),"Q9"
  ? JOIN$(sArray(),$CR),,FORMAT$(UBOUND(sArray))
END FUNCTION

'----    sqlite query planner   ----
'https://www.sqlite.org/optoverview.html
The command-line shell program (version 3.8.10.1).

easy.bat
@cls
@echo off
sqlite3.exe sample.db3 < easy.txt
@echo on


rem easy.txt
.separator | \r\n
.headers on
.mode column
.width 8 8 8 4 6 6 7 13 -3
.schema WordTable
.echo on
.explain on
explain query plan
select number,word
from WordTable
Where Word Like 'TY__A';
select number,word from wordtable where word like 'TY__A';

Fim

May 19, 2015, 07:58:36 am #5 Last Edit: May 19, 2015, 09:37:35 am by Fim
-- ORDKKORS.D.DB3  Distribuerad databas
-- ===================================
Drop Table if Exists Wordlist;
Create Table         Wordlist(
    Length       Integer     not null ,
    Word         Text        not null,
    Group        Text        not null ,
    Unique (Word, Length));

Drop Index if exists Length_idx;
Create Index Length_idx      on Wordlist (Length);

Drop Index if exists Word_idx;
Create Index Word_idx      on Wordlist (Word);

Drop Index if exists Group_idx;
Create Index Group_idx      on Wordlist (Group);
-------------------

I can not use the LIKE because the search pattern is a bit more complex, some exempels

A??2?2A                          Means that letters 4 and 6 must be the sama letter
123456789                     Means that all letters must be different
QW,.?                               Means that the third letter will be a vowel and the fourth a vowel
E?
Fim W

Fim

"Did you run your routine through the query planner in Sqlite3.exe?"

I use a PowerBasic-program.
All testing is done in the program that will be distributed to the users.
The last edition is available here www.ordkors.se

Fim W

cj

May 19, 2015, 08:09:25 am #7 Last Edit: May 19, 2015, 09:01:24 am by cj
It seems like you have it under control.
For others:
The query planner is on the SQLite site and used to be sure sql statements are optimized.
If you haven't used the command line shell, you might give it a try to examine the query plan.

cj

May 26, 2015, 03:37:52 pm #8 Last Edit: May 27, 2015, 05:34:40 am by cj
Fim,
"Did you run your routine through the query planner in Sqlite3.exe?"
I meant you can test indexes to be sure they are totally used with explain statement:
http://www.sqlitening.com/support/index.php?topic=9369.0