Database access spped issue

[John]

Hi

I have a terrible scenario. I had an ms access front end/back end app that
searched on partial strings. I have coded the app in .net (access backend)
and am using the below code to search on partial strings;.

daCompanyLocate.SelectCommand = selCompanyLocate
selCompanyLocate.CommandText = "SELECT Company, [Company 2] as Parent, Type,
Tel, [Address 1] as Address, [Post Code], Town, ID " & _
"FROM Clients " & _
"ORDER BY Company"

ds.CompanyLocate.Clear()
daCompanyLocate.Fill(ds.CompanyLocate)

dv = New DataView(ds.CompanyLocate)
dv.RowFilter = "Company like '*" & Me.txtLocate.Text & "*' OR [Parent] like
'*" & Me.txtLocate.Text & "*'"

grdLocate.DataSource = dv

Unfortunately client is not impressed with the speed of this ado.net/vb.net
version compared to the same feature in access. Could someone please advise
me if I can speed this up somehow?

Thanks

Regards

 

[gllincoln]

Hi John,

Just a thought - how big is the recordset of Company and [Company 2]? Is
this a frequent flier query?
If you populated a local hash table with the two fields and the primary
key - you could do a very fast 'IN RAM' query of that array, and go fetch
the records that matched the primary key (or keys). If this is doable, it
would be faster than dragging the whole recordset over; and a second pass
done right after, would be nearly instantaneous.

Failing that - you might want to index the criteria fields - if they aren't
already.

Hope this helps,

 

[Guest]

I have a terrible scenario. I had an ms access front end/back end app
that searched on partial strings. I have coded the app in .net (access
backend) and am using the below code to search on partial strings;.

daCompanyLocate.SelectCommand = selCompanyLocate
selCompanyLocate.CommandText = "SELECT Company, [Company 2] as Parent,
Type, Tel, [Address 1] as Address, [Post Code], Town, ID " & _
"FROM Clients
" & _ "ORDER
BY Company"

Are you pulling all the data back? You should only pull a small batch of
records at a time.

Also look at using a datareader instead of a dataset/dataview.

Lastly, check your table indexes and/or table design.

 

[Mr. Arnold]

Hi

I have a terrible scenario. I had an ms access front end/back end app that
searched on partial strings. I have coded the app in .net (access backend)
and am using the below code to search on partial strings;.

daCompanyLocate.SelectCommand = selCompanyLocate
selCompanyLocate.CommandText = "SELECT Company, [Company 2] as Parent,
Type, Tel, [Address 1] as Address, [Post Code], Town, ID " & _
"FROM Clients " &
_
"ORDER BY Company"

ds.CompanyLocate.Clear()
daCompanyLocate.Fill(ds.CompanyLocate)

dv = New DataView(ds.CompanyLocate)
dv.RowFilter = "Company like '*" & Me.txtLocate.Text & "*' OR [Parent]
like '*" & Me.txtLocate.Text & "*'"

grdLocate.DataSource = dv

Unfortunately client is not impressed with the speed of this
ado.net/vb.net version compared to the same feature in access. Could
someone please advise me if I can speed this up somehow?

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

You give the illusion of speed by not trying to load all the records at one
time. You work with subsets of data and load the subset.

You pass parms to the database and let the database do things like
filtering, bringing back a subset.

 

[Marcia Bunn]

I have a terrible scenario. I had an ms access front end/back end app
that searched on partial strings. I have coded the app in .net (access
backend) and am using the below code to search on partial strings;.

daCompanyLocate.SelectCommand = selCompanyLocate
selCompanyLocate.CommandText = "SELECT Company, [Company 2] as Parent,
Type, Tel, [Address 1] as Address, [Post Code], Town, ID " & _
"FROM Clients
" & _ "ORDER
BY Company"

Are you pulling all the data back? You should only pull a small batch of
records at a time.

Also look at using a datareader instead of a dataset/dataview.

Lastly, check your table indexes and/or table design.