table without UPDATA and DELETE

Question

table without UPDATA and DELETE

xbarf

SSC Enthusiast

Points: 182
More actions
November 4, 2010 at 7:07 am

#240728

My DB has only one table. This table has only two columns (X & Y) but many many records.
The only operations on this table are: INSERT and SELECT.
I mean, records are never deleted or updated.
Also the first value in each record (X) is a unique big integer and always will be inserted in ascending order. That is: if I insert a new record, it's X will be more than existing X values in the table.
My question: I have to optimize the SELECT command. Is there any feature in SQL Sever 2008 to help me? Please consider special features of this table. Maybe there are some facilities in the SQL Server to help optimizing this very simple table.
Thank you.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31172 More actions · Answer 1

Is your integer column set up as an identity field and a primary key?

Sounds like indexing could help you out. (A bunch of folks on these forums know more about indexing than I do; can any of you chime in?)

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 2

How many records are we talking about? Maybe partitioning could help you.

How does the SELECT look like? Proper indexing will help you here.

N 56°04'39.16"
E 12°55'05.25"

WayneS SSC Guru Points: 95461 More actions · Answer 3

It sounds like the datatype for column X is a BIGINT. What is the datatype of column Y? Is column X an IDENTITY column?

Is the WHERE clause in the SELECT queries running against column X or Y? Does the SELECT query retrieve the other column also?

If possible, please post the table DDL (CREATE TABLE script, along with it's current indexes / constraints). See the first link in my signature for how to do that.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

xbarf (11/4/2010)
My DB has only one table. This table has only two columns (X & Y) but many many records.
The only operations on this table are: INSERT and SELECT.
I mean, records are never deleted or updated.
Also the first value in each record (X) is a unique big integer and always will be inserted in ascending order. That is: if I insert a new record, it's X will be more than existing X values in the table.
My question: I have to optimize the SELECT command. Is there any feature in SQL Sever 2008 to help me? Please consider special features of this table. Maybe there are some facilities in the SQL Server to help optimizing this very simple table.

I'll love to see the business requirements for such database 🙂

Now, seriously, let me summarize what other posters are saying.

1- Post DDL for table creation

2- Post DDL for any existing index.

3- Post predicate of queries you want to run faster.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

xbarf SSC Enthusiast Points: 182 More actions · Answer 5

X is a BIGINT. It is also the primary key of the table.

Y is a REAL value.

About 200 new records will be INSERTed into the table per second.

So, after one year, about 6,000,000,000 records are in the table.

The SELECT statements only uses value of X:

SELECT * from t WHERE X > ??? AND X < ???

In fact, X is time ; Y is value of a physical property during time.

The table is so big that even COUNTing number of records needs a great deal of time.

Thanx 4 ur help

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 6

Alter column ColX to be a bigint identity NONCLUSTERED primary key.

Alter column Coly to be a REAL clustered column.

Then your selects will be really speedy! 😀

But then again, inserting new values could lock the table if the ColY values are random in nature.

For 6 billion records, I would consider partitioning the table according to ColX value, one partition per billion records. Partititioning is only available in Enterprise Edition. Which is what you want when having this number of records.

Also consider page compression!

N 56°04'39.16"
E 12°55'05.25"

xbarf SSC Enthusiast Points: 182 More actions · Answer 7

values of Y column are random and I do not want to make an index on them.

I would think that compression will slow it down. Isn't so?

"inserting new values could lock the table"

What does "table lock" mean?

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 8

If you make the ColY clustered, inserting a new record into the clustered index will make the index reorganize and while doing so, the index is locked and since clustered index basically is the table, no new insert will be possible.

But then again, you can set the clustered index as ONLINE, bu I haven't checked performance.

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 9

I have another thought... In what range does ColY varies within?

0-100?

N 56°04'39.16"
E 12°55'05.25"

xbarf SSC Enthusiast Points: 182 More actions · Answer 10

If it helps, values of colY can be in the range of [0-100] or [1-100].

But as said, colY is random in nature and can not be used as an index.

It is an essential requirement for my application to INSERT new records at any time.

Also new records will be INSERTed into the table continuously (day & night ; 24 x 7), but SELECTed a few times during each day.

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 11

How does the distribution of ColY values look like?

If the distribution is somewhat even, consider making the partitioning key on ColY value.

Maybe 10 partitions, ranging 0-(10, 10-(20, 20-(30, 30-(40, 40-(50, 50-(60, 60-(70, 70-(80, 80-(90, 90-100.

Then you can keep the clustered index on ColX and SQL Server will be smart enough to only fetch data from the "active" partitions (where the data really is).

You can also create 100 partitions and 10 filegroups, laying partitions with number ending on 1 on filegroup 1, partitions ending on 2 on filegroup 2 and so on. This way there will be less chance of locks.

N 56°04'39.16"
E 12°55'05.25"

xbarf SSC Enthusiast Points: 182 More actions · Answer 12

it seems to be an smart solution,

however even a small chance of blocking will be destructive to my app.

can I distribute the load on two or more servers?

because of importance of data, I should use a mirror (online backup) for my DB. Maybe each server can produce a part of result for SELECT query.

of course I am not sure about feasibility and technical benefits of this solution

xbarf SSC Enthusiast Points: 182 More actions · Answer 13

Is there any method to distribute the load on servers?

my servers are a principal sever and a mirror for it.