Long Wait - 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

SQL Server Development (2000)

Long Wait

Author

Topic

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 00:24:02

I have got 3 tables each table has about 8700000 rows!
I need to perform a join on these tables .I have tried to do this in sql reporting(business intelligence 2005)... but the report is taking ages to process... i usually wait till 45 min for the report to be processed but nothing happens so due to time constraint i stop the report in the middle...
is there any way to run the report with this much data faster???

Cheers
Mita

Kristen
Test

22859 Posts

Posted - 2006-06-06 : 00:31:06

Probably best to post the query you are trying to run so we can make suggestions about whether it can be optimised.

Kristen

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 04:04:15

hi
my query is something like this..
select * from table1
union select * from table 2.

where both of these tables have got about 900000 rows each..

now what should i do to make the above query run faster in business intelligence studio???

Kristen
Test

22859 Posts

Posted - 2006-06-06 : 04:25:59

"my query is something like this"

Trouble is without seeing the actual query we'd all be just guessing.

But if that is pretty much what you are doing then:

Did you mean UNION rather than UNION ALL?

No WHERE clause? If so then selecting between 900,000 and 1,800,000 rows back to the application is going to take a long time whatever you do.

Kristen

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-06 : 04:49:55

The number of rows you mention keeps changing.

Why do you need a report that spurts out over a million end results? Noone is going to look at such a thing. Why not tell us what you actually need to do and what you are doing at the moment, then maybe someone can produce a better way.

-------
Moo. :)

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 17:02:38

Hi
Thanks for replying
what i need is just to append the data for two years..each table has got almost a million rows

there is no where condition..just a simple append..
now wats the best way to do this???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-06 : 17:14:11

quote:

now wats the best way to do this???

Why do you want to return millions of rows? There is no way to optimize that type of query.

Tara Kizer
aka tduggan

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 18:01:33

hi Tara
I am working as an information analyst ...we have been using ms access for data analysis but now upgrading to sql 2005...
we recieve data from the entire country for performing analysis..
i ll show you a sample data..
its like
table 1
Companyname id year volume_for_jan vol_for_feb.....vol_dec
abc 1 2000 34 333 ......555
2.............................................

table 2
Companyname id year volume_for_jan vol_for_feb.....vol_dec
rrr 44 2001 55 66...............888
77.........................................
now what i need to do is append table2 to table 1 ..
how shall i do this????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-06 : 18:02:36

Do you want the data moved to Table1 or just appended in a result set?

Tara Kizer
aka tduggan

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 18:27:31

what i need is a report which is something like this
Comp_name
id
Year
vol_jan vol_feb .....vol_dec
HOw shall i do this???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-06 : 18:30:45

You are missing the point. The reason why your report is taking a long time to run is because you haven't added a WHERE clause. You do not need millions of rows returned in the report. No human can process that much data in a report. So what data in those tables do you actually need to return. So far, your query will be:

SELECT Comp_name, id, Year, vol_jan, vol_feb, ...
FROM Table1

But it needs to become:

SELECT Comp_name, id, Year, vol_jan, vol_feb, ...
FROM Table1
WHERE ...

No database management system can return millions of rows in a timely fashion. You need to talk to your boss to see what the real data requirement is because you haven't figured that out yet.

Tara Kizer
aka tduggan

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 18:51:00

well we have one field which is common for all the tables and that field is ID
So i need to sort of perform a grouping on these tables and the grouping levels are
Comp_name
ID
Year
vol_jan vol_feb....... vol_dec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-06 : 18:52:38

We do not have enough information to help you. Please post the DDL for all tables involved. DDL is the CREATE TABLE statement. You can easily generate the code for it from Enterprise Manager using the Generate SQL script wizard.

Tara Kizer
aka tduggan

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 19:23:01

I thought that was a different topic...
any ways i need to add the grouping in my report from these tables..
what specific information do you need??
I dont know how to use ddl as i am using sql 2005...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-06 : 19:32:59

We need to see your table layout. Have your DBA generate the code for us then post it here. You keep abbreviating your data and your colunms, so it's very hard to figure out what you need.

Tara Kizer
aka tduggan

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 19:43:34

what i sent you was just a sample data,here is the original table script
Here is the script for my 3 tables
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Y2004]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Y2004](
[Funder] [nvarchar](255) NULL,
[FMIS Code] [nvarchar](255) NULL,
[Client NHI] [nvarchar](255) NULL,
[Service ID] [nvarchar](255) NULL,
[Year] [int] NULL,
[Jan 04] [float] NULL,
[Feb 04] [float] NULL,
[Mar 04] [float] NULL,
[Apr 04] [float] NULL,
[May 04] [float] NULL,
[Jun 04] [float] NULL,
[Jul 04] [float] NULL,
[Aug 04] [float] NULL,
[Sep 04] [float] NULL,
[Oct 04] [float] NULL,
[Nov 04] [float] NULL,
[Dec 04] [float] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Y2002]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Y2002](
[Funder] [nvarchar](255) NULL,
[FMIS Code] [nvarchar](255) NULL,
[Service ID] [nvarchar](255) NULL,
[Client NHI] [nvarchar](255) NULL,
[Year] [int] NULL,
[Nov 02] [float] NULL,
[Dec 02] [float] NULL,
[id] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Y2003]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Y2003](
[Funder] [nvarchar](255) NULL,
[FMIS Code] [nvarchar](255) NULL,
[Service ID] [nvarchar](255) NULL,
[Client NHI] [nvarchar](255) NULL,
[Year] [int] NULL,
[Jan 03] [float] NULL,
[Feb 03] [float] NULL,
[Mar 03] [float] NULL,
[Apr 03] [float] NULL,
[May 03] [float] NULL,
[June'03] [float] NULL,
[Jul 03] [float] NULL,
[Aug 03] [float] NULL,
[Sep 03] [float] NULL,
[Oct 03] [float] NULL,
[Nov 03] [float] NULL,
[Dec 03] [float] NULL
) ON [PRIMARY]
END

hope this helps

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-06 : 19:51:56

This is a terrible database design. Can you normalize it?

All of your data should be in one table with a column that signifies the date.

But to further help you, we'll need to some a few rows in each of your tables. Do not abbreviate anything. Please post it in the forum of INSERT INTO statements so that we can replicate your environment on our machines. We'll need the expected result set using these sample rows so that we know what your output should look like. Here is an example of how to post all of this:

-----------
CREATE TABLE Table1 (Column1 int, Column2 varchar(50), Column3 int)
INSERT INTO Table1 VALUES(1, 'Tara', 30)
INSERT INTO Table1 VALUES(2, 'Tara', 40)
INSERT INTO Table1 VALUES(3, 'Mike', 25)
INSERT INTO Table1 VALUES(4, 'Mike', 35)

Given the above data, I want to see the following result set:
Tara 70
Mike 60
-----------

Now given the above information, we would try out the problem on our machines then reply with this:

SELECT Column2, SUM(Column3)
FROM Table1
GROUP BY Column2

Tara Kizer
aka tduggan

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 19:59:38

before doing that, ill show you the exact information as to how it is stored
Funder FMIS Code Service ID Client NHI Nov 02 Dec 02
Cant 6630 S-HSPC BRT9316 0 0
Cant 6630 S-HSPC BRT9316 0 0
Cant 6630 S-HSPC BRT9316 0 0
Cant 6630 S-HSPC BRT9316 0 0
Cant 6630 S-HSPC BRT9316 0 6.64285714285714

actually this table has been imported from acces as it was very difficult to perform some queries in sql

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-06 : 20:01:09

It is very hard to read data when you post it like that as it doesn't line up properly. So we can't tell what data goes with which column. Posting INSERT INTO statements allow us to read the data better plus duplicate your problem on our machines.

Providing about 3-5 rows per table involved will help us come up with a solution for you.

Tara Kizer
aka tduggan

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-06 : 20:21:59

CREATE TABLE Table1 (Funder char, FMIS Code int, Service ID int, Client NHI int, year date,Nov int,Dec int)

INSERT INTO Table1 VALUES('cant',12,333,1212,2000,12,33)
INSERT INTO Table1 VALUES('cant',13,3343,1222,2000,112,233)
INSERT INTO Table1 VALUES('cant',103,5343,22,2000,142,733)
---------------------------------------------------------------
CREATE TABLE Table2 (Funder char, FMIS Code int, Service ID int, Client NHI int, year date,Jan int,feb int...Dec int)

INSERT INTO Table2 VALUES('NEL',112,33,172,2001,11,33,77,88,44,2,8,76,4,56)
INSERT INTO Table2 VALUES('NEL',133,043,172,2001,132,233,8,9,00,55,34,87,9,1)
INSERT INTO Table2 VALUES('NEL',138,943,6,2001,142,733,8,6,7,4,4,2,5,8)

----------------------------------------------------------------------
CREATE TABLE Table3 (Funder char, FMIS Code int, Service ID int, Client NHI int, year date,Jan int,feb int...Dec int)
INSERT INTO Table3 VALUES('NEL',112,33,172,2003,11,33,77,88,44,2,8,76,4,56)
INSERT INTO Table3 VALUES('NEL',133,043,172,2003,132,233,8,9,00,55,34,87,9,1)
INSERT INTO Table3 VALUES('NEL',138,943,6,2003,142,733,8,6,7,4,4,2,5,8)

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-06-07 : 01:18:39

here is the sample data for the report output..hope it makes it more clear now...

table 1
Companyname id year volume_for_jan vol_for_feb.....vol_dec
abc 1 2000 34 333 ......555
2 2000 33 22 666

table 2
Companyname id year volume_for_jan vol_for_feb.....vol_dec
rrr 44 2001 55 66.............888
24 2001 22 35 454

each table has almost 800000 rows

I need to generate a report out of these tables which should be as follows

Company name Id Year Vol_for_jan Vol_for_feb.........
abc 1 2000 34 33
2 2000 33 22
rrr 44 2001 55 66
..................................................

when i try to run the report in business intelligence ,it takes forever to run and doesnt even produce the report at the end...

what shall i do????

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources