Data schema or technology change?

  • I have a data structure like this:

    CREATE TABLE table
    (
        ID varchar(20) not null primary key,
        column1 int not null
        column2 datetime not null
        ....
        column50 varchar(40) null
    )

    And requirements:
        - dynamic column filters in WHERE statement
        - dynamic columns in ORDER BY
        - paging by dynamic columns
        - the data is changing (insert, update, delete)
        
    For example (everything is dynamic):
          SELECT *
        FROM table
        WHERE column1 = 'xzz'
            AND column2 > 10
        ORDER BY column50, column3
        OFFSET 100000 ROWS FETCH NEXT 1000 ROWS ONLY
        
        SELECT
            column41,
            column30,
            column15
        FROM table
        WHERE column20 in (20, 30, 40)
            OR column30 > 1000
        ORDER BY column41 DESC
        OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

        
    Do you have recommendation to this problem?

    I think there are 2 ways:
        1.) A better database schema to store data, but I don't have idea.
        2.) Better technology for this problem (e.g.: nosql?)

    Thanks

  • First, you have a primary key that doesn't have much of a natural use, so the question is, what about your data is unique to a given row?   I suspect this data structure somewhat because I don't know what the data is supposed to represent in the real world.   Could use a lot more detail in that regard, as well as some idea of the overall objective.    Just seeing the kind of queries you want to run isn't quite enough to base a decision on.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Cluster the table by the most common search.  Most searches aren't totally random each time.  If this one really is, use an int (or bigint) key, not a char(20) one (the char(20) can be stored in another table and looked up by using the int / bigint value).

    Compress the data if at all possible.  Typically page compression yields best results in these kind of cases.  Although, fair warning, it then takes much longer to rebuild/reorg the table, so avoid those on a page-compressed table unless it's truly needed.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 3 posts - 1 through 2 (of 2 total)

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