[Show all top banners]

NepaliBhai
Replies to this thread:

More by NepaliBhai
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 SQL - Select Statement - Performance Issue
[VIEWED 5809 TIMES]
SAVE! for ease of future access.
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
 
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.
 
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.

 
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

 
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

 
Posted on 09-17-15 12:54 PM     [Snapshot: 199]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

whats your query ???
 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 7 days
Recommended Popular Threads Controvertial Threads
Those who are in TPS, what’s your backup plan?
Now, more than ever, is it a good idea to apply for advance parole?
DACA and TPS in Danger but what abt Healthcare workers?
If you got nothing to lose, fight tooth and nail explore every avenue
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters