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

There are few suggestion for the post itself. I cannot see the reason for lot of blank lines. Because of that it is very discouraging to try reading the code. User Code tags, edit queries.
Actual DB related GENERAL suggestions
1. reduce the size of your variables, though they are VARCAHR. for ex. @orderBy need not be 4000. On older version, I have seen it making a lot of difference.

2. If you notice @sql 8000 - never used (in the code given). Why declare if not used? Remove all unused variables. Reduce column lengths to meaningful lengths.

3. In joins, that I couldn't really read the queries; but the rule of thumb is start with largest (max number of records) table.

4. is there reason to use @TempReport as opposed to #TempReport ?

5. For advanced topics, study "execution plan", specially if you have 2008, it will suggest you indexes to be added.

Let us know how much this helped. Close thread if this really helps.

Canä't you just increaser timeout settings. I have seen as short timeout as about 15 sec when little bit more complex query will fail.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.