20 table Join - 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)

20 table Join

Author

Topic

programmer76
Starting Member

6 Posts

Posted - 2006-06-01 : 21:56:35

Hi,

I've started working on this new project. I've a big query below that is joining around 20 table. Developer before me had written it and i've to correct it.How can i make possible improvement in this query? If i remove some tables or some AND conditions from query then i got thousands of records. I've already created good indexes on all the tables in query. There are no table scans in the execution plan. There are NO book marks. There are some Index scans in the plan but can't take care of that because there are more than 20 tables i'm trying to join here.I will really appreciate your feedback.

Thanks

SELECT DISTINCT Orders.Order_ID, Orders.Order_DateOrdered, Orders.Order_Taxes, Orders.Order_Commission,
Orders.Order_Comments, Orders.Order_OrderNumber, Orders.Order_CustomerNumber,
Orders.Order_Supplier, Orders.Order_CCUsed, Orders.Order_OrderedBy , Orders.Order_Shipping,Orders.Order_AddressInfo,Orders.Order_FullAddressInfo,GiftPurchase.GiftPurchase_Quantity, GiftPurchase.GiftPurchase_Price, GiftPurchase.GiftPurchase_Shipping, GiftPurchase.GiftPurchase_Taxes, UserProduct.UserProduct_Name, UserProduct.UserProduct_Description,UserProduct.UserProduct_ModelType, UserProduct.UserProduct_DeliveryOption, UserProduct.UserProduct_ID,
Shipping.Shipping_ShippedBy, Shipping.Shipping_DateShipped, Shipping.Shipping_ShipmentArrivalDate, Shipping.Shipping_Tracking, Shipping_TransactionFee, Shipping.Shipping_ShipmentMethod, Shipping.Shipping_Cost, Request.Request_Event, ShippingCollection_TS, ShippingCollection_ID,ShippingCollection_MerchantOrderNumber, ShippingCollection_OrderNumber, SupplierOrder_OrderNumber , SupplierOrder_ID, Store_ID,Person_ID, Person_Forename, Person_Surname, Person_Email FROM Orders, GiftPurchase, UserProduct, SupplierOrderOrders, SupplierOrder, Shipping, Request, ShippingCollection, Store, Supplier,Reqfill, Transactions, Person, Event , Purchaser , GiftSupplierFilled, AllocatedGift, AllocateGiftToSupplier WHERE GiftPurchase.GiftPurchase_Request = UserProduct.UserProduct_Request AND Request.Request_ID = UserProduct.UserProduct_Request AND Orders.Order_ID= GiftPurchase.GiftPurchase_Order
AND SupplierOrderOrders.SupplierOrderOrders_Orders = Orders.Order_ID
AND SupplierOrderOrders.SupplierOrderOrders_SupplierOrder = SupplierOrder.SupplierOrder_ID
AND SupplierOrder.SupplierOrder_Supplier = Supplier_ID
AND GiftPurchase.GiftPurchase_Shipping = Shipping.Shipping_ID AND ShippingCollection.ShippingCollection_Shipping = Shipping.Shipping_ID
AND Supplier_ID = 703
AND Supplier_Store = Store_ID
AND Request_ID = Reqfill_Request
AND Request_Event = Event_ID
AND Purchaser_Person = Person_ID
AND Purchaser_Transaction = Transaction_ID
AND Reqfill_Transaction = Transaction_ID

AND Orders.Order_ID = GiftSupplierFilled.GiftSupplierFilled_Order
AND UserProduct.UserProduct_ID=GiftSupplierFilled.GiftSupplierFilled_UserProduct
AND UserProduct.UserProduct_ID = AllocatedGift.AllocatedGift_UserProduct AND Transactions.Transaction_ID = AllocatedGift.AllocatedGift_Transaction
AND AllocatedGift.AllocatedGift_ID = AllocateGiftToSupplier.AllocateGiftToSupplier_AllocatedGift
AND GiftSupplierFilled.GiftSupplierFilled_AllocateGiftToSupplier = AllocateGiftToSupplier.AllocateGiftToSupplier_ID
AND Person_Surname like '%Takaba%'
ORDER BY ShippingCollection_ID DESC, Request_Event, SupplierOrder_ID, Orders.Order_ID, UserProduct.UserProduct_ID;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-01 : 22:07:28

Try
- rewriting the query using proper JOIN syntax ?
- prefix the tables with owner ?

SELECT ...
FROM   dbo.Orders o 
       INNER JOIN dbo.GiftPurchase g ON o.Order_ID= g.GiftPurchase_Order
       INNER JOIN dbo.UserProduct
. . .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 03:18:47

You can make a tremendous improvement for this query! As it is right now, you are selecting the cartesian product (all possible combinations) for the 18 tables you have. That would easily bring the query optimizer to it's knees. Multiply the number of records in every 18 tables you have and post the number here. I'll bet the product is some billions and more. I'll write down some things for you to do now. Remember to use fully qualified named (Order.OrderID, not just OrderID) in the future, for your own sake. It will be much easier to debug sometime in the future. If succeeding to implement the things written below, I would estimate that this query would perform at least 100 times better.

1. Rewrite for using INNER JOINs

SELECT Orders.Order_ID
...
FROM Orders
INNER JOIN GiftPurchase ON GiftPurchase.GiftPurchase_Order = Orders.Order_ID
INNER JOIN ...

I tried to do that for you, but since there are many unqualified names, it wasn't possible for me, since I can't see the table layouts.

2. Place everything in a VIEW, except

AND Supplier_ID = 703
AND Person_Surname like '%Takaba%'

Then call the view from your stored procedure as

SELECT * FROM MyView WHERE MyView.Supplier_ID = 703 AND MyView.Person_Surname LIKE '%Takaba%'

This way, the query optimizer in SQL Server doesn't have to recompile the joins every time! Also, if searching for surname "Takaba", do not use LIKE with % first! That triggers a table scan. If searching for just "Takaba", write Person_surname = 'Takaba'

3. If possible, make the view indexed.

That's my opninion.

Maux Maux
Starting Member

10 Posts

Posted - 2006-06-02 : 14:44:45

Ever so slight nitpick on rewriting


1. Rewrite for using INNER JOINs

SELECT Orders.Order_ID
...
FROM Orders O
INNER JOIN GiftPurchase GP ON GP.GiftPurchase_Order = O.Order_ID
INNER JOIN ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-02 : 15:09:44

quote:
Originally posted by Maux Maux

Ever so slight nitpick on rewriting

1. Rewrite for using INNER JOINs

SELECT Orders.Order_ID
...
FROM Orders O
INNER JOIN GiftPurchase GP ON GP.GiftPurchase_Order = O.Order_ID
INNER JOIN ...

Well, sometimes my mind is faster than my fingers

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-03 : 11:59:58

quote:
Originally posted by Peso

[size=2][font=Verdana]You can make a tremendous improvement for this query! As it is right now, you are selecting the cartesian product (all possible combinations) for the 18 tables you have. That would easily bring the query optimizer to it's knees. Multiply the number of records in every 18 tables you have and post the number here. I'll bet the product is some billions and more.

It's not a cartesian product. The tables are joined in the where clause.
Changing to ansi join syntax shouldn't make any difference to the query plan or result.

Also placing the joins in a view shouldn't make any difference.
Indexing the view would probably help performance at the expense of space.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-06-04 : 21:00:32

Well said nr.

prog 76 -
It seems to me you need to tell us what's wrong with the query. Is it slow? Are you getting different rows than what you expect? Are you simply freaked out by the fact it uses 20 tables? (Sometimes you cannont answer the question you are asking without using a lot of tables - live with it).
It looks like you've got conditions on plenty of PKs (store_id, event_id etc) so I'd be surprised if performance is too bad.
That fact you tried to randomly remove tables and conditions and have not stated what exactly is "wrong" with the query says to me you don't really understand the problem you are trying to solve. That might be a good place to start.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-05 : 17:14:46

Nothing beats a real world example.

I don't think the two syntaxes makes the query optimizer work the same way. The two types of syntaxes behave like the same, of course, but the query optimizer works in a little different way.

I have come up with a very simple test-script to prove my point with the difference of INNER JOIN (ANSI SQL-92) syntax and "Cartesian product With Where" (ANSI SQL-86) syntax.

First of all, my Members table has only 2,429 records and Models table has only 11,247 records, so the cartesian product is small; 27,318,963 records only.

I created a table named Table1 which has three fields.
1. z INT IDENTITY(1, 1)
2. cart INT (for cartesian times)
3. injo INT (for inner join times)

The script itself is very simple.

declare @s datetime, @i smallint, @dummy int, @a int, @b int, @e int

select @e = 100

while @e > 0

begin
	select @i = 100,
		@s = getdate()
	
	while @i > 0	
	SELECT		@dummy = count(*),
			@i = @i - 1
	FROM		models
	CROSS JOIN	members
	WHERE		members.memberid = models.memberid
	
	select @a = datediff(ms, @s, getdate())
	
	select @i = 100,
		@s = getdate()
	
	while @i > 0	
	SELECT		@dummy = count(*),
			@i = @i - 1
	FROM		models
	INNER JOIN	members on members.memberid = models.memberid
	
	select @b = datediff(ms, @s, getdate()) 
	
	insert table1 (cart, injo) values (@a, @b)

	select @e = @e - 1
end

to get the result, i just write

select sum(cart) 'cartesian product ms', sum(injo) 'inner join ms' from table1

On my laptop, the results are


Cartesian product ms    Inner join ms
54057                   53058

a difference of nearly 2 percent! And this is for only one join on two small sets. It is also noteable that both queries uses the same query plan!

I urge other people to run the this test on their tables, preferrably 18 joins and millions of records in each table. Please post your results here...

Here is also my complete list of times.

z           cart        injo        
----------- ----------- ----------- 
1           670         516
2           530         530
3           533         530
4           546         530
5           533         530
6           543         533
7           533         530
8           533         530
9           533         530
10          530         530
11          546         533
12          546         543
13          533         530
14          546         516
15          546         530
16          533         530
17          533         530
18          533         530
19          546         530
20          533         530
21          530         530
22          533         530
23          530         530
24          546         516
25          530         530
26          533         530
27          533         530
28          533         533
29          546         530
30          533         530
31          533         530
32          533         530
33          546         530
34          546         533
35          546         513
36          533         530
37          533         530
38          533         530
39          533         530
40          546         530
41          546         533
42          530         530
43          533         530
44          533         530
45          533         530
46          533         530
47          546         530
48          533         530
49          543         533
50          533         530
51          533         530
52          546         516
53          530         530
54          546         533
55          546         530
56          533         530
57          533         530
58          533         530
59          533         530
60          533         530
61          533         530
62          530         530
63          546         533
64          533         530
65          533         530
66          546         530
67          546         530
68          546         533
69          530         530
70          546         533
71          533         530
72          533         530
73          530         530
74          533         530
75          546         533
76          533         530
77          563         546
78          546         530
79          576         550
80          546         530
81          576         533
82          546         546
83          533         530
84          550         543
85          563         530
86          546         546
87          546         516
88          546         530
89          546         530
90          550         546
91          546         543
92          546         530
93          533         530
94          530         530
95          546         513
96          546         533
97          530         530
98          550         530
99          530         530
100         533         530

(100 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-05 : 17:54:42

quote:
Nothing beats a real world example.
...
On my laptop, the results are

Cartesian product ms Inner join ms
54057 53058

Putting the two types of queries into views, produces following times


cartesian product ms inner join ms 
-------------------- ------------- 
54093                52740

This is now over 2 percent faster but also faster than not using views, a total of 2.5 percent faster than the original posting...

Here we also can note that putting ANSI SQL-86 syntax in a view did not gain performance at all.

Conclusion:
Using ANSI SQL-92 with views is over 2.5 percent faster than using ANSI SQL-86 inline, with small sets of data. The performance with large sets of data and multiple joins, is up to everyone here to figure out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-05 : 18:02:48

quote:
Originally posted by nr

Originally posted by Peso

You can make a tremendous improvement for this query! As it is right now, you are selecting the cartesian product (all possible combinations) for the 18 tables you have. That would easily bring the query optimizer to it's knees. Multiply the number of records in every 18 tables you have and post the number here. I'll bet the product is some billions and more.

It's not a cartesian product.
Yes, it is.
The tables are joined in the where clause.
Yes, sort of.
Changing to ansi join syntax shouldn't make any difference to the query plan or result.
Well, it did made a difference in speed.

Also placing the joins in a view shouldn't make any difference.
Well, it did made a difference in speed.

Indexing the view would probably help performance at the expense of space.
Is that a problem?

I just posted my opinions, based on my experience with SQL Server, to help programmer76 out a little. I thought my posting were helpful. And with my, test-script, I proved my point.

How were you helpful?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-06-05 : 21:19:04

Hi Peso,
I notice the faster wall-clock time is lower on the second case each time. However, you say the query plans are the same. I therefore put it to you that the performance difference you see is more related to the earlier query putting the data pages in the cache than the (identically planned) execution being more efficient. Can you try:
1) Running them in the different order
2) Running the same query twice (or more) times in a row
and post the results?

Personally I don't really like measuring performance by the elapsed time. There are too many variables especially when you add multiple users to the mix. I think the key is to get an efficient execution plan - all the rest is envrionmental. I'm happy to be proven wrong though.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-06 : 05:37:59

Changing the order of ANSI SQL-86 and ANSI SQL-92 queries produced following times

cartesian product ms inner join ms 
-------------------- ------------- 
53153                51727

and this is a time difference of nearly 3 percent. But these times were made after a clean restart of SQL Server and computer, and the times posted last night was produced after a full day work. I stick with the times produced last night, since those queries were made near a real production environment, not a clean startup.

Having two ANSI SQL-92 queries in the @e-loop produced following time

104932 ms

and having two ANSI SQL-86 in th @e-loop produced following time

106409 ms

Now 1.5 percent difference. I'll say that is within the marginal error compared with earlier results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources