Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Повысить скорость поиска  [new]
saszay
Member

Откуда: Тольятти
Сообщений: 464
Добрый день!
есть запрос поиска товара:
DECLARE @Search  varchar(40) = '4FD-00024', @fiiID   smallint = NULL;
DECLARE @ser     varchar(50);
DECLARE @tbl TABLE (tosUID uniqueidentifier primary key);

SET @ser = REPLACE(@Search, '~', '~~');
SET @ser = REPLACE(@ser, '%', '~%');
SET @ser = '%' + @ser + '%';

SELECT tosuID, Tovars_Sup.suiID, tosuName, tosuPriceEff, tosuLink, tosuNameNew, tosuBestPrice, tosuVisible, tosuKod, tosuKol, Tovars_Sup.usiID,
tosuDate, tosuUnion As [Unions], fiName, suName, toName, pgName, PGroup_Sup.ID as pgsuID
    FROM (
        SELECT Tovars_Sup.rowguid As tosuID
            FROM Tovars_Sup WITH(noLock)
            WHERE tosuName LIKE @ser escape '~'
            OR tosuNameNew LIKE @ser escape '~'
                OR tosuKod LIKE @ser escape '~'
  OR Tovars_Sup.vendorCode LIKE @ser escape '~'
               OR tosuDesc LIKE @ser escape '~'
        UNION
        SELECT Tovars_Sup.rowguid As tosuID
            FROM Tovars WITH(noLock)
                INNER JOIN Tovars_Sup WITH(noLock, INDEX=IX_Tovars_Sup_toID) ON Tovars_Sup.toID = Tovars.ID
            WHERE Tovars.vendorCode LIKE @ser escape '~'
                AND toName LIKE @ser escape '~'
        EXCEPT
        SELECT tosUID FROM @tbl
    ) As search
        INNER JOIN Tovars_Sup  WITH(noLock) ON search.tosuID = Tovars_Sup.rowguid
        INNER JOIN Supplier    WITH(noLock) ON Tovars_Sup.suiID = Supplier.suiID
        LEFT OUTER JOIN Firm   WITH(noLock) ON Firm.fiiID = Tovars_Sup.fiiID
        LEFT OUTER JOIN Tovars WITH(noLock) ON Tovars.ID = Tovars_Sup.toID
    LEFT OUTER JOIN PGroup_Sup WITH(noLock) ON PGroup_Sup.pgsuName = Tovars_Sup.tosuPGroup AND PGroup_Sup.pgsuGroup = Tovars_Sup.tosuGroup AND PGroup_Sup.suiID = Supplier.suiID
        LEFT OUTER JOIN PGroup WITH(noLock) ON PGroup.pgiID = Tovars_Sup.pgiID
    WHERE (@fiiID IS NULL OR Tovars_Sup.fiiID = @fiiID)
    ORDER BY pgName, Tovars_Sup.toID, Unions, tosuKol DESC, tosuPriceEff;


Запрос выполняется в среднем 11-13 секунд, хотя сам поиск товара происходит за 2 сек (что внутри search).

Как можно увеличить скорость запроса?
1 июн 16, 16:27    [19246596]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
saszay,

убрать то что вокруг search
1 июн 16, 16:33    [19246629]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
saszay
Member

Откуда: Тольятти
Сообщений: 464
TaPaK,

Оно нужно
1 июн 16, 16:35    [19246640]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
saszay,

нам нужен клей план
1 июн 16, 16:36    [19246651]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
План выполнения покажите в нормальном виде (формат sqlplan) + почитайте про бинарные коллейшены (ускорит поиск по LIKE):

SET NOCOUNT ON

USE [master]
GO

IF DB_ID('db') IS NOT NULL BEGIN
    ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE db
END
GO

CREATE DATABASE db
GO
ALTER DATABASE db MODIFY FILE (NAME = N'db', SIZE = 64MB)
GO
ALTER DATABASE db MODIFY FILE (NAME = N'db_log', SIZE = 40MB)
GO

