Optimisation de plan de requête

Version imprimable

Bonjour,

Je ne trouve pas comment optimiser la requête suivante:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SELECT TOP 50 MSGSEND.*, BILLING.PRICE, BILLING.CURRENCY, BILLING.FLOW, BILLING.REVENUESHARE, BILLING.ISACTIVE, CASE MSGSEND.IDGATEWAY WHEN 25 THEN CONVERT(VARCHAR(10), CONVERT(INT, PRICE * 100)) -- South Africa, Mira Gateway WHEN 28 THEN CURRENCY + CONVERT(VARCHAR(10), CONVERT(INT, PRICE * 100)) -- IPX CPA gateways WHEN 29 THEN CURRENCY + CONVERT(VARCHAR(10), CONVERT(INT, PRICE * 100)) -- IPX BULK gateways ELSE CURRENCY + CONVERT(VARCHAR(10), CONVERT(INT, PRICE)) END AS TARIFF FROM dbo.SMSSEND MSGSEND JOIN dbo.SMSBILLING BILLING(nolock) ON BILLING.IDBILLING = MSGSEND.IDBILLING WHERE IDHANDLESTATUS = 0 AND IDGATEWAY = 4 AND SENDDATETIME < GETDATE() ORDER BY PRIORITY DESC, SENDDATETIME;
Son plan de requête est le suivant:

http://stiffupperacdc.chez-alice.fr/smssend.JPG

La table SMSSEND comporte un index sur sa clé primaire et un autre index IDX_SMSSEND_SENDDATETIME_IDHANDLESTATUS_IDGATEWAY. Actuellement cette requête ramène évidemment 50 lignes, mais le nombre de lignes de la table qui matchent cette requête est 23, la table en contient 7326.
La table SMSBILLING ne comporte seulement un index de clé primaire sur l'IDBILLING.

Elle me fait 46 logical reads sur SMSBILLING et 110 logical reads sur SMSSEND, ce qui est beaucoup trop.

J'ai essayé plusieurs indexes, sans parvenir à obtenir mieux ...

Comment faire ? :D

03/09/2007, 15h47
SQLpro

posteez le create table intégralement sans cela difficile de vous répondre
A +

le voici:

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SMSSEND](
	[IDMESSAGE] [bigint] IDENTITY(1,1) NOT NULL,
	[TRANSID] [varchar](512) NULL,
	[GUID] [bigint] NULL,
	[MSN] [varchar](20) NOT NULL,
	[MSG] [nvarchar](1000) NOT NULL,
	[UNICODE] [int] NOT NULL,
	[SENDDATETIME] [datetime] NOT NULL,
	[LASTUPDATEDDATETIME] [datetime] NULL,
	[SNO] [nvarchar](50) NOT NULL,
	[TPOA] [varchar](11) NULL,
	[PRIORITY] [tinyint] NULL,
	[IDCUSTOMER] [int] NOT NULL,
	[IDCONTENTTYPE] [int] NULL,
	[IDCONTENTOWNER] [int] NULL,
	[IDCONTENT] [int] NULL,
	[IDCONTENTCERTIFICATE] [bigint] NULL,
	[IDPRICEGROUP] [int] NOT NULL,
	[IDOPERATOR] [int] NOT NULL,
	[IDNETWORK] [int] NOT NULL,
	[IDGATEWAY] [tinyint] NOT NULL,
	[IDPREFIX] [int] NOT NULL,
	[IDSHORTSNO] [int] NOT NULL,
	[IDSERVICE] [int] NOT NULL,
	[IDMEDIACODE] [int] NULL,
	[IDACTION] [int] NOT NULL,
	[IDBILLING] [int] NOT NULL,
	[IDHANDLESTATUS] [tinyint] NOT NULL,
	[IDDELIVERYTYPE] [tinyint] NOT NULL,
	[IDACKTYPE] [tinyint] NOT NULL,
	[OPERATORCUSTOMERID] [nvarchar](100) NULL,
	[LINKEDID] [varchar](512) NULL,
	[RETRYCOUNT] [tinyint] NOT NULL CONSTRAINT [DF_SMSSEND_RETRYCOUNT]  DEFAULT ((0)),
	[IDPARENT] [bigint] NULL,
 CONSTRAINT [PK_SMSSEND] PRIMARY KEY CLUSTERED 
(
	[IDMESSAGE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
USE []
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDACKTYPE] FOREIGN KEY([IDACKTYPE])
REFERENCES [dbo].[SMSACKTYPE] ([IDACKTYPE])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDACTION] FOREIGN KEY([IDACTION])
REFERENCES [dbo].[SMSACTION] ([IDACTION])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDBILLING] FOREIGN KEY([IDBILLING])
REFERENCES [dbo].[SMSBILLING] ([IDBILLING])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDCONTENT] FOREIGN KEY([IDCONTENT])
REFERENCES [dbo].[SMSCONTENT] ([IDCONTENT])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDCONTENTCERTIFICATE] FOREIGN KEY([IDCONTENTCERTIFICATE])
REFERENCES [dbo].[SMSCONTENTCERTIFICATE] ([IDCONTENTCERTIFICATE])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDCONTENTOWNER] FOREIGN KEY([IDCONTENTOWNER])
REFERENCES [dbo].[SMSCONTENTOWNER] ([IDCONTENTOWNER])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDCONTENTTYPE] FOREIGN KEY([IDCONTENTTYPE])
REFERENCES [dbo].[SMSCONTENTTYPE] ([IDCONTENTTYPE])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDCUSTOMER] FOREIGN KEY([IDCUSTOMER])
REFERENCES [dbo].[SMSCUSTOMER] ([IDCUSTOMER])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDDELIVERYTYPE] FOREIGN KEY([IDDELIVERYTYPE])
REFERENCES [dbo].[SMSDELIVERYTYPE] ([IDDELIVERYTYPE])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDGATEWAY] FOREIGN KEY([IDGATEWAY])
REFERENCES [dbo].[SMSGATEWAY] ([IDGATEWAY])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDHANDLESTATUS] FOREIGN KEY([IDHANDLESTATUS])
REFERENCES [dbo].[SMSHANDLESTATUS] ([IDHANDLESTATUS])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDMEDIACODE] FOREIGN KEY([IDMEDIACODE])
REFERENCES [dbo].[MEDIACODE] ([IDMEDIACODE])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDNETWORK] FOREIGN KEY([IDNETWORK])
REFERENCES [dbo].[SMSNETWORK] ([IDNETWORK])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDOPERATOR] FOREIGN KEY([IDOPERATOR])
REFERENCES [dbo].[SMSOPERATOR] ([IDOPERATOR])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDPREFIX] FOREIGN KEY([IDPREFIX])
REFERENCES [dbo].[SMSPREFIX] ([IDPREFIX])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDPRICEGROUP] FOREIGN KEY([IDPRICEGROUP])
REFERENCES [dbo].[SMSPRICEGROUP] ([IDPRICEGROUP])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDSERVICE] FOREIGN KEY([IDSERVICE])
REFERENCES [dbo].[SMSSERVICE] ([IDSERVICE])
GO
ALTER TABLE [dbo].[SMSSEND]  WITH CHECK ADD  CONSTRAINT [FK_SMSSEND_IDSHORTSNO] FOREIGN KEY([IDSHORTSNO])
REFERENCES [dbo].[SMSSHORTSNO] ([IDSHORTSNO])

03/09/2007, 18h44
SQLpro

et la table SMSBILLING ?

Essaye de faire un index couvrant avec en premières colonnes :
IDHANDLESTATUS, IDGATEWAY, SENDDATETIME.

A +

Bonjour,

Voici la définition de la table SMSBILLING, désolé pour l'oubli:

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
CREATE TABLE [dbo].[SMSBILLING](
	[IDBILLING] [int] IDENTITY(0,1) NOT NULL,
	[IDOPERATOR] [int] NOT NULL,
	[IDSHORTSNO] [int] NOT NULL,
	[PRICE] [decimal](8, 2) NOT NULL,
	[CURRENCY] [varchar](3) NOT NULL,
	[FLOW] [char](2) NOT NULL,
	[REVENUESHARE] [decimal](2, 2) NOT NULL,
	[ISACTIVE] [bit] NOT NULL CONSTRAINT [DF_SMSBILLING_ISACTIVE]  DEFAULT ((1)),
 CONSTRAINT [PK_SMSBILLING] PRIMARY KEY CLUSTERED 
(
	[IDBILLING] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
USE [TOTO]
GO
ALTER TABLE [dbo].[SMSBILLING]  WITH CHECK ADD  CONSTRAINT [FK_SMSBILLING_IDOPERATOR] FOREIGN KEY([IDOPERATOR])
REFERENCES [dbo].[SMSOPERATOR] ([IDOPERATOR])
GO
ALTER TABLE [dbo].[SMSBILLING]  WITH CHECK ADD  CONSTRAINT [FK_SMSBILLING_IDSHORTSNO] FOREIGN KEY([IDSHORTSNO])
REFERENCES [dbo].[SMSSHORTSNO] ([IDSHORTSNO])
GO
ALTER TABLE [dbo].[SMSBILLING]  WITH CHECK ADD  CONSTRAINT [CHK_SMSBILLING_FLOW] CHECK  (([FLOW]='MO' OR [FLOW]='MT'))
GO
ALTER TABLE [dbo].[SMSBILLING]  WITH CHECK ADD  CONSTRAINT [CHK_SMSBILLING_IDSHORTSNO] CHECK  ((NOT ([IDSHORTSNO]=(1420) OR [IDSHORTSNO]=(1419))))
GO
ALTER TABLE [dbo].[SMSBILLING]  WITH CHECK ADD  CONSTRAINT [CHK_SMSBILLING_PRICE] CHECK  (([PRICE]>=(0)))
GO
ALTER TABLE [dbo].[SMSBILLING]  WITH CHECK ADD  CONSTRAINT [CHK_SMSBILLING_REVENUESHARE] CHECK  (([REVENUESHARE]>=(0) AND [REVENUESHARE]<=(100)))

Je vais commencer par créer un index comme vous me l'indiquez, je vous tiens au courant :D