I don't use transactions. I only use selects. What do you mean with "wrapper mode"?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.
(Solved) NextDatabaseRow extreme slow
Re: NextDatabaseRow extreme slow
Re: NextDatabaseRow extreme slow
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)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.
Re: NextDatabaseRow extreme slow
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.
- SQLite3 BaseFunction-Include for Win + Lin + Unicode
- SQLite Again! ... (unicode added)
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
...
- SQLite Again! ... (unicode added)
Last edited by Danilo on Fri Mar 19, 2021 8:38 am, edited 1 time in total.
Re: NextDatabaseRow extreme slow
PB_Database_DynamicCursor slows extreme down 276302ms.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 ...
I think I will write a own LibCall for the sqlite Dll
Re: NextDatabaseRow extreme slow
You don't need to write a 'wrapper'.
You can simply use the functions:
viewtopic.php?p=556707#p556707
You can simply use the functions:
viewtopic.php?p=556707#p556707
Re: NextDatabaseRow extreme slow
WOW... I used the second link... and DAMN!!!! This is fast...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.- SQLite3 BaseFunction-Include for Win + Lin + UnicodeCode: Select all
s = sqlite3_step (stmt) if s = #SQLITE_ROW ...
- SQLite Again! ... (unicode added)
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()
8ms in normal mode
Re: NextDatabaseRow extreme slow
That's strange, NextDatabaseRow() for SQLite is just sqlite3_step(), nothing else.
Re: NextDatabaseRow extreme slow
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
Re: NextDatabaseRow extreme slow
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:
C code:
Code: Select all
sqlite3_prepare_v2(Database->Handle, (char *)Request, -1, &Database->Statement, &Tail)
Re: NextDatabaseRow extreme slow
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.
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.
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;
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.
Re: (Solved) NextDatabaseRow extreme slow
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
Re: (Solved) NextDatabaseRow extreme slow
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.
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.
Re: (Solved) NextDatabaseRow extreme slow
Ok,
Maybe language issue?
Are you setting PRAGMA automatic_index = 0?
And you saw no advantage using the wrapper you mentioned above?
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
Re: (Solved) NextDatabaseRow extreme slow
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:
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;
Re: (Solved) NextDatabaseRow extreme slow
Still confused?
You saw no speed difference between PB SQLite commands and SQLite wrapper commands?
It was mainly your PRAGMA settings that improved performance?
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