You are here:

Software Development/To improve sql query performance

Advertisement


Question
Hi Ramanan,
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.

Thanx.

Answer
Hi Karan,
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
"USE AdventureWorks
GO

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]
FROM    
  SYS.DM_DB_INDEX_USAGE_STATS SS
  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_SCANS
   , USER_LOOKUPS
     , USER_UPDATES ASC
GO"

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.

Regards
Ramanan  

Software Development

All Answers


Answers by Expert:


Ask Experts

Volunteer


Ramanan Iyer

Expertise

I can answer questions related to software development involving microsoft technologies like vb, C#, ASP.Net. I can answer questions related to MS SQL Server. I an help in giving tips for best methods for programming.

Experience

I have around 11+ years of experience in the field of software development. I have been involved in development of windows based applications and web portals for European Clients.

Organizations
Webdirekt India Pvt. Ltd. Enexion India Pvt. LTd.

Education/Credentials
I have completed my Masters Degree in Computer Applications in the year 1999.

Awards and Honors
Received scholarship in statistics.

©2016 About.com. All rights reserved.