[SQL2K] Probleme d'optimisation

Bonjour à vous,

Etant relativement débutant dans les bases de données, il faut que j' optimise des requetes existantes pour arriver à un résultat correct...

la requete en cause:

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
SELECT DISTINCT OULINE.OBCONO AS XXCONO, 
OULINE.OBWHLO AS XXWHLO, 
OULINE.OBITNO AS XXITNO, 
OUATTR.AHAALF AS XXPACT, 
                      OULINE.OBCUNO AS XXCUNO, 
OCUSMA.OKCUNM AS XXCUNM, 
LEATTR.AHAALF AS XXECVE, 
ISNULL((SELECT     SUM((ISNULL(INLINE.OBRNQT, 0) 
 + ISNULL(INLINE.OBALQT, 0) + ISNULL(INLINE.OBPLQT, 0)))
                              FROM         OOLINE INLINE
                              WHERE     INLINE.OBCUNO = OULINE.OBCUNO 
AND INLINE.OBWHLO = OULINE.OBWHLO 
AND INLINE.OBCONO = OULINE.OBCONO 
AND INLINE.OBATNR IN
                                                        (SELECT     INATTR.AHATNR
 FROM          MOATTR INATTR, 
  MOATTR RIATTR
 WHERE      INATTR.AHATID = 'CDT' 
 AND INATTR.AHAALF = OUATTR.AHAALF 
 AND INATTR.AHATNR = RIATTR.AHATNR 
 AND RIATTR.AHATID = 'IND' 
 AND RIATTR.AHAALF = LEATTR.AHAALF) 
AND INLINE.OBITNO = OULINE.OBITNO 
AND INLINE.OBDSDT < (CONVERT(int, CONVERT(varchar(30), GETDATE(), 112)))), 0) AS Q12
FROM         dbo.OOLINE OULINE 
INNER JOIN dbo.MOATTR OUATTR ON OULINE.OBATNR = OUATTR.AHATNR INNER JOIN dbo.MOATTR LEATTR ON OUATTR.AHATNR = LEATTR.AHATNR AND OUATTR.AHCONO = LEATTR.AHCONO 
INNER JOIN dbo.OCUSMA OCUSMA ON OULINE.OBCUNO = OCUSMA.OKCUNO AND OULINE.OBCONO = OCUSMA.OKCONO
WHERE     (LEATTR.AHATID = 'IND') 
AND (OUATTR.AHATID = 'CDT') 
AND (OULINE.OBDSDT <= CONVERT(int, CONVERT(varchar(30), GETDATE(), 112)) + 35) 
AND (SUBSTRING(OULINE.OBORST, 1, 1) < '6') 
AND (SUBSTRING(OULINE.OBORST, 1, 1) >= '2')

Donc pour les infos utiles:

Tables
_ooline : environ 400 000 enregistrements
_moattr : environ 9 000 000 enregistrements

Nombre d'enregistrements retournés : 6900

Pour la table OOLINE:

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
34
35
36
37
38
39
40
41
42
CREATE TABLE [OOLINE] (
    [OBCONO] [smallint] NOT NULL ,
    [OBDIVI] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OBORNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OBPONR] [smallint] NOT NULL ,
    [OBPOSX] [tinyint] NOT NULL ,
    [OBORST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OBFACI] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OBWHLO] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OBITNO] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OBORQT] [decimal](15, 6) NOT NULL ,
    [OBRNQT] [decimal](15, 6) NOT NULL ,
    [OBALQT] [decimal](15, 6) NOT NULL ,
    [OBPLQT] [decimal](15, 6) NOT NULL ,
    [OBDLQT] [decimal](15, 6) NOT NULL ,
    [OBIVQT] [decimal](15, 6) NOT NULL ,
    [OBNEPR] [decimal](17, 6) NOT NULL ,
    [OBDWDT] [int] NOT NULL ,
    [OBCODT] [int] NOT NULL ,
    [OBDWDZ] [int] NOT NULL ,
    [OBCODZ] [int] NOT NULL ,
    [OBUCOS] [decimal](17, 6) NOT NULL ,
    [OBUCCD] [tinyint] NOT NULL ,
    [OBDLSP] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OBCUNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OBCUOR] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OBINPR] [decimal](17, 6) NOT NULL ,
    [OBDSDT] [int] NOT NULL ,
    [OBDSHM] [smallint] NOT NULL ,
    [OBPLDT] [int] NOT NULL ,
    [OBFDED] [int] NOT NULL ,
    [OBLDED] [int] NOT NULL ,
    [OBATNR] [decimal](17, 0) NOT NULL ,
    [OBRGDT] [int] NOT NULL [/quote]
Pour la table MOATTR :
[quote]CREATE TABLE [MOATTR] (
    [AHCONO] [smallint] NOT NULL ,
    [AHATNR] [decimal](17, 0) NOT NULL ,
    [AHATID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AHAALF] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

Pour les index :

_ahcono et ahaalf sur MOATTR
_obcono, obdivi, obwhlo, obalqt, obplqt sur OOLINE

Auriez vous des trucs qui vous choqueraient au niveau des performances ?

Sinon, si le post n' est pas conforme ou qu' il y a des choses qui ne vont pas, n' hésiter pas ;)

Merci d'avance