|
22.12.2010, 12:29
#37029293
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Хотела бы создать некую поисковую систему.
CD_session - поисковые сессии
CD_session_condition- описание вопрос сессии
CD_session_object - подходимость экземпляра к сессии (зависит от количества вопросов и подходимости экземпляра к каждому из вопросов. fitness - доля подходимости от 0.0 до 1.0)
CD_session_condition_object - подходимость экземпляра к определённому вопросу сессии (fitness_condition - доля подходимости от 0.0 до 1.0)
 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.
/****** Object: Table [dbo].[CD_session] Script Date: 12/22/2010 11:23:03 ******/
CREATE TABLE [dbo].[CD_session](
[id_session] [int] IDENTITY( 1 , 1 ) NOT NULL,
CONSTRAINT [PK_CD_session] PRIMARY KEY CLUSTERED
(
[id_session] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60 ) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CD_session_condition] Script Date: 12/22/2010 11:23:03 ******/
CREATE TABLE [dbo].[CD_session_condition](
[id_session_condition] [int] IDENTITY( 1 , 1 ) NOT NULL,
[id_session] [int] NOT NULL,
CONSTRAINT [PK_CD_session_condition] PRIMARY KEY CLUSTERED
(
[id_session_condition] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60 ) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CD_session_object](
[id_session] [int] NOT NULL,
[guid_o] [uniqueidentifier] NOT NULL,
[fitness] [real] NULL,
CONSTRAINT [PK_CD_session_object] PRIMARY KEY NONCLUSTERED
(
[id_session] ASC,
[guid_o] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60 ) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CL_CD_session_object_id_session] ON [dbo].[CD_session_object]
(
[id_session] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60 ) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CD_session_object] ON [dbo].[CD_session_object]
(
[id_session] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60 ) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CD_session_object_fitness] ON [dbo].[CD_session_object]
(
[id_session] ASC,
[fitness] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60 ) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CD_session_condition_object] Script Date: 12/22/2010 11:23:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CD_session_condition_object](
[guid_o] [uniqueidentifier] NOT NULL,
[id_session_condition] [int] NOT NULL,
[fitness_condition] [real] NOT NULL,
CONSTRAINT [PK_CD_session_condition_object] PRIMARY KEY CLUSTERED
(
[guid_o] ASC,
[id_session_condition] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60 ) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [CD_session_condition_object__K2_K1] ON [dbo].[CD_session_condition_object]
(
[id_session_condition] ASC,
[guid_o] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_CD_session_CD_dir_object] Script Date: 12/22/2010 11:23:03 ******/
ALTER TABLE [dbo].[CD_session] WITH CHECK ADD CONSTRAINT [FK_CD_session_CD_dir_object] FOREIGN KEY([id_object])
REFERENCES [dbo].[CD_dir_object] ([id_object])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CD_session] CHECK CONSTRAINT [FK_CD_session_CD_dir_object]
GO
/****** Object: ForeignKey [FK_CD_session_condition_CD_session_condition] Script Date: 12/22/2010 11:23:03 ******/
ALTER TABLE [dbo].[CD_session_condition] WITH CHECK ADD CONSTRAINT [FK_CD_session_condition_CD_session_condition] FOREIGN KEY([id_session])
REFERENCES [dbo].[CD_session] ([id_session])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CD_session_condition] CHECK CONSTRAINT [FK_CD_session_condition_CD_session_condition]
GO
/****** Object: ForeignKey [FK_CD_session_condition_object_CD_session_condition] Script Date: 12/22/2010 11:23:03 ******/
ALTER TABLE [dbo].[CD_session_condition_object] WITH CHECK ADD CONSTRAINT [FK_CD_session_condition_object_CD_session_condition] FOREIGN KEY([id_session_condition])
REFERENCES [dbo].[CD_session_condition] ([id_session_condition])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CD_session_condition_object] CHECK CONSTRAINT [FK_CD_session_condition_object_CD_session_condition]
GO
/****** Object: ForeignKey [FK_CD_session_object_CD_session] Script Date: 12/22/2010 11:23:03 ******/
ALTER TABLE [dbo].[CD_session_object] WITH CHECK ADD CONSTRAINT [FK_CD_session_object_CD_session] FOREIGN KEY([id_session])
REFERENCES [dbo].[CD_session] ([id_session])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CD_session_object] CHECK CONSTRAINT [FK_CD_session_object_CD_session]
GO
Проблемы:
1) Очень медленно создается сессия (в CD_session_object записывается 5млн. кандидатов)
2) так же долго удаляется закрываемая сессия (приходится асинхронизировать удаление)
3) возникает блокировка создания новой сессии при удалении или создании параллельной |
|
|
