SQL效率问题 [英] SQL Efficiency Question

问题描述

大家好，

这是一个最佳实践"问题.我要替换使用Unidata(类似Pick)数据库的应用程序.应用程序本身是一个消息传递过程，这不是问题，表的设置也不是问题.我的问题是索引性质的.在Unidata中，有一个用于待处理"消息的小表，和一个用于已发送"消息的大表.

我的问题是:我可以使用一个布尔字段将两个表合并，当消息待处理(尚未发送)时该字段等于1，然后在发送时更改为0.我可以在该字段(列)上创建索引，但是仅当该字段大于0时才可以创建索引吗?这将是一种有效的方法吗?还是继续有两个表可以更好地做到这一点?我主要关心的是确保可以有效地搜索待处理"消息.

谢谢您的协助，
Rich Algeni

Hi folks,

This is a ''best practices'' question. I am replacing an application that used the Unidata (Pick like) database. The application itself is a messaging process, which is not a problem, neither is the setup of the table. My question is of an index nature. In Unidata, there is a small table for ''pending'' messages, and a very large table for ''sent'' messages.

My question is: could I combine the two tables, using a Boolean field that would be equal to 1 when a message was pending (not yet sent), then changed 0 when sent. Could I create an index on this field (column), but only when the field is greater than 0? And would this be an efficient way to do this? Or is it better to continue to have two tables to do this? My main concern is to make sure the ''pending'' messages can be searched efficiently.

Thank you for your assistance,
Rich Algeni

推荐答案

Rich，

我的经验是使用SQL Server 2005的价值.我不知道将两个表合并是一个好主意.在添加和删除记录方面，索引编制是一项昂贵的操作.一旦索引完成，记录的搜索和检索就非常快.仅当值满足特定条件时，我才知道要索引的任何内容.

为了提高速度，我个人将两个表分开放置.当然，唯一可以确定的方法是创建一个测试用例.我确信您可以将数据导出到您选择的数据库中，并进行测试以查看完成两种不同设置所需的工作量.只需进行一点日志记录，或者花费一天或更短的时间，您就会对正确的选择有更好的感觉.

霍根

Rich,

My experience is with SQL Server 2005 for what it''s worth. I don''t know that combining the two tables is a good idea. Indexing is a bit of an expensive operation in terms of adding and removing records. Once the index is complete, the searching and retrieving of records is quite fast. I''m not aware of any to index only if a value meets a certain criteria.

For speed purposes, I would personally keep the two tables separate. Of course, the only way to be sure is to create a test case. I''m sure you could export your data to your DB of choice and run a test to see how much effort it takes to do the two different setups. With a bit of logging and probably a day''s effort or less, you''ll have a better feel for the right choice.

Hogan

感谢霍根的信息！我将进行一些测试，以了解最好的方法.我不确定我是否已经完全摆脱了困境！

Thanks for the info Hogan! I''ll do some testing to see what''s best. I wasn''t sure if I had completely run off the tracks!

这篇关于SQL效率问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！