Jump to content

speed up order by int DESC, text ASC;


Recommended Posts

$sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1";
    $rs = mysqli_query($conn, $sQueryUpdateTime);
    $row = mysqli_fetch_assoc($rs);

the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.

$sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows";
// takes 30 ms. on the indexed int

$sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows";
// takes 300 ms. due to "PatientName" been a text field, even as I did index it

So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.

..tho, I find that any 2nd argument in the ORDER BY is just slow.

Is there a way to query this in a way to have a faster result back ?

Thanks

PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).

Edited by argumentum
Link to post
Share on other sites

so, brain storming, I figure that the way, or only way with my experience, is to make a new column named "order_Dt_Na" and to the date add "001 to 999", where I'd query all names for that date ( that should be under 100, so no big deal ), sort and update the order_Dt_Na column. Every search will be ORDER BY StudyDate DESC, PatientName ASC , so might as well simplify by just ORDER BY order_Dt_Na .

If you have a better idea, or just know better, let me know.

Link to post
Share on other sites
12 hours ago, argumentum said:

So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.

Not so. Whatever engine you use, the query planner must first apply your where clause to select the resulting subset. Then it must sort it as a whole (probably more than 20 rows); this can be helped by an index on (StudyDate, PatientName). Only then it has to honor the offset=N and skip the first N rows, finally output while counting up to 20.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

If your only problem is the sorting being too slow then you should consider getting your data as it is and sort it by yourself. This can be a faster sort than how your db engine can perform.

When the words fail... music speaks
Link to post
Share on other sites

I doubt it since AutoIt is interpreted while any SQL engine is fine-tuned C-like compiled code.

EDIT: BTW doing the sort at application level means doing exactly the same thing as how SQL works as I described in the post above.

You can see that by considering the following job to be done by hand (you're a processor now!):

You're given a deck of 52 regular cards in random order.
Cards fall in 4 colours: Spades, Hearts, Diamonds & Clubs.
Each color is associated to a multiplier: Clubs * 1, Diamonds * 2, Hearts * 3 and Spades * 4.
Say cards are valued like this:  numbered cards = their face value (1=1, ..., 7=7, 10=10), then Jack = 20, Queen = 30, King = 40, each multiplied by the coefficient of the card's colour.
Cards have a name: 1..10 = ace, two, three, ..., ten, jack, queen, king.
You must extract cards in Hearts or Clubs, have at least one 'n' in their name, sort them by name in ascending order and value by descending order, ignoring the first 4 and outputing the next 6.

It's easy to see by oneself that you just can't start sorting the cards and ignoring a number of them until you're done selecting which one is allowed. Yes you partly can but then you must interleave the selection (where clause) and the sort, something very inefficient when coded. In any case you can't starting ignoring cards until the selection/sort steps are done.

Now consider a table of 52 million rows and you give SQL big credit for doing the job for you. Of course creating the most efficient index for a given query greatly helps the query planner.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

After so many years as DBA and database developer I think I can be a good processor. :muttley:

Who said anything about AutoIt? I mean of course AutoIt arrays won't be faster than database engines but sorting algorithms are pretty easy to be implemented in ASM. Anyway what OP asked is  regarding the two queries that sort data by one or two columns. For MS SQL Server for example this doesn't matter, a simple look at the execution plan will reveal you identical executions in the same amount of time. I don't know what database engine OP use but it's pretty obvious that something else is going on there. Maybe showing us the design of that table and your indexes will help us to give you better advices.

Edited by Andreik
When the words fail... music speaks
Link to post
Share on other sites

That's why I proposed a compound index on both columns.

BTW sorting at app-level is still a bad idea: you need to obtain the whole subset of rows from the where clause and if the subset is large that implies exporting the data from the server to the client before the latter can sort, ignore and count. Better make good use of the server fine-tuned code and RAM caches.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

Again, the 20 rows is the consequence of the limit 20 clause. If the selected subset (after processing the where clause) is 50 million rows, you still have to get your hand on all that many rows to sort them all, then ignore the X first of that sorted resultset (offset X clause) and output the Y next (limit Y clause).

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
5 hours ago, Andreik said:

For MS SQL Server for example this doesn't matter

I'm using MySQL but is the MariaDB flavor that comes with XAMPP.
After searching with this TEXT column, I change it to CHAR(60) but to no avail. It took pretty much the same time.
Then I index the heck out of it but the single indexing of both columns did not show noticeable improvement.
Then I added a 21st column, with a int(11) with the date and a counter ( YYYYMMDDnn ) and that gave me the best result as is just sorting by one int. column.

But, and this is a big but. The DB engine. I'd love for the implementation of the SQLite ( the UDF ), to be faster than the MySQL that comes with XAMPP, but if MS SQL is faster and as maintenance free as MySQL then I'd make the jump to MS SQL. Not the full fledged one but a "personal" or express version. Again, if SQLite is on par with these other bigger DB engines, SQLite is portable AND, that makes it beautiful in my eyes 😍

Anyways, there are so many flip-flops in a CPU. Meaning, they all are optimized code with years of development, my choice would be based in my experience and I have little.

So we are biased by our own experiences and abilities. Would you care to tell me what way would you go in my case ?, no triggers or other "fancy" DB use. Just INSERT, UPDATE and SELECT. What say you ?  :) 

