Multiple joins in SQL Server

Hi I have a requirement where in i haev to convert the SQL from Oracle
to the one which will run on the SQL server.
in the Oracle Query i am doing multiple joins, between some 13 tables.
and some of these joins are inner joins and some are Left outer joins.
table1 inner joined with table 2
table2 inner join with table3
table2 inner join with table4
table2 left join with table5
table5 left jin with table6
table6 left jin with table7
table7 left jin with table8
table8 left jin with table9
Any idea how to achieve this'
Tia
naren"narendra vuradi" <nvuradi@.gmail.com> wrote in message
news:aad31742-b563-4da4-8a37-4be71ebf81c7@.e23g2000prf.googlegroups.com...
> Hi I have a requirement where in i haev to convert the SQL from Oracle
> to the one which will run on the SQL server.
> in the Oracle Query i am doing multiple joins, between some 13 tables.
> and some of these joins are inner joins and some are Left outer joins.
> table1 inner joined with table 2
> table2 inner join with table3
> table2 inner join with table4
> table2 left join with table5
> table5 left jin with table6
> table6 left jin with table7
> table7 left jin with table8
> table8 left jin with table9
> Any idea how to achieve this'
> Tia
> naren
Using standard SQL (rather than the proprietary Oracle syntax) it should
look pretty close to the way you wrote it:
SELECT ... /* columns */
FROM table1
INNER JOIN table2
ON table1.col = table2.col
INNER JOIN table3
ON table2.col = table3.col
INNER JOIN table4
ON table2.col = table4.col
LEFT OUTER JOIN table5
ON table2.col = table5.col
LEFT OUTER JOIN table6
ON table5.col = table6.col
LEFT OUTER JOIN table7
ON table6.col = table7.col
LEFT OUTER JOIN table8
ON table7.col = table8.col
LEFT OUTER JOIN table9
ON table8.col = table9.col ;
--
David Portas|||Hi
In addition to David's suggestion , I'd note you that JOIN 13 tables does
not look good in terms of database/application design as well as performance
"narendra vuradi" <nvuradi@.gmail.com> wrote in message
news:aad31742-b563-4da4-8a37-4be71ebf81c7@.e23g2000prf.googlegroups.com...
> Hi I have a requirement where in i haev to convert the SQL from Oracle
> to the one which will run on the SQL server.
> in the Oracle Query i am doing multiple joins, between some 13 tables.
> and some of these joins are inner joins and some are Left outer joins.
> table1 inner joined with table 2
> table2 inner join with table3
> table2 inner join with table4
> table2 left join with table5
> table5 left jin with table6
> table6 left jin with table7
> table7 left jin with table8
> table8 left jin with table9
> Any idea how to achieve this'
> Tia
> naren

Multiple INSERTs into a single table scaling / performance problem

Hello all ...
This one has me a bit confused. I'd really appreciate any and all
suggestions. This may be pilot error on my part...
My requirement is to have multiple programatic database applications, each
on a separate database connection insert data into a single table as fast as
possible. My hope is that I'll see some scaling of rows-per-second inserted
by adding additional processes, connections and simultaneous inserts.
I'm not able to use BCP for this as the data is coming from an application
style feed.
I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on an
AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
tends to get up around 2.9GB at times ... it is the "only" service on the
box.
Here's my table:
CREATE TABLE MyTable
(myID numeric(10,0) not null,
myChar char(8000) null,
myRowversion rowversion)
ON A_TABLE_FILEGROUP
-- No indexes, just a simple heap. I know that each row will require 8K of
-- storage ... about a page each.
My database transaction log is on a different disk/file group on a separate
controller.
I used SQL Query Analyzer for my test. I opened up a single connection and
ran a simple batch along the lines of:
begin tran
while @.i <= 10000
begin
insert into MyTable ...
values ...
select @.i = @.i + 1
end
commit
OK, when I run the above and check the start and finish times I can insert
about 1200 rows per second. I checked the disks with perf mon and I have
very little Average Disk Write Queues on the table and transaction log disks
(on avg less than 1.)
Sooooo.....
When I open up two additional sessions in SQL Query Analyzer ... and kick
off three of the same loops "at the same time" I average ... a sum total of
about 1200 rows per second. No scaling at all. Note that I am running all
three windows from within the same SQL Query Analyzer session, each with a
different connection into the database.
Again, the disks and the whole box are pretty much snoozing.
Any suggestions I might try? I'm sure I'm missing something here.
Thanks so much!!!!
DBwell...
have you try varchar instead of char?
why a numeric and not an integer?
separated disks is not enough.
What is your disk config?
do you use RAID 5 or Raid 0+1?
how many disks on each controller? (do you use SCSI 15krpm disks?)
have you some write cache on your controllers?
does your files are an good initial size? when SQL server need to expand a
file, this takes some ressources, so if your files are larger then your
requirements you'll improve the performance.
have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
and 60MB/s (SQLIO results):
sqlio -kW -b64 -frandom -LS
with this result I achieve 3333 insert/sec using a script near like you (100
000 rows inserted in 30sec; no transactions)
I insert into a table like yours (int, char(8000), rowversion)
doing all the inserts into 1 transaction reduce the time of the process to
16sec.
and finally adding the set nocount on option, I reduce the process to 7sec
my log & database files are on the same disk
Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> Hello all ...
> This one has me a bit confused. I'd really appreciate any and all
> suggestions. This may be pilot error on my part...
> My requirement is to have multiple programatic database applications, each
> on a separate database connection insert data into a single table as fast
> as
> possible. My hope is that I'll see some scaling of rows-per-second
> inserted
> by adding additional processes, connections and simultaneous inserts.
> I'm not able to use BCP for this as the data is coming from an application
> style feed.
> I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on
> an
> AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
> tends to get up around 2.9GB at times ... it is the "only" service on the
> box.
> Here's my table:
> CREATE TABLE MyTable
> (myID numeric(10,0) not null,
> myChar char(8000) null,
> myRowversion rowversion)
> ON A_TABLE_FILEGROUP
> -- No indexes, just a simple heap. I know that each row will require 8K
> of
> -- storage ... about a page each.
> My database transaction log is on a different disk/file group on a
> separate
> controller.
> I used SQL Query Analyzer for my test. I opened up a single connection
> and
> ran a simple batch along the lines of:
> begin tran
> while @.i <= 10000
> begin
> insert into MyTable ...
> values ...
> select @.i = @.i + 1
> end
> commit
> OK, when I run the above and check the start and finish times I can insert
> about 1200 rows per second. I checked the disks with perf mon and I have
> very little Average Disk Write Queues on the table and transaction log
> disks
> (on avg less than 1.)
> Sooooo.....
> When I open up two additional sessions in SQL Query Analyzer ... and kick
> off three of the same loops "at the same time" I average ... a sum total
> of
> about 1200 rows per second. No scaling at all. Note that I am running
> all
> three windows from within the same SQL Query Analyzer session, each with a
> different connection into the database.
> Again, the disks and the whole box are pretty much snoozing.
> Any suggestions I might try? I'm sure I'm missing something here.
> Thanks so much!!!!
> DB
>

multipass query many fact tables

I am new to DW. I am required to design reports which come off of DW. I found a rpt requirement which is from 4 different fact tables, each of it at a diff grain level.

The results change dramatically. I am not joining fact tables directly, but am joining them through common dimension. I am concerned about the correctness of the recs returned. I have also tried to create a view with a facttable A and its dimension tables, and linked that to the originial facttable B which looks like the report theme(In this approach the # of recs will more or less be = to B's records.

I am not finding any examples apart from multipass query mentioned. I would like to see an example to understand. Could anyone point me in the rt direction?

Thanks

Are you working with Analysis Services or just the SQL Server relational database technology?

If you are working with the relational technology, you may need to aggregate your data to a common granularity before performing your joins. This is significantly easier to handle through SSAS.

B.

|||

I am working with SQL Server Relational db(2005).

Thanks