Would a "server app" be an option, so run SQLite on the remote location and
return the dataset?
RBS
On Mon, Feb 6, 2017 at 10:28 AM, 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
