Mi calcul, mi table = mi-nable ?

Version imprimable

21/09/2011, 12h36
Sergejack

Mi calcul, mi table = mi-nable ?

Bonjour.

J'aimerais faire une VIEW pour y lister tous les entiers possible (de -2,147,483,648 à 2,147,483,647).
Dans la plupart des cas, les valeurs utilisées seraient les entiers strictement positifs juq'uà une certaine valeur (1000 ? 10000 ?).
Afin de tirer parti d'un index, je pensais donc faire une table indexée allant de 1 à 4096.

Donc la view est l'union des valeurs
-2,147,483,648 à 0 (valeurs calculés)
1 à 4096 (table indexée)
4097 à 2,147,483,647 (valeurs calculés)

Malheureusement si je fais par exemple sur une telle view un COUNT pour tout entier entre 10 et 20, la requête est très très longue.
Alors qu'elle pourrait se contenter d'utiliser l'index de la table, la requête fait un tas d'opération inutiles (d'un regard humain).

J'ai pourtant bien essayer d'aider SQL Server à bien comprendre l'origine des différentes plages de valeurs à grand renfort de BETWEEN avec quelques essaient même de CASE, de subqueries et j'en passe.

Malheureusement, il n'existe pas à ma connaissance de possibilité d'indiqué que le résultat d'une colonne est par exempe un entier entre 4097 à 2,147,483,647.
Je dis ça, parce que si ma view renvoie par exemple :

pour n de -2,147,483,648 à 0, 0 pour la colonne 'table'
pour n de 1 à 4096, 1 pour la colonne 'table'
pour n de 4097 à 2,147,483,647, 0 pour la colonne 'table'

Et que je fais un query pour retrouver n entre 10 et 20 et 'table' = 1, SQL server se rend bien compte que la valeur 1 n'est présente que dans le résultat en provenance de la table.

Savez vous que faire pour:
1) soit donner un "hint" à sql server sur le range d'une valeur de colonne
2) soit créer un type entier dans un range
3) soit faire comprendre autrement à SQL Server où chercher les lignes
?

Merci.
21/09/2011, 15h03
iberserk

Avez vous un check sur la table indexée stipulant que les valeurs sont comprises entres 1 et 4096?
Peut-on voir le code de la vue?

Citation:

Donc la view est l'union des valeurs
-2,147,483,648 à 0 (valeurs calculés)
1 à 4096 (table indexée)
4097 à 2,147,483,647 (valeurs calculés)

Passez peut être par trois tables/vues(mais beaucoup de lignes...) avec des CHECK pour les plages avant de faire l'union cela devrait suffire?
21/09/2011, 15h06
Sergejack

Citation:

Envoyé par iberserk

Peut-on voir le code de la vue?

Passez peut être par trois tables/vues avec des CHECK pour les plages avant de faire l'union cela devrait suffire?

Je n'ai jamais vu de CHECK sur des vues.
Quant à tout avoir en table, 4 milliards d'entiers dans une table c'est au moins 20Giga en SQL Server (Logs exclu).
21/09/2011, 15h07
iberserk

Citation:

Envoyé par Sergejack

Je n'ai jamais vu de CHECK sur des vues.
Quant à tout avoir en table, 4 milliards d'entiers dans une table c'est au moins 20Giga en SQL Server (Logs exclu).

Oui j'avais édité mon message entre-temps
21/09/2011, 15h09
iberserk

Citation:

Je n'ai jamais vu de CHECK sur des vues.

je parles bien de passer un CHECK sur la table indexée...

Après on va clairement vous demander l’intérêt d'une telle 'VUE'.

Avez vous essayé de préciser le BETWEEN dans la définition de la vue en ajoutant un prédicat sur la table indexée?

J'ai essayé plein de choses dont celle là.
Mais rien n'y a fait.

Par contre depuis j'ai réussit à mieux cerné le cas qui pose le problème.
L problème apparaît avec les nombres négatifs, si je retire tout ce qui est soustraction, SQL Server parvient à agir intelligemment.

Le problème est que je souhaite pouvoir parcourir ces nombres négatifs.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vTally]'))
DROP VIEW [dbo].[vTally]
GO
 
CREATE VIEW dbo.vTally
AS
WITH
L1 AS (SELECT 1 AS n UNION ALL SELECT 1) -- 2^1
, L2 AS (SELECT X.n FROM L1, L1 AS X) -- (2^1)^2 = 2^2
, L3 AS (SELECT X.n FROM L2, L2 AS X) -- (((2^1)^2)^2 = 2^4
, L4 AS (SELECT X.n FROM L3, L3 AS X) -- ((((2^1)^2)^2)^2 = 2^8
, L5 AS (SELECT X.n FROM L4, L4 AS X) -- (((((2^1)^2)^2)^2)^2 = 2^16
, L6 AS (SELECT X.n FROM L5, L5 AS X) -- ((((((2^1)^2)^2)^2)^2)^2 = 2^32
, L7 AS (SELECT ROW_NUMBER() OVER (ORDER BY n) - 2147483647 AS n FROM L6)
SELECT
	T.n
FROM Utils.dbo.tTally AS T
WHERE T.n BETWEEN 1 AND 65536
 
UNION ALL
 
SELECT
	L.n
FROM L7 AS L
WHERE L.n BETWEEN - 2147483647 AND 0
 
UNION ALL
 
SELECT
	L.n
FROM L7 AS L
WHERE L.n BETWEEN 65537 AND 2147483647

21/09/2011, 16h46
iberserk

Soit...
Déjà vous pouvez enlever les UNION ALL vu que les prédicats font le DISTINCT...

Donc si vous interrogez cette vue avec un predicat between 10 and 20 par exemple il ne se contente pas d'utiliser la table?
21/09/2011, 17h27
Sergejack

Citation:

Envoyé par iberserk

Soit...
Déjà vous pouvez enlever les UNION ALL vu que les prédicats font le DISTINCT...

J'avais essayé les UNION tout court mais ça n'avait rien changé.
Je suis donc revenu à l'UNION ALL moins lourd.

Citation:

Envoyé par iberserk

Donc si vous interrogez cette vue avec un predicat between 10 and 20 par exemple il ne se contente pas d'utiliser la table?

Non.
Vous pouvez faire des tests si vous avez des doutes (et si vous êtes curieux).

Code:

1
2
3
SELECT TOP 10 n
FROM dbo.vTally
WHERE n BETWEEN 10 AND 20

Cela est instantané...

22/09/2011, 12h30
Sergejack
Citation:
Envoyé par iberserk

Code:

1 2 3 SELECT TOP 10 n FROM dbo.vTally WHERE n BETWEEN 10 AND 20

Cela est instantané...
J'avais pas pensé au TOP.
Le test devient très rapide.

Mais vous savez, l'objectif premier derrière la création d'une view est rarement d'en faire des tests triviaux...
L'objectif des view est d'offrir à ses utilisateurs de l'abstraction (et donc de la facilité) pour des queries utiles à leurs métiers.

S'il faut commencer à rédiger chaque et tous les appels à ces view en sous-query pour inclure une clause TOP, on se retrouve avec tout sauf de l'abstraction.

Si vous avez d'autres idées (même ponctuées de points de suspension), n'hésitez pas.
22/09/2011, 14h06
iberserk

Pas d'idées mais une question: a quoi vous sert cette vue?
22/09/2011, 14h30
Sergejack

À itérer en toute liberté mais aussi, devant le défis, à parfaire mes connaissance d'SQL Server.
Abandonner une réflexion en se disant "y a moyen de s'en passer" est un recourt à éviter au maximum si on souhaite être bon plutôt qu'à peine capable.
22/09/2011, 14h34
iberserk

Citation:

Abandonner une réflexion en se disant "y a moyen de s'en passer" est un recourt à éviter au maximum si on souhaite être bon plutôt qu'à peine capable.

C'est louable, l'art peut également se révéler en parvenant à admettre que ce n'est pas la manière dont doit être utilisé tel ou tel outil...

Par exemple migrer en base un fichier CSV en passant par .NET en itérant sur chaque ligne plutôt que de faire ça de manière ensembliste...