We are developing an iOS/Android application which downloads large amounts of data from a server.
我们正在开发一个从服务器下载大量数据的iOS / Android应用程序。
We're using JSON to transfer data between the server and client devices.
我们使用JSON在服务器和客户端设备之间传输数据。
Recently the size of our data increased a lot (about 30000 records).
最近我们的数据量增加了很多(约30000条记录)。
When fetching this data, the server request gets timed out and no data gets fetched.
获取此数据时,服务器请求会超时并且不会获取任何数据。
Can anyone suggest the best method to achieve a fast transfer of data?
任何人都可以建议实现快速数据传输的最佳方法吗?
Is there any method to prepare data initially and download data later?
有没有什么方法可以准备最初的数据并在以后下载数据?
Is there any advantage of using multiple databases in the device(SQLite dbS) and perform parallel insertion into db's?
在设备中使用多个数据库(SQLite dbS)并执行到db的并行插入是否有任何优势?
Currently we are downloading/uploading only changed data (using UUID and time-stamp).
目前我们只下载/上传已更改的数据(使用UUID和时间戳)。
Is there any best approach to achieve this efficiently?
有没有最好的方法来有效地实现这一目标?
---- Edit -----
----编辑-----
i think its not only the problem of mysql records, at peak times multiple devices are connecting to the server to access data, so connections also goes to waiting. we are using performance server. i am mainly looking for a solution to handle this sync in device. any good method to simplify the sync or make it faster using multi threading, multiple sqlite db etc,...? or data compression, using views or ...?
我认为它不仅是mysql记录的问题,在高峰时候多个设备连接到服务器来访问数据,因此连接也会等待。我们正在使用性能服务器。我主要是寻找一种解决方案来处理设备中的同步。使用多线程,多个sqlite db等简化同步或使其更快的任何好方法......?或数据压缩,使用视图或......?
2 个解决方案
#1
1
A good way to achieve this would probably be to download no data at all.
实现这一目标的一个好方法可能是根本不下载任何数据。
I guess you won't be showing these 30k lines at your client, so why download them in the first place?
我想你不会在你的客户端显示这些30k行,那么为什么要首先下载它们呢?
It would probably be better to create an API on your server which would help the mobile devices to communicate with the database so the clients would only download the data they actually need / want.
在您的服务器上创建一个API可能会更好,这有助于移动设备与数据库通信,因此客户端只会下载他们实际需要/想要的数据。
Then, with a cache system on the mobile side you could make yourself sure that clients won't download the same thing every time and that content they have already seen would be available off-line.
然后,通过移动端的缓存系统,您可以确保客户端不会每次都下载相同的内容,并且他们已经看到的内容可以脱机使用。
#2
0
When fetching this data, the server request gets timed out and no data gets fetched.
获取此数据时,服务器请求会超时并且不会获取任何数据。
Are you talking only about reads or writes, too?
你是在谈论读写吗?
If you are talking about writing access, as well: Are the 30,000 the result of a single insert/update? Are you using a transactional engine like InnoDB, e.g.? If so, Are your queries wrapped in a single transaction? Having auto commit mode enabled can lead to massive performance issues:
如果您正在谈论写入访问权限:30,000是单个插入/更新的结果吗?您使用像InnoDB这样的事务引擎吗?如果是这样,您的查询是否包含在单个事务中?启用自动提交模式可能会导致严重的性能问题:
Wrap several modifications into a single transaction to reduce the number of flush operations. InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. The rotation speed of a disk is typically at most 167 revolutions/second (for a 10,000RPM disk), which constrains the number of commits to the same 167th of a second if the disk does not “fool” the operating system.
将多个修改包装到单个事务中以减少刷新操作的数量。如果该事务对数据库进行了修改,InnoDB必须在每次事务提交时将日志刷新到磁盘。磁盘的旋转速度通常最高为167转/秒(对于10,000RPM磁盘),如果磁盘不“欺骗”操作系统,则会将提交次数限制为相同的167秒。
Can anyone suggest the best method to achieve a fast transfer of data?
任何人都可以建议实现快速数据传输的最佳方法吗?
How complex is your query designed? Inner or outer joins, correlated or non-correlated subqueries, etc? Use EXPLAIN to inspect the efficiency? Read about EXPLAIN
您的查询设计有多复杂?内部或外部联接,相关或非相关子查询等?使用EXPLAIN检查效率?阅读EXPLAIN
Also, take a look at your table design: Have you made use of normalization? Are you indexing properly?
另外,看看你的桌面设计:你是否使用了规范化?你索引正确吗?
Is there any method to prepare data initially and download data later?
有没有什么方法可以准备最初的数据并在以后下载数据?
How do you mean that? Maybe temporary tables could do the trick.
你是什么意思?也许临时表可以做到这一点。
But without knowing any details of your project, downloading 30,000 records on a mobile at one time sounds weird to me. Probably your application/DB-design needs to be reviewd.
但是,如果不知道项目的任何细节,一次在手机上下载30,000条记录对我来说听起来很奇怪。可能需要审查您的应用程序/数据库设计。
Anyway, for any data that need not be updated/inserted directly to the database use a local SQLite on the mobile. This is much faster, as SQLite is a file-based DB and the data doesn't need to be transferred over the net.
无论如何,对于任何不需要直接更新/插入数据库的数据,请在移动设备上使用本地SQLite。这要快得多,因为SQLite是基于文件的数据库,并且数据不需要通过网络传输。
#1
1
A good way to achieve this would probably be to download no data at all.
实现这一目标的一个好方法可能是根本不下载任何数据。
I guess you won't be showing these 30k lines at your client, so why download them in the first place?
我想你不会在你的客户端显示这些30k行,那么为什么要首先下载它们呢?
It would probably be better to create an API on your server which would help the mobile devices to communicate with the database so the clients would only download the data they actually need / want.
在您的服务器上创建一个API可能会更好,这有助于移动设备与数据库通信,因此客户端只会下载他们实际需要/想要的数据。
Then, with a cache system on the mobile side you could make yourself sure that clients won't download the same thing every time and that content they have already seen would be available off-line.
然后,通过移动端的缓存系统,您可以确保客户端不会每次都下载相同的内容,并且他们已经看到的内容可以脱机使用。
#2
0
When fetching this data, the server request gets timed out and no data gets fetched.
获取此数据时,服务器请求会超时并且不会获取任何数据。
Are you talking only about reads or writes, too?
你是在谈论读写吗?
If you are talking about writing access, as well: Are the 30,000 the result of a single insert/update? Are you using a transactional engine like InnoDB, e.g.? If so, Are your queries wrapped in a single transaction? Having auto commit mode enabled can lead to massive performance issues:
如果您正在谈论写入访问权限:30,000是单个插入/更新的结果吗?您使用像InnoDB这样的事务引擎吗?如果是这样,您的查询是否包含在单个事务中?启用自动提交模式可能会导致严重的性能问题:
Wrap several modifications into a single transaction to reduce the number of flush operations. InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. The rotation speed of a disk is typically at most 167 revolutions/second (for a 10,000RPM disk), which constrains the number of commits to the same 167th of a second if the disk does not “fool” the operating system.
将多个修改包装到单个事务中以减少刷新操作的数量。如果该事务对数据库进行了修改,InnoDB必须在每次事务提交时将日志刷新到磁盘。磁盘的旋转速度通常最高为167转/秒(对于10,000RPM磁盘),如果磁盘不“欺骗”操作系统,则会将提交次数限制为相同的167秒。
Can anyone suggest the best method to achieve a fast transfer of data?
任何人都可以建议实现快速数据传输的最佳方法吗?
How complex is your query designed? Inner or outer joins, correlated or non-correlated subqueries, etc? Use EXPLAIN to inspect the efficiency? Read about EXPLAIN
您的查询设计有多复杂?内部或外部联接,相关或非相关子查询等?使用EXPLAIN检查效率?阅读EXPLAIN
Also, take a look at your table design: Have you made use of normalization? Are you indexing properly?
另外,看看你的桌面设计:你是否使用了规范化?你索引正确吗?
Is there any method to prepare data initially and download data later?
有没有什么方法可以准备最初的数据并在以后下载数据?
How do you mean that? Maybe temporary tables could do the trick.
你是什么意思?也许临时表可以做到这一点。
But without knowing any details of your project, downloading 30,000 records on a mobile at one time sounds weird to me. Probably your application/DB-design needs to be reviewd.
但是,如果不知道项目的任何细节,一次在手机上下载30,000条记录对我来说听起来很奇怪。可能需要审查您的应用程序/数据库设计。
Anyway, for any data that need not be updated/inserted directly to the database use a local SQLite on the mobile. This is much faster, as SQLite is a file-based DB and the data doesn't need to be transferred over the net.
无论如何,对于任何不需要直接更新/插入数据库的数据,请在移动设备上使用本地SQLite。这要快得多,因为SQLite是基于文件的数据库,并且数据不需要通过网络传输。