Dataset or SQL? wich is faster?

9 May 2006 1:42 PM

Joris De Groote

Hi,

I have a question, wich goes faster (The table has 15000 rows in it and
growing every day)?

- at the start of the program run a querry and put this in a dataset and
during the program search the dataset for the information needed
- use a SQL querry for every time you need some value from the database
(wich will return 1 value)?

Thanks
Joris

9 May 2006 1:46 PM

Marina Levit [MVP]

Once the table gets above over a certain size, running a SQL query will be
faster.

Additionally, using up tons of memory to keep thousands of rows in memory at
a time is wasteful. At some point, you won't have enough memory to contain
the entire table anyway.

Show quoteHide quote

"Joris De Groote" <joris.degro***@skynet.be> wrote in message
news:uTuLs52cGHA.1276@TK2MSFTNGP03.phx.gbl...
> Hi,
>
> I have a question, wich goes faster (The table has 15000 rows in it and
> growing every day)?
>
> - at the start of the program run a querry and put this in a dataset and
> during the program search the dataset for the information needed
> - use a SQL querry for every time you need some value from the database
> (wich will return 1 value)?
>
> Thanks
> Joris
>

9 May 2006 1:54 PM

Joris De Groote

So I'll use SQL, thanks!

Show quoteHide quote

"Marina Levit [MVP]" <someone@nospam.com> wrote in message
news:Og$$u72cGHA.2068@TK2MSFTNGP02.phx.gbl...
> Once the table gets above over a certain size, running a SQL query will be
> faster.
>
> Additionally, using up tons of memory to keep thousands of rows in memory
> at a time is wasteful. At some point, you won't have enough memory to
> contain the entire table anyway.
>
> "Joris De Groote" <joris.degro***@skynet.be> wrote in message
> news:uTuLs52cGHA.1276@TK2MSFTNGP03.phx.gbl...
>> Hi,
>>
>> I have a question, wich goes faster (The table has 15000 rows in it and
>> growing every day)?
>>
>> - at the start of the program run a querry and put this in a dataset and
>> during the program search the dataset for the information needed
>> - use a SQL querry for every time you need some value from the database
>> (wich will return 1 value)?
>>
>> Thanks
>> Joris
>>
>
>

9 May 2006 4:44 PM

Jim Wooley

Additionally, don't forget about concurrency issues. What happens when another
user modifies a record you have in your dataset?

If you are not worried about changing data, why bother with the dataset?
Why not just use one of the types in System.Collection which are much smaller.
With either in-memory representation of the data, you are still not benifiting
from any indexing that the database uses to optomize your query.

With the number of records you are talking, I would definately argue in favor
of the database assuming network bandwidth isn't too much of an issue. If
it is, I wouldn't want to think about sending 15000 records across the pipe
either.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx

Show quoteHide quote

> Once the table gets above over a certain size, running a SQL query
> will be faster.
>
> Additionally, using up tons of memory to keep thousands of rows in
> memory at a time is wasteful. At some point, you won't have enough
> memory to contain the entire table anyway.
>
> "Joris De Groote" <joris.degro***@skynet.be> wrote in message
> news:uTuLs52cGHA.1276@TK2MSFTNGP03.phx.gbl...
>
>> Hi,
>>
>> I have a question, wich goes faster (The table has 15000 rows in it
>> and growing every day)?
>>
>> - at the start of the program run a querry and put this in a dataset
>> and
>> during the program search the dataset for the information needed
>> - use a SQL querry for every time you need some value from the
>> database
>> (wich will return 1 value)?
>> Thanks
>> Joris

9 May 2006 5:06 PM

aaron.kempf@gmail.com

15k record? lol

who gives a crap about 15k records; it's like nothing

how wide is it?

9 May 2006 6:42 PM

Liz

"Joris De Groote" <joris.degro***@skynet.be> wrote in message
news:uTuLs52cGHA.1276@TK2MSFTNGP03.phx.gbl...

> I have a question, wich goes faster (The table has 15000 rows in it and
> growing every day)?

> - at the start of the program run a querry and put this in a dataset and
> during the program search the dataset for the information needed
> - use a SQL querry for every time you need some value from the database
> (wich will return 1 value)?

Generally speaking, I don't think the DataSet was designed to migrate the
functionality of a relational database server to client machines and, unless
there is some very particularized reason for doing so, it seems inadvisable.
Let the RDBMS do what it does very well; that's why you have it.

It seems to me that the occasion to employ an in-memory relational store on
the client is in situations where you are bringing together disparate and
disconnected data sources that need to be related ... such as a scenario
where have perhaps two SQL Server tables, one Oracle table and a local
Access table which need to be treated as a single related collection.

L