Hi Brian, I few things. The problem is your network speed compared to local disk speed (also distance is an issue as well) Disk transfer is in Mega bytes where as network IO is in mega bits. SO while IDE standard 33.3MB looks slower than 100mb for your network (even switched) it is in fact slower. Also most IDE are inboard to the motherboard which bypasses the IO bus. Not the same for network cards while lots are inboard they tend to hang off the IO bus and therefore behave the same as plug-in cards(obviously depending on you motherboard).
So no matter what you do local will always be fast, by a magnitude at least. Some things you can check with your network is to make sure you are using a 100mb card (if it's a plug-in) Make sure that you have the card set to 100mb Full duplex as apposed to Auto. There is a Cisco bug in lots of there switches and hubs. This bug relates to auto detect of speed, basically if you have a network card set to Auto every packet is check to ensure it is at the right speed. This slows down the transmission rate a great deal. Make sure you IT department has the switch set-up to 100mb Full duplex as well You could try paging using the offset and limit clauses but depending on your queries these may not give you the results you require. If you query is structured to return the rows in a certain order which the index will not handle this may require a full table scan . Which will mean the same time delay for less rows. The issue is very piece of data that needs to be check has to be transferred from the network location to your P.C. Then that information is then stored and the next retrieved until the whole table is processed. Indexes make it quicker by being sorted and having less information. Another suggestion would be to look at the query and find out if you really need to return so many rows of data (58,840 is a lot) Also have a look at your data if you DB is 37MB and you have 58,840 rows each row is on average 618K . This is a lot of data in a single row. Some times we get carried away with DB design and just because we can store data we do. A good example of this is images and documents. We can store these in a DB but it much better to store a URL for share path to the file and then store the file on the OS drive. Drives ad directories are much better at servicing these items than databases. Or if this isn't the case you could try splitting the table into a one to one relationship table. Have the items you do lookups on in one table and the retrieval items in another. Then you can do a lookup and just join the results to return all the data. This should improve the processing a bit but not the transfer time. It should also reduce the index sizes depending on what you index. Lastly you could try a local cache of the data. use a splash screen with some sort of information like "Init system please wait". Copy the DB locally and then use the local system to query against. When updating you will need to do two updates , but this then fails when other people update the data and you will not see the changes. regards Greg O ----- Original Message ----- From: Brian Pugh To: SQLite Email Sent: Wednesday, November 26, 2003 10:12 AM Subject: [sqlite] Network Performance Hi, Now that I've completed my SQLite application (written in Pyxia's IBasic), using the SQLite DLL, I have some questions My database is about 37mb, and has 114,380 records (rows) If I open and query the database locally (ie, the .db file is in the same folder as the application), things move pretty quick. I can do a query for a certain range of records (59,840) in about 4 seconds Doing the same query with the same .db on a network server is taking 18 seconds. Is this acceptable? I find that pretty slow! Most of my queries are fashioned so that they use indexes - I try not to use the "like" operator We have a certified 100mb network, with Cisco gigabit switches, and I am running the application from a 2.4gHz P4 PC How do other users deal with querying large amounts of information over a network? Are you paging in records, say 500 at a time, and then paging in the next 500 records? Could I set my database up differently? At the moment, when the .db loads up, I am setting: pragma cache_size=8192; pragma synchronous=off; pragma count_changes=off; Are there any other tweaks I could set in this area? If anyone has any ideas or suggestions as to how to improve the query speed over a network, I would love to hear them Thanks a lot, Brian Pugh Halifax, England
