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.
| Author |
Topic |
|
mpolaiah
Starting Member
24 Posts |
 Posted - 2014-09-12 : 07:52:19
|
| Hi Friends, In my table have a 5000000 records and table have 60 columus and i created one primary key and 30 columns non-clusterd index.but i run the store procedure it take 20min like my sp is create PROCEDURE sp_members @fromDate datetime = null, @toDate datetime = nullASBEGINSELECT * FROM td_member J where J.CreatedDateTime between @fromDate and @toDate and (J.IsDeleted=0 OR J.ISDELETED IS Null)and j.IsActive = '1'ENDi pass the date between 2 days it run past.when pass the last one month it take 30 min.please help me |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-09-12 : 08:12:02
|
| If you don't have an index on createddatetime, having one would help. If you have an index with 30 columns and createdtime is somewhere towards the end of the list of 30, then that wouldn't help much.You can look at the execution plan to see where the bottleneck is. In SSMS menu, Query -> Include Actual Execution Plan and then run the query to see the execution plan. |
 |
|
|
mpolaiah
Starting Member
24 Posts |
Posted - 2014-09-12 : 08:21:29
|
| Hi James, we need all 30 columun index must in different search in sp it is need.what do and what not do? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-09-12 : 08:55:09
|
| You can look at the execution plan to see where the bottleneck is. In SSMS menu, Query -> Include Actual Execution Plan and then run the query to see the execution plan.It will also show you the missing indexes which can be useful.When you have 5 million records and you want to filter, if you don't have the proper indexes, SQL Server has to read through all or most of that 5 million records. That is what is taking the time. So what you need to do is help out SQL Server by creating appropriate indexes.What is the index that you currently have? |
|
|
|
mpolaiah
Starting Member
24 Posts |
Posted - 2014-09-13 : 02:35:30
|
| i am creating non clusterindex like create index index_name td_member(createddate) |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-09-13 : 06:26:52
|
| You can also limit the columns , ie.e instead of using * , detail the columns.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-09-15 : 13:45:31
|
| You need to cluster the table on CreatedDateTime. Anything else will waste time and resources. |
|
|
|
|
|
|
Performance issue in store procedure