Quantcast

Why does a query run 50x slower across a network?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Why does a query run 50x slower across a network?

dandl
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why does a query run 50x slower across a network?

Rowan Worth-2
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why does a query run 50x slower across a network?

Simon Slavin-3
In reply to this post by dandl

On 6 Feb 2017, at 10:28am, dandl <[hidden email]> wrote:

> I'm hoping we've done something really dumb and obvious, but we can't see it.

> CREATE INDEX [order_header_type_idx] ON [order_header] ([transaction_type], [sale_type_id]);

Nothing really dumb, but this might help.

Create another two indexes with these three fields in this order:

transaction_type,status_code,sale_type_id
status_code,transaction_type,sale_type_id

Once you’ve done that, run the SQL command ANALYZE on that database.

You can delete the index it doesn’t end up using.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why does a query run 50x slower across a network?

ajm
In respect to the Windows environment I've appreciated that the use of UNC convention over a network (LAN) behaves much slower that "mapping" the logical unit as a drive letter D, E, .. Z in the local host. Altought unfortunately this doesn't seem very handy in all situations.

--
Adolfo.

>
> ---- Mensaje original ----
> De: dandl
> Para:  SQLite mailing list <[hidden email]>
> Fecha:  Mon, 6 Feb 2017 11:02:39 +0000
> Asunto:  Re: [sqlite] Why does a query run 50x slower across a network?
>
>
On 6 Feb 2017, at 10:28am, dandl <[hidden email]> 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...


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why does a query run 50x slower across a network?

Bart Smissaert
In reply to this post by dandl
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why does a query run 50x slower across a network?

Dominique Devienne
On Mon, Feb 6, 2017 at 4:27 PM, Bart Smissaert <[hidden email]>
wrote:

> Would a "server app" be an option, so run SQLite on the remote location and
> return the dataset?
>

Didn't sound like it was, from David's description, but in case I'm
guessing wrong,
then https://github.com/rqlite/rqlite might be of interest or an
inspiration. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why does a query run 50x slower across a network?

James K. Lowden
In reply to this post by ajm
On Mon, 06 Feb 2017 13:12:22 +0100
[hidden email] wrote:

> In respect to the Windows environment I've appreciated that the use
> of UNC convention over a network (LAN) behaves much slower that
> "mapping" the logical unit as a drive letter D, E, .. Z in the local
> host.

That's bizarre.  By mapping a network file service to a drive letter,
the user gains some convenience, and saves the OS very little: only the
work of resolving the name, and maybe some other setup.  Command
conveyance and data transfer should be identical.  In my experience, it
always was.  

If you're seeing noticeable difference, I'd expect you'll find they're
either in name resolution or somewhere in the GUI.  I can't think of
any reason the underlying transport would be affected.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why does a query run 50x slower across a network?

ajm
In reply to this post by ajm
Besides my own experience, although it is a bit older and I couldn't find it in this email list, It is still in my archive, so there you have (copy-paste) of related email of 2014-09-08 in this list, send by jose isaias cabrera <...@cinops.xerox.com> in response of a query:

Re: [sqlite] Does the Connection string support UNC paths?

[hidden email] wrote...

>
>>
>> ---- Mensaje original ----
>> De: "Chris"
>> Para:
>> Fecha: Sat, 6 Sep 2014 23:46:19 -0500
>> Asunto: [sqlite] Does the Connection string support UNC paths?
>>
>>
>>I am old database programmer that just came across SQLite and am working
>>on
>>a small project for a PVR that uses SQLite as it's db provider. I try
>>specifying a UNC path to the database for the datasource in the connection
>>string and I get the following error, "unable to open database file". .
>>When I look at the exception generated, I see an errorcode = 14. However,
>>if I map a network drive, I can open the file and work with it. I am
>>running Windows 7 x64 Pro and system.data.sqlite version 1.0.93.0 with dot
>>net framework 4.0 and Visual Studio 2010.
>>
>>
>>Obviously SQLite supports UNC paths because I am using SQLite database
>>browser to open the same database using a UNC path.
>>
>
> Also, a full pathname, can start with a double backslash (\\), indicating
> the global root, followed by a server name and a share name to indicate
> the path to a network file server.

Just a little suggestion: UNC paths are slower than connecting that same
path to a drive. If you are going to use it a lot, I suggest for you to
connect that path to a drive and it will be much faster. We have a system
using SQLite with a SharedDB and connecting that path to a drive is much
faster. Ihth.

josé

--
Adolfo.
>
> ---- Mensaje original ----
> De: James K. Lowden
> Para:  SQLite mailing list <[hidden email]>
> Fecha:  Mon, 06 Feb 2017
> Asunto:  Re: [sqlite] Why does a query run 50x slower across a network?
>
>> In respect to the Windows environment I've appreciated that the use of UNC convention over a network (LAN) behaves much slower that "mapping" the logical unit as a drive letter D, E, .. Z in the local host. Altought unfortunately this doesn't seem very handy in all situations.

>That's bizarre.  By mapping a network file service to a drive letter,
the user gains some convenience, and saves the OS very little: only the
work of resolving the name, and maybe some other setup.  Command
conveyance and data transfer should be identical.  In my experience, it
always was.  

>If you're seeing noticeable difference, I'd expect you'll find they're
either in name resolution or somewhere in the GUI.  I can't think of
any reason the underlying transport would be affected.  


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Why does a query run 50x slower across a network?

gbinfo
In reply to this post by dandl
There are several reasons why networks are much slower than local disks
(think of SATA vs. Ethernet, SATA Bus vs. Network latency, no client
side caching etc.). This is especially true for random access patterns
like those SQLite uses.

So to minimize file access, (like already suggested by others) carefully
inspect and adjust your indexes using EXPLAIN and do a VACUUM and ANALYZE.

But in general I'd advise against using file-based databases over
network filesystems. They tend to have problems with random access
patterns. I've seen systems where you could happily throw gigabyte-sized
files back and forth but failing miserably on random access.

If you need server-side storage, consider using a full-fledged database
server. Since you are coming from Access, SQL Server Express comes to my
mind but PostgreSQL or Firebird may also be an option.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...