performancequery-performanceselectsql-server-2008-r2
My question may sound childish, but I am really googling a lot regarding this issue. I have a table tblUsers where 20 columns are present (FirstName, LastName, EmailAddress….), which has around 91,000 data.
When I use Select Query
Select * from tblUsers
It takes around 2 min 27 seconds to complete execution.
Is It really any way to speed up the execution time
Any type of suggestions will be welcomed. Thanks in advance
Thanks to Jon Seigel, it seems like the issue in part due to the CPU affinity setting in SQL.
We deployed this VM with SQL from an image which causes a known issue as listed here (scenario #3):
Removing the automatic affinity option and setting the affinity mask option to use all the CPUs available resolved the issue.
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 15;
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO
We have updated our image to include this option. Thanks everyone for the help in this issue.
Please note, this option is deprecated in SQL Server 2012 and is not recommended for use. Additionally, it is related to processor thread handling in Windows 2000 and Windows 2003 operating systems.
The statement you are trying to tune ("select firstName from user;") doesn't have a where clause so it will perform a full table scan. When firstName is indexed, then a scan of the index will occur. This will be faster on the basis that the amount of data the engine must read and is substantially less than the entire row.
Most applications are interested in smaller sets of data (i.e., what are the users with a first name of Peter) and not all of the stored data values. This is where tuning really pays off. A firstName index would be beneficial to find people with a certain firstName but comes at a cost - inserting, updating and deleting row becomes slower. You do not want to add indexes indiscriminately.
Best Answer
You are running
Select * from tblUsersand expecting it to be fast? You are selecting all columns and all rows from a table, so it will take its own time to return the result set. If there is a clustered index, an index scan would be used, and if the table is a heap, a table scan would be used.If you want to increase the speed, you either have to add a WHERE clause to make query sargable or limit the number of columns, although the latter will not affect the execution time so much.
Also, if your data file lies on a fast storage (SSDs, Flash Drives, as compared to SATA disks), the query is likely to become faster.
Moral: If you are running
select *on a table, don't expect the query to be fast, it would take its own time to retrieve all rows. If you are on a production system, runningselect *would really be a performance killer, so avoid it.