Home > Archive > SQL Server Programming > June 2005 > Too much to join
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Hi,
I'm very sure this is a classic performance/design problem that every
experienced db designer knows about. Basically in an application with a
feature that requires to frenquently join 3-4 tables together to return a
list of something useful, it will run into performance problem including
occasional deadlocks.
For example, to retrieve a list of manufactors (name) that a person have
actually bought for the last 3 years will force a very big nested join
select statement.
Table1: Person(personId, name, contact info, address)
Table2: PurchasedItemInThePast (personId,itemId, purchaseTime)
Table3: Item(itemId, price, manufactor)
Table4: Manufactor( manufactorId, manufactorName)
How would you - experienced db designers handle it? Is there anything that
can be done to improve perf (indexing is already known to me)? Need to
redesign it somehow?
Thank you very much in advance,
| |
| Anith Sen 2005-06-09, 8:59 pm |
| >> How would you - experienced db designers handle it? Is there anything[color=darkred]
This is a broad question as with any performance related question, but just
to ask, have you considered using an indexed view for this issue?
--
Anith
| |
| Jens Süßmeyer 2005-06-09, 8:59 pm |
| Thats just ok, I don´t know if the Productsbuyedinthepast are something like
the an archive table, because thats one thing I wouldn´t do unless you are
forced to do that because of the large amount of data to store. (Then I
would prefer storing it in horizontal partitioned tables. But If you are not
sure you can use the Northwind database to compare your design (seems like
you are serving the same purpose) Other databases and schemas or also
available at http://www.databaseanswers.org/data_models/index.htm.
--
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
"Zeng" <Zeng5000@hotmail.com> schrieb im Newsbeitrag
news:uOq7l6TbFHA.3204@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I'm very sure this is a classic performance/design problem that every
> experienced db designer knows about. Basically in an application with a
> feature that requires to frenquently join 3-4 tables together to return a
> list of something useful, it will run into performance problem including
> occasional deadlocks.
>
> For example, to retrieve a list of manufactors (name) that a person have
> actually bought for the last 3 years will force a very big nested join
> select statement.
>
> Table1: Person(personId, name, contact info, address)
> Table2: PurchasedItemInThePast (personId,itemId, purchaseTime)
> Table3: Item(itemId, price, manufactor)
> Table4: Manufactor( manufactorId, manufactorName)
>
> How would you - experienced db designers handle it? Is there anything
> that
> can be done to improve perf (indexing is already known to me)? Need to
> redesign it somehow?
>
> Thank you very much in advance,
>
>
| |
|
| From Books Online, indexed view won't work well for me because some involved
tables are updated very frequently.
"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:uFQqpNUbFHA.2444@TK2MSFTNGP15.phx.gbl...
Need[color=darkred]
>
> This is a broad question as with any performance related question, but
just
> to ask, have you considered using an indexed view for this issue?
>
> --
> Anith
>
>
| |
|
| That was only an example I used to describe the join problem I'm dealing
with. Would you be able to explain how horizontal partioned tables work and
how they can be used to soften the reading load? Thanks!
"Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e9fSYSUbFHA.1040@TK2MSFTNGP10.phx.gbl...
> Thats just ok, I don´t know if the Productsbuyedinthepast are something
like
> the an archive table, because thats one thing I wouldn´t do unless you are
> forced to do that because of the large amount of data to store. (Then I
> would prefer storing it in horizontal partitioned tables. But If you are
not
> sure you can use the Northwind database to compare your design (seems like
> you are serving the same purpose) Other databases and schemas or also
> available at http://www.databaseanswers.org/data_models/index.htm.
> --
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
> "Zeng" <Zeng5000@hotmail.com> schrieb im Newsbeitrag
> news:uOq7l6TbFHA.3204@TK2MSFTNGP12.phx.gbl...
a[color=darkred]
>
>
| |
| Jens Süßmeyer 2005-06-09, 8:59 pm |
| Far too much explaining you from scratch... Kimberley wrote an article about
that on MSDN:
http://msdn.microsoft.com/sql/defau...k5partition.asp
Hope you can get through that, its very interesting.
--
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
"Zeng" <Zeng5000@hotmail.com> schrieb im Newsbeitrag
news:%23lQglXUbFHA.3524@tk2msftngp13.phx.gbl...
> That was only an example I used to describe the join problem I'm dealing
> with. Would you be able to explain how horizontal partioned tables work
> and
> how they can be used to soften the reading load? Thanks!
>
> "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:e9fSYSUbFHA.1040@TK2MSFTNGP10.phx.gbl...
> like
> not
> a
>
>
| |
| Brian Selzer 2005-06-10, 4:03 pm |
| For reporting queries, you might consider using a READ UNCOMMITTED isolation
level. Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before your
query, or if you need finer granularity, use WITH(NOLOCK) after each table
name in the FROM clause. This should eliminate the deadlocks, because
WITH(NOLOCK) means exactly that: NO LOCKS! It would also speed things up a
bit because the locks don't have to be applied.
Use a clustered index on your primary key columns.
"Zeng" wrote:
> Hi,
>
> I'm very sure this is a classic performance/design problem that every
> experienced db designer knows about. Basically in an application with a
> feature that requires to frenquently join 3-4 tables together to return a
> list of something useful, it will run into performance problem including
> occasional deadlocks.
>
> For example, to retrieve a list of manufactors (name) that a person have
> actually bought for the last 3 years will force a very big nested join
> select statement.
>
> Table1: Person(personId, name, contact info, address)
> Table2: PurchasedItemInThePast (personId,itemId, purchaseTime)
> Table3: Item(itemId, price, manufactor)
> Table4: Manufactor( manufactorId, manufactorName)
>
> How would you - experienced db designers handle it? Is there anything that
> can be done to improve perf (indexing is already known to me)? Need to
> redesign it somehow?
>
> Thank you very much in advance,
>
>
>
|
|
|
|
|