Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: How to enhance the performance of long query

    Hi,

    Who can tell me how to enhance the performance of long query, if the no data in the table, we can get the View, but I don't know why the server become very slow(seems crash) when all the table have table.. I know it is a long query, who can tell me how to enhance it? Here is my query. Thanks a lot:-)


    CREATE VIEW dbo.vtest
    AS
    SELECT
    pc.[EnglishProductCategoryName]
    ,Coalesce(p.[ModelName], p.[EnglishProductName]) AS [Model]
    ,c.[CustomerKey]
    ,s.[SalesTerritoryGroup] AS [Region]
    ,CASE
    WHEN Month(GetDate()) < Month(c.[BirthDate])
    THEN DateDiff(yy,c.[BirthDate],GetDate()) - 1
    WHEN Month(GetDate()) = Month(c.[BirthDate])
    AND Day(GetDate()) < Day(c.[BirthDate])
    THEN DateDiff(yy,c.[BirthDate],GetDate()) - 1
    ELSE DateDiff(yy,c.[BirthDate],GetDate())
    END AS [Age]
    ,CASE
    WHEN c.[YearlyIncome] < 40000 THEN 'Low'
    WHEN c.[YearlyIncome] > 60000 THEN 'High'
    ELSE 'Moderate'
    END AS [IncomeGroup]
    ,t.[CalendarYear]
    ,t.[FiscalYear]
    ,t.[MonthNumberOfYear] AS [Month]
    ,f.[SalesOrderNumber] AS [OrderNumber]
    ,f.SalesOrderLineNumber AS LineNumber
    ,f.OrderQuantity AS Quantity
    ,f.ExtendedAmount AS Amount
    FROM
    [dbo].[FactInternetSales] f
    INNER JOIN [dbo].[DimTime] t
    ON f.[OrderDateKey] = t.[TimeKey]
    INNER JOIN [dbo].[DimProduct] p
    ON f.[ProductKey] = p.[ProductKey]
    INNER JOIN [dbo].[DimProductSubcategory] psc
    ON p.[ProductSubcategoryKey] = psc.[ProductSubcatKey]
    INNER JOIN [dbo].[DimProductCategory] pc
    ON psc.[ProductCategoryKey] = pc.[ProductCategoryKey]
    INNER JOIN [dbo].[DimCustomer] c
    ON f.[CustomerKey] = c.[CustomerKey]
    INNER JOIN [dbo].[DimGeography] g
    ON c.[GeographyKey] = g.[GeographyKey]
    INNER JOIN [dbo].[DimSalesTerritory] s
    ON g.[SalesTerritoryKey] = s.[SalesTerritoryKey]

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    A bit more information please. How many records do the tables have? Are there indexes on them?
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    It seems you return all data in the database.
    e.g. you are not selecting a specific product for a specific period etc.

    If your sales table is 2 GB in size, it might take a while to send the 2GB+ result to your client pc provided your app/pc can cope with it.

    Othet things to look at
    indexes
    update statistics
    reorg rebuild
    showplan

    See:

    Performance and Tuning: Basics
    Chapter 13: Indexing for Performance

    Performance and Tuning: Monitoring and Analyzing
    Chapter 3: Using Statistics to Improve Performance
    Chapter 5: Using set showplan
    Chapter 7: Tuning with dbcc traceon

  4. #4
    Join Date
    Apr 2007
    Posts
    63
    There is no more than 600 records in erevy table, but I can get query result within 1 minute on another Sybase server which verion is 12.5, and my Sybase is 15.0, i don't know why I got different query result......, Is there any place need me to set, or change?


    Thanks^_^
    Winnie

  5. #5
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by winniewang
    There is no more than 600 records in erevy table, but I can get query result within 1 minute on another Sybase server which verion is 12.5, and my Sybase is 15.0, i don't know why I got different query result......, Is there any place need me to set, or change?


    Thanks^_^
    Winnie
    I donno about the result set difference. But theres a lot of differences in the optimiser in 15.0 and some really bad bugs in the early ebf version of 15.0. Your best bet is to compare both the showplans. Also, I will try to look up the exact ebf which has the problems.

  6. #6
    Join Date
    Apr 2007
    Posts
    63
    Is it possible there is not enough space in some database, I have allocated 1000M extra space for tempdb, But I don't know any other database on Sybase can effect the query result...

    Thanks^_^
    Winnie

  7. #7
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    If you did get any error like tempdb full, then you cant expect the same result set !!

  8. #8
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    No other database gets used other than tempdb for a SELECT query.

  9. #9
    Join Date
    Apr 2007
    Posts
    63
    I didn't get that tempdb is full after I set extra space for tempdb, But I don't know why the server seems was crashed when I sent this query..

    Is there any option I need to set? like set "select into" is true? or other option?

  10. #10
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Hm.. You are adding too many things into the picture. And going off the original subject to be honest. A server crashing out of a query is not normal. So, you have quite a few issues here.

    I suggest, first get to a point, where you dont have a questionable query, before thinking into performance issues.

    You have to give more details than what you have given. Error Nos, Errorlog details when you ran the query in order for the forum members to help you much better.

    I do understand its tough for someone whos starting on a new product. But I suggest reading up what pdreyer suggested.

  11. #11
    Join Date
    Apr 2007
    Posts
    63
    I see, I think my query is OK, because if all the table is empty, and then I can get the query result, that is empty. If I port data to every table, and sent query , the server seems crash. So I think it is performance issue. And I'm a new one to ues Sybase, so I have to make sure nothing other ingredient(tempdb setting) will affect my query result. The server seems crash and I have to kill it, so I think I can not get error log, right? if not, please tell me how to find it(the directory)?
    Ok, I will read up what pdreyer suggested, I think is a good way to study Sybase.

    Thanks^_^
    Winnie

  12. #12
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    The errorlog should be under $SYBASE/install. Usually it should be servername.log , unless you have given it a different name.

    Server crashing out a query is not a performance issue. When you say crash, Im assuming the server process has died. If thats the case, there will be most likely indications in the errorlog.

  13. #13
    Join Date
    Apr 2007
    Posts
    63
    here is the error log:

    00:00000:00001:2007/12/23 16:58:56.28 server Master device size: 30 megabytes, or 15360 virtual pages. (A virtual page is 2048 bytes.)
    00:00000:00025:2007/12/23 17:01:39.85 server The 2K memory pool of named cache default data cache (cache id 0, cachelet id 1) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
    00:00000:00005:2007/12/23 17:02:37.93 server The 2K memory pool of named cache default data cache (cache id 0, cachelet id 1) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
    00:00000:00015:2007/12/23 17:02:56.22 server The 2K memory pool of named cache default data cache (cache id 0, cachelet id 1) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.

    I think I need to increase the cache size..., right?

  14. #14
    Join Date
    Apr 2007
    Posts
    63
    1> sp_helpcache
    2> go
    Cache Name Config Size Run Size Overhead
    ------------------ ----------- ---------- ----------
    default data cache 0.00 Mb 8.00 Mb 0.57 Mb

    (1 row affected)


    Memory Available For Memory Configured
    Named Caches To Named Caches
    -------------------- ----------------
    8.01 Mb 0.00 Mb


    ------------------ Cache Binding Information: ------------------

    Cache Name Entity Name Type Index Name

    Status
    ---------- ----------- ---- ----------

    ------
    (return status = 0)
    1> sp_cacheconfig
    2> go
    Cache Name Status Type Config Value Run Value
    ------------------ ------ ------- ------------ ------------
    default data cache Active Default 0.00 Mb 8.00 Mb
    ------------ ------------
    Total 0.00 Mb 8.00 Mb
    ================================================== ========================
    Cache: default data cache, Status: Active, Type: Default
    Config Size: 0.00 Mb, Run Size: 8.00 Mb
    Config Replacement: strict LRU, Run Replacement: strict LRU
    Config Partition: 1, Run Partition: 1
    IO Size Wash Size Config Size Run Size APF Percent
    -------- --------- ------------ ------------ -----------
    2 Kb 1638 Kb 0.00 Mb 8.00 Mb 10
    (return status = 0)
    1>

  15. #15
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Hm.. Looks like you got a basic install of ASE server. You said there used to be another model server.

    Do this.

    grep -v DEFAULT OLD.cfg

    This will give you all the settings in your 12.5 server

    Set all those in your new 15.0 server.

    After an ASE install you need to increase a no. of parameters besides increasing tempdb like

    max memory, cache, locks, additional network memory etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •