hi all,
i have a database called DB with a table called Menu_reporting that logs when 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 appriciated.
thanks in advance