Добрый день!

Делаю веб-сервис на C#.

Данный запрос в консоли MS SQL работает:

SELECT SUM(UsrCost)
FROM UsrTouristOffer, UsrTours
WHERE UsrTouristOffer.UsrCode = '0001'
AND UsrTouristOffer.Id = UsrTours.UsrToursDetailId
AND UsrTours.UsrTourStateId = '916A6514-987E-4240-9E6A-46EB5DA1D21A';

Подскажите как сделать запрос из веб-сервиса в базу данных?

Нравится

7 комментариев
Лучший ответ

Pavel Litvinovich,

тут есть пример

    var select = new Select(UserConnection)
            .Column("Contact", "Id").As("ContactId")
            .Column("Contact", "Name").As("ContactName")
            .Column("Account", "Id").As("AccountId")
            .Column("Account", "Name").As("AccountName")
        .From("Contact")
        .Join(JoinType.Inner, "Account")
            .On("Contact", "Id").IsEqual("Account", "PrimaryContactId")
         as Select;

 

Данный запрос с использованием класса Select или EntitySchemaQuery. подробнее и с примерами тут

Алексей Следь,

Это SQL запрос. Мне надо получить результат, а данные которые мне необходимы находятся в двух разных таблицах.

Представьте запрос в другом виде и используйте класс Select или EntitySchemaQuery

SELECT SUM(UsrCost)
FROM UsrTouristOffer uo
Inner join UsrTours ut on uo.Id = ut.UsrToursDetailId
WHERE ou.UsrCode = '0001'
AND ut.UsrTourStateId = '916A6514-987E-4240-9E6A-46EB5DA1D21A';

или вариант CustomQuery

Алексей Следь,

Спасибо! Пробую.

Алексей Следь,

Подскажите, не могу нигде найти пример.

Необходимо запрос, который нормально работает в MS SQL, переписать для Исходного кода на C#:

SELECT SUM(UsrCost)
FROM UsrTours
INNER JOIN UsrTouristOffer ON UsrTours.UsrToursInDetailId = UsrTouristOffer.Id
WHERE UsrTouristOffer.UsrCode = 'AAA'
AND UsrTourStateId = '123456789';.

 

(Чтобы получилось на подобии такого запроса:

Select selectChec = new Select(UserConnection)
                .Column(Func.Count("Id"))
                .From("UsrTouristOffer")
                .Where("UsrCode").IsEqual(Column.Parameter(tourCode)) as Select;
            resultChec = selectChec.ExecuteScalar<decimal>();

)

Pavel Litvinovich,

тут есть пример

    var select = new Select(UserConnection)
            .Column("Contact", "Id").As("ContactId")
            .Column("Contact", "Name").As("ContactName")
            .Column("Account", "Id").As("AccountId")
            .Column("Account", "Name").As("AccountName")
        .From("Contact")
        .Join(JoinType.Inner, "Account")
            .On("Contact", "Id").IsEqual("Account", "PrimaryContactId")
         as Select;

 

Алексей Следь,

Спасибо огромное!

Показать все комментарии

Здравствуйте! Подскажите пожалуйста, а есть ли возможность на стороне MS SQL получить название колонки, которая является отображаемым значением в таблице?

Нравится

1 комментарий
Лучший ответ

Насколько мне известно - нет, так как отображаемое значение таблицы - это понятие объекта EntitySchema в crm.

Насколько мне известно - нет, так как отображаемое значение таблицы - это понятие объекта EntitySchema в crm.

Показать все комментарии

Коллеги, здравствуйте!

Посоветуйте оптимальный вариант интеграции (двусторонний обмен) BPMonline onsite со сторонней СУБД MS SQL / Oracle?

Если правильно понимаю, варианты:
1. BPM-oData / промежуточный шлюз / СУБД
2. BPM-СУБД / СУБД

Очень важно определить "подводные камни" (работа по процессам BPM, отказоустойчивость)

Нравится

3 комментария

В данном случае возможны оба варианта, но, на мой взгляд, более приемлемым является вариант через Odata, так как в варианте с СУБД-СУБД могут сильно отличаться модели данных. Более подробную информацию о возможностях интеграции платформы bpm`online через Odata можете узнать на нашем ресурсе http://academy.terrasoft.ru/documents/?/docs/technic/SDK/7.6.0/webtoc.h…

"Адасюк Валерий Викторович" написал:на нашем ресурсе

Валерий, спасибо, только не открывается страничка (открывается общее содержание)

Дмитрий, путь перехода к нужному разделу следующий
SDK bpm`online>Рекомендуемые средства интеграции>OData

