Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
notmyrealname
98 Posts |
 Posted - 2009-04-29 : 12:50:02
|
| Hi.I'm working on a SQL data app. My boss wants it as fast as possible. The database isn't huge (< 1000000 records). I've been tuning my queries and have gotten them down to a couple of seconds. I noticed that if i just load all of the records into a dataset when my app is started and then use the table.select method for filtering, the speed is instantaneous. The only two downsides that i see are 1) the app takes about 10 seconds to load and 2) the data is only current to the time that the app is loaded. I'm just wondering if anyone thinks that the overhead of doing this would be too high?Thanks. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-29 : 13:13:30
|
| Couple of questions/concerns:- What kind of data are you returning? How many tables are involved?- Are people really working with 1 million rows? Or even 10,000? If they're not, then your queries aren't optimized if they're returning more data than is needed.- If the users are making updates to the data, do you need to save changes to the database right away? Do you then need to refresh everyone's set of data after any change?Typically fast performance is not as important as data correctness/integrity, especially if the system is highly transactional. I'd rather get correct data slowly than wrong data immediately. |
 |
|
|
notmyrealname
98 Posts |
Posted - 2009-04-29 : 13:22:24
|
| Hi robvolk.The main data that will be displayed to the user comes from a single table. This data will be read only and will not require any updating.The user never has to view a million records. Just a few hundred. I know this approach sounds silly but i just noticed that when i do it this way it is faster than my stored procedures (even the most basic sp). 0 seconds versus 2 to 5 seconds. I personally think the 2 to 5 seconds is fine. My superiors really think it should be faster and seem to be most concerned with getting the data as fast as possible.It's just a thought. I know the argument about not loading more data than necessary but it's just really fast! In the end i will probably suggest that this method isn't the best idea.Thanks. |
|
|
|
notmyrealname
98 Posts |
Posted - 2009-04-29 : 13:39:25
|
| Hi. Me again.You might be able to help with this related problem. I think the reason why the table.select method works faster than my sp's is because my sp's get data based on a list of items.I.e.Select Col1 from Table1 Where Col2 IN (@param)The problem with the above sp is that i can't use my @param in an IN clause. I've tried to format my @param a million different ways to get it to work but i don't think it ever will.What i have done instead is this:Select Col1 from Table1 Where (',' + Replace(@param, ' ', ',') + ',' Like '%,' + Col2+ ',%')This is the only way i was able to get my sp to work with a list of items being passed to a single parameter.The Table.Select method in VB.Net, however, can handle my list. I.e. Table1.Select("Col2 IN ('Value1', 'Value2', etc...)So i think it's my stored procedure trying to doing some complex stuff to very simple data that is slowing it down.Do you have any suggestions?Thanks again. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-29 : 13:59:55
|
| I don't know all the implementation details on the .Net data objects, but it sounds like it's creating a prepared SQL statement that is executed on the server. You can run Profiler to confirm that. If so, then it'd only return the rows that match the criteria you're filtering for.Since you're dynamically changing the WHERE clause, not just parameterizing it, you won't get much benefit from using a stored procedure. It's not the ideal way to do it but if it meets your needs then go with it. Keep an eye on your performance if the data grows significantly, and make sure your indexes and statistics remain up-to-date. |
|
|
|
notmyrealname
98 Posts |
Posted - 2009-04-29 : 14:24:22
|
| Thanks. I appreciate the advice. |
|
|
|
|
|
|
|
|
Is this a bad idea?