Discussion :

[ash.ice.loky]

Bonjour,

Je possèdes environ 1to de fichiers binaire de données stocks Ticks (trades and quotes).
Pour faire simple ce sont des informations de prix des actions.

une journée pour une action est environ de 100,000 lignes par symboles (certains symbole dépasse le millions).
j'ai a peu près 5,000 symboles.

Je cherche à mettre une partie des ces informations en base SQLServer, je démarre sur une version 2016 RC1 afin de profiter des columnstore cluster index, mais je pense que la version 2014 doit permettre de faire la même chose.

Je vois 4 façons de gérer les données (les champs sont simplifiés),

solution 1: tout dans la même table en donnant une priorité au symbole
SymbolId (int)
DateAndTime (Datetime)
Price (real)
Quantity (int)
+ permet de faire du traitement analytique
- insertion lente de façon quotidienne



solution 2: tout dans la même table en donnant une priorité à la date
DateAndTime (Datetime)
SymbolId (int)
Price (real)
Quantity (int)
+ permet de faire du traitement analytique
- requétes lentes pour un même symbole puisque l'on est trié par date

solution 3: une table par symbol
DateAndTime (Datetime)
Price (real)
Quantity (int)
+ rapide en insertion et en requete (si symbol unique)
- analyse compliqué en fonction des besoin

solution 4: une table par date
SymbolId (int)
Price (real)
Quantity (int)
+ rapide en insertion et en requete (si date unique)
- analyse compliqué en fonction des besoin

[elsuket]

Bonjour,

Pour un index columnstore, l'ordre des colonnes n'a aucune importance, puisque chaque colonne est stockée et considérée individuellement. C'est normal puisque ce type d'index n'a pas de clé.
Seul un ré-ordonnancement a lieu au niveau du stockage et de façon interne pour optimiser la compression.

@++

[ash.ice.loky]

Ok, ce qui explique que lors de la creation de l'index columnstore cluster il ne me propose pas le choix des colonnes.
Dans ce cas sur une table en columnstore cluster, les index non cluster non aucun interet?

[Christophe LAPORTE]

Yes Sir

S'il y a un CCI sur une table, tu ne peux pas créer de NCI, ni de NCCI sur cette même table.
Combinaisons possibles :
- row store + NCCI (+ NCI)
- Hekaton (+ InMem index) + NCCI

[SQLpro]

De toute façon, la modélisation doit être "indépendante" de la façon de stocker et d'indexer pour être efficace.

Revoyez vous types de la sorte

ID BIGINT IDENTITY PRIMARY KEY
SymbolId (smallint)
DateAndTime (Datetime2(0))
Price (decimal(26, 12))
Quantity (int) --> voir smallint

Après, alim, créez un index columnstore sur toutes les données non clef !

A +

[ash.ice.loky]

Voici à quoi ressemble actuellement la table pour les quotes:
SymbolId (smallint)
DateAndTime (Datetime2(3)) -- millisecond
BidPrice (real) -- pourquoi mettre un decimal ?
BidSize (int)
BidExchangeId (tinyint)
AskPrice (real)
AskSize (int)
AskExchangeId (tinyint)
Condition (tinyint)

La table à été créer avec un Clustered Columnstore Index
Elle possède 750 millions de lignes.
Demander les enregistrement pour un Symbole au hasard d'une journée prend environ 2 minutes (150,000 rows) et il me propose de créer des index couvrant (DateAndTime ,SymbolId) (tous les autres champs)

Je vais essayer de faire une version telle que vous la proposez soit

ID IDENTITY PRIMARY KEY
SymbolId (smallint)
DateAndTime (Datetime2(3)) -- millisecond
BidPrice (real) -- pourquoi mettre un decimal ?
BidSize (int)
BidExchangeId (tinyint)
AskPrice (real)
AskSize (int)
AskExchangeId (tinyint)
Condition (tinyint)

et de mettre un index Columnstore non cluster incluant toutes les colonnes sauf ID

pensez vous utile de mettre un indice non cluster (DateAndTime ,SymbolId) (tous les autres champs) sachant que la sélection entre 2 dates pour un symbole (ou plusieurs) risque d'être la majeur partie des requêtes ?

[SQLpro]

pensez vous utile de mettre un indice non cluster (DateAndTime ,SymbolId) (tous les autres champs) sachant que la sélection entre 2 dates pour un symbole (ou plusieurs) risque d'être la majeur partie des requêtes ?

Sans connaître la distribution statistique des données, difficile à dire !

faites des tests.

Enfin, partitionnez vos données sur la date ne serait pas du luxe.

A +

[ash.ice.loky]

Dans ce cas une question bete:
la différence entre
- un index cluster en colonne et un index en ligne couvrant avec la date
versus
- un index cluster en ligne sur ID (bigint) et un index en ligne couvrant avec la date et un index non cluister en colonne ?

[elsuket]

Comme vous l'indiquait SQLPro, il vous faut tester les différences.
Nous ne connaissons pas la distribution des valeurs dans vos colonnes, et d'ailleurs celle-ci change avec le temps.
Revoyez donc le plan de requête et la sortie de SET STATISTICS IO.

@++