Показать все комментарии

Здравствуйте. Возникла необходимость в процессе объекта добавить код удаления связанных записей для события.

Нужно реализовать такую последовательность:

delete from Reminding where SubjectId in(select id from Activity where UsrProcessId in(select id from UsrProcess where Id = myId))
delete from Activity where UsrProcessId in(select id from UsrProcess where Id = myId)
delete from UsrProcessFile where UsrProcessId = (select id from UsrProcess where Id = myId)
delete from UsrProcess where Id = myId

Позволяет ли система делать вложенные запросы? Как сделать это наиболее эффективно?
Спасибо.

Нравится

5 комментариев

А через CustomQuery не пробовали выполнить?

"Власов Михаил Викторович" написал:

А через CustomQuery не пробовали выполнить?

Насколько я понимаю, CustomQuery работает только для Terrasoft 3.x, нет?

нет в BPMOnline 7.x так же можно составлять CustomQuery запросы

вот пример использования:

string sqlText = "SELECT a.\"Name\"" AS \""AccountName\"""" +

[quote="Власов Михаил Викторович"]

нет в BPMOnline 7.x так же можно составлять CustomQuery запросы

вот пример использования:

string sqlText = "SELECT a.\"Name\"" AS \""AccountName\"""" +

Акмаль, нужно выполнить запрос:

customQuery.Execute();

:smile:

Показать все комментарии

Начну с предыстории.
Есть в нашей компании такая роль/должность - помощник руководителя. Это человек, который выполняет огромные объемы работы, работая в системе Террасофт 3.Х. Периодически помощнику назначаются задачи, сам помощник создает контрагентов, сделки.
Руководством была поставлена задача по контролю помощника, а именно "чтобы каждый день на почту в 16:00 приходило письмо, в котором будут таблицы с задачами, контрагентами и сделками, которые висят на помощнике (кроме задач в работе)". То есть те вопросы, который помощник не распределил по менеджерам или не решил.

Перейдем к технической реализации, это гораздо интереснее :smile:
Так как на реализацию задач по внутреннему проекту по определению не выделяется много времени, то я решил действовать быстро, надежно и наверняка (пусть и несколько некрасиво с точки зрения многоуровневой архитектуры), а именно работать с HTML таблицами и отправкой почты прямо из кода SQL. И да, это можно сделать.
Итак, для настройки возможности отправки email из sql-кода нам надо:

  1. Создать профиль и учетку для отправки писем для авторизации на сервере почты. Делается это в разделе "Management\Database Mail":
  2. Включить возможность отправки email скриптом:
    sp_CONFIGURE 'show advanced', 1
    GO
    RECONFIGURE
    GO
    sp_CONFIGURE 'Database Mail XPs', 1
    GO
    RECONFIGURE
    GO

После такой настройки мы уже можем отправлять email кодом вроде этого:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Svistunov',
    @recipients = 'svistunov@samarasoft.ru;galanin@samarasoft.ru;lazareva@samarasoft.ru',
    @body = @tableHTML,
    @body_format = 'HTML',
    @subject = 'Задачи Лазаревой';

Остается только сформировать переменную @tableHTML на основе SQL-запросов к БД Террасофт. Я сделал это так (для краткости в примере только задачи, контрагенты и сделки формируются аналогично):

DECLARE @tableTasks nvarchar(max);
SET @tableTasks =
    N'

Задачи

'
+
    N'' +
    N'' +
    N''+
    CAST (
                (SELECT td = ISNULL(t.Title,' '),'',
                        td = ISNULL(a.Name,' '),'',
                        td = ISNULL(o.Title,' '),''
                        FROM tbl_Task t
                        LEFTJOIN tbl_Account a ON(t.AccountID=a.ID)
                        LEFTJOIN tbl_Opportunity o ON(t.OpportunityID=o.ID)
                        LEFTJOIN tbl_TaskStatus s ON(t.StatusID=s.ID)
                        WHERE t.OwnerID IN
                        (SELECT c.ID FROM tbl_Contact c WHERE c.Name LIKE'%Лазарева%')
                        AND s.STATUS='В работе'
                FOR XML PATH('tr'), TYPE
                )
                AS NVARCHAR(MAX)
        )+
    N'
ЗадачаКонтрагентСделка
'
;

--@tableAccounts
--@tableOpportunities

DECLARE @tableHTML nvarchar(max);
SET @tableHTML =
    N'

Задачи, Контрагенты и Сделки, где ответственный - Лазарева

'
+
    ISNULL(@tableTasks, '

Задач нет

'
) +
    ISNULL(@tableAccounts, '

Контрагентов нет

'
) +
    ISNULL(@tableOpportunities, '

Сделок нет

'
) +
    N'

Информация сформирована автоматически

'
;

Собственно это все. Остается только создать job, в нем всего один step, а в нем весь необходимый код. Прописываете в job'е расписание и каждый день можете лицезреть в почте вот такое письмо:

Нравится

Поделиться

1 комментарий

Очень полезный материал!

Спасибо!

Показать все комментарии

У меня есть CustomQuery с текстом:

declare @TableName varchar(250)

declare tc cursor FOR
SELECT
        s1.[Code] AS [Code]
FROM
        [tbl_Service] AS s1
WHERE s1.[ServiceTypeCode] = 'Table'
AND s1.Code IN (:IncludedTablesString)
ORDER BY 1 ASC

open tc

while (1=1)
begin
  fetch next FROM tc INTO @TableName
  IF @@fetch_status = -1 break
  IF @@fetch_status = -2 continue

print 'Start process ' + @TableName
...
...
...
print @TableName + ' was processed'

end
close tc
deallocate tc

Мне нужно в параметр :IncludedTablesString подставить набор строк - перечень имен таблиц, например

declare tc cursor FOR
SELECT
        s1.[Code] AS [Code]
FROM
        [tbl_Service] AS s1
WHERE s1.[ServiceTypeCode] = 'Table'
AND s1.Code IN ('tbl_Opportunity', 'tbl_Incident')
ORDER BY 1 ASC

список таблиц - входящий параметр переменной длины (от 0 до много))

Долго мучался, в конце концов решил парсить "прямо там":

declare @TableName varchar(250)

-- для парсинга IncludedTablesString start  
declare @IncludedTablesStringTable TABLE (Code nvarchar(4000))
declare @input_str nvarchar(4000) = :IncludedTablesString
declare @delimeter nvarchar(1) = ','
declare @pos int = charindex(@delimeter,@input_str)
declare @IncludedTablesStringCode nvarchar(4000)

while (@pos != 0)
begin
    SET @IncludedTablesStringCode = SUBSTRING(@input_str, 1, @pos-1)
    INSERT INTO @TABLE (Code) VALUES(@IncludedTablesStringCode)
    SET @input_str = SUBSTRING(@input_str, @pos+1, LEN(@input_str))
    SET @pos = CHARINDEX(@delimeter,@input_str)
    print @pos
end
-- для парсинга IncludedTablesString end

declare tc cursor FOR
SELECT
        s1.[Code] AS [Code]
FROM
        [tbl_Service] AS s1
WHERE s1.[ServiceTypeCode] = 'Table'
AND s1.Code IN (SELECT Code FROM @IncludedTablesStringTable)
ORDER BY 1 ASC

строка должна быть вида('значение,значение2,значение3,' без пробелов - а то тоже попадут и с запятой (или другим разделителем) на конце.

Есть у кого-нибудь другие варианты, кроме запуска запроса в цикле для каждого значения отдельно?

Нравится

Поделиться

3 комментария

Дмитрий, ваш пост стал 10 000 на нашем community. В честь этого хотим наградить Вас праздничным сертификатом :).
certificate

:lol:
почти 23 килобайта безудержной радости!
:lol:

Показать все комментарии

В Террасофт реализована вполне удобная система раздачи прав. По крайней мере, если разобраться :wink:

Но она позволяет настраивать только права на будущие записи. Те, которые уже созданы, приходится обрабатывать поштучно (в 3.4.1 можно уже постранично :biggrin:).
Что же делать бедным администраторам или CRM-координаторам, когда надо, например, ввести новую группу пользователей или переопределить права для старой - ведь это должно касаться, как новых, так и уже созданных записей.

В моей практике подобные ситуации встречались уже не раз. В конце концов, я устал мастерить костыли и сделал себе несколько скриптов для решения подобных задач.

Делюсь с вами.
Считаю, что если Вы читаете это, значит Вы уже знаете, что такое tbl_AccountGroupRight, sq_Service, ds_ItemRight, Dataset.IsEOF, declare cursor и т.д. Хотя это вовсе необязательно, чтобы использовать скрипты - просто мне лень их полностью комментировать :redface:

1. С чего я начал - скорее для истории, чем для нужд населения
2. проходной вариант
3. Уже что-то полезное (Скрипт JS)
4. Самое вкусное для терпеливых

Нравится

Поделиться

7 комментариев

1. С чего я начал - скорее для истории, чем для нужд населения :smile:

Добавление прав на продажи_контакты_контрагенты для пользователя\группы

CREATE TABLE #tbl_OpportunityRight(
	[ID] [uniqueidentifier] NULL,
	[RecordID] [uniqueidentifier] NULL,
	[AdminUnitID] [uniqueidentifier] NULL,
	[CanRead] [int] NULL,
	[CanWrite] [int] NULL,
	[CanDelete] [int] NULL,
	[CanChangeAccess] [int] NULL)
go 
 
insert into #tbl_OpportunityRight (RecordID) (select ID from tbl_Opportunity where ID in (select OpportunityID from tbl_OpportunityInGroup where GroupID = 'B66CAAD9-AF6B-4F12-B88C-1E3453F591C4'))
 
go
 
update #tbl_OpportunityRight set ID = NEWID(), AdminUnitID = '687D0624-5B5E-4F19-961E-D9F03A96939A', CanRead = '1', CanWrite = '1', CanDelete = '0', CanChangeAccess = '0';
 
insert into tbl_OpportunityRight select * from #tbl_OpportunityRight

Выставить прав для определенного пользователя\группы в конкретной таблице

update tbl_OpportunityRight set CanDelete = '0', CanWrite = '0', CanChangeAccess = '0'
	where ID in (select ID from tbl_OpportunityRight where AdminUnitID = 'D268BFB9-8118-4E3B-9F06-1D703D25C23E' and CanWrite = '1') 

_service.rar

2. В контексте решения одной задачи и после долгих мучений была создана процедура:
особого внимания ей уделять не стоит - это проходной вариант

В конкретной таблице (параметр) для конкретной записи (параметр) выставляет права, как будто она была создана от имени конкретного пользователя (параметр) входящего в конкретную группу (не параметр)

USE [Our_backup]
GO
/****** Object:  StoredProcedure [dbo].[tsp_UpdateRightsByDefaults]    Script Date: 07/25/2013 16:57:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[tsp_UpdateRightsByDefaults] 
	@ARightTableName sysname,
	@Owner_ID uniqueidentifier,
	@Record_ID uniqueidentifier,
	@ADBSchema sysname = 'dbo'
with execute as 'fkeys'
AS
BEGIN
	SET NOCOUNT ON;
  DECLARE @ServiceTableID uniqueidentifier
  DECLARE @AdminUnitID uniqueidentifier
  DECLARE @RecordID uniqueidentifier
  DECLARE @TableRightsName varchar(250)
  DECLARE @DefaultGroupID uniqueidentifier
  set @DefaultGroupID = (select ID from [tbl_AdminUnit] where Name = 'продажники') -- меняем имя или пишем конкретный ИД
 
  SET @ServiceTableID = (SELECT [ID] 
  FROM [dbo].[tbl_Service] 
	   WHERE [Code] = (replace(@ARightTableName, 'Right', ''))
    AND [ServiceTypeCode] = N'Table')
 
  SET @AdminUnitID = (SELECT ID from [dbo].[tbl_AdminUnit] where UserContactID = @Owner_ID)
  SET @TableRightsName = '[' + @ADBSchema + '].[' + @ARightTableName +']'
  SET @RecordID = @Record_ID
 
 exec(' DELETE' +@TableRightsName + ' where RecordID = ''' + @RecordID + ''' 
 
  INSERT INTO ' +@TableRightsName + ' (
    [ID]
    ,[RecordID]
    ,[AdminUnitID]
    ,[CanRead]
    ,[CanWrite]
    ,[CanDelete]
    ,[CanChangeAccess])
  SELECT
    newid()
    ,''' + @RecordID + '''
    ,''' + @AdminUnitID + '''
    ,1
    ,1
    ,0
    ,0
 
  INSERT INTO ' + @TableRightsName + ' (
    [ID]
    ,[RecordID]
    ,[AdminUnitID]
    ,[CanRead]
    ,[CanWrite]
    ,[CanDelete]
    ,[CanChangeAccess])
  SELECT
    newid()
    ,''' + @RecordID + '''
    ,[D].[SubjectAdminUnitID]
    ,[D].[CanRead]
    ,[D].[CanWrite]
    ,[D].[CanDelete]
    ,[D].[CanChangeAccess]
  FROM (
    SELECT
      [D].[SubjectAdminUnitID]
      ,MAX([D].[CanRead]) AS [CanRead]
      ,MAX([D].[CanWrite]) AS [CanWrite]
      ,MAX([D].[CanDelete]) AS [CanDelete]
      ,MAX([D].[CanChangeAccess]) AS [CanChangeAccess]
    FROM [dbo].[tbl_TableDefaultRight] AS [D]
    WHERE ([D].[TableServiceID] = ''' + @ServiceTableID + ''')
    AND ([D].[AdminUnitID] = ''' + @DefaultGroupID + ''' 
		 )
  GROUP BY [D].[SubjectAdminUnitID])
	AS [D]
 ')
	SET NOCOUNT OFF;
END

tsp_updaterightsbydefaults.rar

PS. Не забываем - на процедуры надо давать права на исполнения для роли public

3. Уже что-то полезное
Скрипт (JS для клиента - не SQL!!)

Добавляет во все записи все таблиц прав права для конкретного пользователя\группы. Либо редактирует, если уже есть такие права.
Опустил из скрипта таблицы прав на записи групп (например tbl_AccountGroupRight) - т.к. это было не надо, и там немного запутанно - не стал тратить время

ВАЖНО:
* создавался на версии 3.4.1
* для sq_Service надо добавить фильтр IsRightsTable:

//см приложение

scr_recreaterigths.rar

4. Самое вкусное для терпеливых :biggrin:
основано на скриптах от техподдержки, которые (в связи со спецификой :wink:) у меня работали неправильно или вообще не.
делал на 3.4.1 XRM

Раздает, используя механизм аналогичный базовому из триггеров для прав, права по-умолчанию для ВСЕХ записей ВСЕХ таблиц, для которых есть таблица прав. Таблицы берутся по принципу:

SELECT
	s1.[Code] AS [Code]
FROM
	[tbl_Service] AS s1
WHERE s1.[ServiceTypeCode] = 'Table'
and s1.Code <> 'tbl_TableField'
and exists( 
  select * from tbl_Service as s2
  where s2.Code = s1.Code + 'Right'
)

В двух вариантах:
1. как будто запись создал тот, кто ее создал :lol: (CreatedByID), если есть такой юзер, иначе подставляется из переменной @DefaultGroupID - не забудьте поставить свое
2. как будто запись создал ответственный (OwnerID), если есть такое поле в таблице и такой юзер, иначе - как в п.1

есть такой юзер означает, что заведен (и не удален, как было в моем случае) пользователь с таким контактом и он активен (активность необязательна - смотрите сами, надо ли):

	set @UserIsActive = (SELECT COUNT (*) FROM tbl_AdminUnit WHERE UserContactID = @OwnerID and UserIsEnabled = 1) -- UserIsEnabled = 1 опционально
	if (@UserIsActive != 0)
		set @AdminUnitID = (SELECT ID FROM tbl_AdminUnit WHERE UserContactID = @OwnerID)
	else set @AdminUnitID = @DefaultGroupID

prava_po-umolchaniyu_po_otvetstvennomu.rar
prava_po-umolchaniyu_po_sozdatelyu.rar

осторожнее с правами на записи, созданные Supervisor'ом (такие, как например, корневые группы типа Все контрагенты) - создайте для него права по-умолчанию, чтобы не было проблем

я бы даже советовал вообще исключить группы записей из этого процесса

актуальная версия сервисов
извините, нет времени описывать
defaultrigths.rar

Показать все комментарии

Надо было мне сделать фильтр для задач, чтобы выбрать те, которые начинаются через N дней.
Наверное, это можно сделать средствами построителя запросов в админке, но мне как-то сразу показалось, что проще сделать CustomSQL Filter.
Но... я понял, что не понимаю как это сделать...
Озадачился, надо сказать, не на шутку: полез на msdn, sql.ru, забился в гугл... Слава богу вовремя остановился :confused:

((getdate() - tbl_Task.StartDate) :StartDateDiff)
-- параметр - разница в днях (целое или дробное)

Нравится

Поделиться

0 комментариев
Показать все комментарии

Нужна помощь советом в какую сторону нужно смотреть.

Реализовал импорт прайс-листа по продуктам указанного производителя. В начале импорта указывается производитель и выбирается Excel-файл прайс-листа этого производителя. В процессе импорта добавляются/обновляются продукты. Считывается строка из Excel-файла, проверяется наличие этого продукта в БД по артикулу и производителю, дальше в зависимости от наличия продукта выполняется либо добавление (если нет), либо обновление (если есть) продукта. Также, во время импорта происходит проверка на дубли по артикулу (коду) продукта, в конце выводиться отчет.

Попробовал дефрагментировать индексы по этому совету. С большим файлом Excel (22411 строк) импорт занял: до - 45 минут, после - 33 минуты.

Задача: максимально ускорить процесс импорта.

Я доработал функционал, чтобы получить статистику сколько сумарно времени занимает выполнение того или иного участка кода/функции, а также какой процент приходиться на этот участок кода/функции от общего времени импорта.

В результате, узкими местами оказались функции GetOffering (10 минут, 29.4 %), CheckHasDuplicate (11.5 минут, 33.6 %), которые выполняют запрос на выборку (Select query) по продукту:
[[fn]GetOffering] TotalDuration - 600573 ms, NumberOfCalls - 22409, MinDuration - 15 ms, MaxDuration - 328 ms, AverageDuration - 26.8 ms, Portion - 29.4 %
[[fn]CheckHasDuplicate] TotalDuration - 684564 ms, NumberOfCalls - 22409, MinDuration - 15 ms, MaxDuration - 328 ms, AverageDuration - 30.55 ms, Portion - 33.6 %

Portion - это процент от общего времени выполнения импорта прайса.

Вот эти запросы:

функции GetOffering

SELECT
        [tbl_Offering].[ID] AS [ID],
        [tbl_ProductCode].[Code] AS [ProductCode]
FROM
        [dbo].[tbl_Offering] AS [tbl_Offering]
LEFT OUTER JOIN
        [dbo].[tbl_ProductCode] AS [tbl_ProductCode] ON [tbl_ProductCode].[ID] = [tbl_Offering].[ProductCodeID]
WHERE([tbl_Offering].[Article] = N'20237-179' AND
        [tbl_Offering].[VendorID] = '{AFF31DDE-20C5-4207-AD54-275962A4D7F8}')

Execution plan
execution plan

функции CheckHasDuplicate

SELECT
        [tbl_Offering].[ID] AS [ID],
        [tbl_Offering].[OriginalName] AS [OriginalName],
        [tbl_Offering].[VendorID] AS [VendorID],
        [Vendor].[Name] AS [VendorName],
        [tbl_ProductCode].[Code] AS [ProductCode],
        [tbl_Offering].[Article] AS [Article]
FROM
        [dbo].[tbl_Offering] AS [tbl_Offering]
LEFT OUTER JOIN
        [dbo].[tbl_Account] AS [Vendor] ON [Vendor].[ID] = [tbl_Offering].[VendorID]
LEFT OUTER JOIN
        [dbo].[tbl_ProductCode] AS [tbl_ProductCode] ON [tbl_ProductCode].[ID] = [tbl_Offering].[ProductCodeID]
WHERE([tbl_Offering].[Article] = N'PCM-3730I-AE')

Execution plan
execution plan

Большие картинки в прикрепленном архиве execution_plan.rar.

Смотрю в сторону индексов. В таблице tbl_Offering 152773 записи. С индексами раньше особо не работал. Возможно, стоит добавить некластерный индекс по полю Article, по которому происходит фильтрация продуктов? Может, еще что-то посоветуете? Нужно ли запускать какую-то регулярную обработку индексов, чтобы их оптимизировать?

Вот SQL-код создания таблицы tbl_Offering:

/****** Object:  Table [dbo].[tbl_Offering]    Script Date: 05/14/2013 15:05:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Offering](
        [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [PDFOfferingID]  DEFAULT (newid()),
        [CreatedOn] [datetime] NULL,
        [CreatedByID] [uniqueidentifier] NULL,
        [ModifiedOn] [datetime] NULL,
        [ModifiedByID] [uniqueidentifier] NULL,
        [Name] [nvarchar](250) NOT NULL,
        [Code] [nvarchar](250) NULL,
        [CurrencyID] [uniqueidentifier] NULL,
        [BasicPrice] [decimal](15, 2) NULL,
        [DefaultUnitID] [uniqueidentifier] NULL,
        [VendorID] [uniqueidentifier] NULL,
        [SupplierID] [uniqueidentifier] NULL,
        [OwnerID] [uniqueidentifier] NULL,
        [OfferingTypeID] [uniqueidentifier] NULL,
        [URL] [nvarchar](250) NULL,
        [Description] [image] NULL,
        [Weight] [decimal](15, 4) NULL,
        [Volume] [decimal](15, 4) NULL,
        [SerialNumber] [nvarchar](50) NULL,
        [OriginalCountryID] [uniqueidentifier] NULL,
        [UnitDivision] [decimal](15, 4) NULL,
        [IsUsed] [int] NULL,
        [ProductCodeID] [uniqueidentifier] NULL,
        [GuaranteePeriod] [decimal](15, 4) NULL,
        [QuantityInPackage] [decimal](15, 4) NULL,
        [MinRest] [decimal](15, 4) NULL,
        [StatusID] [uniqueidentifier] NULL,
        [OriginalName] [nvarchar](250) NULL,
        [DeliveryTerm] [decimal](15, 4) NULL,
        [Price1FOB] [decimal](15, 4) NULL,
        [Price1DDP] [decimal](15, 4) NULL,
        [VendorCurrencyID] [uniqueidentifier] NULL,
        [SupplierCurrencyID] [uniqueidentifier] NULL,
        [VendorSupplierConvRateID] [nvarchar](250) NULL,
        [DeliveryBasisID] [nvarchar](250) NULL,
        [SupplierMarkup] [decimal](15, 4) NULL,
        [SupplierExtraCostsForUkraine] [decimal](15, 4) NULL,
        [EndUserPriceFOB] [decimal](15, 4) NULL,
        [EndUserPriceDDP] [decimal](15, 4) NULL,
        [IsSpecialSupplierPrice] [int] NULL,
        [Article] [nvarchar](250) NULL,
        [SupplierConversionRateID] [nvarchar](250) NULL,
        [IsFixedCosts] [int] NULL,
        [FixedCosts] [decimal](15, 4) NULL,
        [SpecialSupplierPrice] [decimal](15, 4) NULL,
        [IsRequiredIncomeControl] [int] NULL,
        [OrderCode] [nvarchar](250) NULL,
        [Note] [nvarchar](1000) NULL,
        [IsUsedInPriceListImport] [int] NULL,
        [CategoryID] [uniqueidentifier] NULL,
        [GnomeID] [nvarchar](250) NULL,
        [IsSeparateLineMarkupInSupplie] [int] NULL,
 CONSTRAINT [POfferingID] PRIMARY KEY NONCLUSTERED
(
        [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FK13238CategoryIDtbl_Offeri1] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[tbl_OfferingCategory] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FK13238CategoryIDtbl_Offeri1]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingCurrencyID] FOREIGN KEY([CurrencyID])
REFERENCES [dbo].[tbl_Currency] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingCurrencyID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingDefaultUnitID] FOREIGN KEY([DefaultUnitID])
REFERENCES [dbo].[tbl_Unit] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingDefaultUnitID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingOfferingTypeID] FOREIGN KEY([OfferingTypeID])
REFERENCES [dbo].[tbl_OfferingType] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingOfferingTypeID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingOriginalCountryID] FOREIGN KEY([OriginalCountryID])
REFERENCES [dbo].[tbl_Country] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingOriginalCountryID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingOwnerID] FOREIGN KEY([OwnerID])
REFERENCES [dbo].[tbl_Contact] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingOwnerID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingProductCodeID] FOREIGN KEY([ProductCodeID])
REFERENCES [dbo].[tbl_ProductCode] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingProductCodeID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingStatusID2] FOREIGN KEY([StatusID])
REFERENCES [dbo].[tbl_OfferingStatus] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingStatusID2]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingSupplierCurrencyID2] FOREIGN KEY([SupplierCurrencyID])
REFERENCES [dbo].[tbl_Currency] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingSupplierCurrencyID2]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingSupplierID] FOREIGN KEY([SupplierID])
REFERENCES [dbo].[tbl_Account] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingSupplierID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingVendorCurrencyID] FOREIGN KEY([VendorCurrencyID])
REFERENCES [dbo].[tbl_Currency] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingVendorCurrencyID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingVendorID] FOREIGN KEY([VendorID])
REFERENCES [dbo].[tbl_Account] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingVendorID]

Нравится

4 комментария

Здравствуйте.

Да, Вам точно нужен индекс, т.к. и в GetOffering и в CheckHasDuplicate есть строки вида:
WHERE([tbl_Offering].[Article] = N'20237-179'

Именно здесь мы и получаем "затык"... Осталось определиться нужно ли создавать кластерный индекс?

На самом деле все просто: если у таблицы есть кластерный индекс, то записи, находящиеся в этой таблице, физически (в дисковом файле) упорядочиваются в соответствии с условием индексирования). Именно поэтому кластерный индекс может быть на таблицу только один. Некластерный индекс хранит ссылки на местоположение данных в дисковом файле.

Со всеми вытекающими.

Вытекающие:
1. Скорость доступа к строкам, при указании условия where, совпадающиего с условием индексирования для кластерного индекса, будет выше, чем при поиске записей по простому индексу (не надо высислять абсолютное положение данных из относительного).

2. Если кластерный индекс содержит большое количество часто обновляемых полей, то это отрицательно скажется на производительности, так как приведет к постоянному физическому перупорядочиванию данных.

3. Если кластерный индекс создается по IDENTITY столбцу, и из таблицы часто удаляются записи, то это приводит к сильной фрагментации таблицы.

Также, вот статья о "Идексах". Описано довольно подробно.

Спасибо, Дмитрий.
Подумав, решил попробовать создать некластерный индекс по полю Article, поскольку предполагается частая вставка новых записей (пункт 2 в Ваших вытекующих), и удаление (пункт 3). Сейчас тестирую импорт с некластерным индексом по полю Article, о результатах отпишусь.

Протестировал:
1. Где основное действие - это вставка (Insert) - 15 минут.
2. Где изменение (Update) - 14 минут.

Результаты на лицо :smile:
Спасибо большое за помощь :twisted:

А начиналось все с 1 часа.

Шаги по оптимизации:
1. Вместо использования Dataset.Append(), Dataset.Edit(), Dataset.Post() переделал на использование Insert и Update Query - стало 45 минут.
2. Дефрагментировал индексы - стало 33 минуты.
3. Добавил индекс по полю Article, по которому происходит фильтрация. Индекс по полю VendorID уже был ранее добавлен автоматически, при добавлении поля в сервис таблицы. Стало - 15 минут.

Показать все комментарии


Собственно, если этой роли нет, вылетает ошибка:

Ошибка открытия конфигурации. 'Не удается открыть базу данных "TerraSoft", запрашиваемую именем входа. Не удалось выполнить вход.'

Субд:
Microsoft SQL Server Management Studio 10.50.1600.1
Клиентские средства служб Microsoft Analysis Services 10.50.1600.1
Компоненты доступа к данным (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.7601.17514
Microsoft .NET Framework 2.0.50727.5420
Операционная система 6.1.7601

Terrasoft: 3.3.1.67.
Возникло предположительно после применения старого бэкапа (можно сказать переноса БД на другой сервер).

USE master;
RESTORE DATABASE TerraSoft
FROM DISK = 'C:\TerraSoft.bak'
WITH REPLACE;
GO

Нравится

1 комментарий

Здравствуйте, Олег!

Для устранения ошибки проведите сопоставление пользователей выполнив запрос типа:

sp_change_users_login 'update_one', 'fkeys', 'fkeys'

Данный запрос необходимо выполнить для каждого пользователя Terrasoft заменив в запросе слово “fkeys” на соответственное, например для supervisor’a данный запрос примет вид

sp_change_users_login 'update_one', 'supervisor', 'supervisor'

Так-же, проверьте, включена ли смешанная авторизация на сервере:

С уважением, Шипко Илья
Специалист службы поддержки
Группа компаний Terrasoft

Показать все комментарии