March 15, 2018 at 10:57 am
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
March 15, 2018 at 1:13 pm
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)
March 15, 2018 at 1:40 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply