SQL Efficiency Question

Question

0.00/5 (No votes)

See more:

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

Posted 5-Mar-12 11:25am

Richard Algeni Jr

Add a Solution

2 solutions

Solution 2

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!

Posted 6-Mar-12 9:23am

Richard Algeni Jr

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

snorkie · Accepted Answer · 2012-03-05T11:57:00

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

SQL Efficiency Question

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

SQL Efficiency Question

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us