Need suggestions for optimization of DB query

[grmihel2]

Hi guys,

I found this forum in hope of finding a good place to find some other C# developers that could help me, and that I maybe could help with something else that I have experienced.
I'm using VS08 to develop and maintaince a customer application, running on a MSSQL08 Express.

Everything is working perfectly, but in the maintime there have been some performance leaks, in the search time for object presentation.
I should maybe note, that I'm using the good old WinForms as a UI for presentation.
The problem is, that as the application being used, the more data is getting stored in the DB ofc, and the more object items have to be searched through, and that increased the search and performance time dramatically!

So my issues is, do any of you guys have a suggestion to, how do I increase the search method, to speed things up, and make the performance faster?

I'm using a TreeView list to presentate the objects, and as link between the DB and the system, I'm using MS LinQ to SQL generation (.dbml).
the search code at the very moment that are used is:

public IEnumerable<Adresse> SearchAdresser(String pSearchString)
{
try
{
IEnumerable<Adresse> list = (from p in _context.Adresses where p.Street.StartsWith(pSearchString) || p.Floor.Contains(pSearchString) || p.Number.Contains(pSearchString) || p.Postalcode.Cityname.Contains(pSearchString) select p);
return list;
}
catch (Exception ex)
{
_errorclass.reportError("CRUDAdresse", "SearchAdresser", ex);
throw;
}
}

the code above show an example of the biggest search method, based on search on address where you can find items based on Streetname, Street number, Floor or Cityname.

One of the more simple searches are like the following:

public IEnumerable<Potential_customer> SearchPotential_customer(String pSearchString)
{
try
{
IEnumerable<Potential_customer> list = (from p in _context.Potential_customers where p.Name.StartsWith(pSearchString) select p);
return list;
}
catch (Exception)
{
throw;
}
}


this code above it not that flexible as if I used the p.Name.Contains(pSearchString) as an operator. But sadly enough, this make even more performance leaks....


The problem is there is currently around 1200-2000 objects that has to be shown on a search, and I ain't using Threads (due to its complexity and bad documentation of implementing in WinForms?), so when the system has to search 2000 rows thorugh the LinQ context, it gives a more or less short lockdown on user inputs on the UI (frozen UI for a moment), which is making it annoying....

so does any1 have a suggestion how I could increase the search performance, or an idea of which type of search algorithm I should be using instead???

Any helps are appriciated.
Best regards

[Crash8308]

Instead of hard-coding queries into your source code you should be executing stored procedures. (mySQL 5.0+ and MSSQL server support stored procedures.)

that should increase the responsiveness of the queries because the queries do not need compiled when they are stored procs.

[grmihel2]

Does DB Query really solve the issue? I have runned some tests to try to locate it, and by using the LinQ dbml, it seems to be a local issue, which store the clients RAM up.

When it does the query search, and you look at the clients task manager, you can see that the CPU usage is peaking until the program is done with searching thru the query.
Ain't it correct, that when you work with dbml in LinQ > SQL, then you have some kind of a copy-like of the DB map on the client? Or does the dbml simply just provide a markup for developers? At least it feels like it, since you can access the DB objects as <db.address>.StreetName = "Test";

I though that Stored Procs was increasing the server performance, if you for example had a bottle-neck at your DB server?

[TechGnome]

Your LINQToSQL is running on the client... so what you're seeing about the peak CPU moments is correct. And yes, because it's being performed synchronously, it locks the current thread while it runs. Two ways to handle this as I see it - put the search into a send, secondary thread (I'm not sure what you mean about it being not documented well... threading has always seemed to be one of the most well documented areas I've found) .... or implement the search as a stored procedure. Using an SProc allows the operation to be offloaded to the server, which is designed to handle such queries. And if you really want to kick it up a notch, combine the two... put the call to the stored proc in a secondary thread.

-tg

[Crash8308]

your linqtosql is running on the client... So what you're seeing about the peak cpu moments is correct. And yes, because it's being performed synchronously, it locks the current thread while it runs. Two ways to handle this as i see it - put the search into a send, secondary thread (i'm not sure what you mean about it being not documented well... Threading has always seemed to be one of the most well documented areas i've found) .... Or implement the search as a stored procedure. Using an sproc allows the operation to be offloaded to the server, which is designed to handle such queries. And if you really want to kick it up a notch, combine the two... Put the call to the stored proc in a secondary thread.

-tg

+1

[grmihel2]

Sounds like a good solution on everything. At least I'll implement som SProcs for the search queries, but I might consider the threading too, just to speed everything up a bit.
But as far as I have seen, threads are dealt with method calls from another class?

Eg.
class Searching
{
public List Getcustomers()
{
...
}
}

class Main
{
Searching search = new Searching();
Thread t = new Thread(new ThreadStart(search.Getcustomers()));
....
}

Or is it possible to make a thread in the current class, to another method? It so far seemed a little complicated to implement threading to the WinForms, tho making a mapping class in the data layer between the user interfaces and the data storage might be best, it might sometimes be nessecary to deal with it within the GUI class code itself, do to presentations of the data.

Do you have any nice link, about the threads and documentation on it?

[vcdebugger]

http://msdn.microsoft.com/en-us/libr...8VS.71%29.aspx

[grmihel2]

Now I had some time to experiment with the SPs, and just by running my SPs within the SQL Manager, I could see the advantages of speed with SProc. It would return a list with 7000 rows in lesser than a second...

But now I need some help of what is possible and what ain't when using stored procs in C# app.
Since I have this nice DBML (LinQ to SQL), which make object mappers quiet easy, since they are directly related with the Database objects.

Is it possible to map from the SqlReader.read to an object?
Eg.:
There is an customer object mapped (in DBML) from the customer table in the DB.

ArrayList customers = new ArrayList();
while(reader.read())
{
Customer c = new Customer();
c = reader[];
customers.Add(c);
}

Or do I have to set each parameter manual to the object related? (Cuz then there is a lot of work to do by that).


Are there any way to return a List of objects from a StoredProc? So I just by calling my SProc named GetAllCust @parameter = '', will recieve a list with all customers? (or a list where all custNames = @parameter)???

Hope you can guide me with how powerful this SProc tool is, and can inlight me of the possiblities of combining DBML and SProcs in a beautyful harmony

[grmihel2]

Any1 that can't help me out, if it is possible to map the Stored procedures calls directly to the DBML? Or isn't it possible at all, and then I'm fatched to map each store procedure query to the right object class?

Or is my questions not clear enough, and understandable?