Query optimization

Question

Query optimization

swapnil.bole

Valued Member

Points: 56
More actions
March 8, 2013 at 12:09 am

#272084

Hello friends,
I have table contains millions of records.
When i fired query to get record by id. Query get millions of rows for that it take 2-3 second time.
How can i optimize query?
My sql query is:
SELECT *
FROM Table1
WHERE intId = @intId AND
bintTimeStamp >= @bintStartTimeStamp AND
bintTimeStamp < @bintEndTimeStamp
ORDER BY bintTimeStamp

Viewing 12 posts - 1 through 11 (of 11 total)

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

demonfox SSCertifiable Points: 6289 More actions · Answer 1

swapnil.bole (3/8/2013)
Hello friends,
I have table contains millions of records.
When i fired query to get record by id. Query get millions of rows for that it take 2-3 second time.
How can i optimize query?
My sql query is:
SELECT *
FROM Table1
WHERE intId = @intId AND
bintTimeStamp >= @bintStartTimeStamp AND
bintTimeStamp < @bintEndTimeStamp
ORDER BY bintTimeStamp

can you post the DDL's ..i.e. table definition's ...

and post the actual execution plan ...

Edit : Millions out of Millions .. 2-3 sec .. what's wrong with the time taken ???

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

And please post index definitions.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

swapnil.bole Valued Member Points: 56 More actions · Answer 3

Hello SQLRNNR & demonfox,

@demonfox

Table defination is:

CREATE TABLE TABLE1

(

autTimeSeriesId PRIMARY KEY IDENTITY(1,1),

intIdintNOT NULL,

bintTimeStampbigintNOT NULL,

decValuedecimal(11, 2) NOT NULL,

intTickStatus int NOT NULL

)

my query get 105119 rows out of 2229388.

I have uploaded actual execution plan image.

Please find the attachemnt.

@SQLRNNR You have asked me for index definitions.

i have only one index, which is cluster on "intId" column

demonfox SSCertifiable Points: 6289 More actions · Answer 4

swapnil.bole (3/8/2013)
Hello SSC Eights! & SSCoach,
@SSC Eights!
Table defination is:
CREATE TABLE TABLE1
(
intIdintNOT NULL,
bintTimeStampbigintNOT NULL,
decValuedecimal(11, 2) NOT NULL,
intTickStatus int NOT NULL
)
my query get 105119 rows out of 2229388.
I have uploaded actual execution plan image.
Please find the attachemnt.
@SSCoach You have asked me for index definitions.
i have only one index, which is cluster on "intId" column

Right Click on execution plan > save as name_of_plan.sqlplan ..post that one..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

swapnil.bole Valued Member Points: 56 More actions · Answer 5

swapnil.bole

Valued Member

Points: 56

March 8, 2013 at 1:56 am

#1594854

Please find the attachment

demonfox SSCertifiable Points: 6289 More actions · Answer 6

if you see in the executipn plan .. the estimated no. of rows are around 3600 whereas actual rows are around 100,000 .. that's a huge difference.. try updating the statistics ..

Other than that , I would suggest to use column names instead of * , that's a better practice..

also , you said , Intid is a clustered index,

@SSCoach You have asked me for index definitions.
i have only one index, which is cluster on "intId" column

but, you table definition says otherwise ...

if the create statement is like that , then AutTimeSeries would have Clustered index..

and as per execution plan , I would say , AutTImeSeries is clustered index on the table ..

could you check the table definitions ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

swapnil.bole Valued Member Points: 56 More actions · Answer 7

Hi demonfox,

Actually i am new in sql, so could you please tell me What i have to do exactly?

demonfox SSCertifiable Points: 6289 More actions · Answer 8

swapnil.bole (3/8/2013)
Hi demonfox,
Actually i am new in sql, so could you please tell me What i have to do exactly?

I suggest you go through some of the performance articles , you will get a basic idea about the performance tuning ..

you can start with microsft website msdn..

or read articles about Statistics on ..

http://www.sql-server-performance.com

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

swapnil.bole Valued Member Points: 56 More actions · Answer 9

I updated statistics by executing "sp_updatestats" store procedure but still the estimated no. of rows are around 3600 whereas actual rows are around 100,000 .. are coming.

thanks for giving reply & now i am going through link which you have provided...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

You can probably get better performance by creating an index on intID and bintTimeStamp.

But you really should not do select * in these queries for better performance.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

swapnil.bole Valued Member Points: 56 More actions · Answer 11

I have created index using actual exection plan. Query save 25% time. Now data is completely getting on index basis.

CREATE NONCLUSTERED INDEX IndexName

ON dbo.Table1 (intId,bintTimeStamp)

INCLUDE (autTimeSeriesId,decValue,intTickStatus)

GO