Optimization needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optimization needed

Author

Topic

nurmaln
Starting Member

8 Posts

Posted - 2006-10-05 : 06:47:03

Hi all

I totaly new to this forum and more or less new to T-SQL so don't even know what to look for. I have the following problem:

Table1
Columns:
[Item ID] [Item Name]

Table2

[Item ID] [Property1] [Property2] [Property3] [Weight]

1000 0 0 1 xxx
1000 0 0 2 xxx
1000 0 0 3 xxx
1000 1 0 1 xxx
1000 1 2 3 xxx

What do I need:

I need a table with the following COLUMNS:
[Item ID]
[Item Name]
[Weight WHEN Property1='0' AND Property2='0' AND Property3='1']
[Weight WHEN Property1='0' AND Property2='0' AND Property3='2']
[Weight WHEN Property1='0' AND Property2='0' AND Property3='3']
and so on...

With my limited knowledge I could only put together a query:

SELECT
[Item ID]
,[Item Name]

FROM Table1

-------NOW JOIN THE WITH THE FIRST CRITERIA
LEFT JOIN

(
SELECT
[Item ID]
,[Weight]
FROM Table2
WHERE Property1='0' AND Property2='0' AND Property3='1'
)
ON Table1.[Item ID] = Table2.[Item ID]

-------NOW JOIN THE WITH THE SECOND CRITERIA
LEFT JOIN

(
SELECT
[Item ID]
,[Weight]
FROM Table2
WHERE Property1='0' AND Property2='0' AND Property3='2'
)
ON Table1.[Item ID] = Table2.[Item ID]

----AND SO ON

When I run this report on the server (and join it to a sales report) it takes 3 minutes to make a report for one day, 10 minutes for 1 month, and if I run it for a whole year I only get an error.
Every report on it's own is finished within seconds so I really don't understand why this is so difficult.

Please give me a suggestion on how to make a query and keep in mind that I'm a total beginner.

tnx

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-05 : 06:59:12

I am not clear what your requirement is, but why you need so many left joins for different conditions...Why not something simple like..

Select
t1.ItemID,
t1.ItemName,
t2.Weight
from Table 1
Left Join
(select 
Item ID]
,[Weight]
FROM Table2 
WHERE (Property1='0' AND Property2='0' AND Property3='1') or
      (Property1='0' AND Property2='0' AND Property3='2')
) as t2

Harsh Athalye
India.
"Nothing is Impossible"

nurmaln
Starting Member

8 Posts

Posted - 2006-10-05 : 07:04:45

Sorry if I was unclear.

I need 14 different weights from T2 for each Item.

T2 has has 14 different weights for each Item (14 rows with different Properties)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-05 : 07:07:09

[code]SELECT Table1.[Item ID],
Table1.[Item Name],
Table2.Property1,
Table2.Property2,
Table2.Property3,
SUM(Table2.Weight)
FROM Table1
INNER JOIN Table2 ON Table2.[Item ID] = Table1.[Item ID]
GROUP BY Table1.[Item ID],
Table1.[Item Name][/code]I think it will be better if you provide some sample output based on the sample data above.

Peter Larsson
Helsingborg, Sweden

pootle_flump

1064 Posts

Posted - 2006-10-05 : 07:37:33

Is the required output something like this:


SELECT	[Item ID], 
	[Item Name], 
	SUM([001Weight]) AS [001Weight], 
	SUM([002Weight]) AS [002Weight] --Etc
FROM	--Crosstab	
		(SELECT	Table1.[Item ID],
			Table1.[Item Name],
			CASE WHEN Property1='0' AND Property2='0' AND Property3='1' THEN Weight END AS [001Weight],
			CASE WHEN Property1='0' AND Property2='0' AND Property3='2' THEN Weight END AS [002Weight] --Etc.
		FROM	Table1
			INNER JOIN Table2 
		ON Table2.[Item ID] = Table1.[Item ID]) AS CROST
GROUP BY
	[Item ID],
	[Item Name]

nurmaln
Starting Member

8 Posts

Posted - 2006-10-05 : 07:51:45

Yes this is somthing like what I need, I just don't need any SUM, I just need the values.

OK

First Table

QUERY
SELECT
[No_]
,[Description]
FROM [Item]

RESULT
[No_] [Description]
000003 Item1
000087 Item2
000095 Item3
000096 Item4
...

Second table

QUERY
SELECT

[Item No_] --same as [Item].[No_]
,[Property1]
,[Property2]
,[Property3]
,[Weight]
FROM [Environment Disposal Spec_ L]

RESULT:
[Item No_] [Property1] [Property2] [Property3] [Weight]
000003 0 0 1 0.0280
000003 0 0 2 0.0020
000003 0 0 3 0.0300
000095 2 2 5 1.6000
000096 0 0 1 0.0110
000096 0 0 3 0.0030
000096 2 2 2 0.0110
000096 2 2 5 1.6000
...

