$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 ?
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 ).
I was looking for a way to use the CLR functions to check a SQL 2016 database state, but I've been unable to find an example that I can get to work. Any help would be appreciated. Here's what I have, which always errors when I try to set the connection string and I'm not sure why. Thanks!
#include ".\Includes\CLR.au3" #include ".\Includes\CLR Constants.au3" JustATest() Func JustATest() Local $oAssembly = _CLR_LoadLibrary("System.Data") ConsoleWrite("$oAssembly: " & IsObj($oAssembly) & @CRLF) Local $oSQLConn = _CLR_CreateObject($oAssembly, "System.Data.SqlClient.SqlConnection") ConsoleWrite("$oSQLConn: " & IsObj($oSQLConn) & @CRLF) $oSQLConn.ConnectionString = "Server=serverip;Database=dbname;UID=user;PWD=aaaa;" $oSQLConn.Open EndFunc Edit: I am using ptrex's CLR UDF from here:
Good morning, I am trying to figure out if it is possible to check if these invoice lines exist or not.
Here are the table and column name: INVOICELINE.INVOICELINENUM (a required field if created)
Here is a picture of what I am talking about. (Do not worry about security. the picture is from a demo test site so all information is fake)
is there any way to check if these fields exist or not? (they do not exist unless the user clicks on "New Row")
Example: line 11 does not exist right now. How would I go about to see if it did or not?
This is what I have so far:
SELECT DISTINCT iv.invoicenum, iv.description FROM invoice AS iv JOIN invoiceline AS ivl ON iv.invoicenum = ivl.invoicenum AND iv.siteid = ivl.siteid /* = 'nothing' and yet somehow not null? */ WHERE iv.invoicenum NOT IN (SELECT invoicelinenum FROM invoiceline WHERE invoicelinenum IS NOT NULL) I get 0 results where I should get more than 0.
I have now added a 4th program to the mix, and decided to place all under the banner of a suite - KindEbook Suite.
I believe it will be better served by having its own topic, rather than attempt to edit the first few posts in the old one - KindEbook Wishlist.
It is also a chance to de-clutter and make some improvements.
Add Book & All Formats To Calibre
ConvertEbook (now replaced by Batch Ebook Convert if you wish) Redundant where KFX files are concerned.
Batch Ebook Convert (new)
CalibBrowser - Multi-faceted viewer for calibre (while kind of complimentary, not part of the suite proper, not a step to apply)
Batch Create & Run
RECOMMENDED 3RD PARTY PROGRAMS & RESOURCES
calibre - Integral to the last two of my complimentary programs, and an important element of the first one. Basically an ebook database & conversion program, but also offers plenty more, especially adding ebooks to or removing ebooks from your reading device. Wikipedia
Sigil - A wonderful open source ebook editor & creator. Great for fixing issues with EPUB files, especially after converting from another format. Wikipedia
MobileRead Forums - Excellent resource for all things ebook.
BookBub - Great advisory source for ebooks, that lets you know via email about great bargains at Amazon Kindle, Kobo, Google Play, Barnes & Noble, Apple iBooks. I still use KindEbook Wishlist with anything I obtain at Amazon via BookBub links, but usually just for a purchase record ... essentially I ADD (mostly freebies) and then immediately RELOCATE to my bought (b) user. A BIG Thanks to @willichan who told me many months ago, about BookBub.
Usage Of My Programs
To help others to understand about these programs and how to use them for the best benefit, I thought it best to outline how I use them, usually each and every day.
Quick Summary Of Usage (in order of use)
1. KindEbook Wishlist - Amazon Kindle Wishlist, Price Checker, History of Changes, Record of Purchases.
2. Bucket-for-URLs - Assistant to KindEbook Wishlist with new ebook discoveries, if a lot of them. When BookBub freebies lead to many others at Amazon.
3. AZWPlug - Add Kindle purchases to a database and to calibre, plus get right-click detail of selected ebook file (once added to database).
4. UpdateFromOPF - Make changes or corrections to the Metadata (OPF) file in calibre.
5. Batch Ebook Convert - Convert existing ebook (usually AZW3, sometimes MOBI) to EPUB and if missing, MOBI.
6. TeraCopy Timer - Make backups locally (outside of calibre library), and to thumbstick for transfer to another PC etc.
7. Batch Create & Run - Make passworded and zipped backups to Google Drive (locally).
8. Add Book & All Formats To Calibre - Add one backup copy of each ebook (all formats) to calibre on another PC.
Add Book & All Formats To Calibre
Batch Ebook Convert