(Solved) NextDatabaseRow extreme slow

[Cyllceaux]

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]

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)

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

[Cyllceaux]

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]

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

viewtopic.php?p=556707#p556707

[Cyllceaux]

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]

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

[Cyllceaux]

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]

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]

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.

[skywalk]

Cyllceaux - Was the solution to use wrapper code or was it a newer SQLite3.dll?

[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.

[skywalk]

Ok,
Maybe language issue?

Are you setting PRAGMA automatic_index = 0?

And you saw no advantage using the wrapper you mentioned above?

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

[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;

[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?