Searching/Querying Records from Database

Question

Searching/Querying Records from Database

hellolasantha

Grasshopper

Points: 21
More actions
August 25, 2011 at 5:51 am

#256308

Hi this might be little bit dumb. But i wanna know is there any standard or efficient way of querying from DB.
I am a Web/desktop developer. i am always writing db query statements in TextChanged event.
Eg:sample c# Code
DataTable dt = new DataTable();
SqlDataAdapter = new SqlDataAdapter(_sql, SqlConnection);
SqlDataAdapter.Fill(dt);

Is it ok to write like that. i mean every single text change event program will connect to database, get the result and disconnecting... Is it ok or are there any other ways of doing this tasks

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

Basically that's generating a "SELECT *" statement for every table that you define. Which means it selects everything, every time. It's just going to get slower and slower as your data increases. You want to add a filter through the use of a WHERE clause so that you only bring back certain records. I don't know the exact syntax on your end, but that should be enough to get you started.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hellolasantha Grasshopper Points: 21 More actions · Answer 2

Thanks. is it ok to connect and retrieve data and disconnect from the DB each key pressing...i am not using all details...

SQL statement goes like

Select Col1,Col2 FROM tbl WHERE col1='some value'

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

For every key press? Whoa! You might want to explore one of the NoSQL options to see if they can support you better. SQL Server is likely to barf at that type of behavior depending on how many people you have connected to it. You'd want something that lives in memory and doesn't ever, ever, go to disk to support that type of querying.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323547 More actions · Answer 4

I'd consider loading the datatable you want to search agaisnt ONCE, and then using a filter( if you are using a BindingSource) like bindingSource.Filter("SomeValue LIKE '" & sometextbox.text & "%'")

or a DatsTable.Select(("SomeValue LIKE '" & sometextbox.text & "%'")

avoid roundtrips to the server untill you get to a point where you need details

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hellolasantha Grasshopper Points: 21 More actions · Answer 5

@Grant Fritchey

@Lowell

So

if i get a copy of particular result set and kept in the memory,

Do the key press interraction with the copy (which is in the memory)

would it be efficient???

by the way taking a copy of 1000 records to memory would be a problem with resources isnt it?

anyway i better come up with a good mechanism to avoid this resource problem.....

Thank you very much for you guys

Lowell SSC Guru Points: 323547 More actions · Answer 6

hellolasantha (8/25/2011)
@Grant Fritchey
@Lowell
So
if i get a copy of particular result set and kept in the memory,
Do the key press interraction with the copy (which is in the memory)
would it be efficient???

efficent? not at all. one efficent way to do it is on the actual click of some submit button. That way addition work/trips to the server don't occur.

Now for a fancier user interface that is more proactive, like you are trying to do, there is a cost for doing the work based on a change event; you have to decide if the cost is worthwhile in your interface.

by the way taking a copy of 1000 records to memory would be a problem with resources isnt it?

1000 rows of data isn't much; 100,000 rows of data might be.

That would depend on the end users resources...if your target audience is all higher end machines with lots of RAM, the # of rows probably doesn't make much of an impact.

anyway i better come up with a good mechanism to avoid this resource problem.....
Thank you very much for you guys

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!