USE db
GO

CREATE TABLE data (
     [VARCHAR] VARCHAR(100) NULL
   , [NVARCHAR] NVARCHAR(100) NULL
)
GO

;WITH
    E1(N) AS (
        SELECT * FROM (
            VALUES
                (1),(1),(1),(1),(1),
                (1),(1),(1),(1),(1)
        ) t(N)
    ),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),
    E4(N) AS (SELECT 1 FROM E2 a, E2 b),
    E8(N) AS (SELECT 1 FROM E4 a, E4 b)
INSERT INTO data
SELECT v, v
FROM (
    SELECT TOP(50000) v = REPLACE(CAST(NEWID() AS VARCHAR(36)) + CAST(NEWID() AS VARCHAR(36)), '-', '')
    FROM E8
) t
GO

ALTER TABLE data
    ADD [VARCHAR_BIN] AS UPPER([VARCHAR]) COLLATE Latin1_General_100_Bin2

ALTER TABLE data
    ADD [NVARCHAR_BIN] AS UPPER([NVARCHAR]) COLLATE Latin1_General_100_BIN2

CREATE NONCLUSTERED INDEX [VARCHAR] ON data ([VARCHAR])
CREATE NONCLUSTERED INDEX [NVARCHAR] ON data ([NVARCHAR])

CREATE NONCLUSTERED INDEX [VARCHAR_BIN] ON data ([VARCHAR_BIN])
CREATE NONCLUSTERED INDEX [NVARCHAR_BIN] ON data ([NVARCHAR_BIN])
GO

DECLARE
      @V VARCHAR(10) = 'AB'
    , @NV NVARCHAR(10) = N'AB'

SET STATISTICS TIME ON

SELECT COUNT_BIG(*)
FROM data
WHERE [VARCHAR] LIKE '%' + @V + '%'

SELECT COUNT_BIG(*)
FROM data
WHERE [NVARCHAR] LIKE '%' + @NV + '%'

SELECT COUNT_BIG(*)
FROM data
WHERE [VARCHAR_BIN] LIKE '%' + UPPER(@V) + '%' COLLATE Latin1_General_100_BIN2

SELECT COUNT_BIG(*)
FROM data
WHERE [NVARCHAR_BIN] LIKE '%' + UPPER(@NV) + '%' COLLATE Latin1_General_100_BIN2

SET STATISTICS TIME OFF
1 июн 16, 16:38    [19246661]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
saszay
Member

Откуда: Тольятти
Сообщений: 464
План запроса: https://yadi.sk/d/0_DjPUcTsBHtv
1 июн 16, 16:43    [19246689]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Статистику обновите для Tovars_Sup и OPTION(RECOMPILE) в запрос добавьте
1 июн 16, 16:50    [19246719]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
saszay
Member

Откуда: Тольятти
Сообщений: 464
AlanDenton
Статистику обновите для Tovars_Sup и OPTION(RECOMPILE) в запрос добавьте

Без изменений
1 июн 16, 17:02    [19246776]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Покажите план по аналогии с первым разом. Если он не изменился, то мне уже пора на пенсию уходить...
1 июн 16, 17:04    [19246784]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
saszay,

а зачем вообще весь этот "search" если все таблицы и так есть, почему на них ограничение не накладывать?
1 июн 16, 17:04    [19246785]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
AlanDenton
Покажите план по аналогии с первым разом. Если он не изменился, то мне уже пора на пенсию уходить...

так если для FillId статистики и нет, то ничё и не изменится... Хотя план бы предлагал статистику
1 июн 16, 17:06    [19246791]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
saszay
Member

Откуда: Тольятти
Сообщений: 464
TaPaK,

Мне кажется, что запрос сложнее будет. И не все таблицы есть, например Tovars
1 июн 16, 17:06    [19246794]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
TaPaK TaPaK
так если для FillId статистики и нет, то ничё и не изменится

Если есть индекс, то и статистика по нему всегда есть. У комрада на лицо недооценка количества строк. А то что ничего не поменялось... поглядим по плану. И опять же обновление статистики как делалось... SAMPLED или FULL.
1 июн 16, 17:08    [19246802]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
saszay
TaPaK,

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

мы точно в один и тот же запрос смотрим?
1 июн 16, 17:08    [19246805]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
AlanDenton
TaPaK TaPaK
так если для FillId статистики и нет, то ничё и не изменится

Если есть индекс, то и статистика по нему всегда есть. У комрада на лицо недооценка количества строк. А то что ничего не поменялось... поглядим по плану. И опять же обновление статистики как делалось... SAMPLED или FULL.

кстати статистика ничего и не изменит, оно тянет всё ибо @FillId IS NULL по условию... если мы про один и тот же кусок говорим
1 июн 16, 17:19    [19246843]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
saszay
Member

Откуда: Тольятти
Сообщений: 464
TaPaK
saszay
TaPaK,

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

мы точно в один и тот же запрос смотрим?


Да, в один. Не заметил, конец рабочего дня...
1 июн 16, 17:29    [19246875]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
saszay,

ну так перенесите все свои лайки :) в основной предикат и уберите serch и почувствуйте разницу :)
1 июн 16, 17:30    [19246876]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
saszay
Member

Откуда: Тольятти
Сообщений: 464
Не могу представить: как убрать seacrh и оставить все условия поиска :(
1 июн 16, 17:35    [19246900]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Идея думаю понятна...

DECLARE @Search VARCHAR(40) = '4FD-00024'
      , @fiiID SMALLINT = NULL
      , @ser VARCHAR(50)

DECLARE @tbl TABLE (tosUID UNIQUEIDENTIFIER PRIMARY KEY);

DECLARE @isFilter BIT = (SELECT TOP(1) 1 FROM @tbl)

SET @ser = REPLACE(@Search, '~', '~~');
SET @ser = REPLACE(@ser, '%', '~%');
SET @ser = '%' + @ser + '%';

SELECT s.rowguid As tosuID, s.suiID, tosuName, tosuPriceEff, tosuLink, tosuNameNew, tosuBestPrice, tosuVisible, tosuKod, tosuKol, s.usiID,
    tosuDate, tosuUnion As [Unions], fiName, suName, toName, pgName, PGroup_Sup.ID as pgsuID
FROM dbo.Tovars_Sup s
JOIN dbo.Supplier ON s.suiID = Supplier.suiID
LEFT JOIN dbo.Firm ON Firm.fiiID = s.fiiID
LEFT JOIN dbo.PGroup_Sup ON PGroup_Sup.pgsuName = s.tosuPGroup AND PGroup_Sup.pgsuGroup = s.tosuGroup AND PGroup_Sup.suiID = Supplier.suiID
LEFT JOIN dbo.PGroup ON PGroup.pgiID = s.pgiID
LEFT JOIN dbo.Tovars t ON s.toID = t.ID
WHERE (
            s.tosuName LIKE @ser ESCAPE '~'
        OR s.tosuNameNew LIKE @ser ESCAPE '~'
        OR s.tosuKod LIKE @ser ESCAPE '~'
        OR s.vendorCode LIKE @ser ESCAPE '~'
        OR s.tosuDesc LIKE @ser ESCAPE '~'
        OR (
                t.vendorCode LIKE @ser escape '~'
            AND
                t.toName LIKE @ser escape '~'
        )
    )
    AND (
            @isFilter = 1
        OR
            s.rowguid NOT IN (SELECT tosUID FROM @tbl)
    )
    AND (
            @fiiID IS NULL
        OR
            s.fiiID = @fiiID
    )
ORDER BY pgName, s.toID, Unions, tosuKol DESC, tosuPriceEff
OPTION(RECOMPILE)
1 июн 16, 17:36    [19246908]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
@isFilter = 1 исправить на @isFilter IS NULL
1 июн 16, 17:37    [19246911]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
saszay
Member

Откуда: Тольятти
Сообщений: 464
Маленько неправильная логика: я по Tovars ищу без связки toID = Tovars.ID. В Вашем запросе могут быть совсем другие результаты...
1 июн 16, 17:47    [19246961]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
saszay,

я прям повис...
1 июн 16, 17:50    [19246976]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
saszay
Маленько неправильная логика: я по Tovars ищу без связки toID = Tovars.ID. В Вашем запросе могут быть совсем другие результаты...

это вы так LEFT OUTER JOIN обзываете?
1 июн 16, 17:54    [19246999]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
aleks2
Guest
Если делать просто и правильно

DECLARE @Search  varchar(40) = '4FD-00024', @fiiID   smallint = NULL;
DECLARE @ser     varchar(50);
DECLARE @tbl TABLE (tosUID uniqueidentifier primary key);

SET @ser = REPLACE(@Search, '~', '~~');
SET @ser = REPLACE(@ser, '%', '~%');
SET @ser = '%' + @ser + '%';

declare @t table(tosuID ?тип? primary key );

insert @t
        SELECT Tovars_Sup.rowguid As tosuID
            FROM Tovars_Sup WITH(noLock)
            WHERE ( tosuName LIKE @ser escape '~'
            OR tosuNameNew LIKE @ser escape '~'
                OR tosuKod LIKE @ser escape '~'
                OR Tovars_Sup.vendorCode LIKE @ser escape '~'
                OR tosuDesc LIKE @ser escape '~' )
                AND (@fiiID IS NULL OR Tovars_Sup.fiiID = @fiiID)
        UNION
        SELECT Tovars_Sup.rowguid As tosuID
            FROM Tovars WITH(noLock)
                INNER JOIN Tovars_Sup WITH(noLock, INDEX=IX_Tovars_Sup_toID) ON Tovars_Sup.toID = Tovars.ID
            WHERE Tovars.vendorCode LIKE @ser escape '~'
                AND toName LIKE @ser escape '~'
                AND (@fiiID IS NULL OR Tovars_Sup.fiiID = @fiiID)
        EXCEPT
        SELECT tosUID FROM @tbl;

SELECT tosuID, Tovars_Sup.suiID, tosuName, tosuPriceEff, tosuLink, tosuNameNew, tosuBestPrice, tosuVisible, tosuKod, tosuKol, Tovars_Sup.usiID,
tosuDate, tosuUnion As [Unions], fiName, suName, toName, pgName, PGroup_Sup.ID as pgsuID
    FROM @t As search
        INNER JOIN Tovars_Sup  WITH(noLock) ON search.tosuID = Tovars_Sup.rowguid
        INNER JOIN Supplier    WITH(noLock) ON Tovars_Sup.suiID = Supplier.suiID
        LEFT OUTER JOIN Firm   WITH(noLock) ON Firm.fiiID = Tovars_Sup.fiiID
        LEFT OUTER JOIN Tovars WITH(noLock) ON Tovars.ID = Tovars_Sup.toID
    LEFT OUTER JOIN PGroup_Sup WITH(noLock) ON PGroup_Sup.pgsuName = Tovars_Sup.tosuPGroup AND PGroup_Sup.pgsuGroup = Tovars_Sup.tosuGroup AND PGroup_Sup.suiID = Supplier.suiID
        LEFT OUTER JOIN PGroup WITH(noLock) ON PGroup.pgiID = Tovars_Sup.pgiID
        ORDER BY pgName, Tovars_Sup.toID, Unions, tosuKol DESC, tosuPriceEff;
2 июн 16, 05:46    [19248194]     Ответить | Цитировать Сообщить модератору
 Re: Повысить скорость поиска  [new]
saszay
Member

Откуда: Тольятти
Сообщений: 464
aleks2,

Так тоже пробовал - скорость низкая. Переделал на временную таблицу - скорость возросла в 4 раза!!!
Вот тебе и таблица в памяти...
2 июн 16, 10:06    [19248594]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить