Hi,
I am trying to design a sql2005 Database with 4 tables of 35 million
rows. we need to update fields in one of the table by joining with
other three. Also we need to delete the roughly 2 million rows daily
from these tables as new data is added. Please suggest if breaking all
these tables into different databases is better or having them all in
one single database is better?. Also the joining keys are varchar
fields. So any suggestions on indexing?
thanks
KrisPartitioning is your friend.
If your daily data sets are in different partitions, the drop can be
metadata-only. That is very fast. Essentially, you are truncating the
partition. To make this work, you have to have indexes aligned with the
partitioning function. Read all about partitioning in BOL.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<krishnasingaraju@.gmail.com> wrote in message
news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> Hi,
> I am trying to design a sql2005 Database with 4 tables of 35 million
> rows. we need to update fields in one of the table by joining with
> other three. Also we need to delete the roughly 2 million rows daily
> from these tables as new data is added. Please suggest if breaking all
> these tables into different databases is better or having them all in
> one single database is better?. Also the joining keys are varchar
> fields. So any suggestions on indexing?
> thanks
> Kris
>|||> Partitioning is your friend.
Yes, very well said (I like it). Also reference DPV's (Distributed Partition
Views).
In a DPV you create n databases and link them together with a view. While
I've heard of multiple partitions on one server and that t performed well, a
DPV can be split up onto multiple nodes of an active/active cluster. There
is one important point, which will be in BOL, make sure that the data is
arranged so that at least 80% of the data you need comes from one
partition/server, or performance could actually be worse.
Jay
> If your daily data sets are in different partitions, the drop can be
> metadata-only. That is very fast. Essentially, you are truncating the
> partition. To make this work, you have to have indexes aligned with the
> partitioning function. Read all about partitioning in BOL.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> <krishnasingaraju@.gmail.com> wrote in message
> news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
>> Hi,
>> I am trying to design a sql2005 Database with 4 tables of 35 million
>> rows. we need to update fields in one of the table by joining with
>> other three. Also we need to delete the roughly 2 million rows daily
>> from these tables as new data is added. Please suggest if breaking all
>> these tables into different databases is better or having them all in
>> one single database is better?. Also the joining keys are varchar
>> fields. So any suggestions on indexing?
>> thanks
>> Kris
>|||Given the size info, I'm not sure you really want to even consider DPV.
Linchi
"Jay" wrote:
> > Partitioning is your friend.
> Yes, very well said (I like it). Also reference DPV's (Distributed Partition
> Views).
> In a DPV you create n databases and link them together with a view. While
> I've heard of multiple partitions on one server and that t performed well, a
> DPV can be split up onto multiple nodes of an active/active cluster. There
> is one important point, which will be in BOL, make sure that the data is
> arranged so that at least 80% of the data you need comes from one
> partition/server, or performance could actually be worse.
> Jay
> > If your daily data sets are in different partitions, the drop can be
> > metadata-only. That is very fast. Essentially, you are truncating the
> > partition. To make this work, you have to have indexes aligned with the
> > partitioning function. Read all about partitioning in BOL.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> > <krishnasingaraju@.gmail.com> wrote in message
> > news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> >> Hi,
> >>
> >> I am trying to design a sql2005 Database with 4 tables of 35 million
> >> rows. we need to update fields in one of the table by joining with
> >> other three. Also we need to delete the roughly 2 million rows daily
> >> from these tables as new data is added. Please suggest if breaking all
> >> these tables into different databases is better or having them all in
> >> one single database is better?. Also the joining keys are varchar
> >> fields. So any suggestions on indexing?
> >>
> >> thanks
> >> Kris
> >>
> >
>
>
Showing posts with label joining. Show all posts
Showing posts with label joining. Show all posts
Monday, March 12, 2012
Wednesday, March 7, 2012
Need a little help on joining two tables
I have a bit of an issue that I can not seem to figure out and was hoping to get some feedback/advice from you all.
First a little background. I have two databases and I am adding a new table too one of them. However I need to join the two databases but by columns and the columns I want to use to join them will use different data types and values.
Example database 1 column 1 will be groups.group.id and database 2 column 1 will be users.group.id. However in database 2 (users) the group_id will contain different data.
Database 1 group.id will contain a single integer and database 2 group.id I want to have it contain multiple integers seperated by a comma.
Example code:
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and groups.group.id ? users.group.id
The "?" is where I am having trouble. Does anyone know of a way to join two databases by columns using different data types?
Thanks in advance for any input.
Tdo you mean you want
PSEUDO CODE
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and groups.group.id IN users.group.id|||Originally posted by fattyacid
do you mean you want
PSEUDO CODE
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and groups.group.id IN users.group.id
I thought about the IN statement but I do not think that will work. The data in the groups.group.id column will be a single integer say the number 3. Where the data in the user.group.id column could be multiple intergers say 1, 3, 5, 10, etc...
does that make sense?
Thanks
T|||Here, read this...I've add it to my favortites
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string|||you can try patindex or charindex
so something like
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and patindex('%' + groups.group.id + ',%', users.group.id) <> 0
but it seems like the best way is to not have a list of integers in the user database. You can't seperate the list of integers into seperate records?|||Originally posted by fattyacid
you can try patindex or charindex
so something like
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and patindex('%' + groups.group.id + ',%', users.group.id) <> 0
but it seems like the best way is to not have a list of integers in the user database. You can't seperate the list of integers into seperate records?
Ok thanks Brett and fatty I will see what I can do.
T|||I highly recommend Jeffs udf...
First a little background. I have two databases and I am adding a new table too one of them. However I need to join the two databases but by columns and the columns I want to use to join them will use different data types and values.
Example database 1 column 1 will be groups.group.id and database 2 column 1 will be users.group.id. However in database 2 (users) the group_id will contain different data.
Database 1 group.id will contain a single integer and database 2 group.id I want to have it contain multiple integers seperated by a comma.
Example code:
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and groups.group.id ? users.group.id
The "?" is where I am having trouble. Does anyone know of a way to join two databases by columns using different data types?
Thanks in advance for any input.
Tdo you mean you want
PSEUDO CODE
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and groups.group.id IN users.group.id|||Originally posted by fattyacid
do you mean you want
PSEUDO CODE
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and groups.group.id IN users.group.id
I thought about the IN statement but I do not think that will work. The data in the groups.group.id column will be a single integer say the number 3. Where the data in the user.group.id column could be multiple intergers say 1, 3, 5, 10, etc...
does that make sense?
Thanks
T|||Here, read this...I've add it to my favortites
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string|||you can try patindex or charindex
so something like
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and patindex('%' + groups.group.id + ',%', users.group.id) <> 0
but it seems like the best way is to not have a list of integers in the user database. You can't seperate the list of integers into seperate records?|||Originally posted by fattyacid
you can try patindex or charindex
so something like
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and patindex('%' + groups.group.id + ',%', users.group.id) <> 0
but it seems like the best way is to not have a list of integers in the user database. You can't seperate the list of integers into seperate records?
Ok thanks Brett and fatty I will see what I can do.
T|||I highly recommend Jeffs udf...
Subscribe to:
Posts (Atom)