I'm in a different environment (linux with database on nfs share) but found
the same behaviour. I came to the conclusion that the latency of network
file system operations combined with database fragmentation was largely
responsible for the reduced performance. SQLite is very seek heavy, unlike
a file copy.
SQLite's internal structure naturally results in lots of fragmentation
unless each table/index is fully populated in turn -- you might try a
VACUUM as a quick test and see if that speeds things up.
I've been experimenting with storing each table in a separate database file
and ATTACHing them all together with some success, but that doesn't help
you much since you only have one table!
I've not done the math but said table looks to have very large rows.
Depending on the page size you're using you may only see one or two rows
per read() op, which will kill performance if you ever need to query on an
unindexed column (requiring a table-scan).
I haven't looked at your queries/indices in any detail so no idea if
there's something particular to your design making the problem worse, just
sharing my experience.
-Rowan
On 6 February 2017 at 18:28, dandl <da...@andl.org> wrote:
> We have an application we converted from Access to Sqlite. Mostly it's
> been a great success, but we have two queries that runs 50x slower across a
> gigabit LAN than on a local file system and we don't know why. Performance
> on Access was perfectly acceptable, and on Sqlite is not and we can't
> figure out why. Customers are complaining, and with good reason.
>
> We're using System.Data.Sqlite and the file is being opened as a shared
> UNC pathname. The network can transfer at upwards of 250 Mbps on file
> copies, but the SQL query runs at around 10 Mbps (Windows Perfmon). The
> database is about 90MB. The queries takes 100ms on local file system and 5s
> on network share. [With customer data it can run into minutes.]
>
> I'm hoping we've done something really dumb and obvious, but we can't see
> it. Details follow. Anyone who can shed light very much appreciated.
>
> The query looks like this:
> Query1:
>
> SELECT Max([date]) AS LastOfdate FROM order_header WHERE
> (((transaction_type)=1) AND ((status_code)=-1) AND ((sale_type_id)=1 Or
> (sale_type_id)=2 Or (sale_type_id)=14)) GROUP BY billToCardGuid,
> date([date]) HAVING billToCardGuid=X'A426D7165BBF0ECA3276555D32B6E385'
> ORDER BY date([date]) DESC limit 3
>
> Query2:
>
> SELECT order_header.order_id AS maxID FROM order_header WHERE
> (((order_header.transaction_type)=1) AND ((order_header.status_code)=-1)
> AND ((order_header.sale_type_id)=1 Or (order_header.sale_type_id)=2 Or
> (order_header.sale_type_id)=14)) AND (order_header.billToCardGuid=X'
> A426D7165BBF0ECA3276555D32B6E385') ORDER BY [date] DESC, order_id desc
> limit 1
>
> The schema looks like this:
> CREATE TABLE IF NOT EXISTS "order_header" (
> "order_id" INTEGER DEFAULT 0,
> "user_name" VARCHAR(31) COLLATE NOCASE ,
> "number" INTEGER DEFAULT 0,
> "confirmation_number" VARCHAR(9) COLLATE NOCASE ,
> "creation_date" DATETIME,
> "modification_date" DATETIME,
> "transaction_type" SMALLINT DEFAULT 0,
> "customer_billto_last_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_first_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_company" VARCHAR(50) COLLATE NOCASE ,
> "customer_billto_address" VARCHAR(63) COLLATE NOCASE ,
> "customer_billto_city" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_state" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_zip" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_last_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_first_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_company" VARCHAR(50) COLLATE NOCASE ,
> "customer_shipto_address" VARCHAR(63) COLLATE NOCASE ,
> "customer_shipto_city" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_state" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_zip" VARCHAR(31) COLLATE NOCASE ,
> "customer_fax" VARCHAR(31) COLLATE NOCASE ,
> "customer_ar_balance" REAL DEFAULT 0,
> "customer_bill_rate" REAL DEFAULT 0,
> "customer_tel" VARCHAR(31) COLLATE NOCASE ,
> "date" DATETIME,
> "status_description" VARCHAR(31) COLLATE NOCASE ,
> "status_code" SMALLINT DEFAULT 0,
> "order_comment" TEXT,
> "payment_comment" VARCHAR(63) COLLATE NOCASE ,
> "terms_description" VARCHAR(31) COLLATE NOCASE ,
> "shipmethod_description" VARCHAR(31) COLLATE NOCASE ,
> "shipmethod_amount" REAL DEFAULT 0,
> "shipmethod_tax_rate" REAL DEFAULT 0,
> "shipmethod_tax_code" VARCHAR(3) COLLATE NOCASE ,
> "tax_total" REAL DEFAULT 0,
> "ex_tax_total" REAL DEFAULT 0,
> "grand_total" REAL DEFAULT 0,
> "pay_amount" REAL DEFAULT 0,
> "balance" REAL DEFAULT 0,
> "card" VARCHAR(19) COLLATE NOCASE ,
> "exp" VARCHAR(4) COLLATE NOCASE ,
> "po" VARCHAR(15) COLLATE NOCASE ,
> "payment_date" DATETIME,
> "printed_name" VARCHAR(31) COLLATE NOCASE ,
> "signature" BLOB,
> "line_item_count" SMALLINT DEFAULT 0,
> "flags" SMALLINT DEFAULT 0,
> "employeeGuid" GUID,
> "employee_bill_rate" REAL DEFAULT 0,
> "employee_name" VARCHAR(31) COLLATE NOCASE ,
> "date_hotsynced" DATETIME,
> "date_exported_to_myob" DATETIME,
> "export_status" SMALLINT DEFAULT 0,
> "export_error_no" INTEGER DEFAULT 0,
> "attempt_export" BOOL NOT NULL DEFAULT 1,
> "invoice_status" CHAR(1) DEFAULT 'I',
> "sale_type_id" INTEGER DEFAULT 1,
> "export_Error_Guid" GUID,
> "validated" BOOL NOT NULL DEFAULT 0,
> "reconciled" BOOL NOT NULL DEFAULT 0,
> "txnGuid" GUID,
> "cardGuid" GUID,
> "billToCardGuid" GUID,
> "shipToCardGuid" GUID,
> "locationFromCardGuid" GUID,
> "locationToCardGuid" GUID,
> "unidentified_chunks" BLOB,
> "toDoGuid" GUID,
> "uom_pick_mode" BOOL NOT NULL DEFAULT 0,
> "validationGuid" GUID,
> "territoryGuid" GUID,
> "territoryGroupGuid" GUID,
> "hasTerritory" BOOL NOT NULL DEFAULT 0,
> "parentTranGuid" GUID,
> "cartonQuantity" REAL,
> "pickInstructions" VARCHAR(64) COLLATE NOCASE ,
> "creator" INTEGER,
> "POSMode" BOOL NOT NULL DEFAULT 0,
> "Locked" BOOL NOT NULL DEFAULT 0,
> "relatedTransactionGuid" GUID,
> "displayMode" INTEGER,
> "signature_date" DATETIME,
> "freezerFull" BOOL NOT NULL DEFAULT 0,
> "sortOrder" INTEGER,
> "handheldViewed" BOOL NOT NULL DEFAULT 0,
> "managerGuid" GUID,
> "templateTranGuid" GUID,
> "approved" BOOL NOT NULL DEFAULT 0, [pay_amount_exported] REAL,
> CONSTRAINT "order_header_order_id" PRIMARY KEY("order_id"));
> CREATE UNIQUE INDEX "order_header_txnGuid" ON "order_header" ("txnGuid");
> CREATE INDEX [order_header_type_idx] ON [order_header]
> ([transaction_type], [sale_type_id]);
> CREATE INDEX [order_header_status_idx] ON [order_header] ([status_code],
> [export_status]);
> CREATE INDEX "order_header_billToCardGuid" ON "order_header"
> ("billToCardGuid");
> CREATE INDEX "order_header_cardGuid" ON "order_header" ("cardGuid");
> CREATE INDEX "order_header_confirmation_number" ON "order_header"
> ("confirmation_number");
> CREATE INDEX "order_header_date" ON "order_header" ("date");
> CREATE INDEX "order_header_employeeGuid" ON "order_header"
> ("employeeGuid");
> CREATE INDEX "order_header_locationFromCardGuid" ON "order_header"
> ("locationFromCardGuid");
> CREATE INDEX "order_header_locationToCardGuid" ON "order_header"
> ("locationToCardGuid");
> CREATE INDEX "order_header_parentTranGuid" ON "order_header"
> ("parentTranGuid");
> CREATE INDEX "order_header_sale_type_id" ON "order_header"
> ("sale_type_id");
> CREATE INDEX "order_header_shipToCardGuid" ON "order_header"
> ("shipToCardGuid");
> CREATE INDEX "order_header_templateTranGuid" ON "order_header"
> ("templateTranGuid");
> CREATE INDEX "order_header_territoryGroupGuid" ON "order_header"
> ("territoryGroupGuid");
> CREATE INDEX "order_header_territoryGuid" ON "order_header"
> ("territoryGuid");
> CREATE INDEX "order_header_toDoGuid" ON "order_header" ("toDoGuid");
> CREATE INDEX "order_header_user_name" ON "order_header" ("user_name");
> CREATE INDEX "order_header_validationGuid" ON "order_header"
> ("validationGuid");
>
> Happy to provide more details if it will help.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
