Software Development/To improve sql query performance
I am using sql server 2012. I have table which have 2,00,000 rows. When I am working on my local pc it takes 1-2 second to execute on sql server and working fine on my localhost. But when I upload project on online server it takes 1:34 mins to execute o sql server and also when I search from form and load only 100 rows to form, it takes too much time to fetch records.
I searched for indexing and full index searching but not getting how to use it. Have you any solution that take less time and execute fast on forms.
When working with large data its very important that the indexes on the tables and columns are defined properly. I have had no chance to work with SQL 2012 till now but below are some sample SQL I have used in the past for checking performances:
1. check if index will be used based on density
DBCC SHOW_STATISTICS ('xml2u.[property]', 'IDX_xml2u_Client_ID')
2. check missing index details
SELECT * FROM sys.dm_db_missing_index_details
3. Check Index usage
DECLARE @TABLENAME sysname
SET @TABLENAME= 'HumanResources.Employee'
SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]
, OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]
, I.NAME AS [INDEX NAME]
, I.INDEX_ID AS [INDEX ID]
, USER_SEEKS AS [NUMBER OF SEEKS]
, LAST_USER_SEEK AS [LAST USER SEEK]
, USER_SCANS AS [NUMBER OF SCANS]
, LAST_USER_SCAN AS [LAST USER SCAN]
, USER_LOOKUPS AS [NUMBER OF BOOKMARK LOOKUPS]
, LAST_USER_LOOKUP AS [LAST USER LOOKUP]
, USER_UPDATES AS [NUMBER OF UPDATES]
, LAST_USER_UPDATE AS [LAST USER UPDATE]
INNER JOIN SYS.INDEXES I
ON I.OBJECT_ID = SS.OBJECT_ID
AND I.INDEX_ID = SS.INDEX_ID
WHERE DATABASE_ID = DB_ID()
AND OBJECTPROPERTY(SS.OBJECT_ID,'IsUserTable') = 1
AND SS.OBJECT_ID = OBJECT_ID(@TABLENAME)
ORDER BY USER_SEEKS
, USER_UPDATES ASC
Your approach should be to first speed up the access directly inside SQL Server Mgmt Studio. Use a stored procedure instead of normal SQL in case you are already not using it.
After you are satisfied with the speed inside SQL Server Mgmt Studio check what speed you get in your application.
In practice you should implement paging inside your stored procedures so you only retrieve the number of records you want to display in a page. I don't mean the easy way of implementing paging where by default everything is retrieved from DB but during display only some are displayed.