[VIEWED 5809
TIMES]
|
SAVE! for ease of future access.
|
|
|
NepaliBhai
Please log in to subscribe to NepaliBhai's postings.
Posted on 09-17-15 7:21
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Hey SQL gurus, How can we boost the performance with lesser time for returning data from the table that has 100 columns and 1 million rows. It is taking more than a minute. Ex, Select col1, col2, col3.......col100 from Table1
|
|
|
|
BadhiJanne
Please log in to subscribe to BadhiJanne's postings.
Posted on 09-17-15 7:34
AM [Snapshot: 12]
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Did it decades ago. Indexing should help.
|
|
|
NepaliBhai
Please log in to subscribe to NepaliBhai's postings.
Posted on 09-17-15 7:43
AM [Snapshot: 14]
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
BadhiJanne Jee, If you did this decade ago, you must have done much advanced way now. Please let me know. I am just doing it now. All indexes are in place for joining with other tables. Created View. Selecting from view. Since view is a compiled version, it is relatively quicker than table.
|
|
|
virusno1
Please log in to subscribe to virusno1's postings.
Posted on 09-17-15 11:49
AM [Snapshot: 140]
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Last edited: 17-Sep-15 11:51 AM
|
|
|
virusno1
Please log in to subscribe to virusno1's postings.
Posted on 09-17-15 11:49
AM [Snapshot: 140]
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
NepaliBhai, putting a clustered index may help not much because you are not putting any filter in the select statement. When you mention all the columns and selecting all of them then sql will use table scan as an execution plan. You said you have 100 columns which is terrible design. Your total row size should be less than 8K and if you go over that limit then sql will put your data into external storage and retrieving from external storage other than your actual in row data page is very costly operation. It seems like you can't do much to change the table schema at this time. If you already have a clustered index then I would suggest you to check fragmentation. Your index fragmentation should not be more than 30%. You can either use dbcc showcontig or sys.dm_db_index_physical_stats dmv. Check to see extend fragmentation or page fragmentation. When you have lot of fragmentation then it's nothing but your query is running hard and switching back and forth to get the data from different pages which is really bad. Please paste your dbcc result here I may help. If you see lot of fragmentation then I would suggest to rebuild the index but please consult all the users while rebuilding it. You can do online rebuild or offline rebuild depending upon your sql server version but both of them affects the performance while rebuilding. Last edited: 17-Sep-15 11:51 AM
Last edited: 17-Sep-15 11:54 AM
|
|
|
mero_naam_ho
Please log in to subscribe to mero_naam_ho's postings.
Posted on 09-17-15 12:54
PM [Snapshot: 199]
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
|
|
|