(Solved) NextDatabaseRow extreme slow

Just starting out? Need help? Post your questions and find answers here.
Cyllceaux
Enthusiast
Enthusiast
Posts: 334
Joined: Mon Jun 23, 2014 1:18 pm
Contact:

Re: NextDatabaseRow extreme slow

Post by Cyllceaux »

skywalk wrote:Have you tried running your queries entirely from a sqlite wrapper in PB?
And use begin transaction..commit?
I have not hit your db sizes so I have not been pushed to full wrapper mode.
My last slowdown was fixed with a sqlite version.
I don't use transactions. I only use selects. What do you mean with "wrapper mode"?
Cyllceaux
Enthusiast
Enthusiast
Posts: 334
Joined: Mon Jun 23, 2014 1:18 pm
Contact:

Re: NextDatabaseRow extreme slow

Post by Cyllceaux »

NicTheQuick wrote:It sounds like PB is preloading every row into memory while the C version is only prefetching it, meaning that it should be as slow as Purebasic if you actually read the blobs. On the other hand you are only selecting the column NR, so there should be no blob at all. Well, that's weird.
I thought the same. But in my strange brain, I also thought PB included the sqlite.dll and make the same calls like I do and only wraps the result to PB internal results. I mean, Xojo and Valentina did the same. (I mean, include the dll)
User avatar
Danilo
Addict
Addict
Posts: 3010
Joined: Sat Apr 26, 2003 8:26 am
Location: Planet Earth

Re: NextDatabaseRow extreme slow

Post by Danilo »

Is there a difference when you use the flag #PB_Database_DynamicCursor for DatabaseQuery()?

Maybe you can use the same code like your C code in PB, without using PB's database commands.

Code: Select all

    s = sqlite3_step (stmt)
    if s = #SQLITE_ROW
        ...
- SQLite3 BaseFunction-Include for Win + Lin + Unicode
- SQLite Again! ... (unicode added)
Last edited by Danilo on Fri Mar 19, 2021 8:38 am, edited 1 time in total.
Cyllceaux
Enthusiast
Enthusiast
Posts: 334
Joined: Mon Jun 23, 2014 1:18 pm
Contact:

Re: NextDatabaseRow extreme slow

Post by Cyllceaux »

Danilo wrote:Is there a difference when you use the flag #PB_Database_DynamicCursor for DatabaseQuery()?

Maybe you can use the same code like your C code in PB, without using PB's database commands.

Code: Select all

    s = sqlite3_step (stmt)
    if s = #SQLITE_ROW
        ...
PB_Database_DynamicCursor slows extreme down 276302ms.

I think I will write a own LibCall for the sqlite Dll
infratec
Always Here
Always Here
Posts: 5426
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: NextDatabaseRow extreme slow

Post by infratec »

You don't need to write a 'wrapper'.
You can simply use the functions:

viewtopic.php?p=556707#p556707
Cyllceaux
Enthusiast
Enthusiast
Posts: 334
Joined: Mon Jun 23, 2014 1:18 pm
Contact:

Re: NextDatabaseRow extreme slow

Post by Cyllceaux »

Danilo wrote:Is there a difference when you use the flag #PB_Database_DynamicCursor for DatabaseQuery()?

Maybe you can use the same code like your C code in PB, without using PB's database commands.

Code: Select all

    s = sqlite3_step (stmt)
    if s = #SQLITE_ROW
        ...
- SQLite3 BaseFunction-Include for Win + Lin + Unicode
- SQLite Again! ... (unicode added)
WOW... I used the second link... and DAMN!!!! This is fast...

Code: Select all

If Not SQLiteInit("..\Libs\sqlite\sqlite3x64.dll")
	Debug "Can't initialisize SQLite3"
  End
EndIf

Define hDB = SQLiteOpen("Daten.db")
If hDB = #False
  Debug "Can't create database"
  End
EndIf

Define lStatement = SQLitePrepare(hDB, "SELECT NR from DATEN_VIEW where CKATEGORIEN=0 and PVERBINDUNGEN=0 and CVERBINDUNGEN=0 and ORT=''")
If lStatement
  While SQLiteStep(lStatement)
	  Define nr = SQLiteColumnQuad(lStatement, 0)  
	  If nr
	   
	  EndIf
  Wend
  SQLiteFinalize(lStatement)
EndIf
SQLiteClose(hDB)
SQLiteEnd()
34ms in Debug Mode.
8ms in normal mode
Fred
Administrator
Administrator
Posts: 14348
Joined: Fri May 17, 2002 4:39 pm
Location: France
Contact:

Re: NextDatabaseRow extreme slow

Post by Fred »

That's strange, NextDatabaseRow() for SQLite is just sqlite3_step(), nothing else.
Cyllceaux
Enthusiast
Enthusiast
Posts: 334
Joined: Mon Jun 23, 2014 1:18 pm
Contact:

Re: NextDatabaseRow extreme slow

Post by Cyllceaux »

Fred wrote:That's strange, NextDatabaseRow() for SQLite is just sqlite3_step(), nothing else.

Just a thought... Is the lib compiled another way for PB? Maybe another parameter? Or initialized another way?
Cause UseSQLDatabase("sqlite3x64.dll") doesn't change anything
Fred
Administrator
Administrator
Posts: 14348
Joined: Fri May 17, 2002 4:39 pm
Location: France
Contact:

Re: NextDatabaseRow extreme slow

Post by Fred »

I don't know. Anyway to reproduce your DB here to investigate ? I can see a diff in the prepare statement, but I don't think it should change anything:

C code:

Code: Select all

sqlite3_prepare_v2(Database->Handle, (char *)Request, -1, &Database->Statement, &Tail)
Cyllceaux
Enthusiast
Enthusiast
Posts: 334
Joined: Mon Jun 23, 2014 1:18 pm
Contact:

Re: NextDatabaseRow extreme slow

Post by Cyllceaux »

This is really really stange. I don't know why, but it looks like the "index" is ignored.

If I made a "EXPLAIN QUERY PLAN", the PB ignores the index. I found a workaround to explicit disable autoindex by "pragma". then it runs in 33ms.

Code: Select all

PRAGMA automatic_index = 0;
But I don't understand why.

The next Problem is a "ORDER BY" I use later. This is an official bug in SQLite. SQLIte thinks an autoindex is better for "order by" then a real index.
User avatar
skywalk
Addict
Addict
Posts: 3532
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: (Solved) NextDatabaseRow extreme slow

Post by skywalk »

Cyllceaux - Was the solution to use wrapper code or was it a newer SQLite3.dll?
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Cyllceaux
Enthusiast
Enthusiast
Posts: 334
Joined: Mon Jun 23, 2014 1:18 pm
Contact:

Re: (Solved) NextDatabaseRow extreme slow

Post by Cyllceaux »

The solution was to set the PRAGMA for the automatic_index.

I always tried a couple of DLLs. But it doesn't change anything.

It looks like, it ignores the index and always create a new one. The pragma vorbid the creation for an automatic index so SQLIte has the use the DB index.
User avatar
skywalk
Addict
Addict
Posts: 3532
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: (Solved) NextDatabaseRow extreme slow

Post by skywalk »

Ok,
Maybe language issue?

Are you setting PRAGMA automatic_index = 0?

And you saw no advantage using the wrapper you mentioned above?
Cyllceaux wrote:- SQLite3 BaseFunction-Include for Win + Lin + Unicode
- SQLite Again! ... (unicode added)
WOW... I used the second link... and DAMN!!!! This is fast...
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Cyllceaux
Enthusiast
Enthusiast
Posts: 334
Joined: Mon Jun 23, 2014 1:18 pm
Contact:

Re: (Solved) NextDatabaseRow extreme slow

Post by Cyllceaux »

I know I can use the native methods. But I don't want to.
I tried it with my DB and it worked fine. But my goal was to use PB-Internals.

My default pragma set:

Code: Select all

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA recursive_triggers = OFF;
PRAGMA encoding = 'UTF-8';
PRAGMA automatic_index = OFF;
PRAGMA synchronous = NORMAL;
PRAGMA page_size = 65536;
PRAGMA locking_mode = NORMAL;
PRAGMA cache_size = -32000;
User avatar
skywalk
Addict
Addict
Posts: 3532
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: (Solved) NextDatabaseRow extreme slow

Post by skywalk »

Still confused?
You saw no speed difference between PB SQLite commands and SQLite wrapper commands?
It was mainly your PRAGMA settings that improved performance?
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Post Reply