There are up to 14 different combinations of Properties (for most items there are 4-5 in my system) so I need the folowing resultset:

[Item No_] [Description] [Weight1] [Weight2] [Weight3] ...[Weight14]

000003 Item1 0.0280 0.0020 0.0300
...

I hope it's a bit more clear now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-05 : 07:56:55

How do you know which 14 combinations of total 64 is right?

Peter Larsson
Helsingborg, Sweden

nurmaln
Starting Member

8 Posts

Posted - 2006-10-05 : 08:16:07

this isn't an issue

forget that there are 14 possible (this is only specific for my needs)

imagine there are only 4 possible (and I only need 4 weights), for instance:

0 0 1
0 0 2
0 0 3
1 0 1

pootle_flump

1064 Posts

Posted - 2006-10-05 : 08:22:23

I think it is relevent....

However - did you try my solution?

pootle_flump

1064 Posts

Posted - 2006-10-05 : 08:23:37

BTW - the SUM is to get rid of NULL ~~rows~~ values - it is not actually SUMming the weights. If you run it without the SUM and Group By clause you will see what I mean.

EDIT - ok - anyone fancy educating me on strikethrough?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-05 : 08:29:45

Pootle_flump has a working query. This is the same, somewhat simpler written

SELECT		t1.[Item ID],
		t1.[Item Name],
		SUM(CASE WHEN t2.Property1 = 0 AND t2.Property2 = 0 AND t2.Property3 = 0 THEN t2.Weight ELSE 0 END) [001Weight],
		SUM(CASE WHEN t2.Property1 = 0 AND t2.Property2 = 0 AND t2.Property3 = 1 THEN t2.Weight ELSE 0 END) [002Weight],
		SUM(CASE WHEN t2.Property1 = 0 AND t2.Property2 = 1 AND t2.Property3 = 2 THEN t2.Weight ELSE 0 END) [003Weight],
		SUM(CASE WHEN t2.Property1 = 1 AND t2.Property2 = 1 AND t2.Property3 = 3 THEN t2.Weight ELSE 0 END) [004Weight],
		SUM(CASE WHEN t2.Property1 = 1 AND t2.Property2 = 2 AND t2.Property3 = 0 THEN t2.Weight ELSE 0 END) [005Weight],
		SUM(CASE WHEN t2.Property1 = 1 AND t2.Property2 = 2 AND t2.Property3 = 1 THEN t2.Weight ELSE 0 END) [006Weight],
		SUM(CASE WHEN t2.Property1 = 2 AND t2.Property2 = 3 AND t2.Property3 = 2 THEN t2.Weight ELSE 0 END) [007Weight],
		SUM(CASE WHEN t2.Property1 = 2 AND t2.Property2 = 3 AND t2.Property3 = 3 THEN t2.Weight ELSE 0 END) [008Weight],
		SUM(CASE WHEN t2.Property1 = 2 AND t2.Property2 = 0 AND t2.Property3 = 0 THEN t2.Weight ELSE 0 END) [009Weight]
FROM		Table1 t1
INNER JOIN	Table2 t2 ON t2.[Item ID] = t1.[Item ID]
GROUP BY	t1.[Item ID],
		t1.[Item Name]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-05 : 08:30:55

quote:
Originally posted by pootle_flump

anyone fancy educating me on strikethrough?

Use [s] to start strikthrough and the same tag again, with / to stop strikethrough.

Peter Larsson
Helsingborg, Sweden

pootle_flump

1064 Posts

Posted - 2006-10-05 : 08:42:32

quote:
Originally posted by Peso

quote:
Originally posted by pootle_flump

anyone fancy educating me on strikethrough?

Use [s] to start strikthrough and the same tag again, with / to stop strikethrough.

Another Yak returned to the fold....

Thanks Peter - I didn't know you needed to use square brackets.

Yay for me - 0.1K

nurmaln
Starting Member

8 Posts

Posted - 2006-10-05 : 11:33:12

GO PESO, GO PESO

report used to take 3,5 minutes for one day, now it takes under 3 minutes for 3 months

pootle_flump
that SUM stuff - thans, never thought of using it that way

all of you are great, thanks for fast replys

n.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-05 : 16:04:27

If you use proper indexing, I believe the query will drop further in time, down to some 30 seconds something.

Peter Larsson
Helsingborg, Sweden

pootle_flump

1064 Posts

Posted - 2006-10-05 : 18:15:05

quote:
Originally posted by nurmaln

pootle_flump
that SUM stuff - thans, never thought of using it that way

It occured to me later - I could have used MAX for exactly the same effect and that might have made things easier to understand at first sight.

nurmaln
Starting Member

8 Posts

Posted - 2006-10-06 : 04:13:44

quote:
Originally posted by Peso

If you use proper indexing, I believe the query will drop further in time, down to some 30 seconds something.

Peter Larsson
Helsingborg, Sweden

I believe it would. If I knew what exactly indexing is

No there yet.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources