[winniewang]

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]

[Martijnvs]

A bit more information please. How many records do the tables have? Are there indexes on them?

[pdreyer]

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

[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

[trvishi]

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.

[winniewang]

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

[trvishi]

If you did get any error like tempdb full, then you cant expect the same result set !!

[trvishi]

No other database gets used other than tempdb for a SELECT query.

[winniewang]

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?

[trvishi]

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.

[winniewang]

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

[trvishi]

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.

[winniewang]

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?

[winniewang]

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>

[trvishi]

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.