Edit: from https:// www.sqlite.org/whentouse.html 
SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
SQLite does not compete with client/server databases. SQLite competes with fopen().

https://www.sqlservercentral.com/articles/single-user-performance-of-sqlite-v-sql-server is also a good read.

Edit #2: I'll import the DB to SQLite, and test it. That will decide what is actually better ;) 

Edited by argumentum
Link to post
Share on other sites
6 hours ago, argumentum said:

Would you care to tell me what way would you go in my case ?, no triggers or other "fancy" DB use. Just INSERT, UPDATE and SELECT. What say you ?

Please tell what entities you're managing. From what I gather from the thread, you have:

Table tblstudies:
rowid integer (probably),
studydate char,
intUpdateTime char,
patientname char,
PatientBirthDate char,
probably more columns.

I'll try to make something up later today.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

intUpdateTime is a bigInt(20) to fit "20200229033627", PatientBirthDate is int(11) as "19991231" fits, ..used Integers as much as I can, else, TEXT.
And yes, I have a "RowID" as primary index, as it was an import from SQLite. And there are other tables too.

All these started with SQLite and PHP in my brainstorming/developing days back in 2018, then when I got a better working understanding moved to MySQL and PHP, but now I know that I can do it all in AutoIt and have no need for PHP. So I wanna move it all to AutoIt since my discovery of SSL for AutoIt ( that I found by chance looking into Syncthing ).
HTML is HTML, no matter what generates the HTML. Same with SQL stuff. It all comes down to responsiveness since these web sites have no more than 20 users. Not even 1 user a minute so is not a highly/heavy loaded site(s).
The site is in the customer's server, as I don't find the need to have the site on another "cloud" provider. Already have a server :) 
The idea of going to AutoIt/SQLite, is due to the 
noseyness of the "server admin." in one company, so, I'd move the "magic" site to an encrypted folder in my user account and protect my work while coding in a language I'm familiar with.

Now, back to SQL. I figure that SQLite could run in :memory: and outperform any other DB engine due to the usage I'll give it.
Same with PHP. In AutoIt it will have the pages as $sStrings, all in memory.

This setup should kick the heck out of anything else, at least in my mind. But I will only know for sure once is done and tested.

Now. I have no schooling and very low experience compared with y'all, so ...., I post to ask for advice since my trend of thought, could be basically flawed, period!, and go nowhere fast ( actually painfully slow as I code everything to no avail ) 😅

...and that is the full story  :) 

Link to post
Share on other sites

SQL Server it's pretty reliable and easy to work with. With express edition you are limited at 1 physical processor, 1 GB memory, and 10 GB storage but hey it's completely free. So if I understand well, it's for a website without too much traffic. If this is the case and you can work with these limitations you MS SQL is a powerful database engine.

When the words fail... music speaks
Link to post
Share on other sites
1 hour ago, Andreik said:

If this is the case and you can work with these limitations you MS SQL is a powerful database engine.

In my case they are not limiting, as the site will never get to 1 GB, since 10 years of data takes 100 k.
But is it's performance better than MySQL ?. MySQL need not be "installed". It can run as service but as user too.
And for SQLite, it only takes to load a DLL.

I'll post my findings once I finish coding the "MySQL 2 SQLite DB importer", and query via an API i'll put together as the DB engine*.

*I'll use the HTTP server to run the SQLite. It'll add an extra 5 ms. to the total time of the query.

Link to post
Share on other sites

When it comes to databases there are two serious giants: oracle and sql server so yes it's a pretty reliable and performant database engine with a lot of features. But of course this doesn't means it would be the best fit for every project. Since yours it's quite small and will not grow very much in time sqlite might be a better choice and will give you more portability. You just need to find the balance between the features, performance and your requirements.

When the words fail... music speaks
Link to post
Share on other sites
On 3/5/2020 at 1:13 PM, argumentum said:

This setup should kick the heck out of anything else, at least in my mind. But I will only know for sure once is done and tested.

Update: Done and tested. It does not "kick the heck out of anything else".
Is about 1/3 rd slower ( ~200 ms. vs. ~300 ms. on a slow PC ). Not that bad for an all (almost all)  AutoIt solution (the HTTP is encapsulated by Apache to have SSL).

 

On an unrelated note:

I needed to return the query as JSON and the only solution I came up with is:

Local $iReturnJson = 2 ; ...this is part of a Func but this is the JSONfier loop
    Local $sJson = ""
    ; $aResult comes from _SQLite_GetTable2d()
    For $n1 = 1 To UBound($aResult) - 1
        $sJson &= '{'
        For $n2 = 0 To UBound($aResult, 2) - 1
            $sJson &= '"' & $aResult[0][$n2] & '":' & Json_Encode($aResult[$n1][$n2]) & ','
        Next
        $sJson = StringTrimRight($sJson, 1) & '},'
        If $iReturnJson > 1 Then $sJson &= @CRLF
    Next
    $sJson = StringTrimRight($sJson, ($iReturnJson > 1 ? 3 : 1))
;~  ConsoleWrite($sJson & @CRLF)
    Return $sJson

..tried to use json_group_array() but could not get it to work.
Do any of you know if our SQLite can return the data as JSON ?
Does it only work if CREATE TABLE json_tree() ?

There is no clear and concise examples of a regular SQLite db and getting the data as JSON that I can find and understand. 

Link to post
Share on other sites

The SQLite JSON1 extension provides functions to check, navigate, parse, extract, update Json data; but it does offers only very limited functionality to produce general Json output from the result of a query.

It isn't completely impossible to achieve but this is very cumbersome and each query would need a long and complex ad-hoc formatting.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
On 3/7/2020 at 3:41 PM, argumentum said:

Update: Done and tested. It does not "kick the heck out of anything else".

Update #2: It does "kick the heck out of anything else".
Is twice as fast as PHP with MySQL/MariaDB. Tested on 2 PCs.
This speed is due to getting the JSON I needed straight from SQLite with the DB in :memory:.

I wanted to share this, to not leave the post with the believe that coding a whole site in AutoIt is impractical, or slower :) 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       
      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       
       
      Have fun,
      mLipok
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
      FOR EXAMPLE DATABASE use AdventureWorksDW2016_EXT.bak from:
      https://github.com/microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2016_EXT.bak
      I will relay on this database in my examples.
       
      Here is link to post which shows how "ODBC Data Source Administrator" looks like.
       
    • By shino54
      Hello,
      I would like a query to know if an entry exists
      Thank you in advance.
      $sQuery = "SELECT Alger FROM garage where auto='BL1879'" $result1 = $result.Fields("Alger").Value if $result1="" Then MsgBox(0, "ERROR", "BAD not exist") Else MsgBox(0, "Success!", "OK exist") EndIf Exit  
    • By Nas
      Hi everyone,
      I am trying to make a script that runs a query and show it to me to see if everything is right and then decide if I finish it or not so I made a little script as below :
      #include <ADO.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> _ADO_EVENTS_SetUp(True) _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler) Local $sDriver = 'SQL Server' Local $sDatabase = 'DataBase' ; change this string to YourDatabaseName Local $sServer = 'Localhost' ; change this string to YourServerLocation Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & ';PWD=' & ';' ;~ Global $Query = _ ;~ "BEGIN TRAN" & @CRLF & _ ;~ "UPDATE Table" & @CRLF & _ ;~ "SET HOUR = 4" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "SELECT * FROM Table" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "ROLLBACK TRAN" Global $Query = _ "BEGIN TRAN" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" _Query_Display($sConnectionString, $Query) Func _Query_Display($sConnectionString, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenConString($oConnection, $sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Query Result') EndFunc ;==> _Query_Display When I ran this script it works great, but when I run the query below :
      Global $Query = _ "BEGIN TRAN" & @CRLF & _ "UPDATE Table" & @CRLF & _ "SET HOUR = 4" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" It doesn't show anything, when I take those begin and rollback it does what it should but still not showing me anything at all, is there a way around it that you know of?
      Thank you.
    • By Nas
      Hello Guys,
       
      I am running the Ado example script, if I run this line, it runs without errors :
      Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' But when I try the windows auth one , it failed everytime :
      Func _Example_MSSQL_WindowsAuthorization() Local $sDatabase = 'AdventureWorks2016' Local $sServer = 'SQLSRV' _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address") EndFunc here is the error :
       
      ADO.au3 v.2.1.16 BETA (1204) : ==> COM Error intercepted !
      $oADO_Error.description is:     Provider cannot be found. It may not be properly installed.
      $oADO_Error.windescription:     Exception occurred.
      $oADO_Error.number is:     80020009
      $oADO_Error.lastdllerror is:     0
      $oADO_Error.scriptline is:     1204
      $oADO_Error.source is:     ADODB.Connection
      $oADO_Error.helpfile is:     C:\Windows\HELP\ADO270.CHM
      $oADO_Error.helpcontext is:     1240655
      By the way, I have 64x windows server 2016 with SQL Server , I already installed the odbc my sql connector, any thought on this will be greatly appreciate it.
    • By argumentum
      I was thinking but I don't have the experience, so you may have the experience.
      I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
      The DB is now in MySQL. I wanna do all this chopping and use SQLite. 
      Thanks
      [solved]
×
×
  • Create New...