Discussion :

[elsuket]

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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:



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 ?

[SQLpro]

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

[elsuket]

le voici:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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])

[SQLpro]

et la table SMSBILLING ?

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

A +

[elsuket]

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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