SLOW QUERY - 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 2005 Forums

Transact-SQL (2005)

SLOW QUERY

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2009-07-14 : 17:39:29

I am having trouble with slow query running. When I run the query in Microsoft SQL Server Management Studio it runs fast. I don't have any problem. But when I run the query using ColdFusion Server 8 it runs extremely slow. It use to run fine but the more data the slower. There has to be a good way to run queries over large amounts of data. Also no matter what the query returns I only allow it to return the top 100 results. The table info is listed below. Also this is a database that has data streaming into it. It has been streaming since 6/26/2009 and I have 6.5 million records. Any ideas or help would be great.

Thanks,
Nick

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CDRS](
[ID] [bigint] NOT NULL,
[CallId] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SeqNum] [int] NULL,
[DateTimeInt] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrigGw] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrigIp] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrigPort] [smallint] NOT NULL CONSTRAINT [DF_testTABLE_OrigPort] DEFAULT ((0)),
[TermGw] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TermIp] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TermPort] [smallint] NOT NULL CONSTRAINT [DF_testTABLE_TermPort] DEFAULT ((0)),
[Ani] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastCallNumber] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Duration] [int] NOT NULL CONSTRAINT [DF_testTABLE_Duration] DEFAULT ((0)),
[CallType] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PDD] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsdnCode] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SipCode] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DiscCode] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[InterIntra] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Revenue] [decimal](18, 5) NULL,
[Expense] [decimal](18, 5) NULL,
CONSTRAINT [CDR_index] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-14 : 20:43:09

1. Make sure you have proper index's
2. Run a trace from managment studio, and then coldfusion and see if there is a differance in how the query is running with the differant connections.
3. If you still have trouble, post the differant executions and someone here can hopfully try to assist.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources