SQL performance

Hello,

We developped a POS application where in the reporting the user wants to see a list with for all the articles the sales grouped by month.

In the sales detail table there are 2 million detail lines.

I tried a sample query for the sales for this year grouped

SELECT TransactionDetails.DetailItem, TransactionDetails.DetailItemCol, TransactionDetails.DetailItemWs, sum(TransactionDetails.DetailAmountNet) as NET
FROM TransactionHeaders INNER JOIN TransactionDetails ON (TransactionHeaders.WsRoot = TransactionDetails.DetailHeaderWs) AND (TransactionHeaders.Colroot = TransactionDetails.DetailHeaderCol) AND (TransactionHeaders.HeaderNumber = TransactionDetails.DetailHeader)
where headerdatecreate > '01/01/2006'
group by TransactionDetails.DetailItem, TransactionDetails.DetailItemCol, TransactionDetails.DetailItemWs

And it took 1 min 30 sec. To complete it.

How would you solve this problem? The user give a date range and wants to see a list
with each article and then next to it totals by month, or by week,...

Thanks

Ralph

A column called HeaderDateCreate isn't likely to be a candidate for having its own index. If this is a critical report that you always filter on HeaderDateCreate, you should make sure you have the proper index in place.

We need this date to get a range of sales for a given date.


The user gives fe 01/01/06 -> 31/12/06 on month base

So a report appears:
JAN FEB MRT .... DEC
ART A 1 2 2 44
...
ART B 7 9 19 99

How would this be best implemented?

Ralph

Does the report take 1 min. 30 sec., or does the query take 1 min. 30 sec.?

The report you describe is not based on the query you posted, at least not without a conversion to the cross-tab format.

It is the time the qry needs in SQL Qry analyzer.

This is not the report itself. We're testing how long a query would take to execute.

And we think this is slow.

How are such problems taken on? What is the best way to get such a list?

Ralph

Try adding an index to the table, on the HeaderDateCreate column alone.

There is an index on that field.

in the header table, how many rows, and of those how many meet the date criteria?

Do you have an index on the combination of the details table columns detailheader, detailheadercol, detailheaderws

how many of the rows from the detail table will be selected as part of this criteria?

what is the primary key of the detail table?

the statement doesn't seem to be grouping by month, so how are you expecting this to be done?

Cheers
Twan

Does TransactionDetails have an index on (DetailItem, DetailItemCol, DetailItemWs)?

What is the FK on TransactionDetails that refers to the PK of TransactionHeader? If it is DetailItemWs, then the above-mentioned index should be on (DetailItemWs, DetailItem, DetailItemCol).

There are 2,5 million detail lines in the detail table.

There is an index on TransactionDetails.DetailItem, TransactionDetails.DetailItemCol, TransactionDetails.DetailItemWs

and FK :TransactionDetails.DetailHeader,TransactionDetails.DetailHeaderCol,TransactionDetails.DetailHeaderWs

What is the PK on TransactionHeader?

PK on TransactionHeader is :
TransactionHeaders.HeaderNumber, TransactionHeaders.Colroot, TransactionHeaders.WsRoot

Or would it be usefull to use a totalization table ?

The problem is that the user can ask totals by month, week, year,...

Hi ya,

so detailitem, detailitemcol, detailitemws is the primary key for the detail table?

if so then your query is returning all 2.5million rows and not really summarising anything?

Cheers
Twan

I would like to group on the detail ID's

So that I get a total by ID

The problem is here that I then need to run multiple queries.

Or how would you solve such problem

JAN FEB MRT ... DEC TOTALS
ART A 1 2 399 ... 52 454
....
ART B 67 86 19 ... 98 270

Totals 68 88 418 ... 150 724

So we've got...

ART = detailitem col
A and B = detailitemws

and the months at the top can be weeks, months or years
and the date range is user specified, so a monthly report could cover say 2 years?)

Is that right so far?

Cheers
Twan

Yez

You are correct.

If an user select fe 01/01/2005 -> 31/12/2007 on month base
JAN/05 FEB/05 .... DEC/07
ART .... ... ... ....

How are these problems normally solved?

- By running against details?

- Or by using a summary total table by article

To do this sort of data analysis I'd probably resort to a summary indexed view, table or database

the basic query for this sort of thing would be something like



SELECT
datepart( year, headerdatecreate ) as year,
datepart( month, headerdatecreate ) as month
td.DetailItemCol,
td.DetailItemWs,
sum(td.DetailAmountNet) as NET
FROMTransactionHeaders th
INNER JOIN TransactionDetails td
ON th.WsRoot = td.DetailHeaderWs
AND th.Colroot = td.DetailHeaderCol
AND th.HeaderNumber = td.DetailHeader
whereheaderdatecreate > '01/01/2006'
group by
datepart( year, headerdatecreate ),
datepart( month, headerdatecreate ),
td.DetailItemCol,
td.DetailItemWs
order by
datepart( year, headerdatecreate ),
datepart( month, headerdatecreate ),
td.DetailItemCol,
td.DetailItemWs


indexes on

TransactionDetails( DetailHeader, DetailHeaderCol, DetailHeaderWs )
TransactionHeaders( headerdatecreate, HeaderNumber, ColRoot, wsRoot )

Cheers
Twan

I changed the query to :
SELECTdatepart( year, headerdatecreate ) as year,
datepart( month, headerdatecreate ) as month,
td.DetailItem ,td.DetailItemCol,
td.DetailItemWs,
sum(td.DetailAmountNet) as NET
FROMTransactionHeaders th INNER JOIN TransactionDetails td
ON th.WsRoot = td.DetailHeaderWs
AND th.Colroot = td.DetailHeaderCol
AND th.HeaderNumber = td.DetailHeader
whereheaderdatecreate > '01/01/2006'
group by datepart( year, headerdatecreate ),
datepart( month, headerdatecreate ),
td.DetailItem,
td.DetailItemCol,
td.DetailItemWs
order by datepart( year, headerdatecreate ),
datepart( month, headerdatecreate ),
td.DetailItem,
td.DetailItemCol,
td.DetailItemWs

I added the td.DetailItem field.

I tested it and it took around 1min 20 sec results : 38000 rows.

How would you solve the week / day issue? This is really a good time.

for week and day you can use a different datepart week or day respectively. It would mean having three queries (ideally stored procs)

for day in particular it would be important to be able to have some sort of limit on the daterange, since a 5 year daily totals across all product ranges is surely not that useful anymore...

even 38000 rows still sounds like way too much to me... if this was a regaulr requirement then I'd consider having summary tables generated nightly to be the source for these to avoid having conflicts with your OLTP

Cheers
Twan

What is OLTP?

I thaught 38000 records is ok. It mattered more to have the time performing.

How would you do this nightly?

Anyway I need to thank you already for the reply you have give me, it's already of great help.

OLTP = online transaction processing, the kind of reporting you are doing here is called DSS (decision support service) normally the two cause conflicts in terms of resource usage and sensitivity to delays<br /><br />is someone going to go through 38000 records of data? That would be around three thousand product codes each with 12 months worth of data. assuming 50 lines per A4 sheet that's still 60 pages worth of figures? What would they be looking for? Can you use SQL to give them the answer, rather than given them the summarised data. It's a bit like there is a needle in a field of grass and you want to find it. In the first step you have summarised all of the grass into a bundle of straw, but it is still hard to find the needle... perhaps we can create a magnet to hover over the field which would automatically pick up the needle... bad analogy perhaps, but sounds good inside my head <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />to run it nightly you'd need to create a stored procedure which creates the summary tables and populates them with all of the data summarised by week, month, year. Then schedule this procedure to run every night using Enterprise Manager.<br /><br />Cheers<br />Twan<br />

Twan,

I discovered that jan/01/2005 is week 1 and Jan/02/2005 is week 2

Normally if an user want a sales report summarized by week from
01/01/2005 31/01/2005. the user would expect that week 1 = 01/01 -> 07/01
Week 2 = 08/01 -> 14/07,...

When using the MSSQL
Week 1 = 01/01/2005
Week 2 = 02/01/2005 -> 08/01/2005

This is since MS starts week 1 = Jan/01 and the next week starts on the next sunday.

Otherwise your solution is great.



Thanks

Ralph

Hi Ralph

can you use day of year and then div it by 7?

Cheers
Twan

Twan,

Can you give an example?

Ralph

Hi Ralph

select ( ( datepart( dy, getdate() ) - 1 ) / 7 ) + 1 as MyWeek

Cheers
Twan

It does mean that 31st Dec is week 53, and in a leap year 30th Dec is also week 53

Cheers
Twan

Twan,

How do you build this in the query you have given me a couple posts ago?

Ralph

SELECT datepart( year, headerdatecreate ) as year,
((datepart( dy, headerdatecreate )-1)/7)+1 as week,
td.DetailItem ,td.DetailItemCol,
td.DetailItemWs,
sum(td.DetailAmountNet) as NET
FROM TransactionHeaders th INNER JOIN TransactionDetails td
ON th.WsRoot = td.DetailHeaderWs
AND th.Colroot = td.DetailHeaderCol
AND th.HeaderNumber = td.DetailHeader
where headerdatecreate > '01/01/2006'
group by datepart( year, headerdatecreate ),
datepart( month, headerdatecreate ),
td.DetailItem,
td.DetailItemCol,
td.DetailItemWs
order by datepart( year, headerdatecreate ),
((datepart( dy, headerdatecreate )-1)/7)+1,
td.DetailItem,
td.DetailItemCol,
td.DetailItemWs

Twan,

SELECT datepart( year, headerdatecreate ) as year,
((datepart( dy, headerdatecreate )-1)/7)+1 as week,
td.DetailItem ,td.DetailItemCol,
td.DetailItemWs,
sum(td.DetailAmountNet) as NET
FROM TransactionHeaders th INNER JOIN TransactionDetails td
ON th.WsRoot = td.DetailHeaderWs
AND th.Colroot = td.DetailHeaderCol
AND th.HeaderNumber = td.DetailHeader
where (headerdatecreate > '01/01/2005') and (headerdatecreate < '01/10/2005' )
group by datepart( year, headerdatecreate ),
(((datepart( dy, headerdatecreate )-1)/7)+ 1) as weekd,
td.DetailItem,
td.DetailItemCol,
td.DetailItemWs
order by datepart( year, headerdatecreate ),
((datepart( dy, headerdatecreate )-1)/7)+1,
td.DetailItem,
td.DetailItemCol,
td.DetailItemWs

I tried this but got an error when trying to execute the SQL


(((datepart( dy, headerdatecreate )-1)/7)+ 1) as week I think this is the error line.

Sorry ralph, I should have done the cut and paste in all three places

SELECT datepart( year, headerdatecreate ) as year,
((datepart( dy, headerdatecreate )-1)/7)+1 as week,
td.DetailItem ,td.DetailItemCol,
td.DetailItemWs,
sum(td.DetailAmountNet) as NET
FROM TransactionHeaders th INNER JOIN TransactionDetails td
ON th.WsRoot = td.DetailHeaderWs
AND th.Colroot = td.DetailHeaderCol
AND th.HeaderNumber = td.DetailHeader
where (headerdatecreate > '01/01/2005') and (headerdatecreate < '01/10/2005' )
group by datepart( year, headerdatecreate ),
((datepart( dy, headerdatecreate )-1)/7)+ 1,
td.DetailItem,
td.DetailItemCol,
td.DetailItemWs
order by datepart( year, headerdatecreate ),
((datepart( dy, headerdatecreate )-1)/7)+1,
td.DetailItem,
td.DetailItemCol,
td.DetailItemWs

Cheers
Twan

Twan,

I posted another question in the same forum, to display same fields (sale price) in the same row.