January 31, 2010 at 7:31 pm
hi all,
i have a database called DB with a table called Menu_reporting that logs whatever the user selected through calling the server (See below)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
USE [DB]
GO
/****** Object: Table [dbo].[Menu_Reporting] Script Date: 01/31/2010 17:07:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Menu_Reporting](
[RecNum] [bigint] IDENTITY(1,1) NOT NULL,
[Call_DateTime] [datetime] NOT NULL,
[Menu_Name] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Keypress_at_Menu] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Region_Code] [smallint] NULL,
[Caller_Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Menu_Reporting] PRIMARY KEY CLUSTERED
(
[RecNum] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
this table has almost 4 million records
this table is indexed by Call_Datetime, Menu_Name and region_code and the primary key is rec_num
we have a stored procedure that is called via the web to get what each user selected and from what region... etc
(see below)
~~~~~~~~~~~~~~~~~~~~~~~
USE [DB]
GO
/****** Object: StoredProcedure [dbo].[sp_GetMenuDetailReport] Script Date: 01/28/2010 16:31:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetMenuDetailReport]
@startRecnum int,
@endRecnum int,
@startCallDate varchar(4000),
@endCallDate varchar(4000),
@reg1 varchar(4000),
@reg2 varchar(4000),
@reg3 varchar(4000),
@reg4 varchar(4000),
@reg5 varchar(4000),
@orderBy varchar(4000),
@orderDirection varchar(4000)
AS
BEGIN
declare @sql varchar(8000);
declare @TempReport Table
(
ID int IDENTITY,
Call_dateTime datetime null,
Region_Code varchar(4000),
Region_Name varchar(4000) null,
Menu_Name varchar(4000),
Menu_Description varchar(4000) null,
Keypress_at_Menu varchar(4000),
TotalTimesSelected varchar(4000)
);
---Handle Asc
if @orderDirection='asc'
begin
if @orderBy='Region_Code'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS TotalTimesSelected from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by Menu_Reporting.Region_Code ASC;
end
if @orderBy='Menu_Name'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS TotalTimesSelected from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by Menu_Reporting.Menu_Name ASC;
end
if @orderBy='Menu_Description'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS TotalTimesSelected from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by Menu_Descriptions.Menu_Description ASC;
end
if @orderBy='TotalTimesSelected'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS TotalTimesSelected from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate
and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by TotalTimesSelected ASC;
end
if @orderBy='Keypress_at_Menu'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS TotalTimesSelected
from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate
and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by Menu_Reporting.Keypress_at_Menu ASC;
end
end
else
begin
---DESC ORDER
if @orderBy='Region_Code'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS
TotalTimesSelected
from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate
and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by Menu_Reporting.Region_Code DESC;
end
if @orderBy='Menu_Name'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS TotalTimesSelected
from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate
and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by Menu_Reporting.Menu_Name DESC;
end
if @orderBy='Menu_Description'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS
TotalTimesSelected
from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate
and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by Menu_Descriptions.Menu_Description DESC;
end
if @orderBy='Keypress_at_Menu'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select
Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS TotalTimesSelected
from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by Menu_Reporting.Keypress_at_Menu DESC;
end
if @orderBy='TotalTimesSelected'
begin
INSERT INTO @TempReport (Region_Code,Region_Name,Menu_Name,Menu_Description,Keypress_at_Menu,TotalTimesSelected)
Select Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu, COUNT(*) AS
TotalTimesSelected
from Menu_Reporting
INNER JOIN dbo.Menu_Descriptions ON dbo.Menu_Reporting.Menu_Name = dbo.Menu_Descriptions.Menu_Name INNER JOIN
dbo.REGION_CODES ON dbo.Menu_Reporting.Region_Code = dbo.REGION_CODES.Region_Code
where Menu_Reporting.Call_dateTime> @startCallDate and Menu_Reporting.Call_dateTime<= @endCallDate
and Menu_Reporting.Region_Code in(@reg1,@reg2,@reg3,@reg4,@reg5)
group by Menu_Reporting.Region_Code, REGION_CODES.Region_Name, Menu_Reporting.Menu_Name, Menu_Descriptions.Menu_Description, Menu_Reporting.Keypress_at_Menu
order by TotalTimesSelected DESC;
end
end
if @startRecnum=0 and @endRecnum=0
begin
select count(*) from @TempReport;
end
else
begin
select * from @TempReport where Id >=@startRecnum and Id <=@endRecnum;
end
END
~~~~~~~~~~~~~~~~~~~~~~~
when the table was smallish, the query was executing, but when it started to get huge, it kept timing out, now i can't run it for more than 5 days!
any help would be appreciated.
thanks in advance
January 31, 2010 at 9:12 pm
You ought to check on a few things, like the counts for these SELECTs (without the inserts), and also on the execution plan. My guess would be you are getting a bad plan and the size of the query means that resources are being used heavily.
I would also look to restructure this procedure so that it only puts the required data in the temp table, not all data. Move that WHERE clause at the bottom to each query.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 31, 2010 at 10:04 pm
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Also, do some investigation yourself and see where in the proc the really slow code is. It's seldom that everything's equally slow. These may help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2010 at 1:28 am
Please also see this article (and following discussion thread) for other methods of paging.
February 1, 2010 at 9:06 am
Hi,
1. don't use table variables if you work with million records. Table variables work only for small recordset - for big number of records it is creating normal tempory table in background locking your resources.
2. What version and edition of SQL server do you have: what about partitioning of the reporting table.
3. try to break select statement in parts - apply condition to one table and join result to the other...
February 1, 2010 at 9:20 am
ns_nataly (2/1/2010)
Table variables work only for small recordset - for big number of records it is creating normal tempory table in background locking your resources.
No it doesn't. The reason that table variables perform poorly on large row counts is because they have no statistics and hence the optimiser comes up with really, really bad row estimates and really bad execution plans.
3. try to break select statement in parts - apply condition to one table and join result to the other...
Generally that has no effect on performance. The optimiser can and will rewrite the query to the simplest form before optimising. Moving conditions around the query seldom has any effect. Do note though, it can change the meaning of the query if the joins are outer joins.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2010 at 2:20 am
A couple of comments and suggestions from a newbie.
4 million records isn't really that many so you should be able to get it to work just fine.
The sp seems to be basically the same thing 10 times. If all are slow, be helpful to others and show us just one. If some are slow and some fast, you are probably half way to your answer, and if you can't crack it show us the slowest and the fastest. And toss away all the other bits of the sp that aren't relevant for us - record ranges (unless they are usually supplied). And like others have said, make it easy for someone to set it up and play with - it might take you 15 minutes but it will save 30 for everyone else that looks at it seriously, and be more likely to give you quality answers.
In pratice are 5 geographic areas frequently given, does the query run faster if there is one and the query only checks for one? (i.e. not using in (...) - this generates lots of 'or's internally which can be slow).
A shot in the dark - why are you using big int? You only have four million records, not four billion (and your sp only accepts int range anyway). I assme you've refreshed statistics - perhaps everything is in a date range that SQL Server doesn't think there is any data in - this could make a big difference. Assume you've defragmented indexes (less likely to make a huge difference).
As a last resort, if most queries have a tight date range and this is always specified you could partition the table on date, but it kind of feels like a cludge, you should be able to get it to perform without.
Mike T
February 2, 2010 at 4:15 am
Considering you have Rebuild your index; Just a suggestion
•Try creating an index in the following order (region_code ,Call_Datetime, Menu_Name)
•I really don’t like ordering data on the DB; Can you perform the ordering of data on the application side instead?
This will make your code a lot more simpler and the sort operation will disappear
It definitely would have helped if the execution plan was attached
February 2, 2010 at 7:00 am
Post your Execution plan
and IO statistics.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 2, 2010 at 8:28 am
thanks guys for trying to help
how can i get the execution plan and the statistics i/o?
thanks
February 2, 2010 at 8:32 am
See the article I posted earlier for the exec plan
For IO stats
SET STATISTICS IO ON
then run the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2010 at 2:36 pm
We moved the database to a windows 2008 64bit, sql 2008 for testing and it ran fine! actually the database has 15 million records, and when we executed the query/stored procedure for 2 years period, we got the records in 1 minute and 30 seconds.
The server that is being used right now is windows 2003 32 bit, 4GB Memory, dual core, sql 2005.
i'm trying to get the information for you guys.
Thanks.
February 2, 2010 at 9:47 pm
As gail suggested first read that post.
by the way , press CTRL M and then execute the query , it will provide you the execution plan , save it ,zip it and then attached to this post. dont forget to remove "show execution plan" option ( by pressing CTRL M)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 2, 2010 at 9:51 pm
No it doesn't. The reason that table variables perform poorly on large row counts is because they have no statistics and hence the optimiser comes up with really, really bad row estimates and really bad execution plans.
So is this only reason we should avoid table variable for heavy count of records. ? or it also has small capacity to hold the records?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 2, 2010 at 11:39 pm
Bhuvnesh (2/2/2010)
So is this only reason we should avoid table variable for heavy count of records. ?
That and its limited indexing capabilities
or it also has small capacity to hold the records?
The limit on rows in a table var is the same as for any other table in SQL. Limited only by available space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply