[es] - Pomoc Potrebna u vezi Query

wex-alpha
Sarajevo

Član broj: 7580
Poruke: 845
92.36.142.*

+13 Profil

Pomoc Potrebna u vezi Query - Amazon AWS-RDS

^{28.11.2012. u 19:09 - pre 138 meseci}

Pozdrav svima,

Imam veliki problem sa Full Text Search. Trenutno imam 3 razvojne okoline (Identicna DB velicine 1GB):

1. Lokalno (i3, 4GRAM, MsSQL2012)
2. Online (Dual Xeon, 3Ghz, 4GB RAM)
3. Amazon AWS (razlicite okoline)
- Najniza je Medium, a najveca ExtraLarge 8 ECU units, sto otprilike iznosi 8* 1.GHZ Xeon i 16GB RAM-a za najvecu.

Ciljna grupa mi je Amazon AWS, dakle tu treba da deployam aplikaciju. I problem sa FTS.

Izvrsenja istog querija koji gadja Store Proceduru, koja zatim gadja FTS, query vraca dva rezultata iz kataloga velicine 30MB:
1. Lokalno .5 sec

2. Online 1 sec

3. Amazon (medium 4 sec, Medium3 Sec)

Source Code Store Procedure i samog Querija:
Query koji se izvrsava (dva rezultat vraca):

Code:

exec vibe_SearchTermProducts @SearchTerm=N'fiji'

Stored Procedura:

Code:

USE [there]
GO
/****** Object:  StoredProcedure [dbo].[vibe_SearchTermProducts]    Script Date: 11/28/2012 1:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  StoredProcedure [dbo].[vibe_SearchTermProducts] ******/
ALTER PROC [dbo].[vibe_SearchTermProducts]
    @SearchTerm nvarchar(4000),
    @CategoryID int = 0,
    @SectionID int = 0,
    @GenreID int = 0,
    @VectorID int = 0,
    @ManufacturerID int = 0,
    @Limit int = 0,
    @storeID int = 1
AS BEGIN
    DECLARE @searchFields nvarchar(400)

    DECLARE @DisplayOutOfStockProducts int, @HideProductsWithLessThanThisInventoryLevel int

    SELECT @searchFields = (SELECT dbo.udf_GetAppConfig('Vibe.TRIB.SearchBy', @storeID))

    SELECT @DisplayOutOfStockProducts = case dbo.udf_GetAppConfig('DisplayOutOfStockProducts', @storeID) when 'true' then 1 ELSE 0 END
    SELECT @HideProductsWithLessThanThisInventoryLevel = convert(int, dbo.udf_GetNumericAppConfig('HideProductsWithLessThanThisInventoryLevel', @storeID))

    SET @SearchTerm = rtrim(ltrim(@SearchTerm))
    SET @SearchTerm = nullif(@SearchTerm, '')

    SET @CategoryID = nullif(@CategoryID, 0)
    SET @SectionID = nullif(@SectionID, 0)
    SET @GenreID = nullif(@GenreID, 0)
    SET @VectorID = nullif(@VectorID, 0)
    SET @ManufacturerID = nullif(@ManufacturerID, 0)

    if @SearchTerm <> ''
    begin
        select @SearchTerm = dbo.GetValidSearchString(@SearchTerm)
    end

    IF @Limit <= 0
        SET @Limit = 20

    declare @allowProductFiltering bit

    select @allowProductFiltering = case when ConfigValue = 'true' then 1 else 0 end from dbo.GlobalConfig with(NOLOCK)
        where Name='AllowProductFiltering'

    IF @SearchTerm IS NOT NULL AND @SearchTerm != ''
    BEGIN

        if @allowProductFiltering = 1
        begin
            SELECT distinct top (@Limit) p.ProductID, p.Name, cast(p.Description as nvarchar(MAX)), CAST(pv.Description as nvarchar(MAX)) as VariantDescription, pv.Name as VariantName, p.SEName
            FROM dbo.Product p WITH (NOLOCK)
            LEFT JOIN dbo.ProductVariant pv WITH (NOLOCK) on pv.ProductID = p.ProductID and pv.IsDefault=1
            JOIN dbo.ProductStore ps with(NOLOCK) on p.ProductID = ps.ProductID and ps.StoreID = @storeID
            WHERE p.Deleted = 0 AND p.Published = 1
            AND @SearchTerm = ''
                OR CONTAINS((p.Name, p.Description, p.SKU), @SearchTerm)
            end
        else
        begin
        SELECT distinct top (@Limit) p.ProductID, p.Name, cast(p.Description as nvarchar(MAX)), CAST(pv.Description as nvarchar(MAX)) as VariantDescription, pv.Name as VariantName, p.SEName
        FROM dbo.Product p WITH (NOLOCK)
            LEFT JOIN dbo.ProductVariant pv WITH (NOLOCK) on pv.ProductID = p.ProductID and pv.IsDefault=1
        WHERE p.Deleted = 0 AND p.Published = 1
        AND
            (
                @SearchTerm = ''
                OR CONTAINS((p.Name, p.Description, p.SKU), @SearchTerm)
            )
        end
    END
END

Znam da je tesko bilo sta reci iz prilozenog materijala, ali mozda neka smjernica ili bilo sta.

U prilogu je trace route (MsSQL profiler) za Amazon kao i za lokalnu verziju.

_{[Ovu poruku je menjao wex-alpha dana 29.11.2012. u 10:00 GMT+1]}

[IMG]http://img233.imageshack.us/img233/5078/thumbnailte1.th.jpg[/IMG]

They say I look like daddy :D

Prikačeni fajlovi

amazon.trc - 24.8k

local.trc - 20.37k

Odgovor na temu