ado.net data access performance

[John]

Hi

I am using a strongly typed dataset created by vs 2008. I have added a query
to select next record to the data adapter as below;

SELECT TOP 1 <field list>
FROM MyTable
WHERE (ID > ?)
ORDER BY ID

? is replaced by the ID of the current record.

My problem is when I fill using this query there is a noticeable about half
a second delay before data is retrieved. Is there any way to improve the
performance of this query?

Thanks

Regards

 

[Mr. Arnold]

Hi

I am using a strongly typed dataset created by vs 2008. I have added a
query to select next record to the data adapter as below;

SELECT TOP 1 <field list>
FROM MyTable
WHERE (ID > ?)
ORDER BY ID

? is replaced by the ID of the current record.

My problem is when I fill using this query there is a noticeable about
half a second delay before data is retrieved. Is there any way to improve
the performance of this query?

http://www.devx.com/vb2themax/article/19887/1954>

 

[Michel Posseth [MCP]]

Use a datareader and ditch all the overhead of the drag and drop controls if
it is speed you are after


Michel

 

[John]

If I use datareader to replace "fill" my form controls, what method can I
use to update the values back to db?

Thanks

Regards

 

[Mr. Arnold]

If I use datareader to replace "fill" my form controls, what method can I
use to update the values back to db?

Thanks

You use an ADO.Net Command object that works with such databases such as SQL
Server, Oracle, etc, etc and do record inserts or updates, using a T-SQL
statement, no dataset and manual of the fly.

 

[Michel Posseth [MCP]]

You can use a command object for updating , inserting , delete just as easy
with ACCESS

Michel

 

[Mr. Arnold]

You can use a command object for updating , inserting , delete just as
easy with ACCESS

I didn't say you couldn't. So just what is your problem?

 

[William Vaughn]

This query requires that the entire table be ordered before the ordinal is
returned. I would look at the query plan in SSMS to see what kind of plan is
being generated. I would also make sure that there are supporting indexes on
the ID column. Fill is not the best choice here as you don't need to return
a rowset but an ordinal. For this reason, I would use the
Command.ExecuteScalar. This returns a single value (as an object).

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Michel Posseth [MCP]]

Based on the question and the SQL statement

SELECT TOP 1 <field list>
FROM MyTable
WHERE (ID > ?)
ORDER BY ID

? is used in ACCESS SQL where @varname is used for other databases

I know for a fact that the TS is using ACCESS and you forgot to mention it
in your answer becides all the other databases you did mention the TS migh
have thought that it is not possible with ACCESS.

So i just wanted to point out that a command object could also be used icw
with ACCESS

I didn't say you couldn't. So just what is your problem?

And problems ......

I don`t have anny problems with a person who is probably a professional like
me , who is devoting his probably spare time to help others in newsgroups
like these to point them in the right direction or to solve the problems of
these people And all of this for ........ yes for what ?

Well i have the highest respect for those people and value them as "must be
good people" and if i encounter them in a thread of answers i see there
postings not as an atack on my answer but as a additive to the discussion
" probably it wasn`t clear enough , or i was understood wrong ".

If you were asking about "personal" problems well i have and had plenty of
them but as your name is not Dr Phil i wil not bother you with them

regards

Michel Posseth

 

[Michel Posseth [MCP]]

Ahum :-(

after rereading i am not so sure annymore about this

"> I know for a fact that the TS is using ACCESS


But okay you got the point i hope

Michel

 

[Mr. Arnold]

Ahum :-(

after rereading i am not so sure annymore about this

"> I know for a fact that the TS is using ACCESS


But okay you got the point i hope

I don't know man. Maybe, you need to take a Bromo-Seltzer, drink a glass of
milk, eat a cookie, and settle down here. It's not that serious.

 

[John]

Hi Bill

I have skipped all the fields as there were too many to list. This is the
sql which I have added to dataset data adapter. I am consistently told off
to only pick a small number of records for speed sake so I have come up with
this strategy to allow user to move forward one record at a time. I have a
similar sql for moving back one record at a time.

Thanks

Regards

 

[Mr. Arnold]

Hi Bill

I have skipped all the fields as there were too many to list. This is the
sql which I have added to dataset data adapter. I am consistently told off
to only pick a small number of records for speed sake so I have come up
with this strategy to allow user to move forward one record at a time. I
have a similar sql for moving back one record at a time.

Move forward one record at a time? If I was in your end-user base, there
would be a contract/hit put out on you to take you out in the parking lot.

 

[John]

They are used to access front end based apps which allow them to do that.
Hard to rid of old habits.

Regards

 

[Mr. Arnold]

They are used to access front end based apps which allow them to do that.
Hard to rid of old habits.

You give them what they are use to working with in an application. That way,
they are happy, your boss is happy, and you'll be happy.

 

[Cor Ligthert[MVP]]

Michel,

Will you tell us what is the overhead. Now your answer look to me the same
as insert 64Gb in your 32bits computer.

In my idea will a datareader not make it faster, my general expirience is
even oposite from that.

Cor

 

[Cor Ligthert[MVP]]

John,

Is this in debugging or in release mode, while debugging a strongly typed
dataset is real slow.

Cor

 

[Michel Posseth [MCP]]

Will you tell us what is the overhead.

All the datatable ,dataset and there constraints , validation that you
might not need
A datareader is connected , and is the equivalant of a old fashioned
Firehose cursor ( read only, forward only cursor )

So by using the datareader`s property`s you can skip the dataset , table
stuff if possible in your design and this gives you extra perfomance
and is especially handy for retrieving small blocks of data ,, or in
projects were you process large amounts of data ( as the data is not
preloaded as is common in disconnected architecture ) ofcourse this is
only suitable for readonly data

In my idea will a datareader not make it faster, my general expirience is
even oposite from that.

I wonder in wich special situation you encountered this, cause normally a
datareader should be superior in speed ( for obvious reassons ) however
without the strong typing etc etc

Michel

 

[Chris Dunaway]

If I use datareader to replace "fill" my form controls, what method can I
use to update the values back to db?

Since you are using VS2008, you might consider using LINQ to SQL. If
your queries are not extremely complex, you won't have to write any
SQL at all. You would just call the SubmitChanges method of the
DataContext class:

' DataContext takes a connection string.
Dim db As New DataContext("...\database.mdf") 'Use appropriate
connection string here

' Get a typed table to run queries.
Dim Orders As Table(Of Order) = db.GetTable(Of Order)()

' Query the database for the row to be updated.
Dim ordQuery = _
From ord In Orders _
Where ord.OrderID = 11000 _
Select ord

' Execute the query, and change the column values
' you want to change.
For Each ord As Order In ordQuery
ord.ShipName = "Mariner"
ord.ShipVia = 2
' Insert any additional changes to column values.
Next

' Submit the changes to the database.
db.SubmitChanges()


Of course you would change the code to suit your needs but with this
example, you would have to create an Order class and set up the
mapping to the database, but that is a one time setup. Linq makes the
code much more readable. I can't say if this will solve your
performance hiccup though. Using LINQ does not guarantee high
performance and, indeed, if you don't construct your LINQ queries
properly, you could degrade performance. but often, readability and
maintainability are preferred over a minute performance degradation.
Your mileage may vary, of course.

See this for more information:

http://msdn2.microsoft.com/en-us/library/bb399408.aspx

Chris

 

[William Vaughn]

The DataReader provides only the pipe, not the bucket. That is, it's just a
stream of data that must often be stored locally. Some developers think they
can out-write the coders at MS who implemented the DataTable Load method to
suck the data out of the DataReader--most fail in the attempt. Others use
the DataReader to bring a mountain of rows to the client to extract the
gold. The best solution in this case is to setup a ore processor at the
quarry--not in Denver 100 miles away only to ship it back. I recommend doing
this processing on the client using TSQL if possible or CLR procedures if
not. THIS is invariably faster.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________