SQL Mobile 中的一个未选择的大列的性能

本文介绍了SQL Mobile 中的一个未选择的大列的性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个带有一张表的 SQL Mobile 数据库.它有几列包含有用的、经常查询的数据，还有一列存储不经常查询的每条记录(1000 多个字符)相对较大的字符串.

I have a SQL Mobile database with one table. It has several columns with useful, often queried data and one column that stores a relatively large string per record (1000+ characters) that is not queried often.

想象一下这个假模式，lifeStory"字段是大的.

Imagine this fake schema, the "lifeStory" field is the large one.

table1 String firstName String lastName String address String lifeStory

一个有代表性的查询是

SELECT firstName, lastName, address FROM table1 WHERE firstName = :p1

有没有人知道在这个表中留下那个大的、不常查询的列有任何性能问题?

Does anyone know of any performance concerns leaving that large, infrequently queried column in this table?

推荐答案

使用 VS2005 (c#) 和 SQLServerCE 库我可以告诉你它确实影响性能，但我不能真正回答为什么.此外，实际上是该列中数据的长度对性能产生负面影响，而不是该列本身.

Using VS2005 (c#) and SQLServerCE libriaries I can tell you that it does affect performance but I cannot truly answer why. Furthermore, it's actually the length of the data in that column that negatively impacts performance rather than the column itself.

在一个工作项目中对我自己的一些性能进行测试时，我遇到了类似的情况，只是多了一个列.

Doing some performance testing of my own on a project for work I ran into a similar scenario just one extra column.

table1 c1 NVARCHAR (20) c2 NVARCHAR (20) c3 NVARCHAR (20) c4 NVARCHAR (20) c5 NVARCHAR (4000) //maximum allowed and fully populated for testing purposes

如果你跑...

select c1, c2, c3, c4 From table1

...大约需要 1560 毫秒.

...it takes roughly 1560 milliseconds.

如果您创建两个表，将大列拉出(并自然地提供一个外键将两者关联起来)并在第一个表上运行相同的查询，则大约需要 660 毫秒.

If you create two tables, pulling the large column out (and providing a foreign key to relate the two, naturally) and run the same query on the first table, it takes roughly 660 milliseconds.

最后，其他测试告诉我，重要的不是列数，而是每行数据的大小.即 5 列，2 个字符宽 == 2 列，5 个字符宽.此外，请务必在您的移动设备上运行这些.您可以对这些进行单元测试，但由于我的 PC 的马力明显更大，我发现时间差异为 10-20 毫秒，而不是上面所示的近 1000 毫秒.

Lastly, other tests have shown me that it's not the number of columns but rather the size of the data in each row. i.e. 5 columns, 2 chars wide == 2 columns, 5 chars wide. Also, be sure to run these on your mobile device. You can unit test these but due to the significantly greater horsepower on my PC I found the timing difference to be 10-20 milliseconds rather than the nearly 1000 milliseconds illustrated above.

为什么?这只是一个猜测，但...

Why? This is just a guess but...

在移动世界中，DBMS 完全不同；它不是企业数据库.在幕后，我敢打赌他们仍在执行 OLEDB搜索".

In the mobile world the DBMS just can't be the same; it's not an enterprise database. Under the covers I bet they are still performing the OLEDB "seeks."

总的来说，在移动领域，我了解到我的数据库设计不是最规范化"的，而是支持最常见的用例.祝你好运！

Overall, in the mobile space I've learned design my DB's not to be the most "normalized" but rather to support the most frequent use case. Good luck!