Large Datasets - 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

General SQL Server Forums

Database Design and Application Architecture

Large Datasets

Author

Topic

supergirlgem
Starting Member

3 Posts

Posted - 2008-03-27 : 09:36:18

Hi,

I have a table which has over 450,000 records in it, I have now split this into 4 so each table has around 100,000 records in it but I'm still having the problem of the data being returned really slowly.

What I need to do to this data is group it by a code and show the total for each code for every month of the year (this is currently based on one column and selecting the data accordingly). I have created views and put some indexes onto my table but the results are still being returned slowly. Does anyone have any suggestions of how I can speed this up?

Thanks

Gemma

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 09:44:30

450000 records is not much. It would help to see your DDL and indexes.

See this example!

DECLARE	@Sample TABLE (Code VARCHAR(2), dt DATETIME, Items INT)

INSERT	@Sample
SELECT	'AB', '20080327', 2 UNION ALL
SELECT	'CD', '20080227', 3 UNION ALL
SELECT	'AB', '20080326', 6 UNION ALL
SELECT	'CD', '20080201', 2 UNION ALL
SELECT	'CD', '20080122', 4

SELECT		Code AS theCode,
		DATEADD(MONTH, DATEDIFF(MONTH, '19000101', dt), '19000101') AS theMonth,
		SUM(Items) AS theSum
FROM		@Sample
WHERE		dt >= '20080101'
		AND dt < '20090101'
GROUP BY	Code,
		DATEADD(MONTH, DATEDIFF(MONTH, '19000101', dt), '19000101')

SELECT		Code,
		SUM(CASE WHEN theMonth = 1 THEN Items ELSE 0 END) AS January,
		SUM(CASE WHEN theMonth = 2 THEN Items ELSE 0 END) AS February,
		SUM(CASE WHEN theMonth = 3 THEN Items ELSE 0 END) AS March,
		SUM(CASE WHEN theMonth = 4 THEN Items ELSE 0 END) AS April,
		SUM(CASE WHEN theMonth = 5 THEN Items ELSE 0 END) AS May,
		SUM(CASE WHEN theMonth = 6 THEN Items ELSE 0 END) AS June,
		SUM(CASE WHEN theMonth = 7 THEN Items ELSE 0 END) AS July,
		SUM(CASE WHEN theMonth = 8 THEN Items ELSE 0 END) AS August,
		SUM(CASE WHEN theMonth = 9 THEN Items ELSE 0 END) AS September,
		SUM(CASE WHEN theMonth = 10 THEN Items ELSE 0 END) AS October,
		SUM(CASE WHEN theMonth = 11 THEN Items ELSE 0 END) AS November,
		SUM(CASE WHEN theMonth = 12 THEN Items ELSE 0 END) AS December
FROM		(
			SELECT	Code,
				DATEPART(MONTH, dt) AS theMonth,
				Items
			FROM	@Sample
			WHERE		dt >= '20080101'
					AND dt < '20090101'
		) AS x
GROUP BY	Code

E 12°55'05.25"
N 56°04'39.16"

supergirlgem
Starting Member

3 Posts

Posted - 2008-03-28 : 04:51:13

Hi,

I've used the above and it is working great, the only problem I have now is retrieving the data, I can get the data out of the @sample table, but there is second table being returned which I presume is named x but I can't seem to get the data out of that - any help would be great.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 04:56:39

The demonstration above is just for mimic your environment of which I have no access to.
Substitute the @Sample table name with your real table name and substitute the column names with your real column names.

SELECT		{Your code column name here} AS theCode,
		DATEADD(MONTH, DATEDIFF(MONTH, '19000101', {Your datetime column name here}), '19000101') AS theMonth,
		SUM({Your aggregate column name here}) AS theSum
FROM		{Your table name here}
WHERE		{Your datetime column name here} >= '20080101'
		AND {Your datetime column name here} < '20090101'
GROUP BY	{Your code column name here},
		DATEADD(MONTH, DATEDIFF(MONTH, '19000101', {Your datetime column name here}), '19000101')
ORDER BY	{Your code column name here},
		DATEADD(MONTH, DATEDIFF(MONTH, '19000101', {Your datetime column name here}), '19000101')

SELECT		theCode,
		SUM(CASE WHEN theMonth = 1 THEN {Your aggregate column name here} ELSE 0 END) AS January,
		SUM(CASE WHEN theMonth = 2 THEN {Your aggregate column name here} ELSE 0 END) AS February,
		SUM(CASE WHEN theMonth = 3 THEN {Your aggregate column name here} ELSE 0 END) AS March,
		SUM(CASE WHEN theMonth = 4 THEN {Your aggregate column name here} ELSE 0 END) AS April,
		SUM(CASE WHEN theMonth = 5 THEN {Your aggregate column name here} ELSE 0 END) AS May,
		SUM(CASE WHEN theMonth = 6 THEN {Your aggregate column name here} ELSE 0 END) AS June,
		SUM(CASE WHEN theMonth = 7 THEN {Your aggregate column name here} ELSE 0 END) AS July,
		SUM(CASE WHEN theMonth = 8 THEN {Your aggregate column name here} ELSE 0 END) AS August,
		SUM(CASE WHEN theMonth = 9 THEN {Your aggregate column name here} ELSE 0 END) AS September,
		SUM(CASE WHEN theMonth = 10 THEN {Your aggregate column name here} ELSE 0 END) AS October,
		SUM(CASE WHEN theMonth = 11 THEN {Your aggregate column name here} ELSE 0 END) AS November,
		SUM(CASE WHEN theMonth = 12 THEN {Your aggregate column name here} ELSE 0 END) AS December
FROM		(
			SELECT	{Your code column name here} AS theCode,
				DATEPART(MONTH, {Your datetime column name here}) AS theMonth,
				{Your aggregate column name here}
			FROM	{Your table name here}
			WHERE	{Your datetime column name here} >= '20080101'
				AND {Your datetime column name here} < '20090101'
		) AS x
GROUP BY	theCode
ORDER BY	theCode

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 04:58:57

Also, if there is an index present covering the three columns

{Your code column name here}
{Your datetime column name here}
{Your aggregate column name here}

the query will be really fast.

E 12°55'05.25"
N 56°04'39.16"

supergirlgem
Starting Member

3 Posts

Posted - 2008-03-28 : 05:34:18

Thanks for all your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources