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
