Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

performance - sql server select vs. select on in memory datatable

Posted on 2011-02-23
8
Medium Priority
?
744 Views
Last Modified: 2012-08-13
Hi,
I’m looking for the best way to do the following thing in terms of performance time/memory usage.
I’ve a windows app. build with C#. the machine is win server 2003 with sql server 2005 DB.

I’ve a DB with a few tables, some have 1000's rows and some 10,000's rows.
in my application I need to do many select commands.
there're 2 approaches I’m aware of and I’m looking for the best one in terms of performance:
1. load the tables into datatables at the beginning of the app. run using SqlDataAdapters and then use datatable.select(xxx) to find the rows I need (no primary key selects usually select on text).
in this method I approach sql server only in the beginning of the app., loads the data to the memory and query on the datatables.
2. each time I’ve to query use a stored procedure on the DB. which means 10000's of queries on the DB.

from test I’ve made the 2nd method looks faster, but on task manager it looks like the sqlservr.exe is working very hard and takes a lot of CPU.

so what is the best way? is there another solution which is better than my solutions?
what about LINQ?

Thanks,
Assaf.
0
Comment
Question by:AssafST
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 34967709
Some remarks:

1. Loading the data at startup - what if data has changed ?
2. How big is the return ? Do you return 10 rows, 100 rows, 10000 rows, ... The higher you go, the inneffecient your design is made. Perhaps you can use paging to return the data in smaller pieces ...

0
 

Author Comment

by:AssafST
ID: 34967742
1. the data is changed/updated at the end of the App. run - this App. is used to update the data in the DB, so there's no fear that the data isn't correct.
2. i need all the tables data in order to run the App., so there're 6 tables with ~10,000 rows each.
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 750 total points
ID: 34967781
>> 2. i need all the tables data in order to run the App., so there're 6 tables with ~10,000 rows each.

Do you mean that you update all the tables, all the records ?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:AssafST
ID: 34967820
no. i need all the data in order to update 3 tables (not all the records).
i don't do the update in memory but create a long string of SP commands and at the end of the run update the DB.
something like:
string updateDBstr = "exec SP1 p1, p2,p3; exec SP1 p5, p6,p7; exec SP2 p1, p2,p3;" etc...
objCommO.CommandText = updateDBstr;
objCommO.ExecuteNonQuery();
0
 
LVL 40

Expert Comment

by:lcohan
ID: 34970787
If you have enough memory on the SQL box you should not worry about that as SQL will put those tables in cache and keep them as long as they are needed (especially on not so large tables as yours). Until SQL 2005 you could have use DBCC PINTABLE sql command to force SQL keep those tables in memory but was discontinued.

you should check your page life expectancy/SQL Server Buffer Cache Hit Ratio and if less than 90% you may need better indexing/stats or more RAM on the box
0
 

Author Comment

by:AssafST
ID: 34971946
1. what do you mean by "put those tables in cache"? put them in datatables as i do?
2. "you should check your page life expectancy/SQL Server Buffer Cache Hit Ratio"
how do i check this?

10x
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 750 total points
ID: 34973237
SQL server will put the data in cache and as your tables are small is likely they will be entirely cached on servers with sufficient RAM.

You can run command below to check the values in SQL:


select * from sys.sysperfinfo
   where counter_name in ('Buffer cache hit ratio','Page life expectancy' )
order by counter_name



0
 

Author Closing Comment

by:AssafST
ID: 35139243
not a final solution
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question