You are here:

C#/To improve sql query performance

Advertisement


Question
QUESTION: Hi Murat,
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 there,

You probably fetch all rows and then filter the content you want. You need to optimize your sql code to fetch from server only the rows you need. When listing contents be sure you are using paging.

For more help, share me the sql command you are using to fetch from server.

Hope that helps,

Murat

---------- FOLLOW-UP ----------

query
query  

code
code  
QUESTION: Hi Murat,
I'm fetching records only I want to show. I attached 2 images.
1. Get Count of records and Fetch records as per page number.
2. Web service that pass records to jquery and Bind records to table from jquery function.

Please check it and help me as possible.
Thanx again.

Answer
Hi back,

It's a known issue that ROW_NUMBER() is very slow on large tables like yours. However I'm not sure why there is a huge duration difference between local and server. Please try calling the sql function using sql management studio and see if it takes 1 min+ to fetch records. If still it takes long, we are clear that the problem is in your sql command and it should be updated.

Here is the same problem, and an acceptable solution for paging

Otherwise I'm afraid there is another problem which I cannot foresee, like a connection problem.

Also Is there such a load-time difference between local and server for first function (which just gets count)?

C#

All Answers


Answers by Expert:


Ask Experts

Volunteer


Murat Mehmet

Expertise

I can help with questions about desktop and web programming in C#, including SOAP, XML, database managing, custom controls, security etc.

Experience

I have been developing web and especially desktop applications in C# and VB.Net for almost 5 years. My programming life has begun with VB6 long time ago, so its about 8 years that I am in this business.

Organizations
Was in R & D for 2 years in a popular Turkish technology website: cyber-warrior.com

Education/Credentials
2011 Computer Engineering graduation in University of Trakia in Turkey.

©2016 About.com. All rights reserved.