March 8, 2013 at 12:09 am
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
March 8, 2013 at 12:12 am
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:
March 8, 2013 at 12:14 am
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
March 8, 2013 at 12:38 am
Hello SQLRNNR & 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
March 8, 2013 at 1:44 am
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:
March 8, 2013 at 1:56 am
Please find the attachment
March 8, 2013 at 2:11 am
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:
March 8, 2013 at 3:08 am
Hi demonfox,
Actually i am new in sql, so could you please tell me What i have to do exactly?
March 8, 2013 at 4:57 am
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:
March 8, 2013 at 5:04 am
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...
March 8, 2013 at 9:14 am
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
March 12, 2013 at 12:53 am
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy