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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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