Lot of time takes for Excution of small query

Question

Lot of time takes for Excution of small query

Suresh Mediboyina-615840

Valued Member

Points: 61
More actions
August 4, 2009 at 1:38 am

#95748

Hi SQL Gurus,
I've a table tbl_xxxx,it has 20 records and one primary key and one foreign key constraints. While select the values form this table, some times it takes more than half an hour(37 mins), some times with in 0 sec or 1 or 2 sec the result is displaying.
Why most of times it takes longer time?
In other cases it show error message 'transition failed TCP/IP ......' this type error occure.If I immediately run the same query it shows result.
Please help me thanks a lot in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

Hi Suresh,

Please post the create scripts for table structure,index and sample data and also the query execution plan. If you have only 20 records and if you are using just a simple select it should be very very fast. Can you check whether there is any blocking process.

[font="Verdana"]Thanks
Chandra Mohan[/font]

Suresh Mediboyina-615840 Valued Member Points: 61 More actions · Answer 2

Hi Chandu,

Thank you for quick response,here I'm giving the script of the table

CREATE TABLE [dbo].[tbl_CompanyRates](

[bint_RateId] [bigint] IDENTITY(1,1) NOT NULL,

[float_TotalKMS] [float] NULL,

[int_Days] [int] NULL,

[float_Amt] [float] NULL,

[float_ExKMS] [float] NULL,

[float_PRate] [float] NULL,

[float_FRate] [float] NULL,

[date_Created] [datetime] NULL,

[bint_CompanyId] [bigint] NULL,

[bint_MakeId] [bigint] NULL,

[int_Type] [int] NULL,

[bit_Status] [bit] NULL,

CONSTRAINT [PK_tbl_CompanyRates] PRIMARY KEY CLUSTERED

(

[bint_RateId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

USE [sonytest]

GO

ALTER TABLE [dbo].[tbl_CompanyRates] WITH CHECK ADD CONSTRAINT [FK_tbl_CompanyRates_tbl_CompanyInfo] FOREIGN KEY([bint_CompanyId])

REFERENCES [dbo].[tbl_CompanyInfo] ([bint_CompanyId])

sample data :

bint_RateId (21) float_TotalKMS(0) int_Days (0) float_Amt (0) float_ExKMS (0) float_PRate (0) float_FRate (7.2) date_Created(2008-06-01 00:00:00.000) bint_CompanyId(7)(FK) bint_MakeId(1) int_Type(12) bit_Status(1)

Chand00 SSCertifiable Points: 6466 More actions · Answer 3

Can you also post the query

[font="Verdana"]Thanks
Chandra Mohan[/font]

Suresh Mediboyina-615840 Valued Member Points: 61 More actions · Answer 4

Hi Chandu,

simple query 'select *from tbl_companyrates"

Thank you

Slick84 SSCertifiable Points: 5602 More actions · Answer 5

Suresh,

You could use SQL profiler to set a trace and run the select query. It should give you some helpful information.

Thanks,

S

--
:hehe:

Chand00 SSCertifiable Points: 6466 More actions · Answer 6

Hi,

As Slick suggested did you checkout using profiler. It will help you to understand whats happening in the backend. Everthing seems to be fine as it is a normal simple select query. Checkout running the query from other system also.

[font="Verdana"]Thanks
Chandra Mohan